diff options
Diffstat (limited to 'mysql-test/r')
37 files changed, 432 insertions, 19 deletions
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 8c898bae699..113c2930977 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -112,6 +112,11 @@ insert into t1 set i = null; select last_insert_id(); last_insert_id() 255 +explain extended select last_insert_id(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache 255 AS `last_insert_id()` insert into t1 set i = 254; ERROR 23000: Duplicate entry '254' for key 1 select last_insert_id(); diff --git a/mysql-test/r/bench_count_distinct.result b/mysql-test/r/bench_count_distinct.result index d414e8e466e..2b4701389db 100644 --- a/mysql-test/r/bench_count_distinct.result +++ b/mysql-test/r/bench_count_distinct.result @@ -3,4 +3,9 @@ create table t1(n int not null, key(n)) delay_key_write = 1; select count(distinct n) from t1; count(distinct n) 100 +explain extended select count(distinct n) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL n 4 NULL 200 Using index +Warnings: +Note 1003 select high_priority count(distinct test.t1.n) AS `count(distinct n)` from test.t1 drop table t1; diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index 08f148f94c0..4aab3defa2b 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -23,6 +23,11 @@ false select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END one +explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (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` 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 @@ -57,6 +62,11 @@ fcase count(*) 0 2 2 1 3 1 +explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +Warnings: +Note 1003 select high_priority (case test.t1.a when 1 then 2 when 2 then 3 else 0 end) AS `fcase`,count(0) AS `count(*)` from test.t1 group by (case test.t1.a when 1 then 2 when 2 then 3 else 0 end) select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase; fcase count(*) nothing 2 @@ -122,6 +132,14 @@ CREATE TABLE t1 SELECT COALESCE(1), COALESCE(1.0),COALESCE('a'), COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'), COALESCE('a' COLLATE latin1_bin,'b'); +explain extended SELECT +COALESCE(1), COALESCE(1.0),COALESCE('a'), +COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'), +COALESCE('a' COLLATE latin1_bin,'b'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority 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'latin1_bin'),_latin1'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/cast.result b/mysql-test/r/cast.result index d604a17d270..974a79580ae 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -16,6 +16,11 @@ cast(-5 as unsigned) -1 cast(-5 as unsigned) + 1 select ~5, cast(~5 as signed); ~5 cast(~5 as signed) 18446744073709551610 -6 +explain extended select ~5, cast(~5 as signed); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)` select cast(5 as unsigned) -6.0; cast(5 as unsigned) -6.0 -1.0 diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index b865084b409..6a9da97042b 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -521,6 +521,11 @@ character_set_client latin1 SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' latin1 latin1_swedish_ci 3 1 +explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority 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'` SET CHARACTER SET koi8r; SHOW VARIABLES LIKE 'collation_client'; Variable_name Value diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result index ebc84db7115..c465655473d 100644 --- a/mysql-test/r/ctype_many.result +++ b/mysql-test/r/ctype_many.result @@ -340,6 +340,129 @@ CYR CAPIT SOFT SIGN ø ø CYR CAPIT E ü ü CYR CAPIT YU à à CYR CAPIT YA ñ ñ +select CONVERT(koi8_ru_f, 'cp1251_general_ci', 'utf8_general_ci'), comment from t1; +CONVERT(koi8_ru_f, 'cp1251_general_ci', 'utf8_general_ci') comment +a LAT SMALL A +b LAT SMALL B +c LAT SMALL C +d LAT SMALL D +e LAT SMALL E +f LAT SMALL F +g LAT SMALL G +h LAT SMALL H +i LAT SMALL I +j LAT SMALL J +k LAT SMALL K +l LAT SMALL L +m LAT SMALL M +n LAT SMALL N +o LAT SMALL O +p LAT SMALL P +q LAT SMALL Q +r LAT SMALL R +s LAT SMALL S +t LAT SMALL T +u LAT SMALL U +v LAT SMALL V +w LAT SMALL W +x LAT SMALL X +y LAT SMALL Y +z LAT SMALL Z +A LAT CAPIT A +B LAT CAPIT B +C LAT CAPIT C +D LAT CAPIT D +E LAT CAPIT E +F LAT CAPIT F +G LAT CAPIT G +H LAT CAPIT H +I LAT CAPIT I +J LAT CAPIT J +K LAT CAPIT K +L LAT CAPIT L +M LAT CAPIT M +N LAT CAPIT N +O LAT CAPIT O +P LAT CAPIT P +Q LAT CAPIT Q +R LAT CAPIT R +S LAT CAPIT S +T LAT CAPIT T +U LAT CAPIT U +V LAT CAPIT V +W LAT CAPIT W +X LAT CAPIT X +Y LAT CAPIT Y +Z LAT CAPIT Z +â CYR SMALL A +÷ CYR SMALL BE +þ CYR SMALL VE +ú CYR SMALL GE +ä CYR SMALL DE +å CYR SMALL IE +? CYR SMALL IO +ã CYR SMALL ZHE +ÿ CYR SMALL ZE +ê CYR SMALL I +ì CYR SMALL KA +í CYR SMALL EL +î CYR SMALL EM +ï CYR SMALL EN +ð CYR SMALL O +ò CYR SMALL PE +ô CYR SMALL ER +õ CYR SMALL ES +æ CYR SMALL TE +è CYR SMALL U +ö CYR SMALL EF +é CYR SMALL HA +ç CYR SMALL TSE +à CYR SMALL CHE +ù CYR SMALL SHA +ü CYR SMALL SCHA +ñ CYR SMALL HARD SIGN +ý CYR SMALL YERU +û CYR SMALL SOFT SIGN +ø CYR SMALL E +á CYR SMALL YU +ó CYR SMALL YA +Â CYR CAPIT A +× CYR CAPIT BE +Þ CYR CAPIT VE +Ú CYR CAPIT GE +Ä CYR CAPIT DE +Å CYR CAPIT IE +? CYR CAPIT IO +Ã CYR CAPIT ZHE +ß CYR CAPIT ZE +Ê CYR CAPIT I +Ì CYR CAPIT KA +Í CYR CAPIT EL +Î CYR CAPIT EM +Ï CYR CAPIT EN +Ð CYR CAPIT O +Ò CYR CAPIT PE +Ô CYR CAPIT ER +Õ CYR CAPIT ES +Æ CYR CAPIT TE +È CYR CAPIT U +Ö CYR CAPIT EF +É CYR CAPIT HA +Ç CYR CAPIT TSE +À CYR CAPIT CHE +Ù CYR CAPIT SHA +Ü CYR CAPIT SCHA +Ñ CYR CAPIT HARD SIGN +Ý CYR CAPIT YERU +Û CYR CAPIT SOFT SIGN +Ø CYR CAPIT E +Á CYR CAPIT YU +Ó CYR CAPIT YA +explain extended select CONVERT(koi8_ru_f, 'cp1251_general_ci', 'utf8_general_ci'), comment from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 116 +Warnings: +Note 1003 select high_priority convert(test.t1.koi8_ru_f,_latin1'utf8_general_ci',_latin1'cp1251_general_ci') AS `CONVERT(koi8_ru_f, 'cp1251_general_ci', 'utf8_general_ci')`,test.t1.comment AS `comment` from test.t1 ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL; UPDATE t1 SET bin_f=koi8_ru_f; SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 1f07de17b14..1aef43cd570 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -47,6 +47,9 @@ locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin) select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin); locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin) 0 +select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1); +length(_utf8 0xD0B1) bit_length(_utf8 0xD0B1) char_length(_utf8 0xD0B1) +2 16 1 select 'a' like 'a'; 'a' like 'a' 1 diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 590a1d6904b..e21e05b4dfe 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -155,3 +155,8 @@ a select get_format(DATETIME, 'eur') as a; a %Y-%m-%d-%H.%i.%s +explain extended select makedate(1997,1), 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"),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), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority makedate(1997,1) AS `makedate(1997,1)`,addtime(_latin1'31.12.97 11.59.59.999999 PM',_latin1'31.12.97 11.59.59.999999 PM') 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'31.12.97 11.59.59.999999 PM') 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")` diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index e2d35973383..e3993b89b23 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -9,6 +9,11 @@ select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Only MyISAM tables support collections Full-text indexes are called collections +explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 fulltext a a 0 1 Using where +Warnings: +Note 1003 select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 where (match test.t1.a,test.t1.b against (_latin1'collections')) select * from t1 where MATCH(a,b) AGAINST ("indexes"); a b Full-text indexes are called collections @@ -56,6 +61,11 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); a b MySQL has now support for full-text search +explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 fulltext a a 0 1 Using where +Warnings: +Note 1003 select high_priority 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)) 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_compress.result b/mysql-test/r/func_compress.result index 85842f28e64..0e46f7dd16d 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -7,9 +7,19 @@ length(@test_compress_string) select uncompress(compress(@test_compress_string)); uncompress(compress(@test_compress_string)) string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +explain extended select uncompress(compress(@test_compress_string)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache uncompress(compress((@test_compress_string))) AS `uncompress(compress(@test_compress_string))` select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string); uncompressed_length(compress(@test_compress_string))=length(@test_compress_string) 1 +explain extended select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache (uncompressed_length(compress((@test_compress_string))) = length((@test_compress_string))) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)` select uncompressed_length(compress(@test_compress_string)); uncompressed_length(compress(@test_compress_string)) 117 diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 5eae6b4b871..af6f7956fe3 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -14,6 +14,11 @@ grp group_concat(c) 1 a 2 b,c 3 E,C,D,d,d,D +explain extended select grp,group_concat(c) from t1 group by grp; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort +Warnings: +Note 1003 select high_priority test.t1.grp AS `grp`,group_concat(test.t1.c seperator ',') AS `group_concat(c)` from test.t1 group by test.t1.grp select grp,group_concat(a,c) from t1 group by grp; grp group_concat(a,c) 1 1a @@ -79,6 +84,11 @@ grp group_concat(distinct c order by c desc) 1 a 2 c,b 3 E,D,C +explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort +Warnings: +Note 1003 select high_priority test.t1.grp AS `grp`,group_concat(distinct test.t1.c order by test.t1.c seperator ',') AS `group_concat(distinct c order by c desc)` from test.t1 group by test.t1.grp select grp,group_concat(c order by c separator ",") from t1 group by grp; grp group_concat(c order by c separator ",") 1 a @@ -94,6 +104,11 @@ grp group_concat(distinct c order by c separator ",") 1 a 2 b,c 3 C,D,E +explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort +Warnings: +Note 1003 select high_priority test.t1.grp AS `grp`,group_concat(distinct test.t1.c order by test.t1.c seperator ',') AS `group_concat(distinct c order by c separator ",")` from test.t1 group by test.t1.grp select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp; grp group_concat(distinct c order by c desc separator ",") 1 a diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 8b7581c3cc8..8977652fb6a 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -597,11 +597,16 @@ a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 1 0 NULL NULL NULL NULL NULL 0 0 2 1 1 1.0000 0.0000 1 1 0 1 3 1 1 1.0000 0.0000 1 1 1 1 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 -2 1 1 1.0000 0.0000 1 1 1 1 -3 1 1 1.0000 0.0000 1 1 1 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) bit_xor(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 0 +2 1 1 1.0000 0.0000 1 1 1 1 1 +3 1 1 1.0000 0.0000 1 1 1 1 1 +explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort +Warnings: +Note 1003 select high_priority big_result test.t1.a AS `a`,count(test.t1.b) AS `count(b)`,sum(test.t1.b) AS `sum(b)`,avg(test.t1.b) AS `avg(b)`,std(test.t1.b) AS `std(b)`,min(test.t1.b) AS `min(b)`,max(test.t1.b) AS `max(b)`,bit_and(test.t1.b) AS `bit_and(b)`,bit_or(test.t1.b) AS `bit_or(b)`,bit_xor(test.t1.b) AS `bit_xor(b)` from test.t1 group by test.t1.a drop table t1; create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) type=InnoDB; insert into t1 values (1, 3); diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 0ab41258091..64070179395 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -39,6 +39,25 @@ a a aa aaa +explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using filesort +Warnings: +Note 1003 select high_priority if((test.t1.u = 1),test.t1.st,(test.t1.st collate _latin1'BINARY')) AS `s` from test.t1 where (test.t1.st like _latin1'%a%') order by if((test.t1.u = 1),test.t1.st,(test.t1.st collate _latin1'BINARY')) +select nullif(u=0, 'test') from t1; +nullif(u=0, 'test') +NULL +NULL +NULL +NULL +NULL +1 +1 +explain extended select nullif(u=0, 'test') from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 +Warnings: +Note 1003 select high_priority nullif((test.t1.u = 0),_latin1'test') AS `nullif(u=0, 'test')` from test.t1 drop table t1; create table t1 (num double(12,2)); insert into t1 values (144.54); diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index a197371b345..a13c3f50265 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -142,6 +142,11 @@ a c c select * from t1 where 'a' in (a,b,c collate latin1_bin); a b c a c c +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 Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Note 1003 select high_priority 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'latin1_bin'))) drop table t1; select '1.0' in (1,2); '1.0' in (1,2) diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index d6596e51db4..cdf21250e6d 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -16,6 +16,11 @@ round(5.64,1) round(5.64,2) round(5.64,-1) round(5.64,-2) select abs(-10), sign(-5), sign(5), sign(0); abs(-10) sign(-5) sign(5) sign(0) 10 -1 1 0 +explain extended select abs(-10), sign(-5), sign(5), sign(0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority abs(-(10)) AS `abs(-10)`,sign(-(5)) AS `sign(-5)`,sign(5) AS `sign(5)`,sign(0) AS `sign(0)` select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2); log(exp(10)) exp(log(sqrt(10))*2) log(-1) log(NULL) log(1,1) log(3,9) log(-1,2) log(NULL,2) 10.000000 10.000000 NULL NULL NULL 2.000000 NULL NULL diff --git a/mysql-test/r/func_op.result b/mysql-test/r/func_op.result index 9f49b5809df..25a7ac20b66 100644 --- a/mysql-test/r/func_op.result +++ b/mysql-test/r/func_op.result @@ -1,9 +1,19 @@ select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2; 1+1 1-1 1+1*2 8/5 8%5 mod(8,5) mod(8,5)|0 -(1+1)*-2 2 0 3 1.60 3 3 3 4 +explain extended select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (1 + 1) AS `1+1`,(1 - 1) AS `1-1`,(1 + (1 * 2)) AS `1+1*2`,(8 / 5) AS `8/5`,(8 % 5) AS `8%5`,(8 % 5) AS `mod(8,5)`,((8 % 5) | 0) AS `mod(8,5)|0`,(-((1 + 1)) * -(2)) AS `-(1+1)*-2` select 1 | (1+1),5 & 3,bit_count(7) ; 1 | (1+1) 5 & 3 bit_count(7) 3 1 3 +explain extended select 1 | (1+1),5 & 3,bit_count(7) ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (1 | (1 + 1)) AS `1 | (1+1)`,(5 & 3) AS `5 & 3`,bit_count(7) AS `bit_count(7)` select 1 << 32,1 << 63, 1 << 64, 4 >> 2, 4 >> 63, 1<< 63 >> 60; 1 << 32 1 << 63 1 << 64 4 >> 2 4 >> 63 1<< 63 >> 60 4294967296 9223372036854775808 0 1 0 8 diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result index 35742136ee6..323642cab8c 100644 --- a/mysql-test/r/func_regexp.result +++ b/mysql-test/r/func_regexp.result @@ -36,6 +36,11 @@ insert into t1 (xxx) values('this is a test of some long text to see what happen 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 +explain extended select * from t1 where xxx regexp('is a test of some long text to'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +Warnings: +Note 1003 select high_priority test.t1.xxx AS `xxx` from test.t1 where (test.t1.xxx regexp _latin1'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 86608e7c247..2a3631140b0 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -1,6 +1,11 @@ select interval(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0; interval(55,10,20,30,40,50,60,70,80,90,100) interval(3,1,1+1,1+1+1+1) field("IBM","NCA","ICL","SUN","IBM","DIGITAL") field("A","B","C") elt(2,"ONE","TWO","THREE") interval(0,1,2,3,4) elt(1,1,2,3)|0 elt(1,1.1,1.2,1.3)+0 5 2 4 0 TWO 0 1 1.1 +explain extended select INTERVAL(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority 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` select find_in_set("b","a,b,c"),find_in_set("c","a,b,c"),find_in_set("dd","a,bbb,dd"),find_in_set("bbb","a,bbb,dd"); find_in_set("b","a,b,c") find_in_set("c","a,b,c") find_in_set("dd","a,bbb,dd") find_in_set("bbb","a,bbb,dd") 2 3 3 2 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index ac25277351c..1a4e2fa498b 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -12,9 +12,15 @@ length('\n\t\r\b\0\_\%\\') select bit_length('\n\t\r\b\0\_\%\\'); bit_length('\n\t\r\b\0\_\%\\') 80 -select concat('monty',' was here ','again'),length('hello'),char(ascii('h')); -concat('monty',' was here ','again') length('hello') char(ascii('h')) -monty was here again 5 h +select char_length('\n\t\r\b\0\_\%\\'); +char_length('\n\t\r\b\0\_\%\\') +10 +select length(_latin1'\n\t\n\b\0\\_\\%\\'); +length(_latin1'\n\t\n\b\0\\_\\%\\') +10 +select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'); +concat('monty',' was here ','again') length('hello') char(ascii('h')) ord('h') +monty was here again 5 h 104 select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ; locate('he','hello') locate('he','hello',2) locate('lo','hello',2) 1 0 4 @@ -99,6 +105,9 @@ NULL select md5('hello'); md5('hello') 5d41402abc4b2a76b9719d911017c592 +select crc32("123"); +crc32("123") +2286445522 select sha('abc'); sha('abc') a9993e364706816aba3e25717850c26c9cd0d89d @@ -579,3 +588,8 @@ CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) NULL </a>.......................... DROP TABLE t1; +explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'), concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,(_latin1'HE' collate _latin1'BINARY') AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n\t\r\b\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n\t\r\b\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n\t\r\b\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")` diff --git a/mysql-test/r/func_system.result b/mysql-test/r/func_system.result index a52d5613c04..9d370b43099 100644 --- a/mysql-test/r/func_system.result +++ b/mysql-test/r/func_system.result @@ -37,6 +37,11 @@ version()>=_latin1"3.23.29" select charset(version()); charset(version()) utf8 +explain extended select database(), user(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache database() AS `database()`,user() AS `user()` create table t1 (version char(40)) select database(), user(), version() as 'version'; show create table t1; Table Create Table diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 851c308ba3e..4951d954abb 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -44,19 +44,47 @@ select -1.49 or -1.49,0.6 or 0.6; select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; 3 ^ 11 1 ^ 1 1 ^ 0 1 ^ NULL NULL ^ 1 8 0 1 NULL NULL +explain extended select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (3 ^ 11) AS `3 ^ 11`,(1 ^ 1) AS `1 ^ 1`,(1 ^ 0) AS `1 ^ 0`,(1 ^ NULL) AS `1 ^ NULL`,(NULL ^ 1) AS `NULL ^ 1` select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; 1 XOR 1 1 XOR 0 0 XOR 1 0 XOR 0 NULL XOR 1 1 XOR NULL 0 XOR NULL 0 1 1 0 NULL NULL NULL select 10 % 7, 10 mod 7, 10 div 3; 10 % 7 10 mod 7 10 div 3 3 3 3 +explain extended select 10 % 7, 10 mod 7, 10 div 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (10 % 7) AS `10 % 7`,(10 % 7) AS `10 mod 7`,(10 DIV 3) AS `10 div 3` select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2; (1 << 64)-1 ((1 << 64)-1) DIV 1 ((1 << 64)-1) DIV 2 18446744073709551615 18446744073709551615 9223372036854775807 +explain extended select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority ((1 << 64) - 1) AS `(1 << 64)-1`,(((1 << 64) - 1) DIV 1) AS `((1 << 64)-1) DIV 1`,(((1 << 64) - 1) DIV 2) AS `((1 << 64)-1) DIV 2` create table t1 (a int); insert t1 values (1); select * from t1 where 1 xor 1; a +explain extended select * from t1 where 1 xor 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select high_priority test.t1.a AS `a` from test.t1 where (1 xor 1) +select - a from t1; +- a +-1 +explain extended select - a from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +Warnings: +Note 1003 select high_priority -(test.t1.a) AS `- a` from test.t1 drop table t1; select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1; 5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1 @@ -73,6 +101,11 @@ _koi8r'a' = _koi8r'A' select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci; _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci 1 +explain extended select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (_koi8r'a' = (_koi8r'A' collate _latin1'koi8r_general_ci')) AS `_koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci` select _koi8r'a' = _koi8r'A' COLLATE koi8r_bin; _koi8r'a' = _koi8r'A' COLLATE koi8r_bin 0 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b8709487c6d..9d38083f48a 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -486,3 +486,8 @@ strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0 select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0; strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0 1 +explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),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")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache 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(to_days(curdate())) - weekday(to_days(now()))) AS `weekday(curdate())-weekday(now())`,dayname(to_days(_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)` diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index c286b4d8fc4..b9eaa81ba2a 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -284,9 +284,11 @@ select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; spid count(*) 2 2 1 1 -explain select sql_big_result spid,sum(userid) from t1 group by spid desc; +explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort +Warnings: +Note 1003 select high_priority big_result test.t1.spID AS `spid`,sum(test.t1.userID) AS `sum(userid)` from test.t1 group by test.t1.spID desc explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index fc0b3c652ef..7c88776579b 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -8,6 +8,11 @@ b select count(a) as b from t1 where a=0 having b >=0; b 0 +explain extended select count(a) as b from t1 where a=0 having b >=0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select high_priority count(test.t1.a) AS `b` from test.t1 where (test.t1.a = 0) having (count(test.t1.a) >= 0) drop table t1; CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 4ef25781331..3a7679ce1e3 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -56,4 +56,14 @@ a b c VALUES(a) 5 0 30 NULL 8 9 60 NULL 2 1 11 NULL +explain extended SELECT *, VALUES(a) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +Warnings: +Note 1003 select high_priority test.t1.a AS `a`,test.t1.b AS `b`,test.t1.c AS `c`,values(test.t1.a) AS `VALUES(a)` from test.t1 +explain extended select * from t1 where values(a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select high_priority test.t1.a AS `a`,test.t1.b AS `b`,test.t1.c AS `c` from test.t1 DROP TABLE t1; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 2ef1f0c86f9..9de9fdce2db 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -2,6 +2,11 @@ drop table if exists t1; select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; NULL NULL isnull(null) isnull(1/0) isnull(1/0 = null) ifnull(null,1) ifnull(null,"TRUE") ifnull("TRUE","ERROR") 1/0 is null 1 is not null NULL NULL 1 1 1 1 TRUE TRUE 1 1 +explain extended select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority 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` select 1 | NULL,1 & NULL,1+NULL,1-NULL; 1 | NULL 1 & NULL 1+NULL 1-NULL NULL NULL NULL NULL @@ -23,6 +28,11 @@ field(NULL,"a","b","c") select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null; 2 between null and 1 2 between 3 AND NULL NULL between 1 and 2 2 between NULL and 3 2 between 1 AND null 0 0 NULL NULL NULL +explain extended select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority (2 between NULL and 1) AS `2 between null and 1`,(2 between 3 and NULL) AS `2 between 3 AND NULL`,(NULL between 1 and 2) AS `NULL between 1 and 2`,(2 between NULL and 3) AS `2 between NULL and 3`,(2 between 1 and NULL) AS `2 between 1 AND null` SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0; NULL AND NULL 1 AND NULL NULL AND 1 NULL OR NULL 0 OR NULL NULL OR 0 NULL NULL NULL NULL NULL NULL @@ -35,6 +45,11 @@ NULL AND 0 0 and NULL select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); inet_ntoa(null) inet_aton(null) inet_aton("122.256") inet_aton("122.226.") inet_aton("") NULL NULL NULL NULL NULL +explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority 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("")` create table t1 (x int); insert into t1 values (null); select * from t1 where x != 0; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 84e37bf56a9..37e68d8b13e 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -81,9 +81,11 @@ TV 2 2000 200 TV 2 NULL 200 TV NULL NULL 600 NULL NULL NULL 7785 -explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup; +explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using temporary; Using filesort +Warnings: +Note 1003 select high_priority test.t1.product AS `product`,test.t1.country_id AS `country_id`,test.t1.year AS `year`,sum(test.t1.profit) AS `sum(profit)` from test.t1 group by test.t1.product,test.t1.country_id,test.t1.year with rollup select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup; product country_id sum(profit) TV 1 400 diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 1a1eec1f915..b446d30c83f 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -300,6 +300,11 @@ select USER() from t1; USER() select benchmark(1,1) from t1; benchmark(1,1) +explain extended select benchmark(1,1) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +Warnings: +Note 1003 select high_priority no_cache benchmark(1,1) AS `benchmark(1,1)` from test.t1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index ab60f714d82..37d4f1d9b26 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -40,6 +40,11 @@ ERROR 21000: Operand should contain 2 column(s) select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))) NULL +explain extended select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority ((1,2,(3,4)) in ((3,2,(3,4)),(1,2,(3,NULL)))) AS `row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)))` SELECT (1,2,3)=(0,NULL,3); (1,2,3)=(0,NULL,3) 0 diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result index 79438f8fa40..b8071b16c2e 100644 --- a/mysql-test/r/rpl000001.result +++ b/mysql-test/r/rpl000001.result @@ -40,10 +40,20 @@ create table t1(n int); select get_lock("hold_slave",10); get_lock("hold_slave",10) 1 +explain extended select get_lock("hold_slave",10); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache get_lock(_latin1'hold_slave',10) AS `get_lock("hold_slave",10)` start slave; select release_lock("hold_slave"); release_lock("hold_slave") 1 +explain extended select release_lock("hold_slave"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache release_lock(_latin1'hold_slave') AS `release_lock("hold_slave")` unlock tables; create table t2(id int); insert into t2 values(connection_id()); diff --git a/mysql-test/r/rpl_get_lock.result b/mysql-test/r/rpl_get_lock.result index 369fde7ef8f..8e3e335b2d0 100644 --- a/mysql-test/r/rpl_get_lock.result +++ b/mysql-test/r/rpl_get_lock.result @@ -18,9 +18,14 @@ get_lock("lock",3) select * from t1; n 1 -select is_free_lock("lock"); -is_free_lock("lock") -0 +select is_free_lock("lock"), is_used_lock("lock"); +is_free_lock("lock") is_used_lock("lock") +0 6 +explain extended select is_free_lock("lock"), is_used_lock("lock"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache is_free_lock(_latin1'lock') AS `is_free_lock("lock")`,is_used_lock(_latin1'lock') AS `is_used_lock("lock")` select is_free_lock("lock2"); is_free_lock("lock2") 1 diff --git a/mysql-test/r/rpl_master_pos_wait.result b/mysql-test/r/rpl_master_pos_wait.result index bbd125a98d6..ea917805560 100644 --- a/mysql-test/r/rpl_master_pos_wait.result +++ b/mysql-test/r/rpl_master_pos_wait.result @@ -7,6 +7,11 @@ start slave; select master_pos_wait('master-bin.999999',0,2); master_pos_wait('master-bin.999999',0,2) -1 +explain extended select master_pos_wait('master-bin.999999',0,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache master_pos_wait(_latin1'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/r/select.result b/mysql-test/r/select.result index fca4a0549d3..b9218942a5e 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1466,6 +1466,11 @@ companynr count(*) select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 +explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +Warnings: +Note 1003 select high_priority 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'')) 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 96defcfa79b..048464e6b84 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -199,7 +199,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DERIVED t2 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort Warnings: -Note 1003 select high_priority (select test.t3.a AS `a` from test.t3 where (test.t3.a < 8) order by test.t3.a desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,t2.a AS `a` from (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.a > 1)) tt +Note 1003 select high_priority (select test.t3.a AS `a` from test.t3 where (test.t3.a < 8) order by test.t3.a desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,tt.a AS `a` from (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.a > 1)) tt select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -1013,7 +1013,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found 2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select high_priority no_cache (select no_cache benchmark(1) AS `BENCHMARK(1,1)` from test.t1) AS `(SELECT BENCHMARK(1,1) FROM t1)` from test.t1 +Note 1003 select high_priority no_cache (select no_cache benchmark(1,1) AS `BENCHMARK(1,1)` from test.t1) AS `(SELECT BENCHMARK(1,1) FROM t1)` from test.t1 drop table t1; CREATE TABLE `t1` ( `mot` varchar(30) character set latin1 NOT NULL default '', @@ -1486,16 +1486,20 @@ explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index -explain select s1, s1 = ANY (SELECT s1 FROM t2) from t1; +Warnings: +Note 1003 select high_priority test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from test.t1 +explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index -explain select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; +Warnings: +Note 1003 select high_priority test.t1.s1 AS `s1`,<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from test.t1 +explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index Warnings: -Note 1003 select high_priority test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from test.t1 +Note 1003 select high_priority test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from test.t1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index f12be1d363c..82aefa78bb2 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -492,6 +492,14 @@ collation(load_file('../../std_data/words.dat')), coercibility(load_file('../../std_data/words.dat')); charset(load_file('../../std_data/words.dat')) collation(load_file('../../std_data/words.dat')) coercibility(load_file('../../std_data/words.dat')) binary binary 3 +explain extended select +charset(load_file('../../std_data/words.dat')), +collation(load_file('../../std_data/words.dat')), +coercibility(load_file('../../std_data/words.dat')); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache charset(load_file(_latin1'../../std_data/words.dat')) AS `charset(load_file('../../std_data/words.dat'))`,collation(load_file(_latin1'../../std_data/words.dat')) AS `collation(load_file('../../std_data/words.dat'))`,coercibility(load_file(_latin1'../../std_data/words.dat')) AS `coercibility(load_file('../../std_data/words.dat'))` update t1 set imagem=load_file('../../std_data/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/varbinary.result b/mysql-test/r/varbinary.result index 5464d741f70..cf001158ae2 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -11,9 +11,11 @@ x'31' X'ffff'+0 create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) ); insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; -explain select * from t1 where UNIQ=0x38afba1d73e6a18a; +explain extended select * from t1 where UNIQ=0x38afba1d73e6a18a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const UNIQ UNIQ 8 const 1 +Warnings: +Note 1003 select high_priority test.t1.ID AS `ID`,test.t1.UNIQ AS `UNIQ` from test.t1 where (test.t1.UNIQ = 4084688022709641610) drop table t1; select x'hello'; ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'hello'' at line 1 diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 52ea28e6076..90654bece2e 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -22,6 +22,11 @@ NULL NULL NULL NULL select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; @t1:=(@t2:=1)+@t3:=4 @t1 @t2 @t3 5 5 1 4 +explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3` select @t5; @t5 1.23456 @@ -76,9 +81,19 @@ select @@VERSION=version(); select last_insert_id(345); last_insert_id(345) 345 +explain extended select last_insert_id(345); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache last_insert_id(345) AS `last_insert_id(345)` select @@IDENTITY,last_insert_id(), @@identity; @@IDENTITY last_insert_id() @@identity 345 345 345 +explain extended select @@IDENTITY,last_insert_id(), @@identity; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache 345 AS `@@IDENTITY`,345 AS `last_insert_id()`,345 AS `@@identity` set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; set global concurrent_insert=ON; show variables like 'concurrent_insert'; |