From b1375104b3f83da05fe20a247a3a304890ae34da Mon Sep 17 00:00:00 2001
From: "mhansson/martin@linux-st28.site" <>
Date: Fri, 11 May 2007 16:05:20 +0300
Subject: bug#28273: GROUP_CONCAT and ORDER BY: No warning when result gets
 truncated.

When using GROUP_CONCAT with ORDER BY, a tree is used for the sorting, as
opposed to normal nested loops join used when there is no ORDER BY.

The tree traversal that generates the result counts the lines that have been
cut down. (as they get cut down to the field's max_size)
But the check of that count was before the tree traversal, so no
warning was generated if the output is truncated.

Fixed by moving the check to after the tree traversal.
---
 mysql-test/r/func_gconcat.result | 26 ++++++++++++++++++++++++++
 mysql-test/t/func_gconcat.test   | 14 ++++++++++++++
 sql/item_sum.cc                  |  9 ++++-----
 3 files changed, 44 insertions(+), 5 deletions(-)

diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 71419b5b2c3..20df776ec1b 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -737,4 +737,30 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
 GROUP_CONCAT(DISTINCT UCASE(b))
 ONE.1,TWO.2,ONE.3
 DROP TABLE t1;
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
+( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+GROUP_CONCAT( a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
+GROUP_CONCAT( DISTINCT a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+GROUP_CONCAT( a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
+GROUP_CONCAT( DISTINCT a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 0dd82864520..7771f216f69 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -507,4 +507,18 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
 SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
 DROP TABLE t1;
 
+#
+# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
+#
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
+                      ( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;  
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;          
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; 
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 4579ecd48ae..d6b31d43389 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -3339,6 +3339,10 @@ String* Item_func_group_concat::val_str(String* str)
   DBUG_ASSERT(fixed == 1);
   if (null_value)
     return 0;
+  if (!result.length() && tree)
+    /* Tree is used for sorting as in ORDER BY */
+    tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
+              left_root_right);
   if (count_cut_values && !warning)
   {
     /*
@@ -3350,11 +3354,6 @@ String* Item_func_group_concat::val_str(String* str)
                           ER_CUT_VALUE_GROUP_CONCAT,
                           ER(ER_CUT_VALUE_GROUP_CONCAT));
   }
-  if (result.length())
-    return &result;
-  if (tree)
-    tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
-              left_root_right);
   return &result;
 }
 
-- 
cgit v1.2.1


From d71043b8892ffa6ac1fc66b1ab7e082bcb234bab Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Mon, 14 May 2007 16:41:09 -0700
Subject: Fixed bug #27937: crash for the the second execution of a prepared
 statement from a UNION query with ORDER BY an expression containing RAND().
 The crash happened because the global order by list in the union query was
 not re-initialized for execution. (Local order by lists were re-initialized
 though).

---
 mysql-test/r/ps.result | 22 ++++++++++++++++++++++
 mysql-test/t/ps.test   | 18 ++++++++++++++++++
 sql/sql_union.cc       |  5 +++++
 3 files changed, 45 insertions(+)

diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index d8a75737efc..49e4bf2f318 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1087,4 +1087,26 @@ t2	CREATE TABLE `t2` (
 drop database mysqltest;
 deallocate prepare stmt1;
 deallocate prepare stmt2;
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+PREPARE st1 FROM
+'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
 End of 4.1 tests.
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index a0133897f50..5e5dcc36b19 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -1128,4 +1128,22 @@ drop database mysqltest;
 deallocate prepare stmt1;
 deallocate prepare stmt2;
 #
+
+#
+# Bug #27937: crash on the second execution for prepared statement 
+#             from UNION with ORDER BY an expression containing RAND()
+#
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+
+PREPARE st1 FROM
+  '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+
+EXECUTE st1;
+EXECUTE st1;
+
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
+
 --echo End of 4.1 tests.
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index f3f814831f5..615d060a5a8 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -135,6 +135,11 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd)
   fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
 					   (byte **)
 					   &result_table_list.next);
+  for (ORDER *order= (ORDER *) global_parameters->order_list.first;
+       order;
+       order= order->next)
+    order->item= &order->item_ptr;
+
   return options_tmp;
 }
 
-- 
cgit v1.2.1


From 22dfb9f0f7e0ad3d6dfbd4b328c4f49b6e76eb6d Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Mon, 14 May 2007 18:26:00 -0700
Subject: Post-merge fix.

---
 mysql-test/r/ps.result | 22 ++++++++++++++++++++++
 mysql-test/t/ps.test   | 18 ++++++++++++++++++
 2 files changed, 40 insertions(+)

diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 20bff6bda1c..662386f468a 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1114,6 +1114,28 @@ execute stmt;
 show create table t1;
 drop table t1;
 deallocate prepare stmt;
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+PREPARE st1 FROM
+'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
 End of 4.1 tests.
 create table t1 (a varchar(20));
 insert into t1 values ('foo');
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 3fbcf84a1f9..d5290f4ee5c 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -1194,6 +1194,24 @@ show create table t1;
 --enable_warnings
 drop table t1;
 deallocate prepare stmt;
+#
+
+#
+# Bug #27937: crash on the second execution for prepared statement 
+#             from UNION with ORDER BY an expression containing RAND()
+#
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+
+PREPARE st1 FROM
+  '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+
+EXECUTE st1;
+EXECUTE st1;
+
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
 
 --echo End of 4.1 tests.
 
-- 
cgit v1.2.1


From 268be068747255a34aaf84668ff1c00f499da8f6 Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Mon, 14 May 2007 23:55:18 -0700
Subject: Fixed bug #28272: crash that occurs when running an EXPLAIN command
 for a query over an empty table right after its creation. The crash is the
 result of an attempt made by JOIN::optimize to evaluate the WHERE condition
 when no records have been actually read. The added test case can reproduce
 the crash only with InnoDB tables and only with 5.0.x.

---
 mysql-test/r/innodb_mysql.result | 21 +++++++++++++++++++++
 mysql-test/t/innodb_mysql.test   | 25 +++++++++++++++++++++++++
 sql/sql_select.cc                |  6 ++++++
 3 files changed, 52 insertions(+)

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 12c8b414d2d..029f3768f9b 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -161,4 +161,25 @@ SELECT * FROM t1;
 c1	cnt
 1a	2
 DROP TABLE t1;
+CREATE TABLE t1 (
+a1 decimal(10,0) DEFAULT NULL,
+a2 blob,
+a3 time DEFAULT NULL,
+a4 blob,
+a5 char(175) DEFAULT NULL,
+a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+a7 tinyblob,
+INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
 End of 4.1 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 0973385dc5b..b5e7fc6e405 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -191,4 +191,29 @@ INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
 SELECT * FROM t1;
 DROP TABLE t1;
 
+#
+# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table
+#
+
+CREATE TABLE t1 (
+  a1 decimal(10,0) DEFAULT NULL,
+  a2 blob,
+  a3 time DEFAULT NULL,
+  a4 blob,
+  a5 char(175) DEFAULT NULL,
+  a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  a7 tinyblob,
+  INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+DROP TABLE t1;
+
 --echo End of 4.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 656d1b5639a..8b5664a7f96 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -634,6 +634,12 @@ JOIN::optimize()
     DBUG_PRINT("error",("Error: make_select() failed"));
     DBUG_RETURN(1);
   }
+  if (conds &&!outer_join && const_table_map != found_const_table_map && 
+      (select_options & SELECT_DESCRIBE) &&
+      select_lex->master_unit() == &thd->lex->unit) // upper level SELECT
+  {
+    conds=new Item_int((longlong) 0,1);	// Always false
+  }
   if (make_join_select(this, select, conds))
   {
     zero_result_cause=
-- 
cgit v1.2.1


From 5bc137ff17411255590f6e8b86fb9ce4e80b81de Mon Sep 17 00:00:00 2001
From: "mhansson/martin@linux-st28.site" <>
Date: Tue, 15 May 2007 15:29:12 +0300
Subject: Bug#27573: MIN() on an indexed column which is always NULL sets
 _other_ results to NULL

For queries of the form SELECT MIN(key_part_k) FROM t1
WHERE key_part_1 = const and ... and key_part_k-1 = const,
the opt_sum_query optimization tries to
use an index to substitute MIN/MAX functions with their values according
to the following rules:
1) Insert the minimum non-null values where the WHERE clause still matches, or
3) A row of nulls

However, the correct semantics requires that there is a third case 2)
such that a NULL value is substituted if there are only NULL values for
key_part_k.

The patch modifies opt_sum_query() to handle this missing case.
---
 mysql-test/r/bdb_notembedded.result | 35 +++++++++++++++
 mysql-test/r/func_group.result      | 47 ++++++++++++++++++++
 mysql-test/t/bdb_notembedded.test   | 38 ++++++++++++++++
 mysql-test/t/func_group.test        | 33 ++++++++++++++
 sql/opt_sum.cc                      | 86 ++++++++++++++++++++++++++++++++-----
 5 files changed, 229 insertions(+), 10 deletions(-)
 create mode 100644 mysql-test/r/bdb_notembedded.result
 create mode 100644 mysql-test/t/bdb_notembedded.test

diff --git a/mysql-test/r/bdb_notembedded.result b/mysql-test/r/bdb_notembedded.result
new file mode 100644
index 00000000000..14cb5fad915
--- /dev/null
+++ b/mysql-test/r/bdb_notembedded.result
@@ -0,0 +1,35 @@
+set autocommit=1;
+reset master;
+create table bug16206 (a int);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+f	n	Format_desc	1	n	Server ver: VERSION, Binlog ver: 4
+f	n	Query	1	n	use `test`; create table bug16206 (a int)
+f	n	Query	1	n	use `test`; insert into bug16206 values(1)
+f	n	Query	1	n	use `test`; insert into bug16206 values(2)
+drop table bug16206;
+reset master;
+create table bug16206 (a int) engine=         bdb;
+insert into bug16206 values(0);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+insert into bug16206 values(3);
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+f	n	Format_desc	1	n	Server ver: VERSION, Binlog ver: 4
+f	n	Query	1	n	use `test`; create table bug16206 (a int) engine=         bdb
+f	n	Query	1	n	use `test`; insert into bug16206 values(0)
+f	n	Query	1	n	use `test`; insert into bug16206 values(1)
+f	n	Query	1	n	use `test`; BEGIN
+f	n	Query	1	n	use `test`; insert into bug16206 values(2)
+f	n	Query	1	n	use `test`; COMMIT
+f	n	Query	1	n	use `test`; insert into bug16206 values(3)
+drop table bug16206;
+set autocommit=0;
+End of 5.0 tests
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index e421da1c462..d3e5d586973 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1321,4 +1321,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
 a	average
 1	32768.5000
 DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT MIN(a), MIN(b) FROM t1;
+MIN(a)	MIN(b)
+NULL	1
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	a	a	5	const	2	Using where
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b)	MIN(c)
+3	2
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+MIN(a)	MIN(b)
+2	NULL
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+MIN(a)	MIN(b)
+2	NULL
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+MIN(b)	min(c)
+NULL	2
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+MIN(a)	MIN(b)
+1	1
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+MIN(a)	MIN(b)
+1	2
+DROP TABLE t1, t2, t3, t4, t5;
 End of 5.0 tests
diff --git a/mysql-test/t/bdb_notembedded.test b/mysql-test/t/bdb_notembedded.test
new file mode 100644
index 00000000000..24e64ebbfb2
--- /dev/null
+++ b/mysql-test/t/bdb_notembedded.test
@@ -0,0 +1,38 @@
+-- source include/not_embedded.inc
+-- source include/have_bdb.inc
+
+#
+# Bug #16206: Superfluous COMMIT event in binlog when updating BDB in autocommit mode
+#
+set autocommit=1;
+
+let $VERSION=`select version()`;
+
+reset master;
+create table bug16206 (a int);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+--replace_result $VERSION VERSION
+--replace_column 1 f 2 n 5 n
+show binlog events;
+drop table bug16206;
+
+reset master;
+create table bug16206 (a int) engine=         bdb;
+insert into bug16206 values(0);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+insert into bug16206 values(3);
+--replace_result $VERSION VERSION
+--replace_column 1 f 2 n 5 n
+show binlog events;
+drop table bug16206;
+
+set autocommit=0;
+
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index b102148472a..5ed017e2f74 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -817,5 +817,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
 
 DROP TABLE t1;
 
+#
+# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ 
+# results to NULL
+#
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+SELECT MIN(a), MIN(b) FROM t1;
+
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+
+CREATE TABLE t5( a INT, b INT, KEY( a, b) ); 
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+
+DROP TABLE t1, t2, t3, t4, t5;
+
 ###
 --echo End of 5.0 tests
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 9222e15ff91..b9de54dbf5c 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -206,12 +206,68 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds)
 
           if (!ref.key_length)
             error= table->file->index_first(table->record[0]);
-          else
-	    error= table->file->index_read(table->record[0],key_buff,
-					   ref.key_length,
-					   range_fl & NEAR_MIN ?
-					   HA_READ_AFTER_KEY :
-					   HA_READ_KEY_OR_NEXT);
+          else 
+          {
+            /*
+              Use index to replace MIN/MAX functions with their values
+              according to the following rules:
+           
+              1) Insert the minimum non-null values where the WHERE clause still
+                 matches, or
+              2) a NULL value if there are only NULL values for key_part_k.
+              3) Fail, producing a row of nulls
+
+              Implementation: Read the smallest value using the search key. If
+              the interval is open, read the next value after the search
+              key. If read fails, and we're looking for a MIN() value for a
+              nullable column, test if there is an exact match for the key.
+            */
+            if (!(range_fl & NEAR_MIN))
+              /* 
+                 Closed interval: Either The MIN argument is non-nullable, or
+                 we have a >= predicate for the MIN argument.
+              */
+              error= table->file->index_read(table->record[0], ref.key_buff,
+                                             ref.key_length, 
+                                             HA_READ_KEY_OR_NEXT);
+            else
+            {
+              /*
+                Open interval: There are two cases:
+                1) We have only MIN() and the argument column is nullable, or
+                2) there is a > predicate on it, nullability is irrelevant.
+                We need to scan the next bigger record first.
+              */
+              error= table->file->index_read(table->record[0], ref.key_buff, 
+                                             ref.key_length, HA_READ_AFTER_KEY);
+              /* 
+                 If the found record is outside the group formed by the search
+                 prefix, or there is no such record at all, check if all
+                 records in that group have NULL in the MIN argument
+                 column. If that is the case return that NULL.
+
+                 Check if case 1 from above holds. If it does, we should read
+                 the skipped tuple.
+              */
+              if (ref.key_buff[prefix_len] == 1 && 
+                  /* 
+                     Last keypart (i.e. the argument to MIN) is set to NULL by
+                     find_key_for_maxmin only if all other keyparts are bound
+                     to constants in a conjunction of equalities. Hence, we
+                     can detect this by checking only if the last keypart is
+                     NULL.
+                  */                     
+                  (error == HA_ERR_KEY_NOT_FOUND ||
+                   key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
+              {
+                DBUG_ASSERT(item_field->field->real_maybe_null());
+                error= table->file->index_read(table->record[0], ref.key_buff,
+                                               ref.key_length, 
+                                               HA_READ_KEY_EXACT);
+              }
+            }
+          }
+          /* Verify that the read tuple indeed matches the search key */
 	  if (!error && reckey_in_range(0, &ref, item_field->field, 
 			                conds, range_fl, prefix_len))
 	    error= HA_ERR_KEY_NOT_FOUND;
@@ -739,14 +795,24 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
           if (!max_fl && key_part_used == key_part_to_use && part->null_bit)
           {
             /*
-              SELECT MIN(key_part2) FROM t1 WHERE key_part1=const
-              If key_part2 may be NULL, then we want to find the first row
-              that is not null
+              The query is on this form:
+
+              SELECT MIN(key_part_k) 
+              FROM t1 
+              WHERE key_part_1 = const and ... and key_part_k-1 = const
+
+              If key_part_k is nullable, we want to find the first matching row
+              where key_part_k is not null. The key buffer is now {const, ...,
+              NULL}. This will be passed to the handler along with a flag
+              indicating open interval. If a tuple is read that does not match
+              these search criteria, an attempt will be made to read an exact
+              match for the key buffer.
             */
+            /* Set the first byte of key_part_k to 1, that means NULL */
             ref->key_buff[ref->key_length]= 1;
             ref->key_length+= part->store_length;
             *range_fl&= ~NO_MIN_RANGE;
-            *range_fl|= NEAR_MIN;                // > NULL
+            *range_fl|= NEAR_MIN; // Open interval
           }
           /*
             The following test is false when the key in the key tree is
-- 
cgit v1.2.1


From fbb2332628d2db8231bc9c4eb6b495f8657600f6 Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Wed, 16 May 2007 00:30:12 +0400
Subject: Bug#28208: Wrong result of a non-const STRING function with a const
 DATETIME function.

A wrong  condition was used to check that the
Arg_comparator::can_compare_as_dates() function calculated the value of the
string constant. When comparing a non-const STRING function with a constant
DATETIME function it leads to saving an arbitrary value as a cached value of
the DATETIME function.

Now the Arg_comparator::set_cmp_func() function initializes the const_value
variable to the impossible DATETIME value (-1) and this const_value is
cached only if it was changed by the Arg_comparator::can_compare_as_dates()
function.
---
 mysql-test/r/type_datetime.result | 6 ++++++
 mysql-test/t/type_datetime.test   | 9 +++++++++
 sql/item_cmpfunc.cc               | 5 ++---
 3 files changed, 17 insertions(+), 3 deletions(-)

diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index b45ddd597c4..573139bed48 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -346,3 +346,9 @@ call test27759();
 a	b	a_then_b	b_then_a	c_then_a
 2007-04-10	2007-04-11	2007-04-10	2007-04-10	2004-04-09 00:00:00
 drop procedure test27759;
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+left(f1,10) = curdate()
+1
+drop table t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index c111f2f02cf..4c2416000be 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -225,3 +225,12 @@ END;|
 DELIMITER ;|
 call test27759();
 drop procedure test27759;
+
+#
+# Bug#28208: Wrong result of a non-const STRING function with a const
+#            DATETIME function.
+#
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+drop table t1;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 4f0347dd506..9a251312aca 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -661,7 +661,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
                                         Item_result type)
 {
   enum enum_date_cmp_type cmp_type;
-  ulonglong const_value;
+  ulonglong const_value= (ulonglong)-1;
   a= a1;
   b= a2;
 
@@ -674,8 +674,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
     a_cache= 0;
     b_cache= 0;
 
-    if (cmp_type != CMP_DATE_WITH_DATE &&
-        ((*b)->const_item() || (*a)->const_item()))
+    if (const_value != (ulonglong)-1)
     {
       Item_cache_int *cache= new Item_cache_int();
       /* Mark the cache as non-const to prevent re-caching. */
-- 
cgit v1.2.1


From 8c34ae268cc10d57ae592b286a83550d08ad221c Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Wed, 16 May 2007 23:42:10 -0700
Subject: Fixed bug #28337: wrong results for grouping queries with correlated
 subqueries in WHERE conditions. This bug was introduced by the patch for bug
 27321.

---
 mysql-test/r/subselect.result | 30 ++++++++++++++++++++++++++++++
 mysql-test/t/subselect.test   | 24 ++++++++++++++++++++++++
 sql/item.cc                   |  3 ++-
 3 files changed, 56 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 2e82d948edb..92a4a6f3f5a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4041,4 +4041,34 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id	st
+3	FL
+1	GA
+7	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id	st
+1	GA
+3	FL
+7	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id	st
+2	GA
+4	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id	st
+2	GA
+4	FL
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 64de2ada93c..5840e434b64 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2882,4 +2882,28 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 DROP TABLE t1;
 
+#
+# Bug #28377: grouping query with a correlated subquery in WHERE condition
+#  
+
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+  (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+    GROUP BY id;
+
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+    GROUP BY id;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
diff --git a/sql/item.cc b/sql/item.cc
index 30486e7559a..60c5b3b4a8c 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3503,7 +3503,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
           prev_subselect_item->const_item_cache= 0;
           set_field(*from_field);
           if (!last_checked_context->select_lex->having_fix_field &&
-              select->group_list.elements)
+              select->group_list.elements &&
+              (place == SELECT_LIST || place == IN_HAVING))
           {
             Item_outer_ref *rf;
             /*
-- 
cgit v1.2.1


From 0e3c4f67299950e23a3ba4b40defde2b0fc3b911 Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Thu, 17 May 2007 23:09:45 +0400
Subject: Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR
 function is involved.

The Arg_comparator::compare_datetime() comparator caches its arguments if
they are constants i.e. const_item() returns true. The
Item_func_get_user_var::const_item() returns true or false based on
the current query_id and the query_id where the variable was created.
Thus even if a query can change its value its const_item() still will return
true. All this leads to a wrong comparison result when an object of the
Item_func_get_user_var class is involved.

Now the Arg_comparator::can_compare_as_dates() and the
get_datetime_value() functions never cache result of the GET_USER_VAR()
function (the Item_func_get_user_var class).
---
 mysql-test/r/type_datetime.result | 24 ++++++++++++++++++++++++
 mysql-test/t/type_datetime.test   | 12 ++++++++++++
 sql/item_cmpfunc.cc               | 15 +++++++++++++--
 3 files changed, 49 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 573139bed48..48cc54fb3ef 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -352,3 +352,27 @@ select left(f1,10) = curdate() from t1;
 left(f1,10) = curdate()
 1
 drop table t1;
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+Warnings:
+Warning	1292	Incorrect date value: '' for column 'f1' at row 1
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+drop table t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 4c2416000be..cf512aa3649 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -234,3 +234,15 @@ create table t1 (f1 date);
 insert into t1 values (curdate());
 select left(f1,10) = curdate() from t1;
 drop table t1;
+
+#
+# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
+#            is involved.
+#
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+drop table t1;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 9a251312aca..3a79940b571 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -633,7 +633,13 @@ Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
 
   if (cmp_type != CMP_DATE_DFLT)
   {
-    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+    /*
+      Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+      for the current thread but it still may change during the execution.
+    */
+    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() &&
+        (str_arg->type() != Item::FUNC_ITEM ||
+        ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC))
     {
       THD *thd= current_thd;
       ulonglong value;
@@ -780,7 +786,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
       MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
     value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
   }
-  if (item->const_item() && cache_arg)
+  /*
+    Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+    for the current thread but it still may change during the execution.
+  */
+  if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
+      ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
   {
     Item_cache_int *cache= new Item_cache_int();
     /* Mark the cache as non-const to prevent re-caching. */
-- 
cgit v1.2.1