From 06a302eff231c27cc71d9714d87b34560945fe8e Mon Sep 17 00:00:00 2001
From: unknown <gkodinov/kgeorge@macbook.gmz>
Date: Mon, 14 Aug 2006 15:45:48 +0300
Subject: Bug #21302: Result not properly sorted when using an ORDER BY on a
 second              table in a join  The optimizer removes redundant columns
 in ORDER BY. It is considering redundant every reference to const table
 column, e.g b in : create table t1 (a int, b int, primary key(a)); select 1
 from t1 order by b where a = 1

But it must not remove references to const table columns if the
const table is an outer table because there still can be 2 values :
the const value and NULL. e.g.:
create table t1 (a int, b int, primary key(a));
select t2.b c from t1 left join t1 t2 on (t1.a = t2.a and t2.a = 5)
  order by c;


mysql-test/r/join_outer.result:
  Bug #21302: Result not properly sorted when using an ORDER BY on a second
              table in a join
   - don't remove columns of const tables in ORDER BY if the const table
     is an outer table.
mysql-test/r/order_by.result:
  Bug #21302: Result not properly sorted when using an ORDER BY on a second
              table in a join
   - test case
mysql-test/t/order_by.test:
  Bug #21302: Result not properly sorted when using an ORDER BY on a second
              table in a join
   - test case
sql/sql_select.cc:
  Bug #21302: Result not properly sorted when using an ORDER BY on a second
              table in a join
   - don't remove columns of const tables in ORDER BY if the const table
     is an outer table.
---
 mysql-test/r/join_outer.result |  2 +-
 mysql-test/r/order_by.result   | 37 +++++++++++++++++++++++++++++++++++++
 mysql-test/t/order_by.test     | 32 ++++++++++++++++++++++++++++++++
 3 files changed, 70 insertions(+), 1 deletion(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index eae023813b5..7fc1f8b6489 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -735,7 +735,7 @@ explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
 (t2 s left join t1 m on m.match_id = 1) 
 order by m.match_id desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
 1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 (t2 s left join t1 m on m.match_id = 1) 
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index a36935a583d..69a5509b68c 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -852,3 +852,40 @@ b	a
 20	1
 10	2
 DROP TABLE t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+explain SELECT t1.b as a, t2.b as c FROM 
+t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+SELECT t1.b as a, t2.b as c FROM 
+t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+a	c
+1	NULL
+3	NULL
+2	2
+explain SELECT t1.b as a, t2.b as c FROM 
+t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)  
+ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 SELECT * from t1;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 SELECT * from t1;
+CREATE TABLE t4 LIKE t1;
+INSERT INTO t4 SELECT * from t1;
+INSERT INTO t1 values (0,0),(4,4);
+SELECT t1.*,t2.* FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
+ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
+a	b	a	b
+0	0	NULL	NULL
+4	4	NULL	NULL
+1	1	1	1
+2	2	2	2
+3	3	3	3
+DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 98e542dac95..96b843ee699 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -578,3 +578,35 @@ INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
 DROP TABLE t1;
 
 # End of 4.1 tests
+
+#
+# Bug#21302: Result not properly sorted when using an ORDER BY on a second 
+#             table in a join
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+
+explain SELECT t1.b as a, t2.b as c FROM 
+ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+SELECT t1.b as a, t2.b as c FROM 
+ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+
+# check that it still removes sort of const table
+explain SELECT t1.b as a, t2.b as c FROM 
+ t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)  
+ORDER BY c;
+
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 SELECT * from t1;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 SELECT * from t1;
+CREATE TABLE t4 LIKE t1;
+INSERT INTO t4 SELECT * from t1;
+INSERT INTO t1 values (0,0),(4,4);
+
+SELECT t1.*,t2.* FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
+ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
+
+DROP TABLE t1,t2,t3,t4;
-- 
cgit v1.2.1


From 6675c2c2d348703736d000924d9f2a2c354342fc Mon Sep 17 00:00:00 2001
From: unknown <gkodinov/kgeorge@macbook.gmz>
Date: Mon, 14 Aug 2006 18:19:29 +0300
Subject: Bug #21174: Index degrades sort performance and             
 optimizer does not honor IGNORE INDEX  - Allow an index to be used for
 sorting the table    instead of filesort only if it is not disabled by   
 IGNORE INDEX.

mysql-test/r/group_by.result:
  Bug #21174: Index degrades sort performance and
               optimizer does not honor IGNORE INDEX
   - test case
mysql-test/t/group_by.test:
  Bug #21174: Index degrades sort performance and
               optimizer does not honor IGNORE INDEX
   - test case
---
 mysql-test/r/group_by.result |  9 +++++++++
 mysql-test/t/group_by.test   | 12 ++++++++++++
 2 files changed, 21 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index e5c177503fa..57cb09fe44c 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -821,3 +821,12 @@ a	b	real_b
 68	France	France
 DROP VIEW v1;
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
+EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	4	
+EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+DROP TABLE t1;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index ce1e4e59600..8a514108dc3 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -655,3 +655,15 @@ where t2.b=v1.a GROUP BY t2.b;
 
 DROP VIEW v1;
 DROP TABLE t1,t2;
+
+#
+# Bug #21174: Index degrades sort performance and 
+#             optimizer does not honor IGNORE INDEX
+#
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
+
+EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; 
+EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
+
+DROP TABLE t1;
-- 
cgit v1.2.1


From 3915c3d94c48a96fdc9163d0841ca33159fc3268 Mon Sep 17 00:00:00 2001
From: unknown <gkodinov/kgeorge@macbook.gmz>
Date: Tue, 15 Aug 2006 10:13:17 +0300
Subject: Bug #21159: Optimizer: wrong result after AND with different data
 types Disable const propagation for Item_hex_string. This must be done
 because Item_hex_string->val_int() is not the same as
 (Item_hex_string->val_str() in BINARY column)->val_int(). We cannot simply
 disable the replacement in a particular context ( e.g. <bin_col> = <int_col>
 AND <bin_col> = <hex_string>) since Items don't know the context they are in
 and there are functions like IF (<hex_string>, 'yes', 'no'). Note that this
 will disable some valid cases as well (e.g. : <bin_col> = <hex_string> AND
 <bin_col2> = <bin_col>) but there's no way to distinguish the valid cases
 without having the Item's parent say something like :
 Item->set_context(Item::STRING_RESULT) and have all the Items that contain
 other Items do that consistently.

mysql-test/r/compare.result:
  Bug #21159: Optimizer: wrong result after AND with different data types
   - test case
mysql-test/t/compare.test:
  Bug #21159: Optimizer: wrong result after AND with different data types
   - test case
sql/sql_select.cc:
  Bug #21159: Optimizer: wrong result after AND with different data types
   - disable const propagation for Item_hex_string.
---
 mysql-test/r/compare.result | 7 +++++++
 mysql-test/t/compare.test   | 9 +++++++++
 2 files changed, 16 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index 6f667aabac0..da0ca8ddba1 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -42,3 +42,10 @@ CHAR(31) = ''	'' = CHAR(31)
 SELECT CHAR(30) = '', '' = CHAR(30);
 CHAR(30) = ''	'' = CHAR(30)
 0	0
