merge

7 files changed, 110 insertions, 20 deletions

diff --git a/mysql-test/include/check-testcase.test b/mysql-test/include/check-testcase.test
index 5bdbf6cd072..bf70b66e5df 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 --skip-lock-tables --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;
 
 
 
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index fd9cf69907c..e0a6c44eb01 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1453,4 +1453,27 @@ LIMIT 1)
 1
 DROP TABLE derived1;
 DROP TABLE D;
+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/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/func_group.test b/mysql-test/t/func_group.test
index 4eedd433d34..be4e9c32686 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -973,5 +973,34 @@ GROUP BY int_nokey LIMIT 1;
 DROP TABLE derived1;
 DROP TABLE D;
 
+#
+# 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/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
@@ -15,6 +15,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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7e37bd7c67d..b1f74d26db3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -402,11 +402,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);