#At file:///home/gluh/MySQL/mysql-5.1-bug-47669/ based on revid:sergey.glukhov@stripped
3343 Sergey Glukhov 2010-02-16
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/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
@ sql/item.cc
print 'NULL' if field value is null.
modified:
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
sql/item.cc
=== 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-16 11:14:07 +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 2009-01-16 15:38:38 +0000
+++ b/mysql-test/r/having.result 2010-02-16 11:14:07 +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-16 11:14:07 +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-16 11:14:07 +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-16 11:14:07 +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 'sql/item.cc'
--- a/sql/item.cc 2010-02-11 17:41:53 +0000
+++ b/sql/item.cc 2010-02-16 11:14:07 +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-20100216111407-xrr61mw7hlm0sop2.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3343)Bug#47669 | Sergey Glukhov | 16 Feb |
