List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:February 26 2010 1:40pm
Subject:bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3361)
Bug#47669
View as plain text  
#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:svoj@stripped

 3361 Sergey Glukhov	2010-02-26
      Bug#47669 Query showed by EXPLAIN EXTENDED gives different result from original query
      Item_field::print method does not take into
      account fields whose values may be null.
      The fix is to print 'NULL' if field value is null.
     @ mysql-test/r/explain.result
        test case
     @ mysql-test/r/func_str.result
        result fix
     @ mysql-test/r/having.result
        result fix
     @ mysql-test/r/select.result
        result fix
     @ mysql-test/r/subselect.result
        result fix
     @ mysql-test/r/union.result
        result fix
     @ mysql-test/t/explain.test
        test case
     @ sql/item.cc
        print 'NULL' if field value is null.

    modified:
      mysql-test/r/explain.result
      mysql-test/r/func_str.result
      mysql-test/r/having.result
      mysql-test/r/select.result
      mysql-test/r/subselect.result
      mysql-test/r/union.result
      mysql-test/t/explain.test
      sql/item.cc
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result	2009-12-08 09:26:11 +0000
+++ b/mysql-test/r/explain.result	2010-02-26 13:40:01 +0000
@@ -210,4 +210,19 @@ Error	1140	Mixing of GROUP columns (MIN(
 Note	1003	select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
 SET SESSION sql_mode=@old_sql_mode;
 DROP TABLE t1;
+#
+# Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query
+#
+CREATE TABLE t1 (c int);
+INSERT INTO t1 VALUES (NULL);
+CREATE TABLE t2 (d int);
+INSERT INTO t2 VALUES (NULL), (0);
+EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
+Note	1003	select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
+DROP TABLE t1, t2;
 End of 5.1 tests.

=== modified file 'mysql-test/r/func_str.result'
--- a/mysql-test/r/func_str.result	2009-12-04 15:36:58 +0000
+++ b/mysql-test/r/func_str.result	2010-02-26 13:40:01 +0000
@@ -1307,12 +1307,12 @@ explain extended select encode(f1,'zxcv'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select encode('','zxcv') AS `enc` from `test`.`t1`
+Note	1003	select encode(NULL,'zxcv') AS `enc` from `test`.`t1`
 explain extended select decode(f1,'zxcv') as 'enc' from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select decode('','zxcv') AS `enc` from `test`.`t1`
+Note	1003	select decode(NULL,'zxcv') AS `enc` from `test`.`t1`
 drop table t1;
 create table t1 (a bigint not null)engine=myisam;
 insert into t1 set a = 1024*1024*1024*4;

=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2010-02-26 11:39:25 +0000
+++ b/mysql-test/r/having.result	2010-02-26 13:40:01 +0000
@@ -12,7 +12,7 @@ explain extended select count(a) as b fr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select count('0') AS `b` from `test`.`t1` where 0 having (`b` >= 0)
+Note	1003	select count(NULL) AS `b` from `test`.`t1` where 0 having (`b` >= 0)
 drop table t1;
 CREATE TABLE t1 (
 raw_id int(10) NOT NULL default '0',

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2010-01-29 11:08:49 +0000
+++ b/mysql-test/r/select.result	2010-02-26 13:40:01 +0000
@@ -4050,10 +4050,10 @@ join_0.c1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0'
+Note	1003	select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL
 SHOW WARNINGS;
 Level	Code	Message
-Note	1003	select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0'
+Note	1003	select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL
 DROP TABLE t1;
 SELECT 1 AS ` `;
 
@@ -4648,17 +4648,17 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1
+Note	1003	select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1
+Note	1003	select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1
+Note	1003	select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1
 DROP TABLE t1;
 #
 # Bug#45266: Uninitialized variable lead to an empty result.

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2010-02-02 16:30:23 +0000
+++ b/mysql-test/r/subselect.result	2010-02-26 13:40:01 +0000
@@ -1496,7 +1496,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`)))
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1504,7 +1504,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`)))
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1515,7 +1515,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select '0' AS `b` from `test`.`t2` group by 1)))
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL AS `b` from `test`.`t2` group by 1)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1523,7 +1523,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select '0' AS `b` from `test`.`t2` group by 1)))
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL AS `b` from `test`.`t2` group by 1)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);

=== modified file 'mysql-test/r/union.result'
--- a/mysql-test/r/union.result	2010-01-06 10:24:51 +0000
+++ b/mysql-test/r/union.result	2010-02-26 13:40:01 +0000
@@ -1585,7 +1585,7 @@ id	select_type	table	type	possible_keys	
 2	UNION	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
 Warnings:
-Note	1003	select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a`
+Note	1003	select NULL AS `a` from `test`.`t1` union select NULL AS `a` from `test`.`t1` order by `a`
 DROP TABLE t1;
 End of 5.0 tests
 # 

=== modified file 'mysql-test/t/explain.test'
--- a/mysql-test/t/explain.test	2009-12-08 09:26:11 +0000
+++ b/mysql-test/t/explain.test	2010-02-26 13:40:01 +0000
@@ -187,4 +187,16 @@ SET SESSION sql_mode=@old_sql_mode;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query
+--echo #
+
+CREATE TABLE t1 (c int);
+INSERT INTO t1 VALUES (NULL);
+CREATE TABLE t2 (d int);
+INSERT INTO t2 VALUES (NULL), (0);
+EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
+DROP TABLE t1, t2;
+
+
 --echo End of 5.1 tests.

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-02-25 19:13:11 +0000
+++ b/sql/item.cc	2010-02-26 13:40:01 +0000
@@ -5690,9 +5690,14 @@ void Item_field::print(String *str, enum
     char buff[MAX_FIELD_WIDTH];
     String tmp(buff,sizeof(buff),str->charset());
     field->val_str(&tmp);
-    str->append('\'');
-    str->append(tmp);
-    str->append('\'');
+    if (field->is_null())
+      str->append("NULL");
+    else
+    {
+      str->append('\'');
+      str->append(tmp);
+      str->append('\'');
+    }
     return;
   }
   Item_ident::print(str, query_type);


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100226134001-5pnihv36uax9kyzv.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3361)Bug#47669Sergey Glukhov26 Feb