diff options
Diffstat (limited to 'mysql-test')
109 files changed, 1814 insertions, 370 deletions
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 94affe6b2f7..46d6819b819 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -337,13 +337,23 @@ while test $# -gt 0; do EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --gdb" EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --gdb" ;; - --valgrind) - VALGRIND="valgrind --alignment=8 --leak-check=yes --num-callers=16" + --valgrind | --valgrind-all) + VALGRIND=`which valgrind` # this will print an error if not found + # Give good warning to the user and stop + if [ -z "$VALGRIND" ] ; then + $ECHO "You need to have the 'valgrind' program in your PATH to run mysql-test-run with option --valgrind. Valgrind's home page is http://developer.kde.org/~sewardj ." + exit 1 + fi + VALGRIND="$VALGRIND --alignment=8 --leak-check=yes --num-callers=16" EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --skip-safemalloc --skip-bdb" EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --skip-safemalloc --skip-bdb" SLEEP_TIME_AFTER_RESTART=10 SLEEP_TIME_FOR_DELETE=60 USE_RUNNING_SERVER="" + if test "$1" = "--valgrind-all" + then + VALGRIND="$VALGRIND -v --show-reachable=yes" + fi ;; --valgrind-options=*) TMP=`$ECHO "$1" | $SED -e "s;--valgrind-options=;;"` @@ -394,7 +404,7 @@ SLAVE_MYLOG="$MYSQL_TEST_DIR/var/log/slave.log" SLAVE_MYERR="$MYSQL_TEST_DIR/var/log/slave.err" CURRENT_TEST="$MYSQL_TEST_DIR/var/log/current_test" -SMALL_SERVER="-O key_buffer_size=1M -O sort_buffer=256K -O max_heap_table_size=1M" +SMALL_SERVER="--key_buffer_size=1M --sort_buffer=256K --max_heap_table_size=1M" export MASTER_MYPORT export SLAVE_MYPORT @@ -795,13 +805,13 @@ manager_launch() ident=$1 shift if [ $USE_MANAGER = 0 ] ; then - $@ >> $CUR_MYERR 2>&1 & + $@ >> $CUR_MYERR 2>&1 & sleep 2 #hack return fi $MYSQL_MANAGER_CLIENT $MANAGER_QUIET_OPT --user=$MYSQL_MANAGER_USER \ --password=$MYSQL_MANAGER_PW --port=$MYSQL_MANAGER_PORT <<EOF -def_exec $ident $@ +def_exec $ident "$@" set_exec_stdout $ident $CUR_MYERR set_exec_stderr $ident $CUR_MYERR set_exec_con $ident root localhost $CUR_MYSOCK @@ -1163,7 +1173,7 @@ run_testcase () echo $tname > $CURRENT_TEST SKIP_SLAVE=`$EXPR \( $tname : rpl \) = 0` if [ $USE_MANAGER = 1 ] ; then - many_slaves=`$EXPR \( $tname : rpl_failsafe \) != 0` + many_slaves=`$EXPR \( \( $tname : rpl_failsafe \) != 0 \) \| \( \( $tname : rpl_chain_temp_table \) != 0 \)` fi if [ -n "$SKIP_TEST" ] ; then 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_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 3db2f3d5519..07ea97258d9 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -219,6 +219,13 @@ a test drop table t1; create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `word` varchar(255) collate latin1_german2_ci NOT NULL default '', + `word2` varchar(255) collate latin1_german2_ci NOT NULL default '', + KEY `word` (`word`) +) TYPE=MyISAM CHARSET=latin1 COLLATE=latin1_german2_ci insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); update t1 set word2=word; select word, word=binary 0xdf as t from t1 having t > 0; @@ -273,6 +280,11 @@ drop table t1; CREATE TABLE t1 ( s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci ); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s1` char(5) collate latin1_german2_ci default NULL +) TYPE=MyISAM CHARSET=latin1 COLLATE=latin1_german2_ci INSERT INTO t1 VALUES ('Ü'); INSERT INTO t1 VALUES ('ue'); SELECT DISTINCT s1 FROM t1; 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_recoding.result b/mysql-test/r/ctype_recoding.result index 5a53c1db2e1..1fe07692290 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -1,5 +1,5 @@ SET CHARACTER SET koi8r; -DROP TABLE IF EXISTS ÔÁÂÌÉÃÁ; +DROP TABLE IF EXISTS ÔÁÂÌÉÃÁ, t1; SET CHARACTER SET koi8r; CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp1251) SELECT _koi8r'ÐÒÏÂÁ' AS a; SHOW CREATE TABLE t1; @@ -14,7 +14,7 @@ SELECT HEX(a) FROM t1; HEX(a) EFF0EEE1E0 DROP TABLE t1; -CREATE TABLE ÔÁÂÌÉÃÁ +CREATE TABLE `ÔÁÂÌÉÃÁ` ( ÐÏÌÅ CHAR(32) CHARACTER SET koi8r NOT NULL COMMENT "ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ" ) COMMENT "ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ"; 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..346d086f34c 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -1,151 +1,315 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 't1' SHOW GLOBAL VARIABLES LIKE "%_format%"; Variable_name Value date_format %d.%m.%Y -datetime_format %Y/%d/%m-%H:%i:%s +datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 time_format %H.%i.%s SHOW SESSION VARIABLES LIKE "%_format%"; Variable_name Value date_format %d.%m.%Y -datetime_format %Y/%d/%m-%H:%i:%s +datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 time_format %H.%i.%s -SET date_format="%d.%m.%Y"; -select CAST("01.01.2001" as DATE) as a; -a -01.01.2001 -SET datetime_format="%d.%m.%Y %H.%i.%s"; -select CAST("01.01.2001 05.12.06" as DATETIME) as a; -a -01.01.2001 05.12.06 -SET time_format="%H.%i.%s"; -select CAST("05.12.06" as TIME) as a; -a -05.12.06 -SET datetime_format="%d.%m.%Y %h:%i:%s %p"; -select CAST("01.01.2001 05:12:06AM" as DATETIME) as a; -a -01.01.2001 05:12:06 AM -select CAST("01.01.2001 05:12:06 PM" as DATETIME) as a; -a -01.01.2001 05:12:06 PM -SET time_format="%h:%i:%s %p"; -select CAST("05:12:06 AM" as TIME) as a; -a -05:12:06 AM -select CAST("05:12:06.1234PM" as TIME) as a; -a -05:12:06.001234 PM -SET time_format="%h.%i.%s %p"; -SET date_format='%d.%m.%y'; -SET datetime_format="%d.%m.%y %h.%i.%s %p"; -select CAST("12-12-06" as DATE) as a; -a -12.12.06 -select adddate("01.01.97 11.59.59.000001 PM", 10); -adddate("01.01.97 11.59.59.000001 PM", 10) -11.01.97 11.59.59.000001 PM -select datediff("31.12.97 11.59:59.000001 PM","01.01.98"); -datediff("31.12.97 11.59:59.000001 PM","01.01.98") --1 -select weekofyear("31.11.97 11:59:59.000001 PM"); -weekofyear("31.11.97 11:59:59.000001 PM") -49 -select makedate(1997,1); -makedate(1997,1) -01.01.97 -select addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"); -addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002") -02.01.98 01.01.01.000001 AM -select maketime(23,11,12); -maketime(23,11,12) -11.11.12 PM -select timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"); -timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM") -8795.59.59.999999 PM -SET time_format="%H%i%s"; -SET time_format="%h%i%s"; -ERROR HY000: Unknown error +SET time_format='%H%i%s'; +SET time_format='%H:%i:%s.%f'; +SET time_format='%h-%i-%s.%f%p'; +SET time_format='%h:%i:%s.%f %p'; +SET time_format='%h:%i:%s%p'; +SET date_format='%Y%m%d'; +SET date_format='%Y.%m.%d'; +SET date_format='%d.%m.%Y'; +SET date_format='%m-%d-%Y'; +set datetime_format= '%Y%m%d%H%i%s'; +set datetime_format= '%Y-%m-%d %H:%i:%s'; +set datetime_format= '%m-%d-%y %H:%i:%s.%f'; +set datetime_format= '%d-%m-%Y %h:%i:%s%p'; +set datetime_format= '%H:%i:%s %Y-%m-%d'; +set datetime_format= '%H:%i:%s.%f %m-%d-%Y'; +set datetime_format= '%h:%i:%s %p %Y-%m-%d'; +set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d'; +SHOW SESSION VARIABLES LIKE "%format"; +Variable_name Value +date_format %m-%d-%Y +datetime_format %h:%i:%s.%f %p %Y-%m-%d +default_week_format 0 +time_format %h:%i:%s%p +SET time_format='%h:%i:%s'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s' +SET time_format='%H %i:%s'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H %i:%s' +SET time_format='%H::%i:%s'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H::%i:%s' +SET time_format='%H:%i:%s%f'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%f' +SET time_format='%H:%i.%f:%s'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i.%f:%s' +SET time_format='%H:%i:%s%p'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%p' +SET time_format='%h:%i:%s.%f %p %Y-%m-%d'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s.%f %p %Y-%m-%d' +SET time_format='%H%i%s.%f'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H%i%s.%f' +SET time_format='%H:%i-%s.%f'; +ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i-%s.%f' SET date_format='%d.%m.%d'; -ERROR HY000: Unknown error -SET datetime_format="%d.%m.%y %h.%i.%s"; -ERROR HY000: Unknown error +ERROR 42000: Variable 'date_format' can't be set to the value of '%d.%m.%d' +SET datetime_format='%h.%m.%y %d.%i.%s'; +ERROR 42000: Variable 'datetime_format' can't be set to the value of '%h.%m.%y %d.%i.%s' +set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d'; +ERROR 42000: Variable 'datetime_format' can't be set to the value of '%H:%i:%s.%f %p %Y-%m-%d' +set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d'; +SET SESSION datetime_format=default; +select @@global.datetime_format, @@session.datetime_format; +@@global.datetime_format @@session.datetime_format +%H:%i:%s %Y-%m-%d %H:%i:%s %Y-%m-%d +SET GLOBAL datetime_format=default; +SET SESSION datetime_format=default; +select @@global.datetime_format, @@session.datetime_format; +@@global.datetime_format @@session.datetime_format +%Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s SET GLOBAL date_format=default; -SHOW GLOBAL VARIABLES LIKE "date_format%"; -Variable_name Value -date_format %d.%m.%Y SET GLOBAL time_format=default; -SHOW GLOBAL VARIABLES LIKE "time_format%"; -Variable_name Value -time_format %H.%i.%s SET GLOBAL datetime_format=default; -SHOW GLOBAL VARIABLES LIKE "datetime_format%"; -Variable_name Value -datetime_format %Y/%d/%m-%H:%i:%s -SET date_format=default; -SHOW SESSION VARIABLES LIKE "date_format%"; -Variable_name Value -date_format %d.%m.%Y SET time_format=default; -SHOW SESSION VARIABLES LIKE "time_format%"; -Variable_name Value -time_format %H.%i.%s +SET date_format=default; SET datetime_format=default; -SHOW SESSION VARIABLES LIKE "datetime_format%"; -Variable_name Value -datetime_format %Y/%d/%m-%H:%i:%s -SET time_format='%i:%s:%H'; -select cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME); -cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME) -59:59:12 -SET GLOBAL date_format='%Y-%m-%d'; -SET GLOBAL time_format='%H:%i:%s'; -SET GLOBAL datetime_format='%Y-%m-%d %H:%i:%s'; -SET date_format='%Y-%m-%d'; -SET time_format='%H:%i:%s'; -SET datetime_format='%Y-%m-%d %H:%i:%s'; -select str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S"); -str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") -2001-01-15 12:59:59 -select str_to_date("15 September 2001", "%d %M %Y"); -str_to_date("15 September 2001", "%d %M %Y") -2001-09-15 00:00:00 -select str_to_date("15 Septembeb 2001", "%d %M %Y"); -str_to_date("15 Septembeb 2001", "%d %M %Y") -NULL -select str_to_date("15 MAY 2001", "%d %b %Y"); -str_to_date("15 MAY 2001", "%d %b %Y") -2001-05-15 00:00:00 -select str_to_date("Sunday 15 MAY 2001", "%W %d %b %Y"); -str_to_date("Sunday 15 MAY 2001", "%W %d %b %Y") -2001-05-15 00:00:00 -select str_to_date("Sundai 15 MAY 2001", "%W %d %b %Y"); -str_to_date("Sundai 15 MAY 2001", "%W %d %b %Y") -NULL -select str_to_date("Sundai 15 MA", "%W %d %b %Y"); -str_to_date("Sundai 15 MA", "%W %d %b %Y") -NULL -select str_to_date("Tuesday 52 2001", "%W %V %X"); -str_to_date("Tuesday 52 2001", "%W %V %X") -NULL -select str_to_date("Sunday 01 2001", "%W %V %X"); -str_to_date("Sunday 01 2001", "%W %V %X") -NULL -select str_to_date("Tuesday 00 2002", "%W %U %Y"); -str_to_date("Tuesday 00 2002", "%W %U %Y") -2002-01-01 00:00:00 -select str_to_date("Thursday 53 1998", "%W %u %Y"); -str_to_date("Thursday 53 1998", "%W %u %Y") -1998-12-31 00:00:00 -select str_to_date("15-01-2001", "%d-%m-%Y %H:%i:%S"); -str_to_date("15-01-2001", "%d-%m-%Y %H:%i:%S") -2001-01-15 00:00:00 -select str_to_date("15-01-20", "%d-%m-%Y"); -str_to_date("15-01-20", "%d-%m-%Y") -NULL -select str_to_date("15-2001-1", "%d-%Y-%c"); -str_to_date("15-2001-1", "%d-%Y-%c") -2001-01-15 00:00:00 +select str_to_date(concat('15-01-2001',' 2:59:58.999'), +concat('%d-%m-%Y',' ','%H:%i:%s.%f')); +str_to_date(concat('15-01-2001',' 2:59:58.999'), +concat('%d-%m-%Y',' ','%H:%i:%s.%f')) +2001-01-15 02:59:58.000999 +create table t1 (date char(30), format char(30) not null); +insert into t1 values +('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'), +('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S'), +('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'), +('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'), +('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'), +('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'), +('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'), +('10:20:10', '%H:%i:%s'), +('10:20:10', '%h:%i:%s.%f'), +('10:20:10AM', '%h:%i:%s%p'), +('10:20:10.44AM', '%h:%i:%s.%f%p'), +('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'), +('15 September 2001', '%d %M %Y'), +('15 SEPTEMB 2001', '%d %M %Y'), +('15 MAY 2001', '%d %b %Y'), +('Sunday 15 MAY 2001', '%W %d %b %Y'), +('Sund 15 MAY 2001', '%W %d %b %Y'), +('Tuesday 00 2002', '%W %U %Y'), +('Thursday 53 1998', '%W %u %Y'), +('15-01-2001', '%d-%m-%Y %H:%i:%S'), +('15-01-20', '%d-%m-%y'), +('15-2001-1', '%d-%Y-%c'); +select date,format,str_to_date(date, format) as str_to_date from t1; +date format str_to_date +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S 2003-01-02 08:11:02 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12 +2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.012345 +2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.012345 +2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.012345 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 +10:20:10 %H:%i:%s 0000-00-00 10:20:10 +10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 +10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 +10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.000044 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 +15 September 2001 %d %M %Y 2001-09-15 00:00:00 +15 SEPTEMB 2001 %d %M %Y 2001-01-15 00:00:00 +15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 +Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 +Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 +Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 +Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 +15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 +15-01-20 %d-%m-%y 2020-01-15 00:00:00 +15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 +select date,format,concat('',str_to_date(date, format)) as con from t1; +date format con +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S 2003-01-02 08:11:02 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12 +2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.012345 +2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.012345 +2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.012345 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 +10:20:10 %H:%i:%s 0000-00-00 10:20:10 +10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 +10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 +10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.000044 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 +15 September 2001 %d %M %Y 2001-09-15 00:00:00 +15 SEPTEMB 2001 %d %M %Y 2001-01-15 00:00:00 +15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 +Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 +Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 +Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 +Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 +15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 +15-01-20 %d-%m-%y 2020-01-15 00:00:00 +15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 +select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1; +date format datetime +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S 2003-01-02 08:11:02 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12 +2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.012345 +2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.012345 +2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.012345 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 +10:20:10 %H:%i:%s 0000-00-00 10:20:10 +10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 +10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 +10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.000044 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 +15 September 2001 %d %M %Y 2001-09-15 00:00:00 +15 SEPTEMB 2001 %d %M %Y 2001-01-15 00:00:00 +15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 +Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 +Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 +Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 +Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 +15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 +15-01-20 %d-%m-%y 2020-01-15 00:00:00 +15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 +select date,format,DATE(str_to_date(date, format)) as date2 from t1; +date format date2 +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S 2003-01-02 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 +2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 +2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 +2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 +10:20:10 %H:%i:%s 0000-00-00 +10:20:10 %h:%i:%s.%f 0000-00-00 +10:20:10AM %h:%i:%s%p 0000-00-00 +10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 +15 September 2001 %d %M %Y 2001-09-15 +15 SEPTEMB 2001 %d %M %Y 2001-01-15 +15 MAY 2001 %d %b %Y 2001-05-15 +Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 +Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 +Tuesday 00 2002 %W %U %Y 2002-01-01 +Thursday 53 1998 %W %u %Y 1998-12-31 +15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 +15-01-20 %d-%m-%y 2020-01-15 +15-2001-1 %d-%Y-%c 2001-01-15 +select date,format,TIME(str_to_date(date, format)) as time from t1; +date format time +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S 08:11:02 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12 +2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.012345 +2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.012345 +2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.012345 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12 +10:20:10 %H:%i:%s 10:20:10 +10:20:10 %h:%i:%s.%f 10:20:10 +10:20:10AM %h:%i:%s%p 10:20:10 +10:20:10.44AM %h:%i:%s.%f%p 10:20:10.000044 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58 +15 September 2001 %d %M %Y 00:00:00 +15 SEPTEMB 2001 %d %M %Y 00:00:00 +15 MAY 2001 %d %b %Y 00:00:00 +Sunday 15 MAY 2001 %W %d %b %Y 00:00:00 +Sund 15 MAY 2001 %W %d %b %Y 00:00:00 +Tuesday 00 2002 %W %U %Y 00:00:00 +Thursday 53 1998 %W %u %Y 00:00:00 +15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00 +15-01-20 %d-%m-%y 00:00:00 +15-2001-1 %d-%Y-%c 00:00:00 +select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1; +date format time2 +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S 08:11:02 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12 +2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.012345 +2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.012345 +2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.012345 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12 +10:20:10 %H:%i:%s 10:20:10 +10:20:10 %h:%i:%s.%f 10:20:10 +10:20:10AM %h:%i:%s%p 10:20:10 +10:20:10.44AM %h:%i:%s.%f%p 10:20:10.000044 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58 +15 September 2001 %d %M %Y 00:00:00 +15 SEPTEMB 2001 %d %M %Y 00:00:00 +15 MAY 2001 %d %b %Y 00:00:00 +Sunday 15 MAY 2001 %W %d %b %Y 00:00:00 +Sund 15 MAY 2001 %W %d %b %Y 00:00:00 +Tuesday 00 2002 %W %U %Y 00:00:00 +Thursday 53 1998 %W %u %Y 00:00:00 +15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00 +15-01-20 %d-%m-%y 00:00:00 +15-2001-1 %d-%Y-%c 00:00:00 +truncate table t1; +insert into t1 values +('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'), +('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'), +('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'), +('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'), +('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'), +('10:20:10AM', '%H:%i:%s%p'), +('15 Septembei 2001', '%d %M %Y'), +('15 Ju 2001', '%d %M %Y'), +('Sund 15 MA', '%W %d %b %Y'), +('Sunday 01 2001', '%W %V %X'), +('Thursdai 12 1998', '%W %u %Y'), +(NULL, get_format(DATE,'USA')), +('Tuesday 52 2001', '%W %V %X'); +select date,format,str_to_date(date, format) as str_to_date from t1; +date format str_to_date +2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL +2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL +2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL +2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL +2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL +10:20:10AM %H:%i:%s%p NULL +15 Septembei 2001 %d %M %Y NULL +15 Ju 2001 %d %M %Y NULL +Sund 15 MA %W %d %b %Y NULL +Sunday 01 2001 %W %V %X NULL +Thursdai 12 1998 %W %u %Y NULL +NULL %m.%d.%Y NULL +Tuesday 52 2001 %W %V %X NULL +select date,format,concat(str_to_date(date, format),'') as con from t1; +date format con +2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL +2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL +2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL +2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL +2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL +10:20:10AM %H:%i:%s%p NULL +15 Septembei 2001 %d %M %Y NULL +15 Ju 2001 %d %M %Y NULL +Sund 15 MA %W %d %b %Y NULL +Sunday 01 2001 %W %V %X NULL +Thursdai 12 1998 %W %u %Y NULL +NULL %m.%d.%Y NULL +Tuesday 52 2001 %W %V %X NULL +truncate table t1; +insert into t1 values +('10:20:10AM', '%h:%i:%s'), +('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'), +('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'); +select date,format,str_to_date(date, format) as str_to_date from t1; +date format str_to_date +10:20:10AM %h:%i:%s 0000-00-00 10:20:10 +2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12 +03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 0003-01-02 22:11:12 +select date,format,concat(str_to_date(date, format),'') as con from t1; +date format con +10:20:10AM %h:%i:%s 0000-00-00 10:20:10 +2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12 +03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 0003-01-02 22:11:12 +drop table t1; select get_format(DATE, 'USA') as a; a %m.%d.%Y @@ -154,4 +318,15 @@ a %H%i%s select get_format(DATETIME, 'eur') as a; a -%Y-%m-%d-%H.%i.%s +%Y-%m-%d %H.%i.%s +select get_format(DATE, 'TEST') as a; +a +NULL +select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')); +str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')) +NULL +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/derived.result b/mysql-test/r/derived.result index 12d8bbaf8ae..37742893c2b 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -227,6 +227,4 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 THEMAX.E2 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where -Warnings: -Note 1275 Field or reference 'A.E2' of SELECT #3 was resolved in SELECT #2 drop table t1; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 3dacb9cc124..385e2d52fb5 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 @@ -65,6 +70,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 @@ -287,6 +297,8 @@ insert into t2 values (3, 1, 'xxbuz'); select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); t1_id name t2_id t1_id name 1 data1 1 1 xxfoo +select * from t2 where match name against ('a* b* c* d* e* f*' in boolean mode); +t2_id t1_id name drop table t1,t2; SET NAMES latin1; CREATE TABLE t1 (t text character set utf8 not null, fulltext(t)); diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 85842f28e64..461fc1018c1 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 @@ -55,6 +65,6 @@ NULL 50000 NULL Warnings: -Error 1258 Z_DATA_ERROR: Input data was corrupted for zlib +Error 1258 ZLIB: Input data was corrupted for zlib Error 1255 Too big size of uncompressed data. The maximum size is 1048576. (probably, length of uncompressed data was corrupted) drop table t1; diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result index 5ee0f0f3e93..70ebcf216fb 100644 --- a/mysql-test/r/func_crypt.result +++ b/mysql-test/r/func_crypt.result @@ -1,6 +1,16 @@ +drop table if exists t1; select length(encrypt('foo', 'ff')) <> 0; length(encrypt('foo', 'ff')) <> 0 1 +create table t1 (name varchar(50), pw varchar(64)); +insert into t1 values ('tom', password('my_pass')); +set @pass='my_pass'; +select name from t1 where name='tom' and pw=password(@pass); +name +tom +select name from t1 where name='tom' and pw=password(@undefined); +name +drop table t1; select password('abc'); password('abc') *0D3CED9BEC10A777AEC23CCC353A8C08A633045E @@ -77,3 +87,8 @@ old_password('idkfa') select old_password(' i d k f a '); old_password(' i d k f a ') 5c078dc54ca0fcca +explain extended select password('idkfa '), old_password('idkfa'); +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 password(_latin1'idkfa ') AS `password('idkfa ')`,old_password(_latin1'idkfa') AS `old_password('idkfa')` diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result new file mode 100644 index 00000000000..fe3f5b9473a --- /dev/null +++ b/mysql-test/r/func_default.result @@ -0,0 +1,18 @@ +drop table if exists t1,t2; +create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14'); +insert into t1 values ('','',0,0.0); +select default(str), default(strnull), default(intg), default(rel) from t1; +default(str) default(strnull) default(intg) default(rel) +def NULL 10 3.14 +explain extended select default(str), default(strnull), default(intg), default(rel) 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 default(test.t1.str) AS `default(str)`,default(test.t1.strnull) AS `default(strnull)`,default(test.t1.intg) AS `default(intg)`,default(test.t1.rel) AS `default(rel)` from test.t1 +select * from t1 where str <> default(str); +str strnull intg rel + 0 0 +explain select * from t1 where str <> default(str); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +drop table t1; diff --git a/mysql-test/r/func_encrypt.result b/mysql-test/r/func_encrypt.result index 39c734999b2..abdfda0423f 100644 --- a/mysql-test/r/func_encrypt.result +++ b/mysql-test/r/func_encrypt.result @@ -134,3 +134,8 @@ NULL select hex(des_decrypt(des_encrypt("hello","hidden"))); hex(des_decrypt(des_encrypt("hello","hidden"))) NULL +explain extended select des_decrypt(des_encrypt("hello",4),'password2'), des_decrypt(des_encrypt("hello","hidden")); +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 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"))` 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 2fb38ffe592..7ae3bbb0608 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -559,8 +559,8 @@ id select_type table type possible_keys key key_len ref rows Extra explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index 1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index +1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index drop table t1, t2; CREATE TABLE t1 (a int, b int); select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; @@ -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..04b6fc038b0 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..151a2902423 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -1,43 +1,108 @@ select floor(5.5),floor(-5.5); floor(5.5) floor(-5.5) 5 -6 +explain extended select floor(5.5),floor(-5.5); +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 floor(5.5) AS `floor(5.5)`,floor(-(5.5)) AS `floor(-5.5)` select ceiling(5.5),ceiling(-5.5); ceiling(5.5) ceiling(-5.5) 6 -5 +explain extended select ceiling(5.5),ceiling(-5.5); +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 ceiling(5.5) AS `ceiling(5.5)`,ceiling(-(5.5)) AS `ceiling(-5.5)` select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1); truncate(52.64,1) truncate(52.64,2) truncate(52.64,-1) truncate(52.64,-2) truncate(-52.64,1) truncate(-52.64,-1) 52.6 52.64 50 0 -52.6 -50 +explain extended select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-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 truncate(52.64,1) AS `truncate(52.64,1)`,truncate(52.64,2) AS `truncate(52.64,2)`,truncate(52.64,-(1)) AS `truncate(52.64,-1)`,truncate(52.64,-(2)) AS `truncate(52.64,-2)`,truncate(-(52.64),1) AS `truncate(-52.64,1)`,truncate(-(52.64),-(1)) AS `truncate(-52.64,-1)` select round(5.5),round(-5.5); round(5.5) round(-5.5) 6 -6 +explain extended select round(5.5),round(-5.5); +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 round(5.5,0) AS `round(5.5)`,round(-(5.5),0) AS `round(-5.5)` select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2); round(5.64,1) round(5.64,2) round(5.64,-1) round(5.64,-2) 5.6 5.64 10 0 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 +explain extended 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); +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 log(exp(10)) AS `log(exp(10))`,exp((log(sqrt(10)) * 2)) AS `exp(log(sqrt(10))*2)`,log(-(1)) AS `log(-1)`,log(NULL) AS `log(NULL)`,log(1,1) AS `log(1,1)`,log(3,9) AS `log(3,9)`,log(-(1),2) AS `log(-1,2)`,log(NULL,2) AS `log(NULL,2)` select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); ln(exp(10)) exp(ln(sqrt(10))*2) ln(-1) ln(0) ln(NULL) 10.000000 10.000000 NULL NULL NULL +explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(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 ln(exp(10)) AS `ln(exp(10))`,exp((ln(sqrt(10)) * 2)) AS `exp(ln(sqrt(10))*2)`,ln(-(1)) AS `ln(-1)`,ln(0) AS `ln(0)`,ln(NULL) AS `ln(NULL)` select log2(8),log2(15),log2(-2),log2(0),log2(NULL); log2(8) log2(15) log2(-2) log2(0) log2(NULL) 3.000000 3.906891 NULL NULL NULL +explain extended select log2(8),log2(15),log2(-2),log2(0),log2(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 log2(8) AS `log2(8)`,log2(15) AS `log2(15)`,log2(-(2)) AS `log2(-2)`,log2(0) AS `log2(0)`,log2(NULL) AS `log2(NULL)` select log10(100),log10(18),log10(-4),log10(0),log10(NULL); log10(100) log10(18) log10(-4) log10(0) log10(NULL) 2.000000 1.255273 NULL NULL NULL +explain extended select log10(100),log10(18),log10(-4),log10(0),log10(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 log10(100) AS `log10(100)`,log10(18) AS `log10(18)`,log10(-(4)) AS `log10(-4)`,log10(0) AS `log10(0)`,log10(NULL) AS `log10(NULL)` select pow(10,log10(10)),power(2,4); pow(10,log10(10)) power(2,4) 10.000000 16.000000 +explain extended select pow(10,log10(10)),power(2,4); +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 pow(10,log10(10)) AS `pow(10,log10(10))`,pow(2,4) AS `power(2,4)` set @@rand_seed1=10000000,@@rand_seed2=1000000; select rand(999999),rand(); rand(999999) rand() 0.014231365187309 0.028870999839968 +explain extended select rand(999999),rand(); +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 rand(999999) AS `rand(999999)`,rand() AS `rand()` select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); pi() sin(pi()/2) cos(pi()/2) abs(tan(pi())) cot(1) asin(1) acos(0) atan(1) 3.141593 1.000000 0.000000 0.000000 0.64209262 1.570796 1.570796 0.785398 +explain extended select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(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 pi() AS `pi()`,sin((pi() / 2)) AS `sin(pi()/2)`,cos((pi() / 2)) AS `cos(pi()/2)`,abs(tan(pi())) AS `abs(tan(pi()))`,(1 / tan(1)) AS `cot(1)`,asin(1) AS `asin(1)`,acos(0) AS `acos(0)`,atan(1) AS `atan(1)` select degrees(pi()),radians(360); degrees(pi()) radians(360) 180 6.2831853071796 +explain extended select degrees(pi()),radians(360); +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 degrees(pi()) AS `degrees(pi())`,radians(360) AS `radians(360)` 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..e43f9a9119e 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,11 @@ CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) NULL </a>.......................... DROP TABLE t1; +select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); +substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2) substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2) +1abcd;2abcd 3abcd;4abcd +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"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty"); +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 \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\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")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` 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/gis.result b/mysql-test/r/gis.result index 4317daea0b3..7ee16d54724 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -224,6 +224,11 @@ fid AsText(Envelope(g)) 119 POLYGON((0 0,3 0,3 3,0 3,0 0)) 120 POLYGON((0 0,10 0,10 10,0 10,0 0)) 121 POLYGON((3 6,44 6,44 9,3 9,3 6)) +explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from geo; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE geo ALL NULL NULL NULL NULL 21 +Warnings: +Note 1003 select high_priority dimension(test.geo.g) AS `Dimension(g)`,geometrytype(test.geo.g) AS `GeometryType(g)`,isempty(test.geo.g) AS `IsEmpty(g)`,astext(envelope(test.geo.g)) AS `AsText(Envelope(g))` from test.geo SELECT fid, X(g) FROM pt; fid X(g) 101 10 @@ -236,6 +241,11 @@ fid Y(g) 102 10 103 20 104 20 +explain extended select X(g),Y(g) FROM pt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE pt ALL NULL NULL NULL NULL 4 +Warnings: +Note 1003 select high_priority x(test.pt.g) AS `X(g)`,y(test.pt.g) AS `Y(g)` from test.pt SELECT fid, AsText(StartPoint(g)) FROM ls; fid AsText(StartPoint(g)) 105 POINT(0 0) @@ -266,6 +276,11 @@ fid IsClosed(g) 105 0 106 1 107 0 +explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM ls; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE ls ALL NULL NULL NULL NULL 3 +Warnings: +Note 1003 select high_priority astext(startpoint(test.ls.g)) AS `AsText(StartPoint(g))`,astext(endpoint(test.ls.g)) AS `AsText(EndPoint(g))`,glength(test.ls.g) AS `GLength(g)`,numpoints(test.ls.g) AS `NumPoints(g)`,astext(pointn(test.ls.g,2)) AS `AsText(PointN(g, 2))`,isclosed(test.ls.g) AS `IsClosed(g)` from test.ls SELECT fid, AsText(Centroid(g)) FROM p; fid AsText(Centroid(g)) 108 POINT(15 15) @@ -291,6 +306,11 @@ fid AsText(InteriorRingN(g, 1)) 108 NULL 109 LINESTRING(10 10,20 10,20 20,10 20,10 10) 110 NULL +explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM p; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE p ALL NULL NULL NULL NULL 3 +Warnings: +Note 1003 select high_priority astext(centroid(test.p.g)) AS `AsText(Centroid(g))`,area(test.p.g) AS `Area(g)`,astext(exteriorring(test.p.g)) AS `AsText(ExteriorRing(g))`,numinteriorrings(test.p.g) AS `NumInteriorRings(g)`,astext(interiorringn(test.p.g,1)) AS `AsText(InteriorRingN(g, 1))` from test.p SELECT fid, IsClosed(g) FROM mls; fid IsClosed(g) 114 0 @@ -325,6 +345,11 @@ SELECT fid, NumGeometries(g) from gc; fid NumGeometries(g) 120 2 121 2 +explain extended SELECT fid, NumGeometries(g) from mpt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE mpt ALL NULL NULL NULL NULL 3 +Warnings: +Note 1003 select high_priority test.mpt.fid AS `fid`,numgeometries(test.mpt.g) AS `NumGeometries(g)` from test.mpt SELECT fid, AsText(GeometryN(g, 2)) from mpt; fid AsText(GeometryN(g, 2)) 111 POINT(10 10) @@ -344,6 +369,11 @@ SELECT fid, AsText(GeometryN(g, 2)) from gc; fid AsText(GeometryN(g, 2)) 120 LINESTRING(0 0,10 10) 121 LINESTRING(3 6,7 9) +explain extended SELECT fid, AsText(GeometryN(g, 2)) from mpt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE mpt ALL NULL NULL NULL NULL 3 +Warnings: +Note 1003 select high_priority test.mpt.fid AS `fid`,astext(geometryn(test.mpt.g,2)) AS `AsText(GeometryN(g, 2))` from test.mpt SELECT g1.fid as first, g2.fid as second, Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, @@ -354,6 +384,16 @@ first second w c o e d t i r 120 121 0 0 0 0 0 0 1 0 121 120 0 0 1 0 0 0 1 0 121 121 1 1 0 1 0 0 1 0 +explain extended SELECT g1.fid as first, g2.fid as second, +Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, +Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, +Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r +FROM gc g1, gc g2 ORDER BY first, second; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select high_priority test.g1.fid AS `first`,test.g2.fid AS `second`,within(test.g1.g,test.g2.g) AS `w`,contains(test.g1.g,test.g2.g) AS `c`,overlaps(test.g1.g,test.g2.g) AS `o`,equals(test.g1.g,test.g2.g) AS `e`,disjoint(test.g1.g,test.g2.g) AS `d`,touches(test.g1.g,test.g2.g) AS `t`,intersects(test.g1.g,test.g2.g) AS `i`,crosses(test.g1.g,test.g2.g) AS `r` from test.gc g1 join test.gc g2 order by test.g1.fid,test.g2.fid DROP TABLE pt, ls, p, mpt, mls, mp, gc, geo; CREATE TABLE g1 ( pt point, @@ -391,3 +431,26 @@ DROP TABLE g1; SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))) POINT(1 4) +explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); +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 astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'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 Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'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 +explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +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 srid(geometryfromtext(_latin1'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 Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority issimple(multipoint(point(3,6),point(4,10))) AS `issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,issimple(point(3,6)) AS `issimple(Point(3, 6))` 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/mysqldump.result b/mysql-test/r/mysqldump.result index fa9658c0202..4025a01bc93 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5,7 +5,7 @@ INSERT INTO t1 VALUES (1), (2); <mysqldump> <database name="test"> <table_structure name="t1"> - <field Field="a" Type="int(11)" Null="YES" /> + <field Field="a" Type="int(11)" Null="YES" Key="" Extra="" /> </table_structure> <table_data name="t1"> <row> @@ -18,15 +18,40 @@ INSERT INTO t1 VALUES (1), (2); </database> </mysqldump> DROP TABLE t1; -CREATE TABLE `"t"1` (`a"b"` char(2)); -INSERT INTO `"t"1` VALUES ("1\""), ("\"2"); +CREATE TABLE t1(a int, b text, c varchar(3)); +INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); <?xml version="1.0"?> <mysqldump> <database name="test"> - <table_structure name=""t"1"> - <field Field="a"b"" Type="char(2)" Null="YES" /> + <table_structure name="t1"> + <field Field="a" Type="int(11)" Null="YES" Key="" Extra="" /> + <field Field="b" Type="text" Null="YES" Key="" Extra="" /> + <field Field="c" Type="char(3)" Null="YES" Key="" Extra="" /> </table_structure> - <table_data name=""t"1"> + <table_data name="t1"> + <row> + <field name="a">1</field> + <field name="b">test</field> + <field name="c">tes</field> + </row> + <row> + <field name="a">2</field> + <field name="b">TEST</field> + <field name="c">TES</field> + </row> + </table_data> +</database> +</mysqldump> +DROP TABLE t1; +CREATE TABLE t1 (`a"b"` char(2)); +INSERT INTO t1 VALUES ("1\""), ("\"2"); +<?xml version="1.0"?> +<mysqldump> +<database name="test"> + <table_structure name="t1"> + <field Field="a"b"" Type="char(2)" Null="YES" Key="" Extra="" /> + </table_structure> + <table_data name="t1"> <row> <field name="a"b"">1"</field> </row> @@ -36,4 +61,4 @@ INSERT INTO `"t"1` VALUES ("1\""), ("\"2"); </table_data> </database> </mysqldump> -DROP TABLE `"t"1`; +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/null_key.result b/mysql-test/r/null_key.result index bacd95d852e..9aaea0d800b 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -153,12 +153,12 @@ a b 7 NULL explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where; Using index +1 SIMPLE t1 range a,b a 10 NULL 3 Using where; Using index select * from t1 where (a = 7 or a is null) and (b=7 or b is null); a b +NULL 7 7 NULL 7 7 -NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index 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..e91153cae15 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1,8 +1,9 @@ +set GLOBAL query_cache_size=1355776; flush query cache; flush query cache; reset query cache; flush status; -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t11,t21; drop database if exists mysqltest; create table t1 (a int not null); insert into t1 values (1),(2),(3); @@ -300,6 +301,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 @@ -580,7 +586,7 @@ select * from t1; a set GLOBAL query_cache_size=1024; Warnings: -Warning 1280 Query cache failed to set size 1024, new query cache size is 0 +Warning 1285 Query cache failed to set size 1024, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -588,7 +594,7 @@ select * from t1; a set GLOBAL query_cache_size=10240; Warnings: -Warning 1280 Query cache failed to set size 10240, new query cache size is 0 +Warning 1285 Query cache failed to set size 10240, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -596,7 +602,7 @@ select * from t1; a set GLOBAL query_cache_size=20480; Warnings: -Warning 1280 Query cache failed to set size 20480, new query cache size is 0 +Warning 1285 Query cache failed to set size 20480, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -604,7 +610,7 @@ select * from t1; a set GLOBAL query_cache_size=40960; Warnings: -Warning 1280 Query cache failed to set size 40960, new query cache size is 0 +Warning 1285 Query cache failed to set size 40960, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -717,6 +723,45 @@ Variable_name Value Qcache_queries_in_cache 2 SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; +flush query cache; +reset query cache; +flush status; +set GLOBAL query_cache_size=1048576; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +create table t2 (a text not null); +create table t3 (a text not null); +insert into t3 values("1111111111111111111111111111111111111111111111111111"); +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +drop table t2; +create table t2 (a int not null); +insert into t2 values (1),(2),(3); +create table t4 (a int not null); +insert into t4 values (1),(2),(3); +select * from t4; +select * from t2; +select * from t1 as tt, t1 as ttt where tt.a=1 and ttt.a=2; +select * from t2; +select * from t4; +select * from t1 as tt, t1 as ttt where tt.a=1 and ttt.a=2; +select * from t2; +select * from t4; +select * from t1 as tt, t1 as ttt where tt.a=1 and ttt.a=2; +delete from t2 where a=1; +flush query cache; +select * from t3; +delete from t4 where a=1; +flush query cache; +drop table t1,t2,t3,t4; SET NAMES koi8r; CREATE TABLE t1 (a char(1) character set koi8r); INSERT INTO t1 VALUES (_koi8r'á'),(_koi8r'Á'); @@ -726,7 +771,7 @@ a  'â'='Â' Á  1 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 12 +Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 @@ -737,7 +782,7 @@ a  'â'='Â' Á  0 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 12 +Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 @@ -748,7 +793,7 @@ a ÷ '×'='÷' Á ÷ 0 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 12 +Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 3 @@ -759,8 +804,9 @@ a  'â'='Â' à  0 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 12 +Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 DROP TABLE t1; +SET GLOBAL query_cache_size=0; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 38d3d9b2b7d..1b66f442a9a 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -262,6 +262,23 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using where; Using index 1 SIMPLE t1 ALL i2 NULL NULL NULL 4 Range checked for each record (index map: 0x2) DROP TABLE t1,t2; +CREATE TABLE t1 ( +a int(11) default NULL, +b int(11) default NULL, +KEY a (a), +KEY b (b) +) TYPE=MyISAM; +INSERT INTO t1 VALUES +(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2), +(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3), +(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5), +(33,5),(33,5),(33,5),(33,5),(34,5),(35,5); +EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 5 NULL 2 Using where +SELECT * FROM t1 WHERE a IN(1,2) AND b=5; +a b +DROP TABLE t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); select id from t1 where id in (2,5,9) ; 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_chain_temp_table.result b/mysql-test/r/rpl_chain_temp_table.result new file mode 100644 index 00000000000..5ece80565c7 --- /dev/null +++ b/mysql-test/r/rpl_chain_temp_table.result @@ -0,0 +1,30 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +reset master; +change master to master_host='127.0.0.1',master_port=9307, master_user='root'; +start slave; +create temporary table t1 (a int); +create temporary table t1 (a int); +show status like 'slave_open_temp_tables'; +Variable_name Value +Slave_open_temp_tables 2 +create temporary table t1 (a int); +create temporary table t1 (a int); +show status like 'slave_open_temp_tables'; +Variable_name Value +Slave_open_temp_tables 4 +stop slave; +insert into t1 values(1); +create table t2 as select * from t1; +start slave; +show status like 'slave_open_temp_tables'; +Variable_name Value +Slave_open_temp_tables 4 +select * from t2; +a +1 +drop table t2; 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/rpl_reset_slave.result b/mysql-test/r/rpl_reset_slave.result index 8887462389d..e1fbf12786d 100644 --- a/mysql-test/r/rpl_reset_slave.result +++ b/mysql-test/r/rpl_reset_slave.result @@ -20,3 +20,13 @@ start slave; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Replicate_do_table Replicate_ignore_table Replicate_wild_do_table Replicate_wild_ignore_table Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space Until_condition Until_Log_File Until_Log_pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_behind_master # 127.0.0.1 root MASTER_PORT 1 master-bin.000001 79 slave-relay-bin.000002 123 master-bin.000001 Yes Yes 0 0 79 123 None 0 No # +stop slave; +reset slave; +start slave; +create temporary table t1 (a int); +stop slave; +reset slave; +start slave; +show status like 'slave_open_temp_tables'; +Variable_name Value +Slave_open_temp_tables 1 diff --git a/mysql-test/r/rpl_temporary.result b/mysql-test/r/rpl_temporary.result index cf2b8814047..e7b64066c37 100644 --- a/mysql-test/r/rpl_temporary.result +++ b/mysql-test/r/rpl_temporary.result @@ -74,3 +74,4 @@ f 5 7 drop table t1,t2; +create temporary table t3 (f int); diff --git a/mysql-test/r/rpl_trunc_binlog.result b/mysql-test/r/rpl_trunc_binlog.result index 1cd9cdf08a0..caca15ab4ef 100644 --- a/mysql-test/r/rpl_trunc_binlog.result +++ b/mysql-test/r/rpl_trunc_binlog.result @@ -11,4 +11,3 @@ start slave; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Replicate_do_table Replicate_ignore_table Replicate_wild_do_table Replicate_wild_ignore_table Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space Until_condition Until_Log_File Until_Log_pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_behind_master # 127.0.0.1 root MASTER_PORT 1 master-bin.000002 4 slave-relay-bin.000002 123 master-bin.000001 Yes No 0 Rolling back unfinished transaction (no COMMIT or ROLLBACK) from relay log. Probably cause is that the master died while writing the transaction to it's binary log. 0 79 326 None 0 No # -reset master; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index fca4a0549d3..8edd0182dec 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 @@ -2145,10 +2150,10 @@ a a a select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; a a a 1 1 2 -2 2 2 -3 3 2 1 1 3 +2 2 2 2 2 3 +3 3 2 3 3 3 select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; a a a diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 29bba54b55a..31b5ff84365 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2,32 +2,35 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); (select 2) 2 -explain select (select 2); +explain extended select (select 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1248 Select 2 was reduced during optimisation +Note 1003 select high_priority 2 AS `(select 2)` SELECT (SELECT 1) UNION SELECT (SELECT 2); (SELECT 1) 1 2 -explain SELECT (SELECT 1) UNION SELECT (SELECT 2); +explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1248 Select 2 was reduced during optimisation Note 1248 Select 4 was reduced during optimisation +Note 1003 select high_priority 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)` SELECT (SELECT (SELECT 0 UNION SELECT 0)); (SELECT (SELECT 0 UNION SELECT 0)) 0 -explain SELECT (SELECT (SELECT 0 UNION SELECT 0)); +explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1248 Select 2 was reduced during optimisation +Note 1003 select high_priority (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))` SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; ERROR 42S22: Reference 'a' not supported (forward reference in item list) SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b; @@ -37,7 +40,7 @@ SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a; 1 1 SELECT (SELECT a) as a; ERROR 42S22: Reference 'a' not supported (forward reference in item list) -EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; +EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used @@ -45,6 +48,7 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1275 Field or reference 'a' of SELECT #3 was resolved in SELECT #1 Note 1275 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 +Note 1003 select high_priority 1 AS `1` from (select 1 AS `a`) b having ((select b.a AS `a`) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -172,12 +176,14 @@ a b 2 7 3 8 4 8 -explain (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); +explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using filesort 3 UNION t4 ALL NULL NULL NULL NULL 3 Using where; Using filesort 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 (select high_priority test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.b = (select test.t3.a AS `a` from test.t3 order by test.t3.a desc limit 1))) union (select test.t4.a AS `a`,test.t4.b AS `b` from test.t4 where (test.t4.b = (select (max(test.t2.a) * 4) AS `max(t2.a)*4` from test.t2)) order by test.t4.a) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 @@ -186,12 +192,14 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 -explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived3> system NULL NULL NULL NULL 1 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)`,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 @@ -205,13 +213,14 @@ b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) 8 7.5000 8 4.5000 9 7.5000 -explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: Note 1275 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 +Note 1003 select high_priority test.t4.b AS `b`,(select avg((test.t2.a + (select min(test.t3.a) AS `min(t3.a)` from test.t3 where (test.t3.a >= test.t4.a)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from test.t2) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from test.t4 select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 @@ -252,10 +261,12 @@ select * from t3 where a >= any (select b from t2); a 6 7 -explain select * from t3 where a >= any (select b from t2); +explain extended select * from t3 where a >= any (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 +Warnings: +Note 1003 select high_priority test.t3.a AS `a` from test.t3 where (test.t3.a >= (select min(test.t2.b) from test.t2 limit 1)) select * from t3 where a >= all (select b from t2); a 7 @@ -290,7 +301,7 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a NULL 1 2 2 -explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 @@ -298,6 +309,7 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1275 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 Note 1275 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 +Note 1003 select high_priority (select test.t1.a AS `a` from test.t1 where (test.t1.a = test.t2.a) union select test.t5.a AS `a` from test.t5 where (test.t5.a = test.t2.a)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,test.t2.a AS `a` from test.t2 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -309,12 +321,13 @@ patient_uq clinic_uq 1 1 1 2 2 2 -explain select * from t6 where exists (select * from t7 where uq = clinic_uq); +explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Warnings: Note 1275 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 +Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq) limit 1) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column: 'a' in field list is ambiguous drop table if exists t1,t2,t3; @@ -342,12 +355,14 @@ UNIQUE KEY `email` (`email`) INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); -EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); +EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t8 const PRIMARY PRIMARY 35 const 1 4 SUBQUERY t8 const PRIMARY PRIMARY 35 1 2 SUBQUERY t8 const PRIMARY PRIMARY 35 const 1 3 SUBQUERY t8 const PRIMARY PRIMARY 35 1 +Warnings: +Note 1003 select high_priority test.t8.pseudo AS `pseudo`,(select test.t8.email AS `email` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce'))) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -369,13 +384,17 @@ KEY `topic` (`topic`) ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); -EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; +EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 41 NULL 2 Using where; Using index -EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); +Warnings: +Note 1003 select high_priority distinct test.t1.date AS `date` from test.t1 where (test.t1.date = 20020803) +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 Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY t1 index NULL PRIMARY 41 NULL 2 Using where; Using index +Warnings: +Note 1003 select high_priority (select distinct test.t1.date AS `date` from test.t1 where (test.t1.date = 20020803)) 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 @@ -389,11 +408,13 @@ SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; 1 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; ERROR 21000: Subquery returns more than 1 row -EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL topic 3 NULL 2 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority 1 AS `1` from test.t1 drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, @@ -507,15 +528,19 @@ ERROR 42S22: Unknown column 'a' in 'having clause' SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); -EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row -EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; +EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); +Warnings: +Note 1003 select high_priority max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1 where (test.t1.numeropost = _latin1'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 Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +Warnings: +Note 1003 select high_priority test.t1.numreponse AS `numreponse` from test.t1 where ((test.t1.numeropost = _latin1'1') and (test.t1.numreponse = 3)) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -678,28 +703,32 @@ INSERT INTO t2 VALUES (1),(2); SELECT * FROM t2 WHERE id IN (SELECT 1); id 1 -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ref id id 5 const 1 Using where; Using index Warnings: Note 1248 Select 2 was reduced during optimisation +Note 1003 select high_priority test.t2.id AS `id` from test.t2 where (test.t2.id = 1) SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id 1 SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id 2 -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ref id id 5 const 1 Using where; Using index Warnings: Note 1248 Select 3 was reduced during optimisation Note 1248 Select 2 was reduced during optimisation -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); +Note 1003 select high_priority test.t2.id AS `id` from test.t2 where (test.t2.id = (1 + 1)) +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL id 5 NULL 2 Using where; Using index 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority test.t2.id AS `id` from test.t2 where <in_optimizer>(test.t2.id,<exists>(select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(1)) limit 1 union select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(3)) limit 1)) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -819,12 +848,13 @@ NULL select 10.5 > ANY (SELECT * from t1); 10.5 > ANY (SELECT * from t1) 1 -explain select (select a+1) from t1; +explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Warnings: Note 1275 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 Note 1248 Select 2 was reduced during optimisation +Note 1003 select high_priority (test.t1.a + 1) AS `(select a+1)` from test.t1 select (select a+1) from t1; (select a+1) 2.5 @@ -841,10 +871,12 @@ a t1.a in (select t2.a from t2) 2 1 3 1 4 0 -explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; +explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index 2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 Using index +Warnings: +Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(<index_lookup>(<cache>(test.t1.a) in t2 on a chicking NULL))) AS `t1.a in (select t2.a from t2)` from test.t1 CREATE TABLE t3 (a int(11) default '0'); INSERT INTO t3 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; @@ -853,11 +885,13 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) 2 1 3 1 4 0 -explain SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; +explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where +Warnings: +Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and ((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having <is_not_null_test>(test.t2.a) limit 1)) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1 drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -962,18 +996,24 @@ UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); ERROR HY000: Invalid use of group function drop table t1; CREATE TABLE t1 (a int(1)); -EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; 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 -EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; +Warnings: +Note 1003 select high_priority no_cache (select no_cache rand() AS `RAND()` from test.t1) AS `(SELECT RAND() FROM t1)` from test.t1 +EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; 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 -EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; +Warnings: +Note 1003 select high_priority no_cache (select no_cache ecrypt(_latin1'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 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,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 '', @@ -1061,12 +1101,14 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3); -explain select a,(select (select rand() from t1 limit 1) from t1 limit 1) +explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 +Warnings: +Note 1003 select high_priority no_cache test.t1.a AS `a`,(select no_cache (select no_cache rand() AS `rand()` from test.t1 limit 1) AS `(select rand() from t1 limit 1)` from test.t1 limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from test.t1 drop table t1; select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent); ERROR 42S02: Table 'test.t1' doesn't exist @@ -1115,18 +1157,22 @@ INSERT INTO t1 (pseudo) VALUES ('test'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) 0 -EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a); +EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a where isnull(1) having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) 0 -EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a); +EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a where isnull(1) having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', @@ -1165,10 +1211,12 @@ NULL drop table t1; create table t1 (id int not null auto_increment primary key, salary int, key(salary)); insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); -explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); +explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref salary salary 5 const 1 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +Warnings: +Note 1003 select high_priority test.t1.id AS `id` from test.t1 where (test.t1.salary = (select max(test.t1.salary) AS `MAX(salary)` from test.t1)) drop table t1; CREATE TABLE t1 ( ID int(10) unsigned NOT NULL auto_increment, @@ -1230,27 +1278,33 @@ a 2 3 4 -explain select * from t2 where t2.a in (select a from t1); +explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<primary_index_lookup>(<cache>(test.t2.a) in t1 on PRIMARY))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<primary_index_lookup>(<cache>(test.t2.a) in t1 on PRIMARY where (test.t1.b <> 30)))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 -explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); +explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1)) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1263,27 +1317,33 @@ a 2 3 4 -explain select * from t2 where t2.a in (select a from t1); +explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<index_lookup>(<cache>(test.t2.a) in t1 on a))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<index_lookup>(<cache>(test.t2.a) in t1 on a where (test.t1.b <> 30)))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 -explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); +explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using index 2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1000 Using where; Using index +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1294,10 +1354,12 @@ select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31 a 2 4 -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where +Warnings: +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(<index_lookup>(<cache>(test.t2.a) in t1 on a where (test.t1.b <> 30)))) drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -1350,9 +1412,11 @@ insert into t1 values ('tttt'); select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1); s1 tttt -explain (select * from t1); +explain extended (select * 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.s1 AS `s1` from test.t1) (select * from t1); s1 tttt @@ -1381,22 +1445,30 @@ s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') a1 0 a2 1 a3 1 -explain select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; +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 -explain select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') 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 <> 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 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 1 Using index; Using where +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 where (test.t2.s1 < _latin1'a2'))))) 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); @@ -1406,19 +1478,23 @@ a 6 7 3 -explain select * from t3 where a >= all (select b from t2); +explain extended select * from t3 where a >= all (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +Warnings: +Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a < (select max(test.t2.b) from test.t2 limit 1))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a 6 7 -explain select * from t3 where a > all (select max(b) from t2 group by a); +explain extended select * from t3 where a > all (select max(b) from t2 group by a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +Warnings: +Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a <= <max>(select max(test.t2.b) AS `max(b)` from test.t2 group by test.t2.a))) drop table t2, t3; CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now()); 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/union.result b/mysql-test/r/union.result index bf47e6ad430..5729d3b32ee 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -86,10 +86,12 @@ a b 1 a (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b; ERROR 42000: Table 't1' from one of SELECT's can not be used in global ORDER clause -explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; +explain extended (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort +Warnings: +Note 1003 (select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 limit 2) union all (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 order by test.t2.a limit 1) order by b desc (select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; a b 1 a @@ -463,10 +465,12 @@ create table t1 (a int not null primary key auto_increment, b int, key(b)); create table t2 (a int not null primary key auto_increment, b int); insert into t1 (b) values (1),(2),(2),(3); insert into t2 (b) values (10),(11),(12),(13); -explain (select * from t1 where a=1) union (select * from t2 where a=1); +explain extended (select * from t1 where a=1) union (select * from t2 where a=1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 UNION t2 const PRIMARY PRIMARY 4 const 1 +Warnings: +Note 1003 (select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 where (test.t1.a = 1)) union (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.a = 1)) (select * from t1 where a=5) union (select * from t2 where a=1); a b 1 10 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'; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index a036121f297..d02850f6745 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -80,6 +80,7 @@ create table t1 (i tinyint unsigned not null auto_increment primary key); insert into t1 set i = 254; insert into t1 set i = null; select last_insert_id(); +explain extended select last_insert_id(); --error 1062 insert into t1 set i = 254; select last_insert_id(); diff --git a/mysql-test/t/bench_count_distinct.test b/mysql-test/t/bench_count_distinct.test index c1adeab2c44..3ffb95a69c2 100644 --- a/mysql-test/t/bench_count_distinct.test +++ b/mysql-test/t/bench_count_distinct.test @@ -16,4 +16,5 @@ while ($1) } enable_query_log; select count(distinct n) from t1; +explain extended select count(distinct n) from t1; drop table t1; diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index 4d4ceb062e4..9a1b6b6bbfe 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -14,6 +14,7 @@ select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END; select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end; select CASE when 1=0 then "true" else "false" END; select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; +explain extended select 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; select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0; select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0; @@ -30,6 +31,7 @@ select case when 1<0 then "TRUE" else "FALSE" END; create table t1 (a int); insert into t1 values(1),(2),(3),(4); select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; +explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase; drop table t1; @@ -98,5 +100,9 @@ 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'); SHOW CREATE TABLE t1; DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index b3ae8981671..dd17904effb 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -8,6 +8,7 @@ select CONVERT('-1',UNSIGNED); select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; select ~5, cast(~5 as signed); +explain extended select ~5, cast(~5 as signed); select cast(5 as unsigned) -6.0; select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A"; select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME); diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 059808161a3..cec7dd17e6d 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -40,9 +40,9 @@ create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null -- error 1044,1 create table not_existing_database.test (a int); ---error 1103 +--error 1279 create table `a/a` (a int); ---error 1103 +--error 1279 create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int); --error 1059 create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); @@ -66,11 +66,11 @@ create table test_$1.test2$ (a int); drop table test_$1.test2$; drop database test_$1; ---error 1103 +--error 1279 create table `` (a int); ---error 1103 +--error 1279 drop table if exists ``; ---error 1166 +--error 1279 create table t1 (`` int); --error 1279 create table t1 (i int, index `` (i)); @@ -254,12 +254,16 @@ create table t2 like t3; show create table t2; select * from t2; create table t3 like t1; -!$1050 create table t3 like test_$1.t3; +--error 1050 +create table t3 like test_$1.t3; --error 1044,1 create table non_existing_database.t1 like t1; -!$1051 create table t3 like non_existing_table; -!$1050 create temporary table t3 like t1; -!$1103 create table t3 like `a/a`; +--error 1051 +create table t3 like non_existing_table; +--error 1050 +create temporary table t3 like t1; +--error 1279 +create table t3 like `a/a`; drop table t1, t2, t3; drop table t3; drop database test_$1; diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test index d42696e693b..2d9a4be5b36 100644 --- a/mysql-test/t/ctype_collate.test +++ b/mysql-test/t/ctype_collate.test @@ -139,6 +139,7 @@ SHOW FIELDS FROM t1; SET CHARACTER SET 'latin1'; SHOW VARIABLES LIKE 'character_set_client'; SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; +explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; SET CHARACTER SET koi8r; SHOW VARIABLES LIKE 'collation_client'; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 52ee227b011..a5d0c29baf6 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -64,6 +64,7 @@ drop table t1; # The below checks both binary and character comparisons. # create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +show create table t1; insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); update t1 set word2=word; select word, word=binary 0xdf as t from t1 having t > 0; @@ -85,6 +86,7 @@ drop table t1; CREATE TABLE t1 ( s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci ); +show create table t1; INSERT INTO t1 VALUES ('Ü'); INSERT INTO t1 VALUES ('ue'); SELECT DISTINCT s1 FROM t1; diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test index 66732520f4d..a1549f952e5 100644 --- a/mysql-test/t/ctype_many.test +++ b/mysql-test/t/ctype_many.test @@ -147,6 +147,12 @@ UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8); SET CHARACTER SET koi8r; SELECT * FROM t1; +# +# codecovarage for Item_func_conv_charset3 +# +select CONVERT(koi8_ru_f, 'cp1251_general_ci', 'utf8_general_ci'), comment from t1; +explain extended select CONVERT(koi8_ru_f, 'cp1251_general_ci', 'utf8_general_ci'), comment from 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/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index 325a8f075ed..2f08e021df2 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -1,7 +1,7 @@ SET CHARACTER SET koi8r; --disable_warnings -DROP TABLE IF EXISTS ÔÁÂÌÉÃÁ; +DROP TABLE IF EXISTS ÔÁÂÌÉÃÁ, t1; --enable_warnings SET CHARACTER SET koi8r; @@ -11,7 +11,7 @@ SELECT a FROM t1; SELECT HEX(a) FROM t1; DROP TABLE t1; -CREATE TABLE ÔÁÂÌÉÃÁ +CREATE TABLE `ÔÁÂÌÉÃÁ` ( ÐÏÌÅ CHAR(32) CHARACTER SET koi8r NOT NULL COMMENT "ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ" ) COMMENT "ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ"; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 46359e84f1d..54d934b66db 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -23,6 +23,8 @@ select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2); select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin); select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin); +select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1); + select 'a' like 'a'; select 'A' like 'a'; select 'A' like 'a' collate utf8_bin; diff --git a/mysql-test/t/date_formats-master.opt b/mysql-test/t/date_formats-master.opt index 7977a601dd7..ab243fe729c 100644 --- a/mysql-test/t/date_formats-master.opt +++ b/mysql-test/t/date_formats-master.opt @@ -1 +1 @@ ---date_format=%d.%m.%Y --time_format=%H.%i.%s --datetime_format=%Y/%d/%m-%H:%i:%s +--date-format=%d.%m.%Y --time-format=%H.%i.%s diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 9551efaa648..d9219d3ac2e 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -1,82 +1,202 @@ +# +# Test of date format functions +# + +--disable-warnings +drop table if exists t1; +--enable-warnings + SHOW GLOBAL VARIABLES LIKE "%_format%"; SHOW SESSION VARIABLES LIKE "%_format%"; -SET date_format="%d.%m.%Y"; -select CAST("01.01.2001" as DATE) as a; -SET datetime_format="%d.%m.%Y %H.%i.%s"; -select CAST("01.01.2001 05.12.06" as DATETIME) as a; -SET time_format="%H.%i.%s"; -select CAST("05.12.06" as TIME) as a; - -SET datetime_format="%d.%m.%Y %h:%i:%s %p"; -select CAST("01.01.2001 05:12:06AM" as DATETIME) as a; -select CAST("01.01.2001 05:12:06 PM" as DATETIME) as a; - -SET time_format="%h:%i:%s %p"; -select CAST("05:12:06 AM" as TIME) as a; -select CAST("05:12:06.1234PM" as TIME) as a; - -SET time_format="%h.%i.%s %p"; -SET date_format='%d.%m.%y'; -SET datetime_format="%d.%m.%y %h.%i.%s %p"; -select CAST("12-12-06" as DATE) as a; - -select adddate("01.01.97 11.59.59.000001 PM", 10); -select datediff("31.12.97 11.59:59.000001 PM","01.01.98"); -select weekofyear("31.11.97 11:59:59.000001 PM"); -select makedate(1997,1); -select addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"); -select maketime(23,11,12); -select timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"); - -SET time_format="%H%i%s"; ---error 1105 -SET time_format="%h%i%s"; ---error 1105 +# +# Test setting a lot of different formats to see which formats are accepted and +# which aren't +# + +SET time_format='%H%i%s'; +SET time_format='%H:%i:%s.%f'; +SET time_format='%h-%i-%s.%f%p'; +SET time_format='%h:%i:%s.%f %p'; +SET time_format='%h:%i:%s%p'; + +SET date_format='%Y%m%d'; +SET date_format='%Y.%m.%d'; +SET date_format='%d.%m.%Y'; +SET date_format='%m-%d-%Y'; + +set datetime_format= '%Y%m%d%H%i%s'; +set datetime_format= '%Y-%m-%d %H:%i:%s'; +set datetime_format= '%m-%d-%y %H:%i:%s.%f'; +set datetime_format= '%d-%m-%Y %h:%i:%s%p'; +set datetime_format= '%H:%i:%s %Y-%m-%d'; +set datetime_format= '%H:%i:%s.%f %m-%d-%Y'; +set datetime_format= '%h:%i:%s %p %Y-%m-%d'; +set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d'; + +SHOW SESSION VARIABLES LIKE "%format"; + +--error 1231 +SET time_format='%h:%i:%s'; +--error 1231 +SET time_format='%H %i:%s'; +--error 1231 +SET time_format='%H::%i:%s'; +--error 1231 +SET time_format='%H:%i:%s%f'; +--error 1231 +SET time_format='%H:%i.%f:%s'; +--error 1231 +SET time_format='%H:%i:%s%p'; +--error 1231 +SET time_format='%h:%i:%s.%f %p %Y-%m-%d'; +--error 1231 +SET time_format='%H%i%s.%f'; +--error 1231 +SET time_format='%H:%i-%s.%f'; +--error 1231 SET date_format='%d.%m.%d'; ---error 1105 -SET datetime_format="%d.%m.%y %h.%i.%s"; +--error 1231 +SET datetime_format='%h.%m.%y %d.%i.%s'; +--error 1231 +set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d'; + +# +# Test GLOBAL values + +set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d'; +SET SESSION datetime_format=default; +select @@global.datetime_format, @@session.datetime_format; +SET GLOBAL datetime_format=default; +SET SESSION datetime_format=default; +select @@global.datetime_format, @@session.datetime_format; SET GLOBAL date_format=default; -SHOW GLOBAL VARIABLES LIKE "date_format%"; SET GLOBAL time_format=default; -SHOW GLOBAL VARIABLES LIKE "time_format%"; SET GLOBAL datetime_format=default; -SHOW GLOBAL VARIABLES LIKE "datetime_format%"; - -SET date_format=default; -SHOW SESSION VARIABLES LIKE "date_format%"; SET time_format=default; -SHOW SESSION VARIABLES LIKE "time_format%"; +SET date_format=default; SET datetime_format=default; -SHOW SESSION VARIABLES LIKE "datetime_format%"; - -SET time_format='%i:%s:%H'; -select cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME); - -SET GLOBAL date_format='%Y-%m-%d'; -SET GLOBAL time_format='%H:%i:%s'; -SET GLOBAL datetime_format='%Y-%m-%d %H:%i:%s'; -SET date_format='%Y-%m-%d'; -SET time_format='%H:%i:%s'; -SET datetime_format='%Y-%m-%d %H:%i:%s'; - -select str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S"); -select str_to_date("15 September 2001", "%d %M %Y"); -select str_to_date("15 Septembeb 2001", "%d %M %Y"); -select str_to_date("15 MAY 2001", "%d %b %Y"); -select str_to_date("Sunday 15 MAY 2001", "%W %d %b %Y"); -select str_to_date("Sundai 15 MAY 2001", "%W %d %b %Y"); -select str_to_date("Sundai 15 MA", "%W %d %b %Y"); -select str_to_date("Tuesday 52 2001", "%W %V %X"); -select str_to_date("Sunday 01 2001", "%W %V %X"); -select str_to_date("Tuesday 00 2002", "%W %U %Y"); -select str_to_date("Thursday 53 1998", "%W %u %Y"); -select str_to_date("15-01-2001", "%d-%m-%Y %H:%i:%S"); -select str_to_date("15-01-20", "%d-%m-%Y"); -select str_to_date("15-2001-1", "%d-%Y-%c"); + +# +# The following tests will work only when we at some point will enable +# dynamic changing of formats +# + +# SET date_format='%d.%m.%Y'; +# select CAST('01.01.2001' as DATE) as a; +# SET datetime_format='%d.%m.%Y %H.%i.%s'; +# select CAST('01.01.2001 05.12.06' as DATETIME) as a; +# SET time_format='%H.%i.%s'; +# select CAST('05.12.06' as TIME) as a; +# +# SET datetime_format='%d.%m.%Y %h:%i:%s %p'; +# select CAST('01.01.2001 05:12:06AM' as DATETIME) as a; +# select CAST('01.01.2001 05:12:06 PM' as DATETIME) as a; +# +# SET time_format='%h:%i:%s %p'; +# select CAST('05:12:06 AM' as TIME) as a; +# select CAST('05:12:06.1234PM' as TIME) as a; +# +# SET time_format='%h.%i.%s %p'; +# SET date_format='%d.%m.%y'; +# SET datetime_format='%d.%m.%y %h.%i.%s %p'; +# select CAST('12-12-06' as DATE) as a; +# +# select adddate('01.01.97 11.59.59.000001 PM', 10); +# select datediff('31.12.97 11.59:59.000001 PM','01.01.98'); +# select weekofyear('31.11.97 11:59:59.000001 PM'); +# select makedate(1997,1); +# select addtime('31.12.97 11.59.59.999999 PM', '1 1.1.1.000002'); +# select maketime(23,11,12); +# select timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM'); +# +# SET time_format='%i:%s:%H'; +# select cast(str_to_date('15-01-2001 12:59:59', '%d-%m-%Y %H:%i:%S') as TIME); + +# +# Test of str_to_date +# + +select str_to_date(concat('15-01-2001',' 2:59:58.999'), + concat('%d-%m-%Y',' ','%H:%i:%s.%f')); + +create table t1 (date char(30), format char(30) not null); +insert into t1 values +('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'), +('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S'), +('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'), +('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'), +('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'), +('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'), +('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'), +('10:20:10', '%H:%i:%s'), +('10:20:10', '%h:%i:%s.%f'), +('10:20:10AM', '%h:%i:%s%p'), +('10:20:10.44AM', '%h:%i:%s.%f%p'), +('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'), +('15 September 2001', '%d %M %Y'), +('15 SEPTEMB 2001', '%d %M %Y'), +('15 MAY 2001', '%d %b %Y'), +('Sunday 15 MAY 2001', '%W %d %b %Y'), +('Sund 15 MAY 2001', '%W %d %b %Y'), +('Tuesday 00 2002', '%W %U %Y'), +('Thursday 53 1998', '%W %u %Y'), +('15-01-2001', '%d-%m-%Y %H:%i:%S'), +('15-01-20', '%d-%m-%y'), +('15-2001-1', '%d-%Y-%c'); + +# Use through protocol functions +select date,format,str_to_date(date, format) as str_to_date from t1; +# Use as a string +select date,format,concat('',str_to_date(date, format)) as con from t1; +# Use as datetime +select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1; +select date,format,DATE(str_to_date(date, format)) as date2 from t1; +select date,format,TIME(str_to_date(date, format)) as time from t1; +select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1; + +# Test wrong dates + +truncate table t1; +insert into t1 values +('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'), +('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'), +('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'), +('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'), +('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'), +('10:20:10AM', '%H:%i:%s%p'), +('15 Septembei 2001', '%d %M %Y'), +('15 Ju 2001', '%d %M %Y'), +('Sund 15 MA', '%W %d %b %Y'), +('Sunday 01 2001', '%W %V %X'), +('Thursdai 12 1998', '%W %u %Y'), +(NULL, get_format(DATE,'USA')), +('Tuesday 52 2001', '%W %V %X'); +select date,format,str_to_date(date, format) as str_to_date from t1; +select date,format,concat(str_to_date(date, format),'') as con from t1; + +# Test 'maybe' date formats and 'strange but correct' results + +truncate table t1; +insert into t1 values +('10:20:10AM', '%h:%i:%s'), +('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'), +('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'); + +select date,format,str_to_date(date, format) as str_to_date from t1; +select date,format,concat(str_to_date(date, format),'') as con from t1; + +drop table t1; + +# +# Test of get_format +# select get_format(DATE, 'USA') as a; select get_format(TIME, 'internal') as a; select get_format(DATETIME, 'eur') as a; +select get_format(DATE, 'TEST') as a; +select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')); +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"); diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 422ca23de90..3aace91447d 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -16,6 +16,7 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), # nl search select * from t1 where MATCH(a,b) AGAINST ("collections"); +explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); select * from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); select * from t1 where MATCH(a,b) AGAINST ("only"); @@ -42,6 +43,7 @@ explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like ' # boolean search select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); +explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); @@ -131,9 +133,9 @@ select * from t2 having MATCH inhalt AGAINST ('foobar'); # check of fulltext errors # ---error 1281 +--error 1286 CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i)); ---error 1281 +--error 1286 CREATE TABLE t3 (t int(11),i text, j varchar(200) CHARACTER SET latin2, fulltext tix (i,j)); @@ -226,6 +228,12 @@ insert into t2 values (1, 1, 'xxfoo'); insert into t2 values (2, 1, 'xxbar'); insert into t2 values (3, 1, 'xxbuz'); select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); + +# +# bug with many short (< ft_min_word_len) words in boolean search +# +select * from t2 where match name against ('a* b* c* d* e* f*' in boolean mode); + drop table t1,t2; # diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 069fbed8562..06ebb388517 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -7,7 +7,9 @@ select @test_compress_string:='string for test compress function aaaaaaaaaaaaaaa select length(@test_compress_string); select uncompress(compress(@test_compress_string)); +explain extended select uncompress(compress(@test_compress_string)); select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string); +explain extended select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string); select uncompressed_length(compress(@test_compress_string)); select length(compress(@test_compress_string))<length(@test_compress_string); diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test index c1c7090cab3..f352a98e3cd 100644 --- a/mysql-test/t/func_crypt.test +++ b/mysql-test/t/func_crypt.test @@ -1,9 +1,21 @@ -- source include/have_crypt.inc +--disable_warnings +drop table if exists t1; +--enable_warnings + select length(encrypt('foo', 'ff')) <> 0; --replace_result $1$aa$4OSUA5cjdx0RUQ08opV27/ aaqPiZY5xR5l. +create table t1 (name varchar(50), pw varchar(64)); +insert into t1 values ('tom', password('my_pass')); +set @pass='my_pass'; +select name from t1 where name='tom' and pw=password(@pass); +select name from t1 where name='tom' and pw=password(@undefined); +drop table t1; + # Test new and old password handling functions + select password('abc'); select password(''); select old_password('abc'); @@ -34,3 +46,5 @@ select password('idkfa'); select password(' idkfa'); select old_password('idkfa'); select old_password(' i d k f a '); + +explain extended select password('idkfa '), old_password('idkfa'); diff --git a/mysql-test/t/func_default.test b/mysql-test/t/func_default.test new file mode 100644 index 00000000000..6ae9f088f92 --- /dev/null +++ b/mysql-test/t/func_default.test @@ -0,0 +1,19 @@ +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + + +create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14'); + +insert into t1 values ('','',0,0.0); +select default(str), default(strnull), default(intg), default(rel) from t1; +explain extended select default(str), default(strnull), default(intg), default(rel) from t1; +select * from t1 where str <> default(str); +explain select * from t1 where str <> default(str); + +#TODO: uncomment when bug will be fixed +#create table t2 select default(str), default(strnull), default(intg), default(rel) from t1; +#show create table from t1; +#insert into t2 select select default(str), default(strnull), default(intg), default(rel) from t1; + +drop table t1;
\ No newline at end of file diff --git a/mysql-test/t/func_encrypt.test b/mysql-test/t/func_encrypt.test index fbb71f850e2..fe81a814dda 100644 --- a/mysql-test/t/func_encrypt.test +++ b/mysql-test/t/func_encrypt.test @@ -68,3 +68,5 @@ select @a = des_decrypt(des_encrypt("hello")); select hex("hello"); select hex(des_decrypt(des_encrypt("hello",4),'password2')); select hex(des_decrypt(des_encrypt("hello","hidden"))); + +explain extended select des_decrypt(des_encrypt("hello",4),'password2'), des_decrypt(des_encrypt("hello","hidden")); diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 041225bdaf5..d004c81f14e 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -18,6 +18,7 @@ insert into t1 values (3,9,"D","c"); # Test of MySQL simple request select grp,group_concat(c) from t1 group by grp; +explain extended select grp,group_concat(c) from t1 group by grp; select grp,group_concat(a,c) from t1 group by grp; select grp,group_concat("(",a,":",c,")") from t1 group by grp; @@ -33,9 +34,11 @@ select grp,group_concat(c order by 1) from t1 group by grp; select grp,group_concat(c order by "c") from t1 group by grp; select grp,group_concat(distinct c order by c) from t1 group by grp; select grp,group_concat(distinct c order by c desc) from t1 group by grp; +explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp; select grp,group_concat(c order by c separator ",") from t1 group by grp; select grp,group_concat(c order by c desc separator ",") from t1 group by grp; select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; +explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp; # Test of SQL_LIST objects diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index ac3a1986d93..9e0184d35ce 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -346,7 +346,8 @@ select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) 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; insert into t1 values (3,1); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -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; +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; +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; drop table t1; --disable_warnings create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) type=InnoDB; diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 21e5836c781..7842df4fd0a 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -20,6 +20,13 @@ select if(1,st,st) s from t1 order by s; select if(u=1,st,st) s from t1 order by s; select if(u=1,binary st,st) s from t1 order by s; select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; +explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; + +# +# NULLIF test +# +select nullif(u=0, 'test') from t1; +explain extended select nullif(u=0, 'test') from t1; drop table t1; # diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 689b061f88d..9ff768ec76b 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -72,6 +72,7 @@ select * from t1 where a in ('a'); select * from t1 where 'a' collate latin1_general_ci in (a,b,c); select * from t1 where 'a' collate latin1_bin in (a,b,c); select * from t1 where 'a' in (a,b,c collate latin1_bin); +explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin); drop table t1; select '1.0' in (1,2); diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index bd125dafd53..b6e2d3bd725 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -3,17 +3,30 @@ # select floor(5.5),floor(-5.5); +explain extended select floor(5.5),floor(-5.5); select ceiling(5.5),ceiling(-5.5); +explain extended select ceiling(5.5),ceiling(-5.5); select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1); +explain extended select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1); select round(5.5),round(-5.5); +explain extended select round(5.5),round(-5.5); select 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); +explain extended select abs(-10), sign(-5), sign(5), 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); +explain extended 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); select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); +explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); select log2(8),log2(15),log2(-2),log2(0),log2(NULL); +explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL); select log10(100),log10(18),log10(-4),log10(0),log10(NULL); +explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL); select pow(10,log10(10)),power(2,4); +explain extended select pow(10,log10(10)),power(2,4); set @@rand_seed1=10000000,@@rand_seed2=1000000; select rand(999999),rand(); +explain extended select rand(999999),rand(); select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); +explain extended select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); select degrees(pi()),radians(360); +explain extended select degrees(pi()),radians(360); diff --git a/mysql-test/t/func_op.test b/mysql-test/t/func_op.test index 778c8406b8d..cb6ca58f193 100644 --- a/mysql-test/t/func_op.test +++ b/mysql-test/t/func_op.test @@ -3,5 +3,7 @@ # Simple operands and arithmetic grouping select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2; +explain extended select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2; select 1 | (1+1),5 & 3,bit_count(7) ; +explain extended select 1 | (1+1),5 & 3,bit_count(7) ; select 1 << 32,1 << 63, 1 << 64, 4 >> 2, 4 >> 63, 1<< 63 >> 60; diff --git a/mysql-test/t/func_regexp.test b/mysql-test/t/func_regexp.test index 6ecb56ef9c4..edfa9afcfa6 100644 --- a/mysql-test/t/func_regexp.test +++ b/mysql-test/t/func_regexp.test @@ -36,6 +36,7 @@ drop table t1; create table t1 (xxx char(128)); insert into t1 (xxx) values('this is a test of some long text to see what happens'); select * from t1 where xxx regexp('is a test of some long text to'); +explain extended select * from t1 where xxx regexp('is a test of some long text to'); select * from t1 where xxx regexp('is a test of some long text to '); select * from t1 where xxx regexp('is a test of some long text to s'); select * from t1 where xxx regexp('is a test of some long text to se'); diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index b152c94096c..7d24140c75d 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -3,6 +3,7 @@ # 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; +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; 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"); select find_in_set("d","a,b,c"),find_in_set("dd","a,bbb,d"),find_in_set("bb","a,bbb,dd"); select make_set(0,'a','b','c'),make_set(-1,'a','b','c'),make_set(1,'a','b','c'),make_set(2,'a','b','c'),make_set(1+2,concat('a','b'),'c'); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index b421f47e5b8..4ae586256ae 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -14,7 +14,9 @@ select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; select 'hello' 'monty'; select length('\n\t\r\b\0\_\%\\'); select bit_length('\n\t\r\b\0\_\%\\'); -select concat('monty',' was here ','again'),length('hello'),char(ascii('h')); +select char_length('\n\t\r\b\0\_\%\\'); +select length(_latin1'\n\t\n\b\0\\_\\%\\'); +select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'); select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ; select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE'); select position(binary 'll' in 'hello'),position('a' in binary 'hello'); @@ -46,6 +48,7 @@ select null sounds like 'null'; select 'null' sounds like null; select null sounds like null; select md5('hello'); +select crc32("123"); select sha('abc'); select sha1('abc'); select aes_decrypt(aes_encrypt('abc','1'),'1'); @@ -336,3 +339,7 @@ INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and a INSERT INTO t1 VALUES ('House passes the CAREERS bill'); SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; DROP TABLE t1; + +select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); + +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"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty"); diff --git a/mysql-test/t/func_system.test b/mysql-test/t/func_system.test index 998b2a5c3f4..f3b9b4ffc3f 100644 --- a/mysql-test/t/func_system.test +++ b/mysql-test/t/func_system.test @@ -17,6 +17,7 @@ select version()>="3.23.29"; select version()>=_utf8"3.23.29"; select version()>=_latin1"3.23.29"; select charset(version()); +explain extended select database(), user(); create table t1 (version char(40)) select database(), user(), version() as 'version'; show create table t1; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 95b10c4ffb9..86e0ee2637b 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -19,13 +19,19 @@ select 'b' between 'a' and 'c', 'B' between 'a' and 'c'; select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); select -1.49 or -1.49,0.6 or 0.6; select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +explain extended select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; select 10 % 7, 10 mod 7, 10 div 3; +explain extended select 10 % 7, 10 mod 7, 10 div 3; select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2; +explain extended select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2; create table t1 (a int); insert t1 values (1); select * from t1 where 1 xor 1; +explain extended select * from t1 where 1 xor 1; +select - a from t1; +explain extended select - a from t1; drop table t1; # @@ -41,6 +47,7 @@ select 1 and 0 or 2, 2 or 1 and 0; # select _koi8r'a' = _koi8r'A'; select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci; +explain extended select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci; select _koi8r'a' = _koi8r'A' COLLATE koi8r_bin; select _koi8r'a' COLLATE koi8r_general_ci = _koi8r'A'; select _koi8r'a' COLLATE koi8r_bin = _koi8r'A'; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 30d616915ab..96bdb547469 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -7,6 +7,7 @@ drop table if exists t1,t2,t3; select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29"); select period_add("9602",-12),period_diff(199505,"9404") ; + select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0; select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), @@ -231,3 +232,5 @@ select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d" select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0; select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0; select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0; + +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); diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index b7c41135123..c8f50e968ab 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -82,9 +82,11 @@ SELECT fid, Dimension(g) FROM geo; SELECT fid, GeometryType(g) FROM geo; SELECT fid, IsEmpty(g) FROM geo; SELECT fid, AsText(Envelope(g)) FROM geo; +explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from geo; SELECT fid, X(g) FROM pt; SELECT fid, Y(g) FROM pt; +explain extended select X(g),Y(g) FROM pt; SELECT fid, AsText(StartPoint(g)) FROM ls; SELECT fid, AsText(EndPoint(g)) FROM ls; @@ -92,12 +94,14 @@ SELECT fid, GLength(g) FROM ls; SELECT fid, NumPoints(g) FROM ls; SELECT fid, AsText(PointN(g, 2)) FROM ls; SELECT fid, IsClosed(g) FROM ls; +explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM ls; SELECT fid, AsText(Centroid(g)) FROM p; SELECT fid, Area(g) FROM p; SELECT fid, AsText(ExteriorRing(g)) FROM p; SELECT fid, NumInteriorRings(g) FROM p; SELECT fid, AsText(InteriorRingN(g, 1)) FROM p; +explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM p; SELECT fid, IsClosed(g) FROM mls; @@ -108,17 +112,24 @@ SELECT fid, NumGeometries(g) from mpt; SELECT fid, NumGeometries(g) from mls; SELECT fid, NumGeometries(g) from mp; SELECT fid, NumGeometries(g) from gc; +explain extended SELECT fid, NumGeometries(g) from mpt; SELECT fid, AsText(GeometryN(g, 2)) from mpt; SELECT fid, AsText(GeometryN(g, 2)) from mls; SELECT fid, AsText(GeometryN(g, 2)) from mp; SELECT fid, AsText(GeometryN(g, 2)) from gc; +explain extended SELECT fid, AsText(GeometryN(g, 2)) from mpt; SELECT g1.fid as first, g2.fid as second, Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gc g1, gc g2 ORDER BY first, second; +explain extended SELECT g1.fid as first, g2.fid as second, +Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, +Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, +Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r +FROM gc g1, gc g2 ORDER BY first, second; DROP TABLE pt, ls, p, mpt, mls, mp, gc, geo; @@ -142,3 +153,9 @@ SHOW FIELDS FROM g1; DROP TABLE g1; SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); +explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); +explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)')))); +SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +#select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)),issimple(PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),issimple(GeometryFromText('POINT(1 4)')), issimple(AsWKB(GeometryFromText('POINT(1 4)'))); +explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)); diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 752896ce7b2..705e32c82df 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -260,7 +260,7 @@ explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null; select spid,count(*) from t1 where spid between 1 and 2 group by spid; select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; -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; explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; select sql_big_result spid,sum(userid) from t1 group by spid desc; explain select sql_big_result score,count(*) from t1 group by score desc; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 7d5fbee011f..7e4cbe76cca 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -10,6 +10,7 @@ select count(a) as b from t1 where a=0 having b > 0; insert into t1 values (null); select count(a) as b from t1 where a=0 having b > 0; select count(a) as b from t1 where a=0 having b >=0; +explain extended select count(a) as b from t1 where a=0 having b >=0; drop table t1; # diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index 4f19aca8738..550bce867cd 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -23,4 +23,6 @@ INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a); SELECT *, VALUES(a) FROM t1; +explain extended SELECT *, VALUES(a) FROM t1; +explain extended select * from t1 where values(a); DROP TABLE t1; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 01db0420943..954fdcb925a 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -9,11 +9,16 @@ INSERT INTO t1 VALUES (1), (2); --exec $MYSQL_DUMP --skip-all -X test t1 DROP TABLE t1; +CREATE TABLE t1(a int, b text, c varchar(3)); +INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); +--exec $MYSQL_DUMP --skip-all -X test t1 +DROP TABLE t1; + # # Bug #1707 # -CREATE TABLE `"t"1` (`a"b"` char(2)); -INSERT INTO `"t"1` VALUES ("1\""), ("\"2"); ---exec $MYSQL_DUMP --skip-all -X test \"t\"1 -DROP TABLE `"t"1`; +CREATE TABLE t1 (`a"b"` char(2)); +INSERT INTO t1 VALUES ("1\""), ("\"2"); +--exec $MYSQL_DUMP --skip-all -X test t1 +DROP TABLE t1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index fa36249dce0..0be755ba7ad 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -8,6 +8,7 @@ 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; +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; select 1 | NULL,1 & NULL,1+NULL,1-NULL; select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0; select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null; @@ -15,10 +16,12 @@ select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL); select 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; +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; SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0; SELECT (NULL OR NULL) IS NULL; select NULL AND 0, 0 and NULL; select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); +explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); create table t1 (x int); insert into t1 values (null); diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 17bf6230f76..7443aeee6f4 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -32,7 +32,7 @@ select product, sum(profit),avg(profit) from t1 group by product with rollup; # Sub totals select product, country_id , year, sum(profit) from t1 group by product, country_id, year; select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup; -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; select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup; # limit diff --git a/mysql-test/t/overflow.test b/mysql-test/t/overflow.test index 6619a87cabb..7a9616fd24e 100644 --- a/mysql-test/t/overflow.test +++ b/mysql-test/t/overflow.test @@ -1,4 +1,4 @@ connect (con1,localhost,boo,,); connection con1; --- error 1064,1102 +-- error 1064,1102,1279 drop database AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA; diff --git a/mysql-test/t/query_cache-master.opt b/mysql-test/t/query_cache-master.opt deleted file mode 100644 index cfdce628e74..00000000000 --- a/mysql-test/t/query_cache-master.opt +++ /dev/null @@ -1 +0,0 @@ ---set-variable=query_cache_size=1355776 diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index ed4c5c91c4d..14cbf4c906d 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -3,6 +3,7 @@ # # Tests with query cache # +set GLOBAL query_cache_size=1355776; # Reset query cache variables. @@ -11,7 +12,7 @@ flush query cache; # This crashed in some versions reset query cache; flush status; --disable_warnings -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t11,t21; drop database if exists mysqltest; --enable_warnings @@ -184,6 +185,7 @@ select RAND() from t1; select UNIX_TIMESTAMP() from t1; select USER() from t1; select benchmark(1,1) from t1; +explain extended select benchmark(1,1) from t1; show status like "Qcache_queries_in_cache"; # # Tests when the cache is filled @@ -489,6 +491,55 @@ SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; # +# query cache crash on using same table twice in one query test +# +flush query cache; +reset query cache; +flush status; +set GLOBAL query_cache_size=1048576; + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +create table t2 (a text not null); +create table t3 (a text not null); +insert into t3 values("1111111111111111111111111111111111111111111111111111"); +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +insert into t2 select * from t3; +insert into t3 select * from t2; +drop table t2; +create table t2 (a int not null); +insert into t2 values (1),(2),(3); +create table t4 (a int not null); +insert into t4 values (1),(2),(3); + +disable_result_log; +select * from t4; +select * from t2; +select * from t1 as tt, t1 as ttt where tt.a=1 and ttt.a=2; +select * from t2; +select * from t4; +select * from t1 as tt, t1 as ttt where tt.a=1 and ttt.a=2; +select * from t2; +select * from t4; +select * from t1 as tt, t1 as ttt where tt.a=1 and ttt.a=2; + +delete from t2 where a=1; +flush query cache; +select * from t3; +enable_result_log; +delete from t4 where a=1; +flush query cache; + +drop table t1,t2,t3,t4; + +# # Test character set related variables: # character_set_result # character_set_client @@ -534,3 +585,4 @@ show status like "Qcache_queries_in_cache"; # Keep things tidy # DROP TABLE t1; +SET GLOBAL query_cache_size=0; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 40ae49f8005..65ba2ee6671 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -205,6 +205,30 @@ explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; explain select * from t1 force index(i2), t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; DROP TABLE t1,t2; +# +# bug #1724: use RANGE on more selective column instead of REF on less +# selective + +CREATE TABLE t1 ( + a int(11) default NULL, + b int(11) default NULL, + KEY a (a), + KEY b (b) +) TYPE=MyISAM; + + +INSERT INTO t1 VALUES +(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2), +(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3), +(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5), +(33,5),(33,5),(33,5),(33,5),(34,5),(35,5); + +# we expect that optimizer will choose key on A +EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; +SELECT * FROM t1 WHERE a IN(1,2) AND b=5; + +DROP TABLE t1; + # test for a bug with in() and unique key create table t1 (id int(10) primary key); diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 252830cfc98..ecb0c6adfc1 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -18,6 +18,7 @@ select (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))); -- error 1240 select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4)); select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); +explain extended select 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); SELECT (1,2,3)=(1,NULL,3); diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index f464f1e2751..445bd579279 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -62,10 +62,12 @@ enable_query_log; # Try to cause a large relay log lag on the slave connection slave; select get_lock("hold_slave",10); +explain extended select get_lock("hold_slave",10); start slave; #hope this is long enough for I/O thread to fetch over 16K relay log data sleep 3; select release_lock("hold_slave"); +explain extended select release_lock("hold_slave"); unlock tables; connection master; diff --git a/mysql-test/t/rpl_chain_temp_table.test b/mysql-test/t/rpl_chain_temp_table.test new file mode 100644 index 00000000000..007b018e9d8 --- /dev/null +++ b/mysql-test/t/rpl_chain_temp_table.test @@ -0,0 +1,99 @@ + # This test makes some assumptions about values of thread ids, which should be +# true if the servers have been restarted for this test. So we want to +# stop/restart servers. Note that if assumptions are wrong, the test will not +# fail; it will just fail to test the error-prone scenario. +# Using the manager is the only way to have more than one slave server. +# So you must run this test with --manager. + +require_manager; +server_stop master; +server_start master; +server_stop slave; +server_start slave; +# no need for slave_sec (no assumptions on thread ids for this server). + +source include/master-slave.inc; +connect (slave_sec,localhost,root,,test,0,slave.sock-1); +connection master; +save_master_pos; +connection slave; +sync_with_master; +reset master; +save_master_pos; +connection slave_sec; +eval change master to master_host='127.0.0.1',master_port=$SLAVE_MYPORT, master_user='root'; +start slave; +sync_with_master; + +# :P now we have a chain ready-to-test. + +connection master; +create temporary table t1 (a int); +save_master_pos; +connection slave; +sync_with_master; +connection master1; +create temporary table t1 (a int); +save_master_pos; +connection slave; +sync_with_master; +save_master_pos; + +# First test: + +connection slave_sec; +# Before BUG#1686 ("If 2 master threads with same-name temp table, slave makes +# bad binlog") was fixed, sync_with_master failed +sync_with_master; +show status like 'slave_open_temp_tables'; + +# 'master' and 'master1' usually have thread id 2-3 or 3-4. +# 'slave' and 'slave1' usually have thread id 2-3. +connection slave; +create temporary table t1 (a int); +connection slave1; +create temporary table t1 (a int); +# So it's likely that in the binlog of slave we get +# server_id=of_master thread_id=3 create temp... +# server_id=of_slave thread_id=3 create temp... +# which would confuse slave-sec unless slave-sec uses server id to distinguish +# between temp tables (here thread id is obviously not enough to distinguish). + +save_master_pos; + +# Second test: + +connection slave_sec; +# If we did not use the server id to distinguish between temp tables, +# sync_with_master would fail +sync_with_master; +show status like 'slave_open_temp_tables'; + +# Third test (BUG#1240 "slave of slave breaks when STOP SLAVE was issud on +# parent slave and temp tables"). +stop slave; +connection slave; +insert into t1 values(1); +create table t2 as select * from t1; +save_master_pos; +connection slave_sec; +start slave; +sync_with_master; +show status like 'slave_open_temp_tables'; +select * from t2; + +# clean up +connection slave; +drop table t2; +save_master_pos; +connection slave_sec; +sync_with_master; + +# On purpose, we don't delete the temporary tables explicitely. +# So temp tables remain on slave (remember they are not deleted when the slave +# SQL thread terminates). If you run this test with +# --valgrind --valgrind-options=--show-reachable=yes +# you will see if they get cleaned up at slave's shutdown (that is, if the +# memory they use is freed (it should) by mysqld before it terminates). +# If they wouldn't be cleaned up, you would see some "still reachable" blocks in +# Valgrind. diff --git a/mysql-test/t/rpl_get_lock.test b/mysql-test/t/rpl_get_lock.test index e93268e6074..c3b033fb03a 100644 --- a/mysql-test/t/rpl_get_lock.test +++ b/mysql-test/t/rpl_get_lock.test @@ -5,6 +5,7 @@ insert into t1 values(get_lock("lock",2)); dirty_close master; connection master1; select get_lock("lock",2); + select release_lock("lock"); #ignore disable_query_log; @@ -21,7 +22,8 @@ connection slave; sync_with_master; select get_lock("lock",3); select * from t1; -select is_free_lock("lock"); +select is_free_lock("lock"), is_used_lock("lock"); +explain extended select is_free_lock("lock"), is_used_lock("lock"); # Check lock functions select is_free_lock("lock2"); select is_free_lock(NULL); diff --git a/mysql-test/t/rpl_master_pos_wait.test b/mysql-test/t/rpl_master_pos_wait.test index 24479636c91..4d4d51b04ab 100644 --- a/mysql-test/t/rpl_master_pos_wait.test +++ b/mysql-test/t/rpl_master_pos_wait.test @@ -7,6 +7,7 @@ sync_with_master; # Ask for a master log that has certainly not been reached yet # timeout= 2 seconds select master_pos_wait('master-bin.999999',0,2); +explain extended select master_pos_wait('master-bin.999999',0,2); # Testcase for bug 651 (master_pos_wait() hangs if slave idle and STOP SLAVE). send select master_pos_wait('master-bin.999999',0); connection slave1; diff --git a/mysql-test/t/rpl_reset_slave.test b/mysql-test/t/rpl_reset_slave.test index e03916ec73a..d58e9c711d1 100644 --- a/mysql-test/t/rpl_reset_slave.test +++ b/mysql-test/t/rpl_reset_slave.test @@ -2,6 +2,8 @@ # --master-* options from mysqld, as this is what is going to be used next time # slave threads will be started). In bug 985, it displayed old values (of before # RESET SLAVE). +# See if slave crashes when doing a CREATE TEMPORARY TABLE twice, separated by +# RESET SLAVE. source include/master-slave.inc; connection master; @@ -28,3 +30,19 @@ sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 33 # show slave status; + +# test of crash with temp tables & RESET SLAVE +# (test to see if RESET SLAVE clears temp tables in memory and disk) +stop slave; +reset slave; +start slave; +connection master; +create temporary table t1 (a int); +save_master_pos; +connection slave; +sync_with_master; +stop slave; +reset slave; +start slave; +sync_with_master; +show status like 'slave_open_temp_tables'; diff --git a/mysql-test/t/rpl_temporary-master.opt b/mysql-test/t/rpl_temporary-master.opt new file mode 100644 index 00000000000..e69de29bb2d --- /dev/null +++ b/mysql-test/t/rpl_temporary-master.opt diff --git a/mysql-test/t/rpl_temporary.test b/mysql-test/t/rpl_temporary.test index f1373d7ef23..0df8ceb6377 100644 --- a/mysql-test/t/rpl_temporary.test +++ b/mysql-test/t/rpl_temporary.test @@ -108,3 +108,11 @@ drop temporary table t3; select * from t2; drop table t1,t2; + +# Create last a temporary table that is not dropped at end to ensure that we +# don't get any memory leaks for this + +create temporary table t3 (f int); +sync_with_master; + +# The server will now close done diff --git a/mysql-test/t/rpl_trunc_binlog.test b/mysql-test/t/rpl_trunc_binlog.test index 4c14ec72b5c..32052af9184 100644 --- a/mysql-test/t/rpl_trunc_binlog.test +++ b/mysql-test/t/rpl_trunc_binlog.test @@ -23,5 +23,3 @@ sleep 3; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 33 # show slave status; -connection master; -reset master; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index d727befe661..b817544bfb9 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1596,6 +1596,7 @@ select count(*) from t1; select companynr,count(*),sum(fld1) from t2 group by companynr; select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; +explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0b8d862a1fe..f356e7931c8 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4,11 +4,11 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; --enable_warnings select (select 2); -explain select (select 2); +explain extended select (select 2); SELECT (SELECT 1) UNION SELECT (SELECT 2); -explain SELECT (SELECT 1) UNION SELECT (SELECT 2); +explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2); SELECT (SELECT (SELECT 0 UNION SELECT 0)); -explain SELECT (SELECT (SELECT 0 UNION SELECT 0)); +explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0)); -- error 1246 SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; -- error 1246 @@ -16,7 +16,7 @@ SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a; -- error 1246 SELECT (SELECT a) as a; -EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; +EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; -- error 1054 SELECT (SELECT 1), a; @@ -74,17 +74,17 @@ insert into t3 values (6),(7),(3); select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3; (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); -explain (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); +explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; -explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as 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); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; -explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; select * from t3 where exists (select * from t2 where t2.b=t3.a); select * from t3 where not exists (select * from t2 where t2.b=t3.a); select * from t3 where a in (select b from t2); @@ -100,7 +100,7 @@ insert into t2 values (100, 5); select * from t3 where a < any (select b from t2); select * from t3 where a < all (select b from t2); select * from t3 where a >= any (select b from t2); -explain select * from t3 where a >= any (select b from t2); +explain extended select * from t3 where a >= any (select b from t2); select * from t3 where a >= all (select b from t2); delete from t2 where a=100; -- error 1240 @@ -120,7 +120,7 @@ insert into t5 values (5); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; insert into t5 values (2); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; -explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; -- error 1241 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -128,7 +128,7 @@ create table t7( uq int primary key, name char(25)); insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); insert into t6 values (1,1),(1,2),(2,2),(1,3); select * from t6 where exists (select * from t7 where uq = clinic_uq); -explain select * from t6 where exists (select * from t7 where uq = clinic_uq); +explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); # not unique fields -- error 1052 @@ -157,7 +157,7 @@ CREATE TABLE `t8` ( INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); -EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); +EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); -- error 1240 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); @@ -180,14 +180,14 @@ CREATE TABLE `t1` ( ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); -EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; -EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); +EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; +EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; -- error 1241 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; -EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); drop table t1; #forumconthardwarefr7 searchconthardwarefr7 @@ -276,9 +276,9 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); -- error 1241 -EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; -EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); +EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; +EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; CREATE TABLE t1 (a int(1)); @@ -395,11 +395,11 @@ SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (1),(2); SELECT * FROM t2 WHERE id IN (SELECT 1); -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); -- error 1093 @@ -455,7 +455,7 @@ select 1.5 > ALL (SELECT * from t1); select 10.5 > ALL (SELECT * from t1); select 1.5 > ANY (SELECT * from t1); select 10.5 > ANY (SELECT * from t1); -explain select (select a+1) from t1; +explain extended select (select a+1) from t1; select (select a+1) from t1; drop table t1; @@ -468,11 +468,11 @@ CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t2 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; -explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; +explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; CREATE TABLE t3 (a int(11) default '0'); INSERT INTO t3 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; -explain SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; +explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; drop table t1,t2,t3; #LIMIT is not supported now @@ -549,9 +549,9 @@ drop table t1; #test of uncacheable subqueries CREATE TABLE t1 (a int(1)); -EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1; -EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; -EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; drop table t1; @@ -613,7 +613,7 @@ drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3); -explain select a,(select (select rand() from t1 limit 1) from t1 limit 1) +explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1) from t1; drop table t1; @@ -677,10 +677,10 @@ CREATE TABLE `t1` ( ) TYPE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (pseudo) VALUES ('test'); SELECT 0 IN (SELECT 1 FROM t1 a); -EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a); +EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); -EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a); +EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); drop table t1; CREATE TABLE `t1` ( @@ -727,7 +727,7 @@ drop table t1; # create table t1 (id int not null auto_increment primary key, salary int, key(salary)); insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); -explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); +explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); drop table t1; CREATE TABLE t1 ( @@ -794,11 +794,11 @@ insert into t1 values (1,10), (2,20), (3,30), (4,40); insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); select * from t2 where t2.a in (select a from t1); -explain select * from t2 where t2.a in (select a from t1); +explain extended select * from t2 where t2.a in (select a from t1); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); -explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); +explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -816,15 +816,15 @@ enable_query_log; insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); select * from t2 where t2.a in (select a from t1); -explain select * from t2 where t2.a in (select a from t1); +explain extended select * from t2 where t2.a in (select a from t1); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); -explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); +explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31); -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); drop table t1, t2, t3; # @@ -891,7 +891,7 @@ create table t1 (s1 char(5)); select (select 'a','b' from t1 union select 'a','b' from t1) from t1; insert into t1 values ('tttt'); select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1); -explain (select * from t1); +explain extended (select * from t1); (select * from t1); drop table t1; @@ -906,10 +906,10 @@ select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; select s1, s1 = ANY (SELECT s1 FROM t2) from t1; select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; -explain select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; -explain select s1, s1 = ANY (SELECT s1 FROM t2) from t1; -explain select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; -explain select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; +explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; +explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; +explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; +explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; drop table t1,t2; # @@ -919,14 +919,14 @@ create table t2 (a int, b int); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); -explain select * from t3 where a >= all (select b from t2); +explain extended select * from t3 where a >= all (select b from t2); # # optimized static ALL/ANY with grouping # insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); -explain select * from t3 where a > all (select max(b) from t2 group by a); +explain extended select * from t3 where a > all (select max(b) from t2 group by a); drop table t2, t3; # diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index f618c342936..4be0cd0c6a2 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -65,7 +65,7 @@ drop table t1; # disable_query_log; ---error 1103,1103 +--error 1279,1279 create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="tmp"; # Check that we cannot link over a table from another database. @@ -75,7 +75,7 @@ create database mysqltest; --error 1,1 create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; ---error 1103,1103 +--error 1279,1279 create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; --error 1,1 diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index ebe342a4ef5..c9c3284fe0d 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -298,6 +298,10 @@ select charset(load_file('../../std_data/words.dat')), collation(load_file('../../std_data/words.dat')), coercibility(load_file('../../std_data/words.dat')); +explain extended select + charset(load_file('../../std_data/words.dat')), + collation(load_file('../../std_data/words.dat')), + 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; drop table t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index b64f0b4e2ee..1f3f4085734 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -25,7 +25,7 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; --error 1249 (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b; -explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; +explain extended (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; (select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; select found_rows(); select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2; @@ -275,7 +275,7 @@ create table t2 (a int not null primary key auto_increment, b int); insert into t1 (b) values (1),(2),(2),(3); insert into t2 (b) values (10),(11),(12),(13); -explain (select * from t1 where a=1) union (select * from t2 where a=1); +explain extended (select * from t1 where a=1) union (select * from t2 where a=1); (select * from t1 where a=5) union (select * from t2 where a=1); (select * from t1 where a=5 and a=6) union (select * from t2 where a=1); (select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1); diff --git a/mysql-test/t/varbinary.test b/mysql-test/t/varbinary.test index d58954a189b..9425bd7bd99 100644 --- a/mysql-test/t/varbinary.test +++ b/mysql-test/t/varbinary.test @@ -18,7 +18,7 @@ select 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; drop table t1; # diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index c53559ed4cf..6365ad77c57 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -16,6 +16,7 @@ select @test_int,@test_double,@test_string,@test_string2; set @test_int=null,@test_double=null,@test_string=null,@test_string2=null; select @test_int,@test_double,@test_string,@test_string2; select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; select @t5; # @@ -52,7 +53,9 @@ select @@local.max_join_size, @@global.max_join_size; select @@identity, length(@@version)>0; select @@VERSION=version(); select last_insert_id(345); +explain extended select last_insert_id(345); select @@IDENTITY,last_insert_id(), @@identity; +explain extended select @@IDENTITY,last_insert_id(), @@identity; set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; |