+create table t1 (a tinyint(1),b binary(1));
+insert into t1 values (0x01,0x01);
+select * from t1 where a=b;
+a	b
+select * from t1 where a=b and b=0x01;
+a	b
+drop table if exists t1;
diff --git a/mysql-test/t/compare.test b/mysql-test/t/compare.test
index a42ba5ac88a..337035a8095 100644
--- a/mysql-test/t/compare.test
+++ b/mysql-test/t/compare.test
@@ -37,3 +37,12 @@ SELECT CHAR(31) = '', '' = CHAR(31);
 SELECT CHAR(30) = '', '' = CHAR(30);
 
 # End of 4.1 tests
+
+#
+#Bug #21159: Optimizer: wrong result after AND with different data types
+#
+create table t1 (a tinyint(1),b binary(1));
+insert into t1 values (0x01,0x01);
+select * from t1 where a=b;
+select * from t1 where a=b and b=0x01;
+drop table if exists t1;
-- 
cgit v1.2.1


From 57745f96ce8b9c096b058e5241aa5e5b11c123c3 Mon Sep 17 00:00:00 2001
From: unknown <gkodinov/kgeorge@rakia.(none)>
Date: Tue, 15 Aug 2006 13:01:04 +0300
Subject: Bug #21302: Result not properly sorted when using an ORDER BY        
     on a second table in a join - undeterministic output of the test case
 removed.

---
 mysql-test/r/order_by.result | 14 +++++++-------
 mysql-test/t/order_by.test   |  2 +-
 2 files changed, 8 insertions(+), 8 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d4165092082..ee3d49ed3e0 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -882,12 +882,12 @@ INSERT INTO t3 SELECT * from t1;
 CREATE TABLE t4 LIKE t1;
 INSERT INTO t4 SELECT * from t1;
 INSERT INTO t1 values (0,0),(4,4);
-SELECT t1.*,t2.* FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
+SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
 ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
-a	b	a	b
-0	0	NULL	NULL
-4	4	NULL	NULL
-1	1	1	1
-2	2	2	2
-3	3	3	3
+b
+NULL
+NULL
+1
+2
+3
 DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 96b843ee699..f4498befa7e 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -606,7 +606,7 @@ CREATE TABLE t4 LIKE t1;
 INSERT INTO t4 SELECT * from t1;
 INSERT INTO t1 values (0,0),(4,4);
 
-SELECT t1.*,t2.* FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
+SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
 ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
 
 DROP TABLE t1,t2,t3,t4;
-- 
cgit v1.2.1


From 2f5ae7c536ae37a408f1e391ef21c743bd9b1b0e Mon Sep 17 00:00:00 2001
From: unknown <gkodinov/kgeorge@macbook.gmz>
Date: Tue, 15 Aug 2006 15:48:49 +0300
Subject: Bug #21302: Result not properly sorted when using an ORDER BY on a
 second table in a join  - undeterminstic tests fixed

mysql-test/r/order_by.result:
  Bug #21302: Result not properly sorted when using an ORDER BY on a second table in a join
   - more undeterminstic tests fixed
mysql-test/t/order_by.test:
  Bug #21302: Result not properly sorted when using an ORDER BY on a second table in a join
   - more undeterminstic tests fixed
---
 mysql-test/r/order_by.result | 10 +++++-----
 mysql-test/t/order_by.test   |  2 +-
 2 files changed, 6 insertions(+), 6 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index ee3d49ed3e0..64653de5e9c 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -862,13 +862,13 @@ ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
-SELECT t1.b as a, t2.b as c FROM 
+SELECT t2.b as c FROM 
 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
 ORDER BY c;
-a	c
-1	NULL
-3	NULL
-2	2
+c
+NULL
+NULL
+2
 explain SELECT t1.b as a, t2.b as c FROM 
 t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)  
 ORDER BY c;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index f4498befa7e..1104c859ab8 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -589,7 +589,7 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
 explain SELECT t1.b as a, t2.b as c FROM 
  t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
 ORDER BY c;
-SELECT t1.b as a, t2.b as c FROM 
+SELECT t2.b as c FROM 
  t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
 ORDER BY c;
 
-- 
cgit v1.2.1


From 9907e970aea28ce186e110084c3a4f2d6ac0c602 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Tue, 15 Aug 2006 20:33:14 +0400
Subject: BUG#21077: Possible crash caused by invalid sequence of handler::*
 calls: The crash was caused by invalid sequence of handler::** calls:  
 ha_smth->index_init();   ha_smth->index_next_same(); (2) (2) is an invalid
 call as it was not preceeded by any 'scan setup' call like index_first() or
 index_read(). The cause was that QUICK_SELECT::reset() didn't "fully reset"
 the quick select- current QUICK_RANGE wasn't forgotten, and quick select
 might attempt to continue reading the range, which would result in the above
 mentioned invalid sequence of handler calls.

5.x versions are not affected by the bug - they already have the missing
"range=NULL" clause.



mysql-test/r/innodb_mysql.result:
  Testcase for BUG#21077
mysql-test/t/innodb_mysql.test:
  Testcase for BUG#21077
sql/opt_range.h:
  BUG#21077: Possible crash caused by invalid sequence of handler::* calls:
   - Make QUICK_SELECT::reset() really reset the quick select
---
 mysql-test/r/innodb_mysql.result | 21 +++++++++++++++++++++
 mysql-test/t/innodb_mysql.test   | 27 +++++++++++++++++++++++++++
 2 files changed, 48 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 920d7aa42ce..ee4c114087d 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -83,3 +83,24 @@ b	a
 3	3
 3	3
 DROP TABLE t1, t2, t3;
+CREATE TABLE `t1` (`id1` INT) ;
+INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
+CREATE TABLE `t2` (
+`id1` INT,
+`id2` INT NOT NULL,
+`id3` INT,
+`id4` INT NOT NULL,
+UNIQUE (`id2`,`id4`),
+KEY (`id1`)
+) ENGINE=InnoDB;
+INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES 
+(1,1,1,0),
+(1,1,2,1),
+(5,1,2,2),
+(6,1,2,3),
+(1,2,2,2),
+(1,2,1,1);
+SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
+id1
+2
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 0b789e1a6d5..a5fe248604f 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -90,3 +90,30 @@ SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
   t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 
   ORDER BY t1.b LIMIT 5;
 DROP TABLE t1, t2, t3;
+
+
+# BUG#21077 (The testcase is not deterministic so correct execution doesn't
+# prove anything) For proof one should track if sequence of ha_innodb::* func
+# calls is correct.
+CREATE TABLE `t1` (`id1` INT) ;
+INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
+
+CREATE TABLE `t2` (
+  `id1` INT,
+  `id2` INT NOT NULL,
+  `id3` INT,
+  `id4` INT NOT NULL,
+  UNIQUE (`id2`,`id4`),
+  KEY (`id1`)
+) ENGINE=InnoDB;
+
+INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES 
+(1,1,1,0),
+(1,1,2,1),
+(5,1,2,2),
+(6,1,2,3),
+(1,2,2,2),
+(1,2,1,1);
+
+SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
+DROP TABLE t1, t2;
-- 
cgit v1.2.1


