diff options
37 files changed, 337 insertions, 160 deletions
diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index d408a0b6153..0c0e2d623c8 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -27,7 +27,7 @@ explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" E id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select (case 1 when 1 then _latin1'one' when 2 then _latin1'two' else _latin1'more' end) AS `CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END` +Note 1003 select (case 1 when 1 then 'one' when 2 then 'two' else 'more' end) AS `CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END` select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END; CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END two @@ -147,7 +147,7 @@ COALESCE('a' COLLATE latin1_bin,'b'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select coalesce(1) AS `COALESCE(1)`,coalesce(1.0) AS `COALESCE(1.0)`,coalesce(_latin1'a') AS `COALESCE('a')`,coalesce(1,1.0) AS `COALESCE(1,1.0)`,coalesce(1,_latin1'1') AS `COALESCE(1,'1')`,coalesce(1.1,_latin1'1') AS `COALESCE(1.1,'1')`,coalesce((_latin1'a' collate latin1_bin),_latin1'b') AS `COALESCE('a' COLLATE latin1_bin,'b')` +Note 1003 select coalesce(1) AS `COALESCE(1)`,coalesce(1.0) AS `COALESCE(1.0)`,coalesce('a') AS `COALESCE('a')`,coalesce(1,1.0) AS `COALESCE(1,1.0)`,coalesce(1,'1') AS `COALESCE(1,'1')`,coalesce(1.1,'1') AS `COALESCE(1.1,'1')`,coalesce(('a' collate latin1_bin),'b') AS `COALESCE('a' COLLATE latin1_bin,'b')` SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index a71d840d229..1d3752f09e7 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -1511,7 +1511,7 @@ explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where Warnings: -Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> _latin1'')) +Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> '')) select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index 026c0ebdcf2..69b48ee2952 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -519,7 +519,7 @@ explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select charset(_latin1'a') AS `charset('a')`,collation(_latin1'a') AS `collation('a')`,coercibility(_latin1'a') AS `coercibility('a')`,(_latin1'a' = _latin1'A') AS `'a'='A'` +Note 1003 select charset('a') AS `charset('a')`,collation('a') AS `collation('a')`,coercibility('a') AS `coercibility('a')`,('a' = 'A') AS `'a'='A'` SET CHARACTER SET koi8r; SHOW VARIABLES LIKE 'collation_client'; Variable_name Value diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index fbe46e9fa21..dfd8244ef77 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -445,7 +445,7 @@ explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,addtime(_latin1'31.12.97 11.59.59.999999 PM',_latin1'1 1.1.1.000002') AS `addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,subtime(_latin1'31.12.97 11.59.59.999999 PM',_latin1'1 1.1.1.000002') AS `subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,timediff(_latin1'01.01.97 11:59:59.000001 PM',_latin1'31.12.95 11:59:59.000002 PM') AS `timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM")`,cast(str_to_date(_latin1'15-01-2001 12:59:59',_latin1'%d-%m-%Y %H:%i:%S') as time) AS `cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME)`,maketime(23,11,12) AS `maketime(23,11,12)`,microsecond(_latin1'1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")` +Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,addtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,subtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS `subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM') AS `timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM")`,cast(str_to_date('15-01-2001 12:59:59','%d-%m-%Y %H:%i:%S') as time) AS `cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME)`,maketime(23,11,12) AS `maketime(23,11,12)`,microsecond('1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")` create table t1 (d date); insert into t1 values ('2004-07-14'),('2005-07-14'); select date_format(d,"%d") from t1 order by 1; diff --git a/mysql-test/r/ddl_i18n_koi8r.result b/mysql-test/r/ddl_i18n_koi8r.result index fbaf2f2fd4a..dfdc8fd463e 100644 --- a/mysql-test/r/ddl_i18n_koi8r.result +++ b/mysql-test/r/ddl_i18n_koi8r.result @@ -20,7 +20,7 @@ CREATE VIEW v2 AS SELECT _utf8'тест' as c1| SHOW CREATE VIEW v1| View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _koi8r'' AS `c1`,`t1`.`` AS `c2` from `t1` koi8r koi8r_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select '' AS `c1`,`t1`.`` AS `c2` from `t1` koi8r koi8r_general_ci SHOW CREATE VIEW v2| View Create View character_set_client collation_connection @@ -60,7 +60,7 @@ set names koi8r| SHOW CREATE VIEW v1| View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _koi8r'' AS `c1`,`t1`.`` AS `c2` from `t1` koi8r koi8r_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select '' AS `c1`,`t1`.`` AS `c2` from `t1` koi8r koi8r_general_ci SHOW CREATE VIEW v2| View Create View character_set_client collation_connection @@ -105,7 +105,7 @@ set names koi8r| SHOW CREATE VIEW v1| View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _koi8r'' AS `c1`,`t1`.`` AS `c2` from `t1` koi8r koi8r_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select '' AS `c1`,`t1`.`` AS `c2` from `t1` koi8r koi8r_general_ci SHOW CREATE VIEW v2| View Create View character_set_client collation_connection diff --git a/mysql-test/r/ddl_i18n_utf8.result b/mysql-test/r/ddl_i18n_utf8.result index d0f176c1985..c009265d384 100644 --- a/mysql-test/r/ddl_i18n_utf8.result +++ b/mysql-test/r/ddl_i18n_utf8.result @@ -20,7 +20,7 @@ CREATE VIEW v2 AS SELECT _koi8r'' as c1| SHOW CREATE VIEW v1| View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _utf8'тест' AS `c1`,`t1`.`кол` AS `c2` from `t1` utf8 utf8_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'тест' AS `c1`,`t1`.`кол` AS `c2` from `t1` utf8 utf8_general_ci SHOW CREATE VIEW v2| View Create View character_set_client collation_connection @@ -60,7 +60,7 @@ set names utf8| SHOW CREATE VIEW v1| View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _utf8'тест' AS `c1`,`t1`.`кол` AS `c2` from `t1` utf8 utf8_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'тест' AS `c1`,`t1`.`кол` AS `c2` from `t1` utf8 utf8_general_ci SHOW CREATE VIEW v2| View Create View character_set_client collation_connection @@ -105,7 +105,7 @@ set names utf8| SHOW CREATE VIEW v1| View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _utf8'тест' AS `c1`,`t1`.`кол` AS `c2` from `t1` utf8 utf8_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'тест' AS `c1`,`t1`.`кол` AS `c2` from `t1` utf8 utf8_general_ci SHOW CREATE VIEW v2| View Create View character_set_client collation_connection diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index c3fa427127b..2fc00073448 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -17,7 +17,7 @@ explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 fulltext a a 0 1 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against (_latin1'collections')) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) select * from t1 where MATCH(a,b) AGAINST ("indexes"); a b Full-text indexes are called collections @@ -87,7 +87,7 @@ explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 fulltext a a 0 1 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against (_latin1'support -collections' in boolean mode)) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support -collections' in boolean mode)) select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); a b MySQL has now support for full-text search diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result index 32377c9ba63..25b921681c5 100644 --- a/mysql-test/r/func_crypt.result +++ b/mysql-test/r/func_crypt.result @@ -91,7 +91,7 @@ explain extended select password('idkfa '), old_password('idkfa'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select password(_latin1'idkfa ') AS `password('idkfa ')`,old_password(_latin1'idkfa') AS `old_password('idkfa')` +Note 1003 select password('idkfa ') AS `password('idkfa ')`,old_password('idkfa') AS `old_password('idkfa')` select encrypt('1234','_.'); encrypt('1234','_.') # diff --git a/mysql-test/r/func_encrypt.result b/mysql-test/r/func_encrypt.result index dc2b5498b2a..04af76429f8 100644 --- a/mysql-test/r/func_encrypt.result +++ b/mysql-test/r/func_encrypt.result @@ -182,4 +182,4 @@ explain extended select des_decrypt(des_encrypt("hello",4),'password2'), des_dec id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select des_decrypt(des_encrypt(_latin1'hello',4),_latin1'password2') AS `des_decrypt(des_encrypt("hello",4),'password2')`,des_decrypt(des_encrypt(_latin1'hello',_latin1'hidden')) AS `des_decrypt(des_encrypt("hello","hidden"))` +Note 1003 select des_decrypt(des_encrypt('hello',4),'password2') AS `des_decrypt(des_encrypt("hello",4),'password2')`,des_decrypt(des_encrypt('hello','hidden')) AS `des_decrypt(des_encrypt("hello","hidden"))` diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index e9a17324397..42a2aa840ea 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -43,7 +43,7 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using filesort Warnings: -Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) +Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like '%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) select nullif(u, 1) from t1; nullif(u, 1) NULL diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 80fa8f17a2a..6e1f9bd8188 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -146,7 +146,7 @@ explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (_latin1'a' in (`test`.`t1`.`a`,`test`.`t1`.`b`,(`test`.`t1`.`c` collate latin1_bin))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ('a' in (`test`.`t1`.`a`,`test`.`t1`.`b`,(`test`.`t1`.`c` collate latin1_bin))) drop table t1; set names utf8; create table t1 (a char(10) character set utf8 not null); diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result index 5de5962efeb..9338a76e320 100644 --- a/mysql-test/r/func_like.result +++ b/mysql-test/r/func_like.result @@ -5,12 +5,12 @@ explain extended select * from t1 where a like 'abc%'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index a a 13 NULL 5 20.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like _latin1'abc%') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like 'abc%') explain extended select * from t1 where a like concat('abc','%'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index a a 13 NULL 5 20.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like concat(_latin1'abc',_latin1'%')) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like concat('abc','%')) select * from t1 where a like "abc%"; a abc diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result index 9d85b1d3e7f..794ae79973a 100644 --- a/mysql-test/r/func_regexp.result +++ b/mysql-test/r/func_regexp.result @@ -52,7 +52,7 @@ explain extended select * from t1 where xxx regexp('is a test of some long text id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from `test`.`t1` where ('this is a test of some long text to see what happens' regexp _latin1'is a test of some long text to') +Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from `test`.`t1` where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to') select * from t1 where xxx regexp('is a test of some long text to '); xxx this is a test of some long text to see what happens diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index 4dd11cafdcd..3f9f7b85731 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -5,7 +5,7 @@ explain extended select INTERVAL(55,10,20,30,40,50,60,70,80,90,100),interval(3,1 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select interval((55,10,20,30,40,50,60,70,80,90,100)) AS `INTERVAL(55,10,20,30,40,50,60,70,80,90,100)`,interval((3,1,(1 + 1),(((1 + 1) + 1) + 1))) AS `interval(3,1,1+1,1+1+1+1)`,field(_latin1'IBM',_latin1'NCA',_latin1'ICL',_latin1'SUN',_latin1'IBM',_latin1'DIGITAL') AS `field("IBM","NCA","ICL","SUN","IBM","DIGITAL")`,field(_latin1'A',_latin1'B',_latin1'C') AS `field("A","B","C")`,elt(2,_latin1'ONE',_latin1'TWO',_latin1'THREE') AS `elt(2,"ONE","TWO","THREE")`,interval((0,1,2,3,4)) AS `interval(0,1,2,3,4)`,(elt(1,1,2,3) | 0) AS `elt(1,1,2,3)|0`,(elt(1,1.1,1.2,1.3) + 0) AS `elt(1,1.1,1.2,1.3)+0` +Note 1003 select interval((55,10,20,30,40,50,60,70,80,90,100)) AS `INTERVAL(55,10,20,30,40,50,60,70,80,90,100)`,interval((3,1,(1 + 1),(((1 + 1) + 1) + 1))) AS `interval(3,1,1+1,1+1+1+1)`,field('IBM','NCA','ICL','SUN','IBM','DIGITAL') AS `field("IBM","NCA","ICL","SUN","IBM","DIGITAL")`,field('A','B','C') AS `field("A","B","C")`,elt(2,'ONE','TWO','THREE') AS `elt(2,"ONE","TWO","THREE")`,interval((0,1,2,3,4)) AS `interval(0,1,2,3,4)`,(elt(1,1,2,3) | 0) AS `elt(1,1,2,3)|0`,(elt(1,1.1,1.2,1.3) + 0) AS `elt(1,1.1,1.2,1.3)+0` SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56); INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56) 1 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 6f6edd5112b..ea79eefb316 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -817,57 +817,57 @@ explain extended select md5('hello'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select md5(_latin1'hello') AS `md5('hello')` +Note 1003 select md5('hello') AS `md5('hello')` explain extended select sha('abc'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select sha(_latin1'abc') AS `sha('abc')` +Note 1003 select sha('abc') AS `sha('abc')` explain extended select sha1('abc'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select sha(_latin1'abc') AS `sha1('abc')` +Note 1003 select sha('abc') AS `sha1('abc')` explain extended select soundex(''); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select soundex(_latin1'') AS `soundex('')` +Note 1003 select soundex('') AS `soundex('')` explain extended select 'mood' sounds like 'mud'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select (soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'` +Note 1003 select (soundex('mood') = soundex('mud')) AS `'mood' sounds like 'mud'` explain extended select aes_decrypt(aes_encrypt('abc','1'),'1'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')` +Note 1003 select aes_decrypt(aes_encrypt('abc','1'),'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')` explain extended select concat('*',space(5),'*'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')` +Note 1003 select concat('*',repeat(' ',5),'*') AS `concat('*',space(5),'*')` explain extended select reverse('abc'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select reverse(_latin1'abc') AS `reverse('abc')` +Note 1003 select reverse('abc') AS `reverse('abc')` explain extended select rpad('a',4,'1'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')` +Note 1003 select rpad('a',4,'1') AS `rpad('a',4,'1')` explain extended select lpad('a',4,'1'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')` +Note 1003 select lpad('a',4,'1') AS `lpad('a',4,'1')` explain extended select concat_ws(',','',NULL,'a'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')` +Note 1003 select concat_ws(',','',NULL,'a') AS `concat_ws(',','',NULL,'a')` explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -882,7 +882,7 @@ explain extended select locate("a","b",2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)` +Note 1003 select locate('a','b',2) AS `locate("a","b",2)` explain extended select format(130,10); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -907,7 +907,7 @@ explain extended select binary 'HE'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select cast(_latin1'HE' as char charset binary) AS `binary 'HE'` +Note 1003 select cast('HE' as char charset binary) AS `binary 'HE'` explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' '); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -917,7 +917,7 @@ explain extended select FIELD('b' COLLATE latin1_bin,'A','B'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')` +Note 1003 select field(('b' collate latin1_bin),'A','B') AS `FIELD('b' COLLATE latin1_bin,'A','B')` explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -937,37 +937,37 @@ explain extended select length('\n\t\r\b\0\_\%\\'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')` +Note 1003 select length('\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')` explain extended select bit_length('\n\t\r\b\0\_\%\\'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')` +Note 1003 select bit_length('\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')` explain extended select bit_length('\n\t\r\b\0\_\%\\'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')` +Note 1003 select bit_length('\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')` explain extended select concat('monty',' was here ','again'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')` +Note 1003 select concat('monty',' was here ','again') AS `concat('monty',' was here ','again')` explain extended select length('hello'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select length(_latin1'hello') AS `length('hello')` +Note 1003 select length('hello') AS `length('hello')` explain extended select char(ascii('h')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select char(ascii(_latin1'h')) AS `char(ascii('h'))` +Note 1003 select char(ascii('h')) AS `char(ascii('h'))` explain extended select ord('h'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select ord(_latin1'h') AS `ord('h')` +Note 1003 select ord('h') AS `ord('h')` explain extended select quote(1/0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -977,17 +977,17 @@ explain extended select crc32("123"); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select crc32(_latin1'123') AS `crc32("123")` +Note 1003 select crc32('123') AS `crc32("123")` explain extended select replace('aaaa','a','b'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')` +Note 1003 select replace('aaaa','a','b') AS `replace('aaaa','a','b')` explain extended select insert('txs',2,1,'hi'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')` +Note 1003 select insert('txs',2,1,'hi') AS `insert('txs',2,1,'hi')` explain extended select left(_latin2'a',1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -1012,12 +1012,12 @@ explain extended select SUBSTR('abcdefg',3,2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)` +Note 1003 select substr('abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)` explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)` +Note 1003 select substring_index('1abcd;2abcd;3abcd;4abcd',';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)` explain extended select trim(_latin2' a '); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -1037,7 +1037,7 @@ explain extended select decode(encode(repeat("a",100000),"monty"),"monty"); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select decode(encode(repeat(_latin1'a',100000),_latin1'monty'),_latin1'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")` +Note 1003 select decode(encode(repeat('a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")` SELECT lpad(12345, 5, "#"); lpad(12345, 5, "#") 12345 @@ -1282,39 +1282,39 @@ EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > _latin1'ab') +Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > 'ab') EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab') +Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab') EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading _latin1'y' from `test`.`t1`.`s`) > _latin1'ab') +Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading 'y' from `test`.`t1`.`s`) > 'ab') EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing _latin1'y' from `test`.`t1`.`s`) > _latin1'ab') +Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing 'y' from `test`.`t1`.`s`) > 'ab') EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab') +Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab') DROP TABLE t1; create table t1(f1 varchar(4)); explain extended select encode(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 encode('',_latin1'zxcv') AS `enc` from `test`.`t1` +Note 1003 select encode('','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('',_latin1'zxcv') AS `enc` from `test`.`t1` +Note 1003 select decode('','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; @@ -1390,7 +1390,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 100.00 Using index 1 SIMPLE t1 ref code code 13 const 3 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5)) +Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and (length(`test`.`t1`.`code`) = 5)) DROP TABLE t1,t2; select encode(NULL, NULL); encode(NULL, NULL) diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 4dd8fb1531d..9cdf8a3cf50 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -839,7 +839,7 @@ explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_d id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname(_latin1'1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)` +Note 1003 select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)` SET @TMP='2007-08-01 12:22:49'; CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES ('2007-08-01 12:22:59'); @@ -1069,7 +1069,7 @@ timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,_latin1'2001-02-01 12:59:59.120000',_latin1'2001-05-01 12:58:58.119999') AS `a2` +Note 1003 select timestampdiff(WEEK,'2001-02-01','2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,'2001-02-01 12:59:59.120000','2001-05-01 12:58:58.119999') AS `a2` select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') 100 100 04 04 4 diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 73fb9cc69e0..d91e21e9399 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -439,12 +439,12 @@ explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))` +Note 1003 select astext(geometryfromwkb(aswkb(geometryfromtext('POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))` explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)')))); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))))` +Note 1003 select astext(geometryfromwkb(aswkb(geometryfromtext('POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))))` SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); SRID(GeomFromText('LineString(1 1,2 2)',101)) 101 @@ -452,7 +452,7 @@ explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select srid(geometryfromtext(_latin1'LineString(1 1,2 2)',101)) AS `SRID(GeomFromText('LineString(1 1,2 2)',101))` +Note 1003 select srid(geometryfromtext('LineString(1 1,2 2)',101)) AS `SRID(GeomFromText('LineString(1 1,2 2)',101))` explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 103a7f5515a..bdc8d586f4e 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1562,7 +1562,7 @@ explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 50.78 Using where; Using index Warnings: -Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = _latin1'i121') and (`test`.`t1`.`b` = _latin1'a') and (`test`.`t1`.`a2` >= _latin1'b')) +Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`b` = 'a') and (`test`.`t1`.`a2` >= 'b')) explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by @@ -1579,7 +1579,7 @@ explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 50.61 Using where; Using index Warnings: -Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = _latin1'i121') and (`test`.`t2`.`b` = _latin1'a') and (`test`.`t2`.`a2` >= _latin1'b')) +Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`b` = 'a') and (`test`.`t2`.`a2` >= 'b')) explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by @@ -1808,7 +1808,7 @@ explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: -Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`b` = _latin1'c') and (`test`.`t1`.`a1` > _latin1'a') and (`test`.`t1`.`a2` > _latin1'a')) +Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`b` = 'c') and (`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a')) explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index @@ -1816,7 +1816,7 @@ explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: -Note 1003 select (ord(`test`.`t1`.`a1`) + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`)) AS `ord(a1) + count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > _latin1'a') and (`test`.`t1`.`a2` > _latin1'a')) +Note 1003 select (ord(`test`.`t1`.`a1`) + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`)) AS `ord(a1) + count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a')) select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); count(distinct a1,a2,b) 4 @@ -1924,19 +1924,19 @@ where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a1 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`c` > _latin1'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2` explain extended select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`d` > _latin1'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select a1,a2,b,c from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`d` > _latin1'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c` explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index @@ -1944,7 +1944,7 @@ explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`c` > _latin1'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index @@ -1968,12 +1968,12 @@ explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2, id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: -Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select distinct(a1) from t1 where ord(a2) = 98; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index d07aed5317a..d0bfab6237a 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2011,7 +2011,7 @@ SET character_set_client = @saved_cs_client; /*!50001 SET collation_connection = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ -/*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') */ +/*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like 'a%') */ /*!50002 WITH CASCADED CHECK OPTION */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; @@ -2182,7 +2182,7 @@ SET character_set_client = @saved_cs_client; /*!50001 SET collation_connection = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ -/*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') */ +/*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like 'a%') */ /*!50002 WITH CASCADED CHECK OPTION */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index ccb8e87b714..91a4c273832 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -387,5 +387,5 @@ explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like " id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index NULL a 5 NULL 5 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like _latin1'1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a` +Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a` drop table t1; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 5a2ebc37cc8..64b8aa74af3 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -6,7 +6,7 @@ explain extended select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnu id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select NULL AS `NULL`,NULL AS `NULL`,isnull(NULL) AS `isnull(null)`,isnull((1 / 0)) AS `isnull(1/0)`,isnull(((1 / 0) = NULL)) AS `isnull(1/0 = null)`,ifnull(NULL,1) AS `ifnull(null,1)`,ifnull(NULL,_latin1'TRUE') AS `ifnull(null,"TRUE")`,ifnull(_latin1'TRUE',_latin1'ERROR') AS `ifnull("TRUE","ERROR")`,isnull((1 / 0)) AS `1/0 is null`,(1 is not null) AS `1 is not null` +Note 1003 select NULL AS `NULL`,NULL AS `NULL`,isnull(NULL) AS `isnull(null)`,isnull((1 / 0)) AS `isnull(1/0)`,isnull(((1 / 0) = NULL)) AS `isnull(1/0 = null)`,ifnull(NULL,1) AS `ifnull(null,1)`,ifnull(NULL,'TRUE') AS `ifnull(null,"TRUE")`,ifnull('TRUE','ERROR') AS `ifnull("TRUE","ERROR")`,isnull((1 / 0)) AS `1/0 is null`,(1 is not null) AS `1 is not null` select 1 | NULL,1 & NULL,1+NULL,1-NULL; 1 | NULL 1 & NULL 1+NULL 1-NULL NULL NULL NULL NULL @@ -49,7 +49,7 @@ explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),ine id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select inet_ntoa(NULL) AS `inet_ntoa(null)`,inet_aton(NULL) AS `inet_aton(null)`,inet_aton(_latin1'122.256') AS `inet_aton("122.256")`,inet_aton(_latin1'122.226.') AS `inet_aton("122.226.")`,inet_aton(_latin1'') AS `inet_aton("")` +Note 1003 select inet_ntoa(NULL) AS `inet_ntoa(null)`,inet_aton(NULL) AS `inet_aton(null)`,inet_aton('122.256') AS `inet_aton("122.256")`,inet_aton('122.226.') AS `inet_aton("122.226.")`,inet_aton('') AS `inet_aton("")` create table t1 (x int); insert into t1 values (null); select * from t1 where x != 0; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 2f305f6ec5d..d3dca16fe4f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1507,7 +1507,7 @@ explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where Warnings: -Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> _latin1'')) +Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> '')) select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index c5fcb833933..320550c0ccd 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1404,7 +1404,7 @@ CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT '' AS test; set names utf8; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _koi8r'тест' AS `test` koi8r koi8r_general_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'тест' AS `test` koi8r koi8r_general_ci SHOW CREATE PROCEDURE p1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index ca5c5b42102..f9c6914468c 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -324,29 +324,29 @@ Pos Instruction 0 set str@1 NULL 1 set_case_expr (12) 0 i@0 2 jump_if_not 5(12) (case_expr@0 = 1) -3 set str@1 _latin1'1' +3 set str@1 '1' 4 jump 12 5 jump_if_not 8(12) (case_expr@0 = 2) -6 set str@1 _latin1'2' +6 set str@1 '2' 7 jump 12 8 jump_if_not 11(12) (case_expr@0 = 3) -9 set str@1 _latin1'3' +9 set str@1 '3' 10 jump 12 -11 set str@1 _latin1'unknown' +11 set str@1 'unknown' 12 stmt 0 "SELECT str" SHOW PROCEDURE CODE proc_19194_searched; Pos Instruction 0 set str@1 NULL 1 jump_if_not 4(11) (i@0 = 1) -2 set str@1 _latin1'1' +2 set str@1 '1' 3 jump 11 4 jump_if_not 7(11) (i@0 = 2) -5 set str@1 _latin1'2' +5 set str@1 '2' 6 jump 11 7 jump_if_not 10(11) (i@0 = 3) -8 set str@1 _latin1'3' +8 set str@1 '3' 9 jump 11 -10 set str@1 _latin1'unknown' +10 set str@1 'unknown' 11 stmt 0 "SELECT str" SHOW PROCEDURE CODE proc_19194_nested_1; Pos Instruction @@ -354,59 +354,59 @@ Pos Instruction 1 set str_j@3 NULL 2 set_case_expr (27) 0 i@0 3 jump_if_not 6(27) (case_expr@0 = 10) -4 set str_i@2 _latin1'10' +4 set str_i@2 '10' 5 jump 27 6 jump_if_not 20(27) (case_expr@0 = 20) -7 set str_i@2 _latin1'20' +7 set str_i@2 '20' 8 jump_if_not 11(18) (j@1 = 1) -9 set str_j@3 _latin1'1' +9 set str_j@3 '1' 10 jump 18 11 jump_if_not 14(18) (j@1 = 2) -12 set str_j@3 _latin1'2' +12 set str_j@3 '2' 13 jump 18 14 jump_if_not 17(18) (j@1 = 3) -15 set str_j@3 _latin1'3' +15 set str_j@3 '3' 16 jump 18 -17 set str_j@3 _latin1'unknown' +17 set str_j@3 'unknown' 18 stmt 0 "select "i was 20"" 19 jump 27 20 jump_if_not 23(27) (case_expr@0 = 30) -21 set str_i@2 _latin1'30' +21 set str_i@2 '30' 22 jump 27 23 jump_if_not 26(27) (case_expr@0 = 40) -24 set str_i@2 _latin1'40' +24 set str_i@2 '40' 25 jump 27 -26 set str_i@2 _latin1'unknown' +26 set str_i@2 'unknown' 27 stmt 0 "SELECT str_i, str_j" SHOW PROCEDURE CODE proc_19194_nested_2; Pos Instruction 0 set str_i@2 NULL 1 set str_j@3 NULL 2 jump_if_not 5(27) (i@0 = 10) -3 set str_i@2 _latin1'10' +3 set str_i@2 '10' 4 jump 27 5 jump_if_not 20(27) (i@0 = 20) -6 set str_i@2 _latin1'20' +6 set str_i@2 '20' 7 set_case_expr (18) 0 j@1 8 jump_if_not 11(18) (case_expr@0 = 1) -9 set str_j@3 _latin1'1' +9 set str_j@3 '1' 10 jump 18 11 jump_if_not 14(18) (case_expr@0 = 2) -12 set str_j@3 _latin1'2' +12 set str_j@3 '2' 13 jump 18 14 jump_if_not 17(18) (case_expr@0 = 3) -15 set str_j@3 _latin1'3' +15 set str_j@3 '3' 16 jump 18 -17 set str_j@3 _latin1'unknown' +17 set str_j@3 'unknown' 18 stmt 0 "select "i was 20"" 19 jump 27 20 jump_if_not 23(27) (i@0 = 30) -21 set str_i@2 _latin1'30' +21 set str_i@2 '30' 22 jump 27 23 jump_if_not 26(27) (i@0 = 40) -24 set str_i@2 _latin1'40' +24 set str_i@2 '40' 25 jump 27 -26 set str_i@2 _latin1'unknown' +26 set str_i@2 'unknown' 27 stmt 0 "SELECT str_i, str_j" SHOW PROCEDURE CODE proc_19194_nested_3; Pos Instruction @@ -414,59 +414,59 @@ Pos Instruction 1 set str_j@3 NULL 2 set_case_expr (28) 0 i@0 3 jump_if_not 6(28) (case_expr@0 = 10) -4 set str_i@2 _latin1'10' +4 set str_i@2 '10' 5 jump 28 6 jump_if_not 21(28) (case_expr@0 = 20) -7 set str_i@2 _latin1'20' +7 set str_i@2 '20' 8 set_case_expr (19) 1 j@1 9 jump_if_not 12(19) (case_expr@1 = 1) -10 set str_j@3 _latin1'1' +10 set str_j@3 '1' 11 jump 19 12 jump_if_not 15(19) (case_expr@1 = 2) -13 set str_j@3 _latin1'2' +13 set str_j@3 '2' 14 jump 19 15 jump_if_not 18(19) (case_expr@1 = 3) -16 set str_j@3 _latin1'3' +16 set str_j@3 '3' 17 jump 19 -18 set str_j@3 _latin1'unknown' +18 set str_j@3 'unknown' 19 stmt 0 "select "i was 20"" 20 jump 28 21 jump_if_not 24(28) (case_expr@0 = 30) -22 set str_i@2 _latin1'30' +22 set str_i@2 '30' 23 jump 28 24 jump_if_not 27(28) (case_expr@0 = 40) -25 set str_i@2 _latin1'40' +25 set str_i@2 '40' 26 jump 28 -27 set str_i@2 _latin1'unknown' +27 set str_i@2 'unknown' 28 stmt 0 "SELECT str_i, str_j" SHOW PROCEDURE CODE proc_19194_nested_4; Pos Instruction 0 set str_i@2 NULL 1 set str_j@3 NULL 2 jump_if_not 5(26) (i@0 = 10) -3 set str_i@2 _latin1'10' +3 set str_i@2 '10' 4 jump 26 5 jump_if_not 19(26) (i@0 = 20) -6 set str_i@2 _latin1'20' +6 set str_i@2 '20' 7 jump_if_not 10(17) (j@1 = 1) -8 set str_j@3 _latin1'1' +8 set str_j@3 '1' 9 jump 17 10 jump_if_not 13(17) (j@1 = 2) -11 set str_j@3 _latin1'2' +11 set str_j@3 '2' 12 jump 17 13 jump_if_not 16(17) (j@1 = 3) -14 set str_j@3 _latin1'3' +14 set str_j@3 '3' 15 jump 17 -16 set str_j@3 _latin1'unknown' +16 set str_j@3 'unknown' 17 stmt 0 "select "i was 20"" 18 jump 26 19 jump_if_not 22(26) (i@0 = 30) -20 set str_i@2 _latin1'30' +20 set str_i@2 '30' 21 jump 26 22 jump_if_not 25(26) (i@0 = 40) -23 set str_i@2 _latin1'40' +23 set str_i@2 '40' 24 jump 26 -25 set str_i@2 _latin1'unknown' +25 set str_i@2 'unknown' 26 stmt 0 "SELECT str_i, str_j" CALL proc_19194_nested_1(10, 1); str_i str_j @@ -793,7 +793,7 @@ end while; end// show procedure code proc_33618_h; Pos Instruction -0 set count1@1 _latin1'0' +0 set count1@1 '0' 1 set vb@2 NULL 2 set last_row@3 NULL 3 jump_if_not 24(24) (num@0 >= 1) @@ -818,7 +818,7 @@ Pos Instruction 22 jump 3 show procedure code proc_33618_c; Pos Instruction -0 set count1@1 _latin1'0' +0 set count1@1 '0' 1 set vb@2 NULL 2 set last_row@3 NULL 3 jump_if_not 23(23) (num@0 >= 1) diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 3622fb51c99..9ad515a53a3 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -1508,7 +1508,7 @@ explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where Warnings: -Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> _latin1'')) +Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> '')) select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index 3018fce5cad..1bd427a65e2 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -1511,7 +1511,7 @@ explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where Warnings: -Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> _latin1'')) +Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> '')) select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 20d44933128..1ef250aae99 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -393,13 +393,13 @@ EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03') +Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03') EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` +Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; date 2002-08-03 @@ -540,13 +540,13 @@ EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = _latin1'1') +Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1') EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = _latin1'1')) +Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1')) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1024,7 +1024,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found 2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select (select encrypt(_latin1'test') AS `ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` +Note 1003 select (select encrypt('test') AS `ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found @@ -1481,7 +1481,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; create table t2 (a int, b int); create table t3 (a int); @@ -2820,19 +2820,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 46724de4281..1c846700346 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -112,7 +112,7 @@ v1 CREATE TEMPORARY TABLE `v1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'This is view' AS `A` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'This is view' AS `A` latin1 latin1_swedish_ci drop view v1; select * from v1; A diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index c98c8c5b68c..7cf85b9b315 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -519,7 +519,7 @@ coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select charset(load_file(_latin1'MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `charset(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`,collation(load_file(_latin1'MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `collation(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`,coercibility(load_file(_latin1'MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))` +Note 1003 select charset(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `charset(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`,collation(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `collation(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))`,coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat')) AS `coercibility(load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat'))` update t1 set imagem=load_file('MYSQLTEST_VARDIR/std_data_ln/words.dat') where id=1; select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1; if(imagem is null, "ERROR", "OK") length(imagem) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 09b997797b4..3f8ed1b4e56 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3061,7 +3061,7 @@ TheEnd TheEnd SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'The\ZEnd' AS `TheEnd` latin1 latin1_swedish_ci DROP VIEW v1; CREATE TABLE t1 (mydate DATETIME); INSERT INTO t1 VALUES @@ -3613,7 +3613,9 @@ ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default val set @@sql_mode=@old_mode; drop view v1; drop table t1; -End of 5.0 tests. +# ----------------------------------------------------------------- +# -- End of 5.0 tests. +# ----------------------------------------------------------------- DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; USE `d-1`; @@ -3676,4 +3678,48 @@ DROP TABLE t1; # End of test case for Bug#26676. -End of 5.1 tests. +# ----------------------------------------------------------------- +# -- Bug#32538: View definition picks up character set, but not collation +# ----------------------------------------------------------------- + +DROP VIEW IF EXISTS v1; + +SET collation_connection = latin1_general_ci; +CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; + +SELECT COLLATION(c1), COLLATION(c2) FROM v1; +COLLATION(c1) COLLATION(c2) +latin1_swedish_ci latin1_general_ci + +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'text1' AS `c1`,'text2' AS `c2` latin1 latin1_general_ci + +SELECT * FROM v1 WHERE c1 = 'text1'; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_general_ci,COERCIBLE) for operation '=' + +SELECT * FROM v1 WHERE c2 = 'text2'; +c1 c2 +text1 text2 + +use test; +SET names latin1; + +SELECT COLLATION(c1), COLLATION(c2) FROM v1; +COLLATION(c1) COLLATION(c2) +latin1_swedish_ci latin1_general_ci + +SELECT * FROM v1 WHERE c1 = 'text1'; +c1 c2 +text1 text2 + +SELECT * FROM v1 WHERE c2 = 'text2'; +ERROR HY000: Illegal mix of collations (latin1_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' + +DROP VIEW v1; + +# -- End of test case for Bug#32538. + +# ----------------------------------------------------------------- +# -- End of 5.1 tests. +# ----------------------------------------------------------------- diff --git a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result index 20e9bf6283e..a96d2fd1bab 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result +++ b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result @@ -73,7 +73,7 @@ explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 fulltext a a 0 1 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against (_latin1'collections')) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) select * from t1 where MATCH(a,b) AGAINST ("indexes"); a b Full-text indexes are called collections diff --git a/mysql-test/suite/rpl/r/rpl_get_lock.result b/mysql-test/suite/rpl/r/rpl_get_lock.result index 235640acad0..f7c9541bd9f 100644 --- a/mysql-test/suite/rpl/r/rpl_get_lock.result +++ b/mysql-test/suite/rpl/r/rpl_get_lock.result @@ -25,7 +25,7 @@ explain extended select is_free_lock("lock"), is_used_lock("lock"); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select is_free_lock(_latin1'lock') AS `is_free_lock("lock")`,is_used_lock(_latin1'lock') AS `is_used_lock("lock")` +Note 1003 select is_free_lock('lock') AS `is_free_lock("lock")`,is_used_lock('lock') AS `is_used_lock("lock")` select is_free_lock("lock2"); is_free_lock("lock2") 1 diff --git a/mysql-test/suite/rpl/r/rpl_master_pos_wait.result b/mysql-test/suite/rpl/r/rpl_master_pos_wait.result index 951e944fddc..ab9b8cdad9d 100644 --- a/mysql-test/suite/rpl/r/rpl_master_pos_wait.result +++ b/mysql-test/suite/rpl/r/rpl_master_pos_wait.result @@ -11,7 +11,7 @@ explain extended select master_pos_wait('master-bin.999999',0,2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select master_pos_wait(_latin1'master-bin.999999',0,2) AS `master_pos_wait('master-bin.999999',0,2)` +Note 1003 select master_pos_wait('master-bin.999999',0,2) AS `master_pos_wait('master-bin.999999',0,2)` select master_pos_wait('master-bin.999999',0); stop slave sql_thread; master_pos_wait('master-bin.999999',0) diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 58ef9c1eff1..3f10bc62289 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3468,7 +3468,9 @@ set @@sql_mode=@old_mode; drop view v1; drop table t1; ---echo End of 5.0 tests. +--echo # ----------------------------------------------------------------- +--echo # -- End of 5.0 tests. +--echo # ----------------------------------------------------------------- # # Bug#21370 View renaming lacks tablename_to_filename encoding @@ -3540,4 +3542,67 @@ DROP TABLE t1; --echo # End of test case for Bug#26676. --echo ---echo End of 5.1 tests. +########################################################################### + +--echo # ----------------------------------------------------------------- +--echo # -- Bug#32538: View definition picks up character set, but not collation +--echo # ----------------------------------------------------------------- +--echo + +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +--echo + +SET collation_connection = latin1_general_ci; +CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; + +--echo + +SELECT COLLATION(c1), COLLATION(c2) FROM v1; + +--echo + +SHOW CREATE VIEW v1; + +--echo + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM v1 WHERE c1 = 'text1'; + +--echo + +SELECT * FROM v1 WHERE c2 = 'text2'; + +--echo + +use test; +SET names latin1; + +--echo + +SELECT COLLATION(c1), COLLATION(c2) FROM v1; + +--echo + +SELECT * FROM v1 WHERE c1 = 'text1'; + +--echo + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM v1 WHERE c2 = 'text2'; + +--echo + +DROP VIEW v1; + +--echo +--echo # -- End of test case for Bug#32538. +--echo + +########################################################################### + +--echo # ----------------------------------------------------------------- +--echo # -- End of 5.1 tests. +--echo # ----------------------------------------------------------------- diff --git a/sql/item.cc b/sql/item.cc index ab9243fcaf5..10c1c58aea8 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -2312,8 +2312,12 @@ my_decimal *Item_float::val_decimal(my_decimal *decimal_value) void Item_string::print(String *str) { - str->append('_'); - str->append(collation.collation->csname); + if (is_cs_specified()) + { + str->append('_'); + str->append(collation.collation->csname); + } + str->append('\''); str_value.print(str); str->append('\''); diff --git a/sql/item.h b/sql/item.h index b98389bc8d4..b3cf9aa6e4c 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1842,6 +1842,7 @@ public: Item_string(const char *str,uint length, CHARSET_INFO *cs, Derivation dv= DERIVATION_COERCIBLE, uint repertoire= MY_REPERTOIRE_UNICODE30) + : m_cs_specified(FALSE) { str_value.set_or_copy_aligned(str, length, cs); collation.set(cs, dv, repertoire); @@ -1860,6 +1861,7 @@ public: } /* Just create an item and do not fill string representation */ Item_string(CHARSET_INFO *cs, Derivation dv= DERIVATION_COERCIBLE) + : m_cs_specified(FALSE) { collation.set(cs, dv); max_length= 0; @@ -1870,6 +1872,7 @@ public: Item_string(const char *name_par, const char *str, uint length, CHARSET_INFO *cs, Derivation dv= DERIVATION_COERCIBLE, uint repertoire= MY_REPERTOIRE_UNICODE30) + : m_cs_specified(FALSE) { str_value.set_or_copy_aligned(str, length, cs); collation.set(cs, dv, repertoire); @@ -1923,6 +1926,48 @@ public: // to prevent drop fixed flag (no need parent cleanup call) void cleanup() {} bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + + /** + Return TRUE if character-set-introducer was explicitly specified in the + original query for this item (text literal). + + This operation is to be called from Item_string::print(). The idea is + that when a query is generated (re-constructed) from the Item-tree, + character-set-introducers should appear only for those literals, where + they were explicitly specified by the user. Otherwise, that may lead to + loss collation information (character set introducers implies default + collation for the literal). + + Basically, that makes sense only for views and hopefully will be gone + one day when we start using original query as a view definition. + + @return This operation returns the value of m_cs_specified attribute. + @retval TRUE if character set introducer was explicitly specified in + the original query. + @retval FALSE otherwise. + */ + inline bool is_cs_specified() const + { + return m_cs_specified; + } + + /** + Set the value of m_cs_specified attribute. + + m_cs_specified attribute shows whether character-set-introducer was + explicitly specified in the original query for this text literal or + not. The attribute makes sense (is used) only for views. + + This operation is to be called from the parser during parsing an input + query. + */ + inline void set_cs_specified(bool cs_specified) + { + m_cs_specified= cs_specified; + } + +private: + bool m_cs_specified; }; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index c98228424a5..764e593a1ed 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9823,8 +9823,11 @@ text_literal: } | UNDERSCORE_CHARSET TEXT_STRING { - $$= new Item_string($2.str, $2.length, $1); - ((Item_string*) $$)->set_repertoire_from_value(); + Item_string *str= new Item_string($2.str, $2.length, $1); + str->set_repertoire_from_value(); + str->set_cs_specified(TRUE); + + $$= str; } | text_literal TEXT_STRING_literal { @@ -9927,15 +9930,22 @@ literal: String *str= tmp ? tmp->quick_fix_field(), tmp->val_str((String*) 0) : (String*) 0; - $$= new Item_string(NULL, /* name will be set in select_item */ - str ? str->ptr() : "", - str ? str->length() : 0, - $1); - if (!$$ || !$$->check_well_formed_result(&$$->str_value, TRUE)) + + Item_string *item_str= + new Item_string(NULL, /* name will be set in select_item */ + str ? str->ptr() : "", + str ? str->length() : 0, + $1); + if (!item_str || + !item_str->check_well_formed_result(&item_str->str_value, TRUE)) { MYSQL_YYABORT; } - ((Item_string *) $$)->set_repertoire_from_value(); + + item_str->set_repertoire_from_value(); + item_str->set_cs_specified(TRUE); + + $$= item_str; } | UNDERSCORE_CHARSET BIN_NUM { @@ -9947,14 +9957,21 @@ literal: String *str= tmp ? tmp->quick_fix_field(), tmp->val_str((String*) 0) : (String*) 0; - $$= new Item_string(NULL, /* name will be set in select_item */ - str ? str->ptr() : "", - str ? str->length() : 0, - $1); - if (!$$ || !$$->check_well_formed_result(&$$->str_value, TRUE)) + + Item_string *item_str= + new Item_string(NULL, /* name will be set in select_item */ + str ? str->ptr() : "", + str ? str->length() : 0, + $1); + if (!item_str || + !item_str->check_well_formed_result(&item_str->str_value, TRUE)) { MYSQL_YYABORT; } + + item_str->set_cs_specified(TRUE); + + $$= item_str; } | DATE_SYM text_literal { $$ = $2; } | TIME_SYM text_literal { $$ = $2; } |