From d795963cba42fefd57a788eb1112bfc4cc13f6d3 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Mon, 24 Nov 2008 17:30:47 +0200 Subject: Bug #39656: Behaviour different for agg functions with & without where - ONLY_FULL_GROUP_BY The check for non-aggregated columns in queries with aggregate function, but without GROUP BY was treating all the parts of the query as if they are in the SELECT list. Fixed by ignoring the non-aggregated fields in the WHERE clause. mysql-test/r/func_group.result: Bug #39656: test case mysql-test/t/func_group.test: Bug #39656: test case sql/sql_select.cc: Bug #39656: ignore the new non-aggregated column refs in a WHERE by saving the state so far and then adding only the new values of the other parts of the bitmask. --- mysql-test/r/func_group.result | 23 +++++++++++++++++++++++ mysql-test/t/func_group.test | 29 +++++++++++++++++++++++++++++ sql/sql_select.cc | 10 ++++++++++ 3 files changed, 62 insertions(+) diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 772e432355b..a7f4c58f4af 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1425,4 +1425,27 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; AVG(a) CAST(AVG(a) AS DECIMAL) 15 15 DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1), (1,2), (1,3); +SET SQL_MODE='ONLY_FULL_GROUP_BY'; +SELECT COUNT(*) FROM t1; +COUNT(*) +3 +SELECT COUNT(*) FROM t1 where a=1; +COUNT(*) +3 +SELECT COUNT(*),a FROM t1; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a; +COUNT(*) +9 +SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) +FROM t1 outr; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT COUNT(*) FROM t1 a JOIN t1 outr +ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a); +COUNT(*) +0 +SET SQL_MODE=default; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index dbe6d3113d5..38779ac1a2f 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -926,5 +926,34 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; DROP TABLE t1; +# +# Bug #39656: Behaviour different for agg functions with & without where - +# ONLY_FULL_GROUP_BY +# + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1), (1,2), (1,3); + +SET SQL_MODE='ONLY_FULL_GROUP_BY'; + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t1 where a=1; + +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*),a FROM t1; + +SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a; + +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) + FROM t1 outr; + +SELECT COUNT(*) FROM t1 a JOIN t1 outr + ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a); + +SET SQL_MODE=default; +DROP TABLE t1; + + ### --echo End of 5.0 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 428d1709f94..2ac33c4e07f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -390,11 +390,21 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, { int res; nesting_map save_allow_sum_func=thd->lex->allow_sum_func ; + /* + Need to save the value, so we can turn off only the new NON_AGG_FIELD + additions coming from the WHERE + */ + uint8 saved_flag= thd->lex->current_select->full_group_by_flag; DBUG_ENTER("setup_without_group"); thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level); res= setup_conds(thd, tables, leaves, conds); + /* it's not wrong to have non-aggregated columns in a WHERE */ + if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) + thd->lex->current_select->full_group_by_flag= saved_flag | + (thd->lex->current_select->full_group_by_flag & ~NON_AGG_FIELD_USED); + thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level; res= res || setup_order(thd, ref_pointer_array, tables, fields, all_fields, order); -- cgit v1.2.1 From d5057740a02cb4d6fbd63ea8e37d40d328c046d4 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Mon, 24 Nov 2008 17:24:03 +0100 Subject: Bug#40954: Crash in MyISAM index code with concurrency test using partitioned tables Problem was usage of read_range_first with an empty key. Solution was to not to give a key if it was empty. mysql-test/r/partition.result: Bug#40954: Crash in MyISAM index code with concurrency test using partitioned tables Updated test result. mysql-test/t/partition.test: Bug#40954: Crash in MyISAM index code with concurrency test using partitioned tables Added test case --- mysql-test/r/partition.result | 12 ++++++++++++ mysql-test/t/partition.test | 15 +++++++++++++++ sql/ha_partition.cc | 3 ++- 3 files changed, 29 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index adb055dd5e5..93684ba05e5 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1,4 +1,16 @@ drop table if exists t1, t2; +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk) +) +/*!50100 PARTITION BY HASH (pk) +PARTITIONS 2 */; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; +pk +DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL, KEY(a)) PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index e8df2b01a94..6a12e4a4d12 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -14,6 +14,21 @@ drop table if exists t1, t2; --enable_warnings +# +# Bug#40954: Crash if range search and order by. +# +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + PRIMARY KEY (pk) +) +/*!50100 PARTITION BY HASH (pk) +PARTITIONS 2 */; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; +DROP TABLE t1; + # # Bug#40494: Crash MYSQL server crashes on range access with partitioning # and order by diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index a1131a99fa4..0c96b06381c 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -4490,7 +4490,8 @@ int ha_partition::handle_ordered_index_scan(uchar *buf, bool reverse_order) This can only read record to table->record[0], as it was set when the table was being opened. We have to memcpy data ourselves. */ - error= file->read_range_first(&m_start_key, end_range, eq_range, TRUE); + error= file->read_range_first(m_start_key.key? &m_start_key: NULL, + end_range, eq_range, TRUE); memcpy(rec_buf_ptr, table->record[0], m_rec_length); reverse_order= FALSE; break; -- cgit v1.2.1 From 2578609322e891f78c9d2807a9533e68c36cef52 Mon Sep 17 00:00:00 2001 From: Patrick Crews Date: Mon, 24 Nov 2008 16:53:32 -0500 Subject: Bug#40866: mysql-test-run's check of tests provides false failures due to timestamp Altering how MTR checks global variable status to exclude timestamp Changed SQL statements to update style. --- mysql-test/include/check-testcase.test | 38 +++++++++++++++++----------------- 1 file changed, 19 insertions(+), 19 deletions(-) diff --git a/mysql-test/include/check-testcase.test b/mysql-test/include/check-testcase.test index 30cb7391f30..a9885f7b0ec 100644 --- a/mysql-test/include/check-testcase.test +++ b/mysql-test/include/check-testcase.test @@ -11,12 +11,12 @@ # # Dump all global variables # -show global variables; +SHOW GLOBAL VARIABLES WHERE variable_name != 'timestamp'; # # Dump all databases # -show databases; +SHOW DATABASES; # # Dump the "test" database, all it's tables and their data @@ -29,23 +29,23 @@ show databases; # --exec $MYSQL_DUMP --skip-comments --no-data mysql use mysql; -select * from columns_priv; -select * from db order by host, db, user; -select * from func; -select * from help_category; -select * from help_keyword; -select * from help_relation; -select * from help_relation; -select * from host; -select * from proc; -select * from procs_priv; -select * from tables_priv; -select * from time_zone; -select * from time_zone_leap_second; -select * from time_zone_name; -select * from time_zone_transition; -select * from time_zone_transition_type; -select * from user; +SELECT * FROM columns_priv; +SELECT * FROM db ORDER BY host, db, user; +SELECT * FROM func; +SELECT * FROM help_category; +SELECT * FROM help_keyword; +SELECT * FROM help_relation; +SELECT * FROM help_relation; +SELECT * FROM host; +SELECT * FROM proc; +SELECT * FROM procs_priv; +SELECT * FROM tables_priv; +SELECT * FROM time_zone; +SELECT * FROM time_zone_leap_second; +SELECT * FROM time_zone_name; +SELECT * FROM time_zone_transition; +SELECT * FROM time_zone_transition_type; +SELECT * FROM user; -- cgit v1.2.1