From c2ef98ad4c81a84bba7f36405f5d6f2384213aa9 Mon Sep 17 00:00:00 2001
From: unknown <evgen@moonbone.local>
Date: Tue, 15 Aug 2006 21:02:55 +0400
Subject: Fixed bug#15950: NOW() optimized away in VIEWs

This bug is a side-effect of bug fix #16377. NOW() is optimized in
BETWEEN to integer constants to speed up query execution. When view is being
created it saves already modified query and thus becomes wrong.

The agg_cmp_type() function now substitutes constant result DATE/TIME functions
for their results only if the current query isn't CREATE VIEW or SHOW CREATE
VIEW.


mysql-test/t/view.test:
  Added a test case for bug#15950: NOW() optimized away in VIEWs
mysql-test/r/view.result:
  Added a test case for bug#15950: NOW() optimized away in VIEWs
sql/item_cmpfunc.cc:
  Fixed bug#15950: NOW() optimized away in VIEWs
  The agg_cmp_type() function now substitutes constant result DATE/TIME functions
  for their results only if the current query isn't CREATE VIEW or SHOW CREATE
  VIEW.
---
 mysql-test/r/view.result | 7 +++++++
 mysql-test/t/view.test   | 9 +++++++++
 2 files changed, 16 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 534065a33b6..5d217755abd 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2850,3 +2850,10 @@ Tables_in_test
 t1
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
+create table t1 (f1 datetime);
+create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
+show create view v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() + interval 1 minute))
+drop view v1;
+drop table t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 5cb85ca6c9b..315a61f2003 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2718,3 +2718,12 @@ DROP TABLE t1;
 --disable_warnings
 DROP VIEW IF EXISTS v1;
 --enable_warnings
+
+#
+# Bug #15950: NOW() optimized away in VIEWs
+#
+create table t1 (f1 datetime);
+create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
+show create view v1;
+drop view v1;
+drop table t1;
-- 
cgit v1.2.1


From 9cf4750ec3cfa97ee839bb84cc25629c5512bab2 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Tue, 15 Aug 2006 21:08:22 +0400
Subject: BUG#21282: Incorrect query results for "t.key NOT IN (<big const
 list>) In fix for BUG#15872, a condition of type "t.key NOT IN (c1, .... cN)"
 where N>1000, was incorrectly converted to   (-inf < X < c_min) OR (c_max <
 X) Now this conversion is removed, we dont produce any range lists for such
 conditions.

mysql-test/r/range.result:
  BUG#21282: Testcase
mysql-test/t/range.test:
  BUG#21282: Testcase
sql/opt_range.cc:
  BUG#21282: Incorrect query results for "t.key NOT IN (<big const list>)
  In fix for BUG#15872, a condition of type "t.key NOT IN (c1, .... cN)"
  where N>1000, was incorrectly converted to
    (-inf < X < c_min) OR (c_max < X)
  Now this conversion is removed, we dont produce any range lists for such
  conditions.
---
 mysql-test/r/range.result | 22 ++++++++++++++++++++++
 mysql-test/t/range.test   | 25 +++++++++++++++++++++++++
 2 files changed, 47 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index a1f03a292c5..14eea4797da 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -838,3 +838,25 @@ select a, hex(filler) from t1 where a not between 'b' and 'b';
 a	hex(filler)
 a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
 drop table t1,t2,t3;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, key(a));
+insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
+set @a="select * from t2 force index (a) where a NOT IN(0";
+select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
+count(*)
+1000
+set @a=concat(@a, ')');
+insert into t2 values (11),(13),(15);
+set @b= concat("explain ", @a);
+prepare stmt1 from @b;
+execute stmt1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	a	a	5	NULL	1003	Using where; Using index
+prepare stmt1 from @a;
+execute stmt1;
+a
+11
+13
+15
+drop table t1, t2;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index d53b05b98b1..57b5ab8f419 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -656,3 +656,28 @@ explain select * from t1 where a not between 'b' and 'b';
 select a, hex(filler) from t1 where a not between 'b' and 'b'; 
 
 drop table t1,t2,t3;
+
+#
+# BUG#21282
+#
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, key(a));
+insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
+
+set @a="select * from t2 force index (a) where a NOT IN(0";
+select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
+set @a=concat(@a, ')');
+
+insert into t2 values (11),(13),(15);
+
+set @b= concat("explain ", @a);
+
+prepare stmt1 from @b;
+execute stmt1;
+
+prepare stmt1 from @a;
+execute stmt1;
+
+drop table t1, t2;
+# End of 5.0 tests
-- 
cgit v1.2.1


From a23d1792bc48c55eb8bee2f3aa947b9599ade190 Mon Sep 17 00:00:00 2001
From: unknown <evgen@sunlight.local>
Date: Tue, 15 Aug 2006 21:45:24 +0400
Subject: Fixed bug#21261: Wrong access rights was required for an insert into
 a view

SELECT right instead of INSERT right was required for an insert into to a view.
This wrong behaviour appeared after the fix for bug #20989. Its intention was
to ask only SELECT right for all tables except the very first for a complex
INSERT query. But that patch has done it in a wrong way and lead to asking
a wrong access right for an insert into a view.

The setup_tables_and_check_access() function now accepts two want_access
parameters. One will be used for the first table and the second for other
tables.


mysql-test/t/view.test:
  Added a test case for bug#21261: Wrong access rights was required for an insert into a view
mysql-test/r/view.result:
  Added a test case for bug#21261: Wrong access rights was required for an insert into a view
sql/sql_update.cc:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  Modified to use updated setup_tables_and_check_access() function.
sql/sql_select.cc:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  Modified to use updated setup_tables_and_check_access() function.
sql/sql_load.cc:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  Modified to use updated setup_tables_and_check_access() function.
sql/sql_insert.cc:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  Modified to use updated setup_tables_and_check_access() function.
sql/sql_delete.cc:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  Modified to use updated setup_tables_and_check_access() function.
sql/sql_base.cc:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  The setup_tables_and_check_access() function now accepts two want_access
  parameters. One will be used for the first table and the second for other
  tables.
sql/mysql_priv.h:
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  The setup_tables_and_check_access() function now accepts two want_access
  parameters.
---
 mysql-test/r/view.result | 19 +++++++++++++++++++
 mysql-test/t/view.test   | 30 ++++++++++++++++++++++++++++++
 2 files changed, 49 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 534065a33b6..2a02ac78752 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2850,3 +2850,22 @@ Tables_in_test
 t1
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
+CREATE DATABASE bug21261DB;
+CREATE TABLE t1 (x INT);
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
+GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost';
+GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost';
+CREATE TABLE t2 (y INT);
+GRANT SELECT ON t2 TO 'user21261'@'localhost';
+INSERT INTO v1 (x) VALUES (5);
+UPDATE v1 SET x=1;
+GRANT SELECT ON v1 TO 'user21261'@'localhost';
+UPDATE v1,t2 SET x=1 WHERE x=y;
+SELECT * FROM t1;
+x
+1
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost';
+DROP USER 'user21261'@'localhost';
+DROP VIEW v1;
+DROP TABLE t1;
+DROP DATABASE bug21261DB;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 5cb85ca6c9b..8a2b170ecb8 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2718,3 +2718,33 @@ DROP TABLE t1;
 --disable_warnings
 DROP VIEW IF EXISTS v1;
 --enable_warnings
