diff options
| author | unknown <anozdrin/alik@quad.> | 2008-02-12 22:09:16 +0300 |
|---|---|---|
| committer | unknown <anozdrin/alik@quad.> | 2008-02-12 22:09:16 +0300 |
| commit | c1d0dd94bb4734f0a441c589b6fc9fa25d2fa768 (patch) | |
| tree | d07b7e82d1b8efe31aa691c59c358479c0f246fb /mysql-test/r | |
| parent | 84f227dffe386ba886040e440cb998508fc9d84d (diff) | |
| download | mariadb-git-c1d0dd94bb4734f0a441c589b6fc9fa25d2fa768.tar.gz | |
Fix for Bug#32538: View definition picks up character set,
but not collation.
The problem here was that text literals in a view were always
dumped with character set introducer. That lead to loosing
collation information.
The fix is to dump character set introducer only if it was
in the original query. That is now possible because there
is no problem any more of loss of character set of string
literals in views -- after WL#4052 the view is dumped
in the original character set.
mysql-test/r/case.result:
Update result file.
mysql-test/r/compress.result:
Update result file.
mysql-test/r/ctype_collate.result:
Update result file.
mysql-test/r/date_formats.result:
Update result file.
mysql-test/r/ddl_i18n_koi8r.result:
Update result file.
mysql-test/r/ddl_i18n_utf8.result:
Update result file.
mysql-test/r/fulltext.result:
Update result file.
mysql-test/r/func_crypt.result:
Update result file.
mysql-test/r/func_encrypt.result:
Update result file.
mysql-test/r/func_if.result:
Update result file.
mysql-test/r/func_in.result:
Update result file.
mysql-test/r/func_like.result:
Update result file.
mysql-test/r/func_regexp.result:
Update result file.
mysql-test/r/func_set.result:
Update result file.
mysql-test/r/func_str.result:
Update result file.
mysql-test/r/func_time.result:
Update result file.
mysql-test/r/gis.result:
Update result file.
mysql-test/r/group_min_max.result:
Update result file.
mysql-test/r/mysqldump.result:
Update result file.
mysql-test/r/negation_elimination.result:
Update result file.
mysql-test/r/null.result:
Update result file.
mysql-test/r/select.result:
Update result file.
mysql-test/r/show_check.result:
Update result file.
mysql-test/r/sp-code.result:
Update result file.
mysql-test/r/ssl.result:
Update result file.
mysql-test/r/ssl_compress.result:
Update result file.
mysql-test/r/subselect.result:
Update result file.
mysql-test/r/temp_table.result:
Update result file.
mysql-test/r/type_blob.result:
Update result file.
mysql-test/r/view.result:
Update result file.
mysql-test/suite/binlog/r/binlog_stm_blackhole.result:
Update result file.
mysql-test/suite/rpl/r/rpl_get_lock.result:
Update result file.
mysql-test/suite/rpl/r/rpl_master_pos_wait.result:
Update result file.
mysql-test/t/view.test:
Add a test case for Bug#32538.
sql/item.cc:
Do not dump character set introducer if it was not specified
explicitly in the original query.
sql/item.h:
Add 'cs_specified' property to Item_string.
sql/sql_yacc.yy:
Set Item_string::cs_specified property to TRUE
when character set introducer is explicitly specified.
Diffstat (limited to 'mysql-test/r')
30 files changed, 186 insertions, 140 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. +# ----------------------------------------------------------------- |