diff options
Diffstat (limited to 'mysql-test')
25 files changed, 242 insertions, 227 deletions
diff --git a/mysql-test/include/federated.inc b/mysql-test/include/federated.inc index 1c53b9ed2c5..15230f47ed8 100644 --- a/mysql-test/include/federated.inc +++ b/mysql-test/include/federated.inc @@ -5,7 +5,7 @@ source ./include/master-slave.inc; # remote table creation connection slave; ---replicate-ignore-db=federated +#--replicate-ignore-db=federated stop slave; --disable_warnings diff --git a/mysql-test/include/sp-vars.inc b/mysql-test/include/sp-vars.inc index 4bac883ee0e..c241af2fb54 100644 --- a/mysql-test/include/sp-vars.inc +++ b/mysql-test/include/sp-vars.inc @@ -1,6 +1,6 @@ delimiter |; ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE PROCEDURE sp_vars_check_dflt() BEGIN @@ -40,7 +40,7 @@ BEGIN SELECT v17, v18, v19, v20; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE PROCEDURE sp_vars_check_assignment() BEGIN @@ -89,35 +89,35 @@ BEGIN SELECT d1, d2, d3; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT BEGIN RETURN 1e200; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT BEGIN RETURN 10 * 10 * 10; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT BEGIN RETURN 'Hello, world'; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) BEGIN RETURN 12 * 10 + 34 + 0.1234; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER BEGIN @@ -126,6 +126,6 @@ BEGIN RETURN div_zero; END| ---------------------------------------------------------------------------- +# -------------------------------------------------------------------------- delimiter ;| diff --git a/mysql-test/mysql-test-run-shell.sh b/mysql-test/mysql-test-run-shell.sh index d75ad9286bc..366884a635a 100644 --- a/mysql-test/mysql-test-run-shell.sh +++ b/mysql-test/mysql-test-run-shell.sh @@ -188,19 +188,14 @@ if [ -d ./sql ] ; then SOURCE_DIST=1 else BINARY_DIST=1 -fi -# ... one level for tar.gz, two levels for a RPM installation -if [ -d ./bin ] ; then - # this is not perfect: we have - # /usr/share/mysql/ # mysql-test-run is here, so this is "$MYSQL_TEST_DIR" - # /usr/bin/ # with MySQL client programs - # so the existence of "/usr/share/bin/" would make this test fail. - BASEDIR=`pwd` -else - cd .. - BASEDIR=`pwd` + # ... one level for tar.gz, two levels for a RPM installation + if [ ! -f ./bin/mysql_upgrade ] ; then + # Has to be RPM installation + cd .. + fi fi +BASEDIR=`pwd` cd $MYSQL_TEST_DIR MYSQL_TEST_WINDIR=$MYSQL_TEST_DIR @@ -2188,6 +2183,9 @@ then $RM -f $MASTER_MYDDIR/log.* $RM -f $MASTER_MYDDIR"1"/log.* + # Remove old log and reject files + $RM -f r/*.reject r/*.progress r/*.log r/*.warnings + wait_for_master=$SLEEP_TIME_FOR_FIRST_MASTER wait_for_slave=$SLEEP_TIME_FOR_FIRST_SLAVE $ECHO "Installing Test Databases" diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 4c6391883e7..b7c2d247ca9 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2045,6 +2045,12 @@ sub cleanup_stale_files () { } closedir(DIR); } + + # Remove old log files + foreach my $name (glob("r/*.reject r/*.progress r/*.log r/*.warnings")) + { + unlink($name); + } } @@ -2426,7 +2432,7 @@ sub ndbcluster_start ($$) { sub rm_ndbcluster_tables ($) { my $dir= shift; foreach my $bin ( glob("$dir/mysql/apply_status*"), - glob("$dir/mysql/schema*") ) + glob("$dir/mysql/schema*")) { unlink($bin); } diff --git a/mysql-test/r/ctype_cp1250_ch.result b/mysql-test/r/ctype_cp1250_ch.result index 007d6103b8a..73f415732cd 100644 --- a/mysql-test/r/ctype_cp1250_ch.result +++ b/mysql-test/r/ctype_cp1250_ch.result @@ -1,4 +1,4 @@ -drop table if exists t1; +DROP TABLE IF EXISTS t1; SHOW COLLATION LIKE 'cp1250_czech_cs'; Collation Charset Id Default Compiled Sortlen cp1250_czech_cs cp1250 34 Yes 2 diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index aebee0b0c6f..1c9bd18d5d2 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -554,7 +554,7 @@ create table t1 ( a varchar(112) charset utf8 collate utf8_bin not null, primary key (a) ) select 'test' as a ; ---warning 1364 +#--warning 1364 show create table t1; drop table t1; @@ -567,7 +567,7 @@ CREATE TABLE t2 ( ); insert into t2 values(111); ---warning 1364 +#--warning 1364 create table t1 ( a varchar(12) charset utf8 collate utf8_bin not null, b int not null, primary key (a) @@ -575,7 +575,7 @@ create table t1 ( show create table t1; drop table t1; ---warning 1364 +#--warning 1364 create table t1 ( a varchar(12) charset utf8 collate utf8_bin not null, b int not null, primary key (a) @@ -583,7 +583,7 @@ create table t1 ( show create table t1; drop table t1; ---warning 1364 +#--warning 1364 create table t1 ( a varchar(12) charset utf8 collate utf8_bin not null, b int null, primary key (a) @@ -591,7 +591,7 @@ create table t1 ( show create table t1; drop table t1; ---warning 1364 +#--warning 1364 create table t1 ( a varchar(12) charset utf8 collate utf8_bin not null, b int not null, primary key (a) @@ -599,7 +599,7 @@ create table t1 ( show create table t1; drop table t1; ---warning 1364 +#--warning 1364 create table t1 ( a varchar(12) charset utf8 collate utf8_bin, b int not null, primary key (a) @@ -613,7 +613,7 @@ create table t1 ( ); insert into t1 values (1,1,1, 1,1,1, 1,1,1); ---warning 1364 +#--warning 1364 create table t2 ( a1 varchar(12) charset utf8 collate utf8_bin not null, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, @@ -621,20 +621,20 @@ create table t2 ( ) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; drop table t2; ---warning 1364 +#--warning 1364 create table t2 ( a1 varchar(12) charset utf8 collate utf8_bin, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int ) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1; drop table t1, t2; ---warning 1364 +#--warning 1364 create table t1 ( a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int ); insert into t1 values (1,1,1, 1,1,1, 1,1,1); ---warning 1364 +#--warning 1364 create table t2 ( a1 varchar(12) charset utf8 collate utf8_bin not null, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 82102a6078e..60d38394fc0 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1508,27 +1508,27 @@ DROP TABLE test_repair_table5; create table t1 (a int) engine=csv; insert t1 values (1); --enable_info -delete from t1; -- delete_row -delete from t1; -- delete_all_rows +delete from t1; # delete_row +delete from t1; # delete_all_rows --disable_info insert t1 values (1),(2); --enable_info -delete from t1; -- delete_all_rows +delete from t1; # delete_all_rows --disable_info insert t1 values (1),(2),(3); flush tables; --enable_info -delete from t1; -- delete_row +delete from t1; # delete_row --disable_info insert t1 values (1),(2),(3),(4); flush tables; select count(*) from t1; --enable_info -delete from t1; -- delete_all_rows +delete from t1; # delete_all_rows --disable_info insert t1 values (1),(2),(3),(4),(5); --enable_info -truncate table t1; -- truncate +truncate table t1; # truncate --disable_info drop table t1; diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test index e59693680bf..aca240b46bc 100644 --- a/mysql-test/t/ctype_collate.test +++ b/mysql-test/t/ctype_collate.test @@ -59,7 +59,7 @@ INSERT INTO t1 (latin1_f) VALUES (_latin1'Z'); INSERT INTO t1 (latin1_f) VALUES (_latin1'z'); --- ORDER BY +# ORDER BY SELECT latin1_f FROM t1 ORDER BY latin1_f; SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci; @@ -69,9 +69,9 @@ SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin; --error 1253 SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci; ---SELECT latin1_f COLLATE koi8r FROM t1 ; +# SELECT latin1_f COLLATE koi8r FROM t1 ; --- AS + ORDER BY +# AS + ORDER BY SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; @@ -80,7 +80,7 @@ SELECT latin1_f COLLATE latin1_bin AS latin1_f_as FROM t1 ORDER BY latin1 SELECT latin1_f COLLATE koi8r_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; --- GROUP BY +# GROUP BY SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f; SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci; @@ -91,7 +91,7 @@ SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin; SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci; --- DISTINCT +# DISTINCT SELECT DISTINCT latin1_f FROM t1; SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1; @@ -102,21 +102,20 @@ SELECT DISTINCT latin1_f COLLATE latin1_bin FROM t1; SELECT DISTINCT latin1_f COLLATE koi8r FROM t1; --- Aggregates ---SELECT MAX(k COLLATE latin1_german2_ci) ---FROM t1 - - --- WHERE ---SELECT * ---FROM t1 ---WHERE (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k - ---HAVING ---SELECT * ---FROM t1 ---HAVING (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k - +# Aggregates + +--disable_parsing +SELECT MAX(k COLLATE latin1_german2_ci) +FROM t1 +WHERE +SELECT * +FROM t1 +WHERE (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k +HAVING +SELECT * +FROM t1 +HAVING (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k; +--enable_parsing # # Check that SHOW displays COLLATE clause diff --git a/mysql-test/t/ctype_cp1250_ch.test b/mysql-test/t/ctype_cp1250_ch.test index 89f82d1a758..7b7018d5901 100644 --- a/mysql-test/t/ctype_cp1250_ch.test +++ b/mysql-test/t/ctype_cp1250_ch.test @@ -3,6 +3,10 @@ drop table if exists t1; --enable_warnings +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + SHOW COLLATION LIKE 'cp1250_czech_cs'; # diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index dedba4caf0f..5a3720dc431 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -298,7 +298,7 @@ INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); SELECT HEX(a) FROM t1; DROP TABLE t1; --- the same should be also done with enum and set +# the same should be also done with enum and set # diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test index 97101fba615..77d7366afe6 100644 --- a/mysql-test/t/func_sapdb.test +++ b/mysql-test/t/func_sapdb.test @@ -35,7 +35,7 @@ SET @@SQL_MODE="ALLOW_INVALID_DATES"; select datediff("1997-11-31 23:59:59.000001","1997-12-31"); SET @@SQL_MODE=""; --- This will give a warning +# This will give a warning select datediff("1997-11-31 23:59:59.000001","1997-12-31"); select datediff("1997-11-30 23:59:59.000001",null); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 9622de96143..94190ff519f 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -300,18 +300,26 @@ select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE la select POSITION(_latin1'B' IN _latin2'abcd'); select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'); ---fix this: ---select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin); ---select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d'); + +# fix this: +--disable_parsing +select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin); +select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d'); +--enable_parsing + --error 1267 select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); --error 1267 select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d'); select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); ---fix this: ---select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2); ---select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2); + +# fix this: +--disable_parsing +select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2); +select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2); +--enable_parsing + --error 1267 select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); --error 1267 diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index d3781d58780..2f5e3dced22 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -118,7 +118,7 @@ drop table t1; # --error 1221 GRANT FILE on mysqltest.* to mysqltest_1@localhost; -select 1; -- To test that the previous command didn't cause problems +select 1; # To test that the previous command didn't cause problems # # Bug #4898: User privileges depending on ORDER BY Settings of table db diff --git a/mysql-test/t/greedy_optimizer.test b/mysql-test/t/greedy_optimizer.test index e547d85b7f3..049d0ab09f7 100644 --- a/mysql-test/t/greedy_optimizer.test +++ b/mysql-test/t/greedy_optimizer.test @@ -140,18 +140,18 @@ insert into t7 values (21,2,3,4,5,6); select @@optimizer_search_depth; select @@optimizer_prune_level; --- This value swithes back to the old implementation of 'find_best()' --- set optimizer_search_depth=63; - old (independent of the optimizer_prune_level) --- --- These are the values for the parameters that control the greedy optimizer --- (total 6 combinations - 3 for optimizer_search_depth, 2 for optimizer_prune_level): +# This value swithes back to the old implementation of 'find_best()' +# set optimizer_search_depth=63; - old (independent of the optimizer_prune_level) +# +# These are the values for the parameters that control the greedy optimizer +# (total 6 combinations - 3 for optimizer_search_depth, 2 for optimizer_prune_level): -- --- set optimizer_search_depth=0; - automatic --- set optimizer_search_depth=1; - min --- set optimizer_search_depth=62; - max (default) +# set optimizer_search_depth=0; - automatic +# set optimizer_search_depth=1; - min +# set optimizer_search_depth=62; - max (default) -- --- set optimizer_prune_level=0 - exhaustive; --- set optimizer_prune_level=1 - heuristic; -- default +# set optimizer_prune_level=0 - exhaustive; +# set optimizer_prune_level=1 - heuristic; # default # @@ -170,17 +170,17 @@ select @@optimizer_prune_level; set optimizer_search_depth=63; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; @@ -195,17 +195,17 @@ select @@optimizer_prune_level; set optimizer_search_depth=0; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; @@ -214,17 +214,17 @@ show status like 'Last_query_cost'; set optimizer_search_depth=1; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; @@ -233,17 +233,17 @@ show status like 'Last_query_cost'; set optimizer_search_depth=62; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; @@ -256,17 +256,17 @@ select @@optimizer_prune_level; set optimizer_search_depth=0; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; @@ -275,17 +275,17 @@ show status like 'Last_query_cost'; set optimizer_search_depth=1; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; @@ -294,17 +294,17 @@ show status like 'Last_query_cost'; set optimizer_search_depth=62; select @@optimizer_search_depth; --- 6-table join, chain +# 6-table join, chain explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, star +# 6-table join, star explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; show status like 'Last_query_cost'; --- 6-table join, clique +# 6-table join, clique explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; show status like 'Last_query_cost'; explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 11ba6885dd1..f9ac3a625cc 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -57,8 +57,8 @@ create index idx_t1_1 on t1 (a1,a2,b,c); create index idx_t1_2 on t1 (a1,a2,b); analyze table t1; --- t2 is the same as t1, but with some NULLs in the MIN/MAX column, and one more --- nullable attribute +# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and +# one more nullable attribute --disable_warnings drop table if exists t2; @@ -68,7 +68,7 @@ create table t2 ( a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' ' ); insert into t2 select * from t1; --- add few rows with NULL's in the MIN/MAX column +# add few rows with NULL's in the MIN/MAX column insert into t2 (a1, a2, b, c, d) values ('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), ('a','a','a',NULL,'xyz'), @@ -92,10 +92,10 @@ create index idx_t2_1 on t2 (a1,a2,b,c); create index idx_t2_2 on t2 (a1,a2,b); analyze table t2; --- Table t3 is the same as t1, but with smaller column lenghts. --- This allows to test different branches of the cost computation procedure --- when the number of keys per block are less than the number of keys in the --- sub-groups formed by predicates over non-group attributes. +# Table t3 is the same as t1, but with smaller column lenghts. +# This allows to test different branches of the cost computation procedure +# when the number of keys per block are less than the number of keys in the +# sub-groups formed by predicates over non-group attributes. --disable_warnings drop table if exists t3; @@ -164,11 +164,11 @@ create index idx_t3_2 on t3 (a1,a2,b); analyze table t3; --- --- Queries without a WHERE clause. These queries do not use ranges. --- +# +# Queries without a WHERE clause. These queries do not use ranges. +# --- plans +# plans explain select a1, min(a2) from t1 group by a1; explain select a1, max(a2) from t1 group by a1; explain select a1, min(a2), max(a2) from t1 group by a1; @@ -176,31 +176,31 @@ explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; --replace_column 7 # 9 # explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; --- Select fields in different order +# Select fields in different order explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; explain select min(a2) from t1 group by a1; explain select a2, min(c), max(c) from t1 group by a1,a2,b; --- queries +# queries select a1, min(a2) from t1 group by a1; select a1, max(a2) from t1 group by a1; select a1, min(a2), max(a2) from t1 group by a1; select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; --- Select fields in different order +# Select fields in different order select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; select min(a2) from t1 group by a1; select a2, min(c), max(c) from t1 group by a1,a2,b; --- --- Queries with a where clause --- +# +# Queries with a where clause +# --- A) Preds only over the group 'A' attributes --- plans +# A) Preds only over the group 'A' attributes +# plans explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; @@ -238,7 +238,7 @@ explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; --replace_column 9 # explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; --- queries +# queries select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; @@ -264,8 +264,8 @@ select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; --- B) Equalities only over the non-group 'B' attributes --- plans +# B) Equalities only over the non-group 'B' attributes +# plans explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; @@ -278,11 +278,11 @@ explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; --- these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() +# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; --- queries +# queries select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; @@ -295,20 +295,20 @@ select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; --- these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() +# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; --- IS NULL (makes sense for t2 only) --- plans +# IS NULL (makes sense for t2 only) +# plans explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; --- queries +# queries select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; @@ -316,8 +316,8 @@ select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; --- C) Range predicates for the MIN/MAX attribute --- plans +# C) Range predicates for the MIN/MAX attribute +# plans --replace_column 9 # explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; @@ -367,7 +367,7 @@ explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or --replace_column 9 # explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; --- queries +# queries select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; @@ -401,19 +401,19 @@ select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; --- analyze the sub-select +# analyze the sub-select explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c = t1.c ) group by a1,a2,b; --- the sub-select is unrelated to MIN/MAX +# the sub-select is unrelated to MIN/MAX explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c > 'b1' ) group by a1,a2,b; --- A,B,C) Predicates referencing mixed classes of attributes --- plans +# A,B,C) Predicates referencing mixed classes of attributes +# plans explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; @@ -435,7 +435,7 @@ explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 --replace_column 9 # explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; --- queries +# queries select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; @@ -452,11 +452,11 @@ select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; --- --- GROUP BY queries without MIN/MAX --- +# +# GROUP BY queries without MIN/MAX +# --- plans +# plans explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; @@ -471,7 +471,7 @@ explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' --replace_column 9 # explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; --- queries +# queries select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; @@ -482,11 +482,11 @@ select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; --- --- DISTINCT queries --- +# +# DISTINCT queries +# --- plans +# plans explain select distinct a1,a2,b from t1; explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); @@ -502,7 +502,7 @@ explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); --- queries +# queries select distinct a1,a2,b from t1; select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); @@ -515,22 +515,22 @@ select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121 select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); select distinct b from t2 where (a2 >= 'b') and (b = 'a'); --- BUG #6303 +# BUG #6303 select distinct t_00.a1 from t1 t_00 where exists ( select * from t2 where a1 = t_00.a1 ); --- BUG #8532 - SELECT DISTINCT a, a causes server to crash +# BUG #8532 - SELECT DISTINCT a, a causes server to crash select distinct a1,a1 from t1; select distinct a2,a1,a2,a1 from t1; select distinct t1.a1,t2.a1 from t1,t2; --- --- DISTINCT queries with GROUP-BY --- +# +# DISTINCT queries with GROUP-BY +# --- plans +# plans explain select distinct a1,a2,b from t1; explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; @@ -548,7 +548,7 @@ explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = --replace_column 9 # explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; --- queries +# queries select distinct a1,a2,b from t1; select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; @@ -562,9 +562,9 @@ select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') gr select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; --- --- COUNT (DISTINCT cols) queries --- +# +# COUNT (DISTINCT cols) queries +# explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); @@ -578,9 +578,9 @@ select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); --- --- Queries with expressions in the select clause --- +# +# Queries with expressions in the select clause +# explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; @@ -595,48 +595,48 @@ select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; --- --- Negative examples: queries that should NOT be treated as optimizable by --- QUICK_GROUP_MIN_MAX_SELECT --- +# +# Negative examples: queries that should NOT be treated as optimizable by +# QUICK_GROUP_MIN_MAX_SELECT +# --- select a non-indexed attribute +# select a non-indexed attribute explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; explain select a1,a2,b,d from t1 group by a1,a2,b; --- predicate that references an attribute that is after the MIN/MAX argument --- in the index +# predicate that references an attribute that is after the MIN/MAX argument +# in the index explain extended select a1,a2,min(b),max(b) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2; --- predicate that references a non-indexed attribute +# predicate that references a non-indexed attribute explain extended select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; explain extended select a1,a2,b,c from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; --- non-equality predicate for a non-group select attribute +# non-equality predicate for a non-group select attribute explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b; --- non-group field with an equality predicate that references a keypart after the --- MIN/MAX argument +# non-group field with an equality predicate that references a keypart after the +# MIN/MAX argument explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; --- disjunction for a non-group select attribute +# disjunction for a non-group select attribute explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; --- non-range predicate for the MIN/MAX attribute +# non-range predicate for the MIN/MAX attribute explain select a1,a2,b,min(c),max(c) from t2 where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; --- not all attributes are indexed by one index +# not all attributes are indexed by one index explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; --- other aggregate functions than MIN/MAX +# other aggregate functions than MIN/MAX explain select a1,a2,count(a2) from t1 group by a1,a2,b; explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; @@ -790,24 +790,24 @@ INSERT INTO t4 VALUES(1); INSERT INTO t5 VALUES(1,1); INSERT INTO t6 VALUES(1); --- original bug query +# original bug query SELECT * FROM t1 NATURAL JOIN (t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); --- inner join swapped +# inner join swapped SELECT * FROM t1 NATURAL JOIN (((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); --- one join less, no ON cond +# one join less, no ON cond SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2); --- wrong error message: 'id2' - ambiguous column +# wrong error message: 'id2' - ambiguous column SELECT * FROM (t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)) diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 1a2afad3b44..7e9a0e1ed18 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -1475,7 +1475,7 @@ INSERT INTO t1 (id) VALUES (NULL); SELECT * FROM t1; DROP TABLE t2, t1; --- Test that foreign keys in temporary tables are not accepted (bug #12084) +# Test that foreign keys in temporary tables are not accepted (bug #12084) CREATE TABLE t1 ( id INT PRIMARY KEY diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index d0005f3b8f7..712e0b64e88 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -362,38 +362,38 @@ insert into t4 values (2, 3); insert into t5 values (11,4); insert into t6 values (2, 3); --- Views with simple natural join. +# Views with simple natural join. create algorithm=merge view v1a as select * from t1 natural join t2; --- as above, but column names are cross-renamed: a->c, c->b, b->a +# as above, but column names are cross-renamed: a->c, c->b, b->a create algorithm=merge view v1b(a,b,c) as select * from t1 natural join t2; --- as above, but column names are aliased: a->c, c->b, b->a +# as above, but column names are aliased: a->c, c->b, b->a create algorithm=merge view v1c as select b as a, c as b, a as c from t1 natural join t2; --- as above, but column names are cross-renamed, and aliased --- a->c->b, c->b->a, b->a->c +# as above, but column names are cross-renamed, and aliased +# a->c->b, c->b->a, b->a->c create algorithm=merge view v1d(b, a, c) as select a as c, c as b, b as a from t1 natural join t2; --- Views with JOIN ... ON +# Views with JOIN ... ON create algorithm=merge view v2a as select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; create algorithm=merge view v2b as select t1.c as b, t1.b as a, t2.a as c from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; --- Views with bigger natural join +# Views with bigger natural join create algorithm=merge view v3a as select * from t1 natural join t2 natural join t3; create algorithm=merge view v3b as select * from t1 natural join (t2 natural join t3); --- View over views with mixed natural join and join ... on +# View over views with mixed natural join and join ... on create algorithm=merge view v4 as select * from v2a natural join v3a; --- Nested natural/using joins. +# Nested natural/using joins. select * from (t1 natural join t2) natural join (t3 natural join t4); select * from (t1 natural join t2) natural left join (t3 natural join t4); select * from (t3 natural join t4) natural right join (t1 natural join t2); @@ -402,12 +402,12 @@ select * from (t4 natural right join t3) natural right join (t2 natural right jo select * from t1 natural join t2 natural join t3 natural join t4; select * from ((t1 natural join t2) natural join t3) natural join t4; select * from t1 natural join (t2 natural join (t3 natural join t4)); --- BUG#15355: this query fails in 'prepared statements' mode --- select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5; --- select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5; +# BUG#15355: this query fails in 'prepared statements' mode +# select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5; +# select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5; select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3)); select * from (t1 natural join t2), (t3 natural join t4); --- MySQL extension - nested comma ',' operator instead of cross join. +# MySQL extension - nested comma ',' operator instead of cross join. select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); @@ -417,7 +417,7 @@ select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c); select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c)); --- Other clauses refer to NJ columns. +# Other clauses refer to NJ columns. select a,b,c from (t1 natural join t2) natural join (t3 natural join t4) where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a; select * from (t1 natural join t2) natural left join (t3 natural join t4) @@ -425,23 +425,23 @@ where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a select * from (t3 natural join t4) natural right join (t1 natural join t2) where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y; --- Qualified column references to NJ columns. +# Qualified column references to NJ columns. select * from t1 natural join t2 where t1.c > t2.a; select * from t1 natural join t2 where t1.b > t2.b; select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL; --- Nested 'join ... on' - name resolution of ON conditions +# Nested 'join ... on' - name resolution of ON conditions select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c; select * from t1 natural join (t2 join t4 on b + 1 = y); select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c); --- MySQL extension - 'join ... on' over nested comma operator +# MySQL extension - 'join ... on' over nested comma operator select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c); select * from (t1 natural join t2) join (t3 natural join t4) on a = y; select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3; --- MySQL extension - refererence qualified coalesced columns +# MySQL extension - refererence qualified coalesced columns select * from t1 natural join t2 where t1.b > 0; select * from t1 natural join (t4 natural join t5) where t4.y > 7; select * from (t4 natural join t5) natural join t1 where t4.y > 7; @@ -449,11 +449,11 @@ select * from t1 natural left join (t4 natural join t5) where t4.y > 7; select * from (t4 natural join t5) natural right join t1 where t4.y > 7; select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b; --- MySQL extension - select qualified columns of NJ columns +# MySQL extension - select qualified columns of NJ columns select t1.*, t2.* from t1 natural join t2; select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4); --- Queries over subselects in the FROM clause +# Queries over subselects in the FROM clause select * from (select * from t1 natural join t2) as t12 natural join (select * from t3 natural join t4) as t34; @@ -464,7 +464,7 @@ select * from (select * from t3 natural join t4) as t34 natural right join (select * from t1 natural join t2) as t12; --- Queries over views +# Queries over views select * from v1a; select * from v1b; select * from v1c; @@ -481,13 +481,13 @@ select * from v1c join v2a on v1c.b = v2a.c; select * from v1d join v2a on v1d.a = v2a.c; select * from v1a join (t3 natural join t4) on a = y; --- TODO: add tests with correlated subqueries for natural join/join on. --- related to BUG#15269 +# TODO: add tests with correlated subqueries for natural join/join on. +# related to BUG#15269 ----------------------------------------------------------------------- --- Negative tests (tests for errors) ----------------------------------------------------------------------- +#-------------------------------------------------------------------- +# Negative tests (tests for errors) +#-------------------------------------------------------------------- -- error 1052 select * from t1 natural join (t3 cross join t4); -- works in Oracle - bug -- error 1052 @@ -504,7 +504,7 @@ select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4); -- error 1052 select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b); --- this one is OK, the next equivalent one is incorrect (bug in Oracle) +# this one is OK, the next equivalent one is incorrect (bug in Oracle) -- error 1052 select * from (t3 join (t4 natural join t5) on (b < z)) natural join @@ -579,12 +579,12 @@ insert into t3 values (2,3); insert into t4 values (1,3); insert into t5 values (1,4); --- this fails +# this fails prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5"; execute stmt1; --- this works +# this works select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5; drop table t1, t2, t3, t4, t5; diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index cf7789428b2..2eb4e6cbbb2 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -7,7 +7,7 @@ drop table if exists t1; --enable_warnings create table t1 (a int not null default 0 primary key, b int not null default 0); -insert into t1 () values (); -- Testing default values +insert into t1 () values (); # Testing default values insert into t1 values (1,1),(2,1),(3,1); update t1 set a=4 where b=1 limit 1; select * from t1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 4aec745f3f7..65e09b006ec 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -177,7 +177,7 @@ drop table t1; # non-null string collation, i.e. case insensitively, # rather than according to NULL's collation, i.e. case sensitively # --- in field +# in field select case 'str' when 'STR' then 'str' when null then 'null' end as c01, case 'str' when null then 'null' when 'STR' then 'str' end as c02, diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index bc0dfd6de76..79049353950 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2701,7 +2701,7 @@ insert into t2 values ('58013'),('58014'),('58015'),('58016'); create table t3 (a_id int(11) not null, b_id char(16) character set utf8); insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); --- both queries are equivalent +# both queries are equivalent select count(*) from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; @@ -2941,7 +2941,7 @@ create table t2 ( insert into t1 (b,c) values (0,1), (0,1); insert into t2 (b,c) values (0,1); --- Row 1 should succeed. Row 2 should fail. Both fail. +# Row 1 should succeed. Row 2 should fail. Both fail. select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 where t1.b <> 1 order by t1.a; diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test index b94de6236d3..cc3e3b93e06 100644 --- a/mysql-test/t/sp-prelocking.test +++ b/mysql-test/t/sp-prelocking.test @@ -209,7 +209,7 @@ select f3() // call sp1() // ---------------- +# --------------- drop procedure sp1// drop function f3// diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index ae5e4365af3..2f35bbf22f2 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -327,14 +327,14 @@ INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); # SQLSTATE 22007 <invalid datetime value> --error 1292 INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); --- should return OK --- We accept this to be a failure +# should return OK +# We accept this to be a failure --error 1292 INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); --error 1292 INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); --- should return SQLSTATE 22007 <invalid datetime value> +# should return SQLSTATE 22007 <invalid datetime value> # deactivated because of Bug#8294 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE @@ -422,8 +422,8 @@ INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); # SQLSTATE 22007 <invalid datetime value> --error 1292 INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); --- should return OK --- We accept this to be a failure +# should return OK +# We accept this to be a failure --error 1292 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); @@ -729,11 +729,11 @@ DROP TABLE t1; CREATE TABLE t1 (col1 NUMERIC(4,2)); INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); --- Note that the +/-10.5555 is inserted as +/-10.55, not +/-10.56 ! +# Note that the +/-10.5555 is inserted as +/-10.55, not +/-10.56 ! INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); --- The 2 following inserts should generate a warning, but doesn't yet --- because NUMERIC works like DECIMAL +# The 2 following inserts should generate a warning, but doesn't yet +# because NUMERIC works like DECIMAL --error 1264 INSERT INTO t1 VALUES (101.55); --error 1264 @@ -744,8 +744,8 @@ INSERT INTO t1 VALUES (-101.55); INSERT INTO t1 VALUES (1010.55); --error 1264 INSERT INTO t1 VALUES (1010); --- The 2 following inserts should generate a warning, but doesn't yet --- because NUMERIC works like DECIMAL +# The 2 following inserts should generate a warning, but doesn't yet +# because NUMERIC works like DECIMAL --error 1264 INSERT INTO t1 VALUES ('101.55'); --error 1264 diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index dee5b1e4fb0..b68cd225a08 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -108,7 +108,7 @@ select * from t3 where a in (select a,b from t2); -- error 1241 select * from t3 where a in (select * from t2); insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10); --- empty set +# empty set select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b); insert into t2 values (2,10); select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b); @@ -2247,11 +2247,11 @@ drop table t1; # Bug#19700: subselect returning BIGINT always returned it as SIGNED # CREATE TABLE t1 (i BIGINT UNSIGNED); -INSERT INTO t1 VALUES (10000000000000000000); -- > MAX SIGNED BIGINT 9323372036854775807 +INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807 INSERT INTO t1 VALUES (1); CREATE TABLE t2 (i BIGINT UNSIGNED); -INSERT INTO t2 VALUES (10000000000000000000); -- same as first table +INSERT INTO t2 VALUES (10000000000000000000); # same as first table INSERT INTO t2 VALUES (1); /* simple test */ diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 1b80a15e4ff..e704014c0ab 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -613,7 +613,7 @@ select truncate(99.999999999999999999999999999999999999,31); #-- should return 99.9999999999999999999999999999999 # select truncate(99999999999999999999999999999999999999,-31); --- should return 90000000000000000000000000000000 +# should return 90000000000000000000000000000000 # #-- 6. Set functions (AVG, SUM, COUNT) should work. # @@ -810,7 +810,7 @@ select 1 / 0; #BUG#6048 Stored procedure causes operating system reboot #BUG#6053 DOUBLE PRECISION literal --- Tests from 'traditional' mode tests +# Tests from 'traditional' mode tests # set sql_mode='ansi,traditional'; # diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index daba7dfaa3c..39444a97984 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -806,7 +806,7 @@ DROP DATABASE mysqltest1; CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1), (2), (3); CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; ---warning 1448 +#--warning 1448 SHOW CREATE VIEW v; --error 1449 SELECT * FROM v; |