+
+#
+# Bug #21261: Wrong access rights was required for an insert to a view
+#
+CREATE DATABASE bug21261DB;
+CONNECT (root,localhost,root,,bug21261DB);
+CONNECTION root;
+
+CREATE TABLE t1 (x INT);
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
+GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost';
+GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost';
+CREATE TABLE t2 (y INT);
+GRANT SELECT ON t2 TO 'user21261'@'localhost';
+
+CONNECT (user21261, localhost, user21261,, bug21261DB);
+CONNECTION user21261;
+INSERT INTO v1 (x) VALUES (5);
+UPDATE v1 SET x=1;
+CONNECTION root;
+GRANT SELECT ON v1 TO 'user21261'@'localhost';
+CONNECTION user21261;
+UPDATE v1,t2 SET x=1 WHERE x=y;
+CONNECTION root;
+SELECT * FROM t1;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost';
+DROP USER 'user21261'@'localhost';
+DROP VIEW v1;
+DROP TABLE t1;
+DROP DATABASE bug21261DB;
-- 
cgit v1.2.1


From c8cafde703bdeb5de636a095f1cf97dc566e80f9 Mon Sep 17 00:00:00 2001
From: unknown <igor@rurik.mysql.com>
Date: Wed, 16 Aug 2006 09:37:19 -0700
Subject: Fixed bug #18165. Made [NOT]BETWEEN predicates SARGable in respect to
 the second and the third arguments.

mysql-test/r/range.result:
  Added a test case to bug #18165.
mysql-test/t/range.test:
  Added a test case to bug #18165.
sql/opt_range.cc:
  Fixed bug #18165.
  Made [NOT]BETWEEN predicates SARGable in respect to the second and
  the third arguments.
  Put in a separate function called get_full_func_mm_tree the functionality
  that builds a conjunction of all SEL_TREEs for a simple predicate of the
  form (f op c), where f was a field and c was a constant, applying different
  equalities f=f' with f' being another field.
---
 mysql-test/r/range.result | 36 ++++++++++++++++++++++++++++++++++++
 mysql-test/t/range.test   | 30 ++++++++++++++++++++++++++++++
 2 files changed, 66 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 14eea4797da..5c2c6e7e965 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -860,3 +860,39 @@ a
 13
 15
 drop table t1, t2;
+CREATE TABLE t1 (
+id int NOT NULL DEFAULT '0',
+b int NOT NULL DEFAULT '0',
+c int NOT NULL DEFAULT '0', 
+INDEX idx1(b,c), INDEX idx2(c));
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+INSERT INTO t1(b,c) VALUES (3,4), (3,4);
+SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+id	b	c
+0	3	4
+0	3	4
+SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+id	b	c
+0	3	4
+0	3	4
+EXPLAIN  SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	3	Using where
+EXPLAIN  SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	3	Using where
+SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+id	b	c
+0	3	4
+0	3	4
+SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+id	b	c
+0	3	4
+0	3	4
+EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	idx1,idx2	idx1,idx2	4,4	NULL	4	Using sort_union(idx1,idx2); Using where
+EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	idx1,idx2	idx1,idx2	4,4	NULL	4	Using sort_union(idx1,idx2); Using where
+DROP TABLE t1;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 57b5ab8f419..776c1a466ca 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -680,4 +680,34 @@ prepare stmt1 from @a;
 execute stmt1;
 
 drop table t1, t2;
+
+#
+# Bug #18165: range access for BETWEEN with a constant for the first argument 
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL DEFAULT '0',
+  b int NOT NULL DEFAULT '0',
+  c int NOT NULL DEFAULT '0', 
+  INDEX idx1(b,c), INDEX idx2(c));
+
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+INSERT INTO t1(b,c) VALUES (3,4), (3,4);
+
+SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+
+EXPLAIN  SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+EXPLAIN  SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+
+SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+
+EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+
+DROP TABLE t1;
+
+
 # End of 5.0 tests
-- 
cgit v1.2.1


From 1cbebc6e18e22d10b4be12634f5c6d2ed72cffca Mon Sep 17 00:00:00 2001
From: unknown <evgen@moonbone.local>
Date: Thu, 17 Aug 2006 18:50:53 +0400
Subject: ndb_condition_pushdown.result:   Corrected test case result after fix
 for bug#18165 view.result, view.test:   Corrected test case for bug#21261

mysql-test/t/view.test:
  Corrected test case for bug#21261
mysql-test/r/view.result:
  Corrected test case for bug#21261
mysql-test/r/ndb_condition_pushdown.result:
  Corrected test case result after fix for bug#18165
---
 mysql-test/r/ndb_condition_pushdown.result | 4 ++--
 mysql-test/r/view.result                   | 2 ++
 mysql-test/t/view.test                     | 2 ++
 3 files changed, 6 insertions(+), 2 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result
index 4e5597a4851..aefbcbf6ede 100644
--- a/mysql-test/r/ndb_condition_pushdown.result
+++ b/mysql-test/r/ndb_condition_pushdown.result
@@ -1307,7 +1307,7 @@ select auto from t1 where
 ('1901-01-01 01:01:01' between date_time and date_time) 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	10	Using where with pushed condition; Using filesort
 select auto from t1 where
 ("aaaa" between string and string) and 
 ("aaaa" between vstring and vstring) and 
@@ -1409,7 +1409,7 @@ select auto from t1 where
 ('1901-01-01 01:01:01' not between date_time and date_time) 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	20	Using where with pushed condition; Using filesort
 select auto from t1 where
 ("aaaa" not between string and string) and 
 ("aaaa" not between vstring and vstring) and 
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index c6bd6b8321b..5b170d056f7 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2851,6 +2851,7 @@ t1
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
 CREATE DATABASE bug21261DB;
+USE bug21261DB;
 CREATE TABLE t1 (x INT);
 CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
 GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost';
@@ -2869,6 +2870,7 @@ DROP USER 'user21261'@'localhost';
 DROP VIEW v1;
 DROP TABLE t1;
 DROP DATABASE bug21261DB;
+USE test;
 create table t1 (f1 datetime);
 create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
 show create view v1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9db60a129a2..23e49588e8b 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2723,6 +2723,7 @@ DROP VIEW IF EXISTS v1;
 # Bug #21261: Wrong access rights was required for an insert to a view
 #
 CREATE DATABASE bug21261DB;
+USE bug21261DB;
 CONNECT (root,localhost,root,,bug21261DB);
 CONNECTION root;
 
@@ -2748,6 +2749,7 @@ DROP USER 'user21261'@'localhost';
 DROP VIEW v1;
 DROP TABLE t1;
 DROP DATABASE bug21261DB;
+USE test;
 
 #
 # Bug #15950: NOW() optimized away in VIEWs
-- 
cgit v1.2.1


From 7a11df8c933581a88ffb3c807db8147fc520ffc2 Mon Sep 17 00:00:00 2001
From: unknown <evgen@moonbone.local>
Date: Mon, 21 Aug 2006 00:23:57 +0400
Subject: Fixed bug#21475: Wrongly applied constant propagation leads to a
 false comparison.

A date can be represented as an int (like 20060101) and as a string (like
"2006.01.01"). When a DATE/TIME field is compared in one SELECT against both
representations the constant propagation mechanism leads to comparison
of DATE as a string and DATE as an int. In this example it compares 2006 and
20060101 integers. Obviously it fails comparison although they represents the
same date.


Now the Item_bool_func2::fix_length_and_dec() function sets the comparison
context for items being compared. I.e. if items compared as strings the
comparison context is STRING.
The constant propagation mechanism now doesn't mix items used in different
comparison contexts. The context check is done in the
Item_field::equal_fields_propagator() and in the change_cond_ref_to_const()
functions.

Also the better fix for bug 21159 is introduced.


mysql-test/t/type_datetime.test:
  Added a test case for bug#21475: Wrongly applied constant propagation leads to a false comparison.
mysql-test/r/type_datetime.result:
  Added a test case for bug#21475: Wrongly applied constant propagation leads to a false comparison.
sql/sql_select.cc:
  Fixed bug#21475: Wrongly applied constant propagation leads to a false comparison.
  The constant propagation mechanism now doesn't mix items used in different
  comparison contexts. The check is done in the change_cond_ref_to_const() function.
sql/item_cmpfunc.cc:
  Fixed bug#21475: Wrongly applied constant propagation leads to a false comparison.
  Now the Item_bool_func2::fix_length_and_dec() function sets the comparison
  context for items being compared.
sql/item.h:
  Fixed bug#21475: Wrongly applied constant propagation leads to a false comparison.
  To the Item class a new field called cmp_context is added.
  It represents the comparison context of an item.
sql/item.cc:
  Fixed bug#21475: Wrongly applied constant propagation leads to a false comparison.
  The constant propagation mechanism now doesn't mix items used in different
  comparison contexts. The context check is done in the
  Item_field::equal_fields_propagator() function.
---
 mysql-test/r/type_datetime.result | 11 +++++++++++
 mysql-test/t/type_datetime.test   | 11 +++++++++++
 2 files changed, 22 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 2addb9c93eb..49e4827cb97 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -168,3 +168,14 @@ dt
 0000-00-00 00:00:00
 0000-00-00 00:00:00
 drop table t1;
+CREATE TABLE t1(a DATETIME NOT NULL);
+INSERT INTO t1 VALUES ('20060606155555');
+SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555");
+a
+2006-06-06 15:55:55
+PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555")';
+EXECUTE s;
+a
+2006-06-06 15:55:55
+DROP PREPARE s;
+DROP TABLE t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 4b6741b4242..cdf73bf6c89 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -114,3 +114,14 @@ select * from t1;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug#21475: Wrongly applied constant propagation leads to a false comparison.
+#
+CREATE TABLE t1(a DATETIME NOT NULL);
+INSERT INTO t1 VALUES ('20060606155555');
+SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555");
+PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555")';
+EXECUTE s;
+DROP PREPARE s;
+DROP TABLE t1;
-- 
cgit v1.2.1


From d2fa8e3a8d3a0a85a7128112bf09a4d4e164a247 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Mon, 21 Aug 2006 14:20:03 +0400
Subject: Fix by Georgi Kodinov: Bug #18744 Test 'join_outer' fails if
 "classic" configuration in 5.0  - moved an InnoDB dependent test to the
 appropriate file

mysql-test/r/innodb_mysql.result:
  Bug #18744 Test 'join_outer' fails if "classic" configuration in 5.0
   - moved an InnoDB dependent test to the appropriate file
mysql-test/r/join_outer.result:
  Bug #18744 Test 'join_outer' fails if "classic" configuration in 5.0
   - moved an InnoDB dependent test to the appropriate file
mysql-test/t/innodb_mysql.test:
  Bug #18744 Test 'join_outer' fails if "classic" configuration in 5.0
   - moved an InnoDB dependent test to the appropriate file
mysql-test/t/join_outer.test:
  Bug #18744 Test 'join_outer' fails if "classic" configuration in 5.0
   - moved an InnoDB dependent test to the appropriate file
---
 mysql-test/r/innodb_mysql.result | 19 +++++++++++++++++++
 mysql-test/r/join_outer.result   | 19 -------------------
 mysql-test/t/innodb_mysql.test   | 20 ++++++++++++++++++++
 mysql-test/t/join_outer.test     | 21 ---------------------
 4 files changed, 39 insertions(+), 40 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 74883b8ccb3..e7d097a1d2f 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -318,3 +318,22 @@ explain select distinct f1, f2 from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	PRIMARY	5	NULL	3	Using index for group-by; Using temporary
 drop table t1;
+CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
+INDEX (name)) ENGINE=InnoDB;
+CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
+FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
+INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
+EXPLAIN
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
+WHERE t1.name LIKE 'A%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
+1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
+EXPLAIN
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
+WHERE t1.name LIKE 'A%' OR FALSE;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 858dd6b2632..2d9652ff0e3 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1137,25 +1137,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	Using where
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
 DROP TABLE t1,t2;
-CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
-INDEX (name)) ENGINE=InnoDB;
-CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
-FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
-INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
-EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
-WHERE t1.name LIKE 'A%';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
-1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
-EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
-WHERE t1.name LIKE 'A%' OR FALSE;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
-DROP TABLE t1,t2;
 DROP VIEW IF EXISTS v1,v2;
 DROP TABLE IF EXISTS t1,t2;
 CREATE TABLE t1 (a int);
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index a2d1edbd4a1..59dbe5e96d4 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -282,3 +282,23 @@ explain select distinct f1 a, f1 b from t1;
 explain select distinct f1, f2 from t1;
 drop table t1;
 
+#
+# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
+# 
+
+CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
+                 INDEX (name)) ENGINE=InnoDB;
+CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
+                 FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
+INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
+
+EXPLAIN
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
+  WHERE t1.name LIKE 'A%';
+
+EXPLAIN
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
+  WHERE t1.name LIKE 'A%' OR FALSE;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index dc4e240750c..20462f2ca3f 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -759,27 +759,6 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b
 
 DROP TABLE t1,t2;
 
-#
-# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
-# 
-
-CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
-                 INDEX (name)) ENGINE=InnoDB;
-CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
-                 FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
-INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
-
-EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
-  WHERE t1.name LIKE 'A%';
-
-EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
-  WHERE t1.name LIKE 'A%' OR FALSE;
-
-DROP TABLE t1,t2;
-
 #
 # Bug 19396: LEFT OUTER JOIN over views in curly braces 
 # 
-- 
cgit v1.2.1


From 959363b9568193ecec9a46a51701420c45316c01 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Mon, 21 Aug 2006 17:03:07 +0400
Subject: BUG#21277: Wrong results in index_merge queries: Remove the code that
 cleared "read fields set" for merged scans. That code was based on assumption
 that "We're going to just read rowids", while actually QUICK_RANGE_SELECT
 code would also need key part values to check that retrieved record(s) fall
 within the scanned intervals.

mysql-test/r/index_merge_innodb.result:
  BUG#21277: Testcase
mysql-test/t/index_merge_innodb.test:
  BUG#21277: Testcase
---
 mysql-test/r/index_merge_innodb.result | 40 ++++++++++++++++++++++++++++++++++
 mysql-test/t/index_merge_innodb.test   | 27 +++++++++++++++++++++++
 2 files changed, 67 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index ae25cc28101..5af3e291c87 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -282,3 +282,43 @@ kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND  kp5 = 'R        ';
 COUNT(*)
 1
 drop table t1;
+create table t1
+(
+key1 int not null, 
+key2 int not null default 0,
+key3 int not null default 0
+);
+insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
+set @d=8;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+alter table t1 add index i2(key2);
+alter table t1 add index i3(key3);
+update t1 set key2=key1,key3=key1;
+explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	i2,i3	i3,i2	4,4	NULL	11	Using sort_union(i3,i2); Using where
+select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+key1	key2	key3
+31	31	31
+32	32	32
+33	33	33
+34	34	34
+35	35	35
+36	36	36
+37	37	37
+38	38	38
+39	39	39
+drop table t1;
diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test
index 25f4e0b4e65..7e6c524e811 100644
--- a/mysql-test/t/index_merge_innodb.test
+++ b/mysql-test/t/index_merge_innodb.test
@@ -299,4 +299,31 @@ SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
 
 drop table t1;
 
+# BUG#21277: Index Merge/sort_union: wrong query results
+create table t1
+(
+  key1 int not null, 
+  key2 int not null default 0,
+  key3 int not null default 0
+);
+
+insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
+
+let $1=7;
+set @d=8;
+while ($1)
+{
+  eval insert into t1 (key1) select key1+@d from t1;
+  eval set @d=@d*2;
+  dec $1;
+}
+
+alter table t1 add index i2(key2);
+alter table t1 add index i3(key3);
+update t1 set key2=key1,key3=key1;
+
+# to test the bug, the following must use "sort_union":
+explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+drop table t1;
 
-- 
cgit v1.2.1


From 97e0f59b3b63f99db090da697b0c1b52e25b4653 Mon Sep 17 00:00:00 2001
From: unknown <tnurnberg@salvation.intern.azundris.com>
Date: Tue, 22 Aug 2006 14:29:48 +0200
Subject: Bug#20411: "GRANT ... REQUIRE ISSUER nnn AND SUBJECT mmm" fails to
 require both

when X.509 subject was required for a connect, we tested whether it was the right
one, but did not refuse the connexion if not. fixed.

(corrected CS now --replace_results socket-path)


mysql-test/r/openssl_1.result:
  Bug#20411: "GRANT ... REQUIRE ISSUER nnn AND SUBJECT mmm" fails to require both

  test not only whether we can connect with a correct X.509 subject when one is
  required, but also assure that we can't without one.
mysql-test/t/openssl_1.test:
  Bug#20411: "GRANT ... REQUIRE ISSUER nnn AND SUBJECT mmm" fails to require both

  test not only whether we can connect with a correct X.509 subject when one is
  required, but also assure that we can't without one.
sql/sql_acl.cc:
  Bug#20411: "GRANT ... REQUIRE ISSUER nnn AND SUBJECT mmm" fails to require both

  actually refuse connexion if X.509 is required, but does not match. kudos to Al Smith.
---
 mysql-test/r/openssl_1.result |  9 ++++++---
 mysql-test/t/openssl_1.test   | 10 +++++++---
 2 files changed, 13 insertions(+), 6 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result
index 1fcfb11525e..8f9fd50eced 100644
--- a/mysql-test/r/openssl_1.result
+++ b/mysql-test/r/openssl_1.result
@@ -3,9 +3,12 @@ create table t1(f1 int);
 insert into t1 values (5);
 grant select on test.* to ssl_user1@localhost require SSL;
 grant select on test.* to ssl_user2@localhost require cipher "DHE-RSA-AES256-SHA";
-grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/L=Uppsala/O=MySQL AB/CN=MySQL Client/emailAddress=abstract.mysql.developer@mysql.com";
-grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/L=Uppsala/O=MySQL AB/CN=MySQL Client/emailAddress=abstract.mysql.developer@mysql.com" ISSUER "/C=SE/L=Uppsala/O=MySQL AB/CN=Abstract MySQL Developer/emailAddress=abstract.mysql.developer@mysql.com";
+grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
+grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
+grant select on test.* to ssl_user5@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "xxx";
 flush privileges;
+connect(localhost,ssl_user5,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR 28000: Access denied for user 'ssl_user5'@'localhost' (using password: NO)
 SHOW STATUS LIKE 'Ssl_cipher';
 Variable_name	Value
 Ssl_cipher	DHE-RSA-AES256-SHA
@@ -39,7 +42,7 @@ f1
 delete from t1;
 ERROR 42000: DELETE command denied to user 'ssl_user4'@'localhost' for table 't1'
 drop user ssl_user1@localhost, ssl_user2@localhost,
-ssl_user3@localhost, ssl_user4@localhost;
+ssl_user3@localhost, ssl_user4@localhost, ssl_user5@localhost;
 drop table t1;
 mysqltest: Could not open connection 'default': 2026 SSL connection error
 mysqltest: Could not open connection 'default': 2026 SSL connection error
diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test
index afee381f5b7..49f8fc4d7d4 100644
--- a/mysql-test/t/openssl_1.test
+++ b/mysql-test/t/openssl_1.test
@@ -10,14 +10,18 @@ insert into t1 values (5);
 
 grant select on test.* to ssl_user1@localhost require SSL;
 grant select on test.* to ssl_user2@localhost require cipher "DHE-RSA-AES256-SHA";
-grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/L=Uppsala/O=MySQL AB/CN=MySQL Client/emailAddress=abstract.mysql.developer@mysql.com";
-grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/L=Uppsala/O=MySQL AB/CN=MySQL Client/emailAddress=abstract.mysql.developer@mysql.com" ISSUER "/C=SE/L=Uppsala/O=MySQL AB/CN=Abstract MySQL Developer/emailAddress=abstract.mysql.developer@mysql.com";
+grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
+grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
+grant select on test.* to ssl_user5@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "xxx";
 flush privileges;
 
 connect (con1,localhost,ssl_user1,,,,,SSL);
 connect (con2,localhost,ssl_user2,,,,,SSL);
 connect (con3,localhost,ssl_user3,,,,,SSL);
 connect (con4,localhost,ssl_user4,,,,,SSL);
+--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
+--error 1045
+connect (con5,localhost,ssl_user5,,,,,SSL);
 
 connection con1;
 # Check ssl turned on
@@ -49,7 +53,7 @@ delete from t1;
 
 connection default;
 drop user ssl_user1@localhost, ssl_user2@localhost,
-ssl_user3@localhost, ssl_user4@localhost;
+ssl_user3@localhost, ssl_user4@localhost, ssl_user5@localhost;
 
 drop table t1;
 
-- 
cgit v1.2.1


From c623e54fb8a939eb877770d875ce184fec543d37 Mon Sep 17 00:00:00 2001
From: unknown <evgen@sunlight.local>
Date: Tue, 22 Aug 2006 17:37:41 +0400
Subject: Fixed bug#16861: User defined variable can have a wrong value if a
 tmp table was used.

Sorting by RAND() uses a temporary table in order to get a correct results.
User defined variable was set during filling the temporary table and later
on it is substituted for its value from the temporary table. Due to this
it contains the last value stored in the temporary table.

Now if the result_field is set for the Item_func_set_user_var object it
updates variable from the result_field value when being sent to a client.

The Item_func_set_user_var::check() now accepts a use_result_field
parameter. Depending on its value the result_field or the args[0] is used
to get current value.


mysql-test/r/user_var.result:
  Added a test case for bug#16861: User defined variable can have a wrong value if a tmp table was used.
mysql-test/t/user_var.test:
  Added a test case for bug#16861: User defined variable can have a wrong value if a tmp table was used.
sql/item_func.cc:
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was used.
  Now if the result_field is set for the Item_func_set_user_var object it
  updates variable from the result_field value when being sent to a client.

  The Item_func_set_user_var::check() now accepts a use_result_field
  parameter. Depending on its value the result_field or the args[0] is used
  to get current value.
sql/item_func.h:
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was used.
  Added a new SUSERVAR_FUNC function type.
  Updated the Item_func_set_user_var::check() function declaration.
  Added the Item_func_set_user_var::send() member function.
sql/set_var.cc:
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was used.
  Modified to use updated Item_func_set_user_var::check() function.
sql/sql_class.cc:
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was used.
  Modified to use updated Item_func_set_user_var::check() function.
sql/sql_select.cc:
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was used.
  Now an Item_func_set_user_var object isn't substituted for an Item_field object after filling a temporary table.
---
 mysql-test/r/user_var.result |  9 +++++++++
 mysql-test/t/user_var.test   | 10 ++++++++++
 2 files changed, 19 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 3a197cd2a47..1664a907c99 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -292,3 +292,12 @@ SELECT @a;
 @a
 18446744071710965857
 drop table bigfailure;
+create table t1(f1 int, f2 int);
+insert into t1 values (1,2),(2,3),(3,1);
+select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+@var:=f2
+3
+select @var;
+@var
+3
+drop table t1;
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index 58c52b59a5a..b2a9728de00 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -202,3 +202,13 @@ SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailur
 SELECT @a;
 
 drop table bigfailure;
+
+#
+# Bug#16861: User defined variable can have a wrong value if a tmp table was
+#            used.
+#
+create table t1(f1 int, f2 int);
+insert into t1 values (1,2),(2,3),(3,1);
+select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+select @var;
+drop table t1;
-- 
cgit v1.2.1


From d0394c70708055af5a5914154b1692ba517115f6 Mon Sep 17 00:00:00 2001
From: unknown <evgen@sunlight.local>
Date: Thu, 24 Aug 2006 03:05:13 +0400
Subject: view.result, view.test:   Corrected test case for the bug#21261
 sql_parse.cc:   Corrected fix for bug#21261

mysql-test/t/view.test:
  Corrected test case for the bug#21261
mysql-test/r/view.result:
  Corrected test case for the bug#21261
sql/sql_parse.cc:
  Corrected fix for bug#21261
---
 mysql-test/r/view.result | 1 +
 mysql-test/t/view.test   | 1 +
 2 files changed, 2 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 5b170d056f7..6dc2db09392 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2861,6 +2861,7 @@ GRANT SELECT ON t2 TO 'user21261'@'localhost';
 INSERT INTO v1 (x) VALUES (5);
 UPDATE v1 SET x=1;
 GRANT SELECT ON v1 TO 'user21261'@'localhost';
+GRANT SELECT ON t1 TO 'user21261'@'localhost';
 UPDATE v1,t2 SET x=1 WHERE x=y;
 SELECT * FROM t1;
 x
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 23e49588e8b..fae3f856cb8 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2740,6 +2740,7 @@ INSERT INTO v1 (x) VALUES (5);
 UPDATE v1 SET x=1;
 CONNECTION root;
 GRANT SELECT ON v1 TO 'user21261'@'localhost';
+GRANT SELECT ON t1 TO 'user21261'@'localhost';
 CONNECTION user21261;
 UPDATE v1,t2 SET x=1 WHERE x=y;
 CONNECTION root;
-- 
cgit v1.2.1


From c74c8195330ff1e7b3274f6c78d968bf026e5037 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Thu, 24 Aug 2006 19:14:36 +0400
Subject: Bug #16255: Subquery in WHERE (the cset by Georgi Kodinov)  Must not
 use Item_direct_ref in HAVING because it points to  the new value (witch is
 not yet calculated for the first row).

mysql-test/r/subselect.result:
  Bug #16255: Subquery in where
   - test case
mysql-test/t/subselect.test:
  Bug #16255: Subquery in where
   - test case
sql/item_subselect.cc:
  Bug #16255: Subquery in where
   Must not use Item_direct_ref in HAVING because it points to
   the new value (witch is not yet calculated for the first row).
---
 mysql-test/r/subselect.result | 11 +++++++++++
 mysql-test/t/subselect.test   | 12 ++++++++++++
 2 files changed, 23 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 983ad628425..e84c3957760 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2895,3 +2895,14 @@ select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
 s1
 2
 drop table t1;
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
+INSERT INTO t1 VALUES(26, 1), (48, 2);
+SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a,MAX(b) FROM t1 GROUP BY a);
+a	b
+26	1
+48	2
+SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a + 0,MAX(b) FROM t1 GROUP BY a);
+a	b
+26	1
+48	2
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index fc97d22cbb1..780a4f3a002 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1861,4 +1861,16 @@ select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
 select * from t1 where (s1 = ALL (select s1/s1 from t1));
 select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
 drop table t1;
+
+#
+# Bug #16255: Subquery in where
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
+
+INSERT INTO t1 VALUES(26, 1), (48, 2);
+
+SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a,MAX(b) FROM t1 GROUP BY a);
+SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a + 0,MAX(b) FROM t1 GROUP BY a);
+
+DROP TABLE t1;
 # End of 4.1 tests
-- 
cgit v1.2.1


From f895a16c721265c75f6492d5ada096e223cae59b Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Thu, 24 Aug 2006 19:16:27 +0400
Subject: BUG#16255: Post-review fixes: adjust the testcase.

mysql-test/r/subselect.result:
  BUG#16255: A proper testcase
mysql-test/t/subselect.test:
  BUG#16255: A proper testcase
---
 mysql-test/r/subselect.result | 33 ++++++++++++++++++++++-----------
 mysql-test/t/subselect.test   | 24 ++++++++++++++++++------
 2 files changed, 40 insertions(+), 17 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index e84c3957760..f5abc4ed42a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2895,14 +2895,25 @@ select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
 s1
 2
 drop table t1;
-CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
-INSERT INTO t1 VALUES(26, 1), (48, 2);
-SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a,MAX(b) FROM t1 GROUP BY a);
-a	b
-26	1
-48	2
-SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a + 0,MAX(b) FROM t1 GROUP BY a);
-a	b
-26	1
-48	2
-DROP TABLE t1;
+create table t1 (
+retailerID varchar(8) NOT NULL,
+statusID   int(10) unsigned NOT NULL,
+changed    datetime NOT NULL,
+UNIQUE KEY retailerID (retailerID, statusID, changed)
+);
+INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
+INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
+INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
+INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
+INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
+INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
+select * from t1 r1 
+where (r1.retailerID,(r1.changed)) in 
+(SELECT r2.retailerId,(max(changed)) from t1 r2 
+group by r2.retailerId);
+retailerID	statusID	changed
+0026	2	2006-01-06 12:25:53
+0037	2	2006-01-06 12:25:53
+0048	1	2006-01-06 12:37:50
+0059	1	2006-01-06 12:37:50
+drop table t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 780a4f3a002..10dfb788c10 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1865,12 +1865,24 @@ drop table t1;
 #
 # Bug #16255: Subquery in where
 #
-CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
-
-INSERT INTO t1 VALUES(26, 1), (48, 2);
+create table t1 (
+  retailerID varchar(8) NOT NULL,
+  statusID   int(10) unsigned NOT NULL,
+  changed    datetime NOT NULL,
+  UNIQUE KEY retailerID (retailerID, statusID, changed)
+);
 
-SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a,MAX(b) FROM t1 GROUP BY a);
-SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a + 0,MAX(b) FROM t1 GROUP BY a);
+INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
+INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
+INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
+INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
+INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
+INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
+
+select * from t1 r1 
+  where (r1.retailerID,(r1.changed)) in 
+         (SELECT r2.retailerId,(max(changed)) from t1 r2 
+          group by r2.retailerId);
+drop table t1;
 
-DROP TABLE t1;
 # End of 4.1 tests
-- 
cgit v1.2.1


From f121994de81d6621fd221a541a1e009530d43276 Mon Sep 17 00:00:00 2001
From: unknown <igor@rurik.mysql.com>
Date: Fri, 25 Aug 2006 02:17:41 -0700
Subject: Fixed bug #21390: wrong estimate of rows after elimination of const
 tables. This resulted in choosing extremely inefficient execution plans in
 same cases when distribution of data in joined were skewed (see the customer
 test case for the bug).

mysql-test/r/select.result:
  Added a test case for bug #21390: wrong estimate of rows
  after elimination of const tables.
  Includded a test case that checks the code added by the patch
  that handles outer joins with no matches after substitution of
  a const table in an efficient way.
mysql-test/t/select.test:
  Added a test case for bug #21390: wrong estimate of rows
  after elimination of const tables.
  Included a test case that checks the code added by the patch
  that handles outer joins with no matches after substitution of
  a const table in an efficient way.
sql/sql_select.cc:
  Fixed bug #21390: wrong estimate of rows after elimination of
  const tables. This resulted in choosing extremely inefficient
  execution plans in same cases when distribution of data in
  joined were skewed (see the customer test case for the bug).
  Also added the code to handle outer joins with no matches after
  substitution of a const table in an efficient way.
  Corrected calculation of the null rejecting key conditions.
---
 mysql-test/r/select.result | 42 ++++++++++++++++++++++++++++++++++++++++--
 mysql-test/t/select.test   | 41 +++++++++++++++++++++++++++++++++++++++++
 2 files changed, 81 insertions(+), 2 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index b5d059be4c5..0c62d3f570f 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2328,9 +2328,9 @@ explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t4	const	id4	NULL	NULL	NULL	1	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t4	ALL	id4	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id1	id2	id3	id4	id44
@@ -3479,3 +3479,41 @@ Warning	1466	Leading spaces are removed from name ' a '
 execute stmt;
 a 
 1
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
+CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
+INSERT INTO t2 VALUES
+(1), (1), (1), (1), (1), (1), (1), (1),
+(2), (2), (2), (2),
+(3), (3),
+(4);
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	ref	idx	idx	4	const	7	Using index
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	ref	idx	idx	4	const	1	Using index
+DROP TABLE t1, t2;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
+CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
+INSERT INTO t2 VALUES (2,1), (3,2);
+CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
+INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+WHERE t1.id=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1	
+1	SIMPLE	t3	ref	idx1	idx1	5	const	3	Using where
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+WHERE t1.id=2;
+id	a	b	c	d	e
+2	NULL	NULL	NULL	2	10
+2	NULL	NULL	NULL	2	20
+2	NULL	NULL	NULL	2	40
+2	NULL	NULL	NULL	2	50
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 197d89d02d5..36b3749b4d7 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2957,3 +2957,44 @@ SELECT 0.9888889889 * 1.011111411911;
 #
 prepare stmt from 'select 1 as " a "';
 execute stmt;
+
+#
+# Bug #21390: wrong estimate of rows after elimination of const tables
+#
+
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
+
+CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
+INSERT INTO t2 VALUES
+  (1), (1), (1), (1), (1), (1), (1), (1),
+  (2), (2), (2), (2),
+  (3), (3),
+  (4);
+
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
+
+DROP TABLE t1, t2;
+
+#
+# No matches for a join after substitution of a const table
+#
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
+
+CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
+INSERT INTO t2 VALUES (2,1), (3,2);
+
+CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
+INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+  WHERE t1.id=2;
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+  WHERE t1.id=2;
+
+
+DROP TABLE t1,t2,t3;
-- 
cgit v1.2.1


From 00c24198cb8929150636be82eb01aa806e26ab67 Mon Sep 17 00:00:00 2001
From: unknown <evgen@moonbone.local>
Date: Wed, 30 Aug 2006 22:11:23 +0400
Subject: sql_base.cc, ndb_condition_pushdown.result, opt_range.cc:   After
 merge changes

mysql-test/r/ndb_condition_pushdown.result:
  After merge changes
sql/sql_base.cc:
  After merge changes
sql/opt_range.cc:
  After merge changes
---
 mysql-test/r/ndb_condition_pushdown.result | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result
index 6333466a8e3..a484cbfe608 100644
--- a/mysql-test/r/ndb_condition_pushdown.result
+++ b/mysql-test/r/ndb_condition_pushdown.result
@@ -1307,7 +1307,7 @@ select auto from t1 where
 ('1901-01-01 01:01:01' between date_time and date_time) 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	10	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	1	Using where with pushed condition; Using filesort
 select auto from t1 where
 ("aaaa" between string and string) and 
 ("aaaa" between vstring and vstring) and 
@@ -1409,7 +1409,7 @@ select auto from t1 where
 ('1901-01-01 01:01:01' not between date_time and date_time) 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	20	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	3	Using where with pushed condition; Using filesort
 select auto from t1 where
 ("aaaa" not between string and string) and 
 ("aaaa" not between vstring and vstring) and 
@@ -1565,7 +1565,7 @@ time_field not in('01:01:01','03:03:03') and
 date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	2	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	range	medium_index	medium_index	3	NULL	6	Using where with pushed condition; Using filesort
 select auto from t1 where
 string not in("aaaa","cccc") and 
 vstring not in("aaaa","cccc") and 
-- 
cgit v1.2.1