diff options
Diffstat (limited to 'mysql-test/t')
62 files changed, 740 insertions, 301 deletions
diff --git a/mysql-test/t/1st.test b/mysql-test/t/1st.test new file mode 100644 index 00000000000..6b93efee944 --- /dev/null +++ b/mysql-test/t/1st.test @@ -0,0 +1,5 @@ +# +# Check that we haven't any strange new tables or databases +# +show databases; +show tables in mysql; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index aa2133db9c5..fe3ca3e0321 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -553,4 +553,3 @@ INSERT INTO `@0023sql1` VALUES (2); SHOW CREATE TABLE `#sql2`; SHOW CREATE TABLE `@0023sql1`; DROP TABLE `#sql2`, `@0023sql1`; - diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index aebee0b0c6f..a2853ca3191 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, @@ -711,3 +711,24 @@ TRUNCATE table t2; INSERT INTO t2 select * from t1; SELECT * from t2; drop table t1,t2; + +# +# Test incorrect database names +# + +--error 1102 +CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error 1102 +DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error 1049 +RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a; +--error 1102 +RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +create database mysqltest; +--error 1102 +RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +drop database mysqltest; +--error 1102 +USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error 1102 +SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 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_create.test b/mysql-test/t/ctype_create.test index e88004bbb8c..060c09a0459 100644 --- a/mysql-test/t/ctype_create.test +++ b/mysql-test/t/ctype_create.test @@ -100,3 +100,8 @@ drop database mysqltest2; ALTER DATABASE DEFAULT CHARACTER SET latin2; # End of 4.1 tests + +--error 1102 +ALTER DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DEFAULT CHARACTER SET latin2; +--error 1102 +ALTER DATABASE `` DEFAULT CHARACTER SET latin2; 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/disabled.def b/mysql-test/t/disabled.def index 51f9625e239..b541b6d8d33 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -16,6 +16,9 @@ concurrent_innodb : BUG#21579 2006-08-11 mleich innodb_concurrent random ndb_autodiscover : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog ndb_autodiscover2 : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog ndb_load : BUG#17233 2006-05-04 tomas failed load data from infile causes mysqld dbug_assert, binlog not flushed +ndb_restore_partition : Problem with cluster/def/schema table that is in std_data/ndb_backup51; Pekka will schdule this to someone +rpl_ndb_sync : Problem with cluster/def/schema table that is in std_data/ndb_backup51; Pekka will schdule this to someone + partition_03ndb : BUG#16385 2006-03-24 mikael Partitions: crash when updating a range partitioned NDB table ps_7ndb : BUG#18950 2006-02-16 jmiller create table like does not obtain LOCK_open rpl_ndb_2innodb : BUG#19227 2006-04-20 pekka pk delete apparently not replicated @@ -26,6 +29,7 @@ rpl_ndb_innodb2ndb : Bug #19710 Cluster replication to partition table fa rpl_ndb_myisam2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement rpl_row_blob_innodb : BUG#18980 2006-04-10 kent Test fails randomly rpl_multi_engine : BUG#22583 2006-09-23 lars +synchronization : Bug#24529 Test 'synchronization' fails on Mac pushbuild; Also on Linux 64 bit. # the below testcase have been reworked to avoid the bug, test contains comment, keep bug open #ndb_binlog_ddl_multi : BUG#18976 2006-04-10 kent CRBR: multiple binlog, second binlog may miss schema log events diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index 32863308687..6eb514fc13c 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -395,4 +395,15 @@ drop event e1; ##show processlist; ##select count(*) from mysql.event; +# +# Test wrong syntax +# + +--error 1102 +SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error 1102 +SHOW EVENTS FROM ``; + +SHOW EVENTS FROM `events\\test`; + drop database events_test; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 95ba633fefd..3a4f2f2f5f2 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -1,11 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# --- source include/not_embedded.inc - connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; diff --git a/mysql-test/t/flush_block_commit.test b/mysql-test/t/flush_block_commit.test index ebb48242a4d..4412b2c357c 100644 --- a/mysql-test/t/flush_block_commit.test +++ b/mysql-test/t/flush_block_commit.test @@ -3,9 +3,6 @@ # We verify that we did not introduce a deadlock. # This is intended to mimick how mysqldump and innobackup work. -# This test doesn't work with the embedded server --- source include/not_embedded.inc - # And it requires InnoDB -- source include/have_innodb.inc diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 78393ee1104..b41f6cd71c8 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -707,3 +707,28 @@ create table t1 select stddev(0); show create table t1; drop table t1; +# +# Bug #23184: SELECT causes server crash +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; + +DROP TABLE t1; + +--echo End of 5.0 tests 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/gis-rtree.test b/mysql-test/t/gis-rtree.test index 09cdcb2435e..e34dd14dbfc 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -242,3 +242,15 @@ INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0))); SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); DROP TABLE t1; # End of 4.1 tests + +# +# bug #21790 (UNKNOWN ERROR on NULLs in RTree) +# +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +--error 1048 +INSERT INTO t1(foo) VALUES (NULL); +--error 1416 +INSERT INTO t1() VALUES (); +--error 1416 +INSERT INTO t1(foo) VALUES (''); +DROP TABLE t1; 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..4feca43ae1a 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): --- --- 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 +# 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_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/information_schema.test b/mysql-test/t/information_schema.test index 4ae88736b98..cec140a5e63 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -37,7 +37,7 @@ create table t3(a int, KEY a_data (a)); create table mysqltest.t4(a int); create table t5 (id int auto_increment primary key); insert into t5 values (10); -create view v1 (c) as select table_name from information_schema.TABLES where table_schema!='cluster'; +create view v1 (c) as select table_name from information_schema.TABLES where table_name<>'binlog_index' AND table_name<>'apply_status'; select * from v1; select c,table_name from v1 @@ -528,7 +528,7 @@ flush privileges; # Bug #9404 information_schema: Weird error messages # with SELECT SUM() ... GROUP BY queries # -SELECT table_schema, count(*) FROM information_schema.TABLES where TABLE_SCHEMA!='cluster' GROUP BY TABLE_SCHEMA; +SELECT table_schema, count(*) FROM information_schema.TABLES where table_name<>'binlog_index' AND table_name<>'apply_status' GROUP BY TABLE_SCHEMA; # @@ -974,6 +974,20 @@ SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT= DROP TABLE bug23037; DROP FUNCTION get_value; + +# +# Bug#22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash +# +create view v1 as +select table_schema as object_schema, + table_name as object_name, + table_type as object_type +from information_schema.tables +order by object_schema; +explain select * from v1; +explain select * from (select table_name from information_schema.tables) as a; +drop view v1; + --echo End of 5.0 tests. # # Show engines diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test index 55a712fef9b..eacf7e562be 100644 --- a/mysql-test/t/innodb-lock.test +++ b/mysql-test/t/innodb-lock.test @@ -1,6 +1,4 @@ -- source include/have_innodb.inc -# Can't test this with embedded server --- source include/not_embedded.inc # # Check and select innodb lock type 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..f39938ec52c 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,17 +481,17 @@ 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 +select * from t1 natural join (t3 cross join t4); # works in Oracle - bug -- error 1052 -select * from (t3 cross join t4) natural join t1; -- works in Oracle - bug +select * from (t3 cross join t4) natural join t1; # works in Oracle - bug -- error 1052 select * from t1 join (t2, t3) using (b); -- 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 0c18a65bfd6..bc599b20d36 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/lock_multi.test b/mysql-test/t/lock_multi.test index 765da1ee18d..6c89a8b4a7b 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -1,11 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# --- source include/not_embedded.inc - --disable_warnings drop table if exists t1,t2; --enable_warnings diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index 65338448555..a6ebfdc14c1 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -61,4 +61,23 @@ drop table t1;// delimiter ;// --disable_info +# +# Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs +# +--enable_metadata +create table t1 (id int(10)); +insert into t1 values (1); +CREATE VIEW v1 AS select t1.id as id from t1; +CREATE VIEW v2 AS select t1.id as renamed from t1; +CREATE VIEW v3 AS select t1.id + 12 as renamed from t1; +select * from v1 group by id limit 1; +select * from v1 group by id limit 0; +select * from v1 where id=1000 group by id; +select * from v1 where id=1 group by id; +select * from v2 where renamed=1 group by renamed; +select * from v3 where renamed=1 group by renamed; +drop table t1; +drop view v1,v2,v3; +--disable_metadata + # End of 4.1 tests diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index e21ecd3a790..c9fec6deb35 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -143,6 +143,24 @@ drop table t1; --exec diff $MYSQLTEST_VARDIR/tmp/bug20328_1.result $MYSQLTEST_VARDIR/tmp/bug20328_2.result # +# Bug #19216: Client crashes on long SELECT +# +--exec echo "select" > $MYSQLTEST_VARDIR/tmp/b19216.tmp +# 3400 * 20 makes 68000 columns that is more than the max number that can fit +# in a 16 bit number. +let $i= 3400; +while ($i) +{ + --exec echo "'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a'," >> $MYSQLTEST_VARDIR/tmp/b19216.tmp + dec $i; +} + +--exec echo "'b';" >> $MYSQLTEST_VARDIR/tmp/b19216.tmp +--disable_query_log +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/b19216.tmp >/dev/null +--enable_query_log + +# # Bug #20103: Escaping with backslash does not work # --exec echo "SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';" > $MYSQLTEST_VARDIR/tmp/bug20103.sql diff --git a/mysql-test/t/mysql_client_test-master.opt b/mysql-test/t/mysql_client_test-master.opt index 3711946168d..2dfcc4a2fb9 100644 --- a/mysql-test/t/mysql_client_test-master.opt +++ b/mysql-test/t/mysql_client_test-master.opt @@ -1 +1 @@ ---log --log-output=FILE +--log=$MYSQLTEST_VARDIR/log/master.log --log-output=FILE diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 99e8e48df1f..b7ff8bbdfdb 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1394,6 +1394,9 @@ revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; drop user myDB_User@localhost; drop database mysqldump_myDB; use test; +connection default; +disconnect root; +disconnect user1; --echo # --echo # BUG#13926: --order-by-primary fails if PKEY contains quote character diff --git a/mysql-test/t/ndb_binlog_basic.test b/mysql-test/t/ndb_binlog_basic.test index 3886900037d..c2a36423445 100644 --- a/mysql-test/t/ndb_binlog_basic.test +++ b/mysql-test/t/ndb_binlog_basic.test @@ -19,7 +19,7 @@ create table t1 (a int primary key) engine=ndb; insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); save_master_pos; --replace_column 1 # -select @max_epoch:=max(epoch)-1 from cluster.binlog_index; +select @max_epoch:=max(epoch)-1 from mysql.binlog_index; delete from t1; alter table t1 add (b int); @@ -38,10 +38,10 @@ drop table t2; # (save_master_pos waits for last gcp to complete, ensuring that we have # the expected data in the binlog) save_master_pos; -select inserts from cluster.binlog_index where epoch > @max_epoch and inserts > 5; -select deletes from cluster.binlog_index where epoch > @max_epoch and deletes > 5; +select inserts from mysql.binlog_index where epoch > @max_epoch and inserts > 5; +select deletes from mysql.binlog_index where epoch > @max_epoch and deletes > 5; select inserts,updates,deletes from - cluster.binlog_index where epoch > @max_epoch and updates > 0; + mysql.binlog_index where epoch > @max_epoch and updates > 0; # # check that purge clears the binlog_index @@ -49,7 +49,7 @@ select inserts,updates,deletes from flush logs; --sleep 1 purge master logs before now(); -select count(*) from cluster.binlog_index; +select count(*) from mysql.binlog_index; # # several tables in different databases @@ -64,9 +64,9 @@ use test; insert into mysqltest.t1 values (2,1),(2,2); save_master_pos; --replace_column 1 # -select @max_epoch:=max(epoch)-1 from cluster.binlog_index; +select @max_epoch:=max(epoch)-1 from mysql.binlog_index; drop table t1; drop database mysqltest; select inserts,updates,deletes from - cluster.binlog_index where epoch > @max_epoch and inserts > 0; + mysql.binlog_index where epoch > @max_epoch and inserts > 0; diff --git a/mysql-test/t/ndb_binlog_ddl_multi.test b/mysql-test/t/ndb_binlog_ddl_multi.test index 78cec137159..064bd88764a 100644 --- a/mysql-test/t/ndb_binlog_ddl_multi.test +++ b/mysql-test/t/ndb_binlog_ddl_multi.test @@ -45,7 +45,7 @@ reset master; --connection server2 alter table t2 add column (b int); ---connections server1 +--connection server1 --source include/show_binlog_events.inc # alter database diff --git a/mysql-test/t/ndb_binlog_multi.test b/mysql-test/t/ndb_binlog_multi.test index e023a54b61c..1c6a1063fea 100644 --- a/mysql-test/t/ndb_binlog_multi.test +++ b/mysql-test/t/ndb_binlog_multi.test @@ -38,7 +38,7 @@ INSERT INTO t2 VALUES (1,1),(2,2); select * from t2 order by a; --replace_column 1 <the_epoch> SELECT @the_epoch:=epoch,inserts,updates,deletes,schemaops FROM - cluster.binlog_index ORDER BY epoch DESC LIMIT 1; + mysql.binlog_index ORDER BY epoch DESC LIMIT 1; let $the_epoch= `SELECT @the_epoch`; # see if we got something on server1 @@ -50,7 +50,7 @@ DROP TABLE t2; --source include/show_binlog_events.inc --replace_result $the_epoch <the_epoch> eval SELECT inserts,updates,deletes,schemaops FROM - cluster.binlog_index WHERE epoch=$the_epoch; + mysql.binlog_index WHERE epoch=$the_epoch; # reset for next test connection server1; @@ -65,12 +65,12 @@ INSERT INTO t1 VALUES (1),(2); --source include/show_binlog_events.inc --replace_column 1 <the_epoch2> SELECT @the_epoch2:=epoch,inserts,updates,deletes,schemaops FROM - cluster.binlog_index ORDER BY epoch DESC LIMIT 1; + mysql.binlog_index ORDER BY epoch DESC LIMIT 1; let $the_epoch2= `SELECT @the_epoch2`; --replace_result $the_epoch <the_epoch> $the_epoch2 <the_epoch2> eval SELECT inserts,updates,deletes,schemaops FROM - cluster.binlog_index WHERE epoch > $the_epoch AND epoch <= $the_epoch2; + mysql.binlog_index WHERE epoch > $the_epoch AND epoch <= $the_epoch2; # now see that we have the events on the other server connection server2; @@ -80,4 +80,4 @@ drop table t1; --source include/show_binlog_events.inc --replace_result $the_epoch <the_epoch> $the_epoch2 <the_epoch2> eval SELECT inserts,updates,deletes,schemaops FROM - cluster.binlog_index WHERE epoch > $the_epoch AND epoch <= $the_epoch2; + mysql.binlog_index WHERE epoch > $the_epoch AND epoch <= $the_epoch2; diff --git a/mysql-test/t/ndb_blob_partition.test b/mysql-test/t/ndb_blob_partition.test index 6173c9d9851..35df57b96ba 100644 --- a/mysql-test/t/ndb_blob_partition.test +++ b/mysql-test/t/ndb_blob_partition.test @@ -36,15 +36,15 @@ set @s0 = 'rggurloniukyehuxdbfkkyzlceixzrehqhvxvxbpwizzvjzpucqmzrhzxzfau'; set @s1 = 'ykyymbzqgqlcjhlhmyqelfoaaohvtbekvifukdtnvcrrjveevfakxarxexomz'; set @s2 = 'dbnfqyzgtqxalcrwtfsqabknvtfcbpoonxsjiqvmhnfikxxhcgoexlkoezvah'; -set @v1 = repeat(@s0, 100); -- 1d42dd9090cf78314a06665d4ea938c35cc760f4 -set @v2 = repeat(@s1, 200); -- 10d3c783026b310218d10b7188da96a2401648c6 -set @v3 = repeat(@s2, 300); -- a33549d9844092289a58ac348dd59f09fc28406a -set @v4 = repeat(@s0, 400); -- daa61c6de36a0526f0d47dc29d6b9de7e6d2630c -set @v5 = repeat(@s1, 500); -- 70fc9a7d08beebc522258bfb02000a30c77a8f1d -set @v6 = repeat(@s2, 600); -- 090565c580809efed3d369481a4bbb168b20713e -set @v7 = repeat(@s0, 700); -- 1e0070bec426871a46291de27b9bd6e4255ab4e5 -set @v8 = repeat(@s1, 800); -- acbaba01bc2e682f015f40e79d9cbe475db3002e -set @v9 = repeat(@s2, 900); -- 9ee30d99162574f79c66ae95cdf132dcf9cbc259 +set @v1 = repeat(@s0, 100); # 1d42dd9090cf78314a06665d4ea938c35cc760f4 +set @v2 = repeat(@s1, 200); # 10d3c783026b310218d10b7188da96a2401648c6 +set @v3 = repeat(@s2, 300); # a33549d9844092289a58ac348dd59f09fc28406a +set @v4 = repeat(@s0, 400); # daa61c6de36a0526f0d47dc29d6b9de7e6d2630c +set @v5 = repeat(@s1, 500); # 70fc9a7d08beebc522258bfb02000a30c77a8f1d +set @v6 = repeat(@s2, 600); # 090565c580809efed3d369481a4bbb168b20713e +set @v7 = repeat(@s0, 700); # 1e0070bec426871a46291de27b9bd6e4255ab4e5 +set @v8 = repeat(@s1, 800); # acbaba01bc2e682f015f40e79d9cbe475db3002e +set @v9 = repeat(@s2, 900); # 9ee30d99162574f79c66ae95cdf132dcf9cbc259 --enable_query_log # -- insert -- diff --git a/mysql-test/t/ndb_index_ordered.test b/mysql-test/t/ndb_index_ordered.test index a03e0729ece..b9a47725b85 100644 --- a/mysql-test/t/ndb_index_ordered.test +++ b/mysql-test/t/ndb_index_ordered.test @@ -50,7 +50,7 @@ update t1 set c = 13 where b <= 3; select * from t1 order by a; update t1 set b = b + 1 where b > 4 and b < 7; select * from t1 order by a; --- Update primary key +# Update primary key update t1 set a = a + 10 where b > 1 and b < 7; select * from t1 order by a; diff --git a/mysql-test/t/ndb_restore_compat.test b/mysql-test/t/ndb_restore_compat.test index 774011e362d..ee55e827d0e 100644 --- a/mysql-test/t/ndb_restore_compat.test +++ b/mysql-test/t/ndb_restore_compat.test @@ -21,7 +21,7 @@ SELECT * FROM GL ORDER BY TIME,ACCOUNT_TYPE; SELECT * FROM ACCOUNT ORDER BY ACCOUNT_ID; SELECT COUNT(*) FROM TRANSACTION; SELECT * FROM SYSTEM_VALUES ORDER BY SYSTEM_VALUES_ID; -SELECT * FROM cluster.apply_status WHERE server_id=0; +SELECT * FROM mysql.apply_status WHERE server_id=0; # # verify restore of 5.0 backup @@ -39,5 +39,5 @@ SELECT * FROM GL ORDER BY TIME,ACCOUNT_TYPE; SELECT * FROM ACCOUNT ORDER BY ACCOUNT_ID; SELECT COUNT(*) FROM TRANSACTION; SELECT * FROM SYSTEM_VALUES ORDER BY SYSTEM_VALUES_ID; -SELECT * FROM cluster.apply_status WHERE server_id=0; +SELECT * FROM mysql.apply_status WHERE server_id=0; DROP DATABASE BANK; 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/order_by.test b/mysql-test/t/order_by.test index 1104c859ab8..d7cf0e2a375 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -514,11 +514,6 @@ set max_sort_length=20; select a from t1 order by a; drop table t1; -create table t1 (a int not null, b int not null, c int not null); -insert t1 values (1,1,1),(1,1,2),(1,2,1); -select a, b from t1 group by a, b order by sum(c); -drop table t1; - # # Bug #7331 # @@ -577,7 +572,27 @@ INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); DROP TABLE t1; +# +# Bug #22457: Column alias in ORDER BY works, but not if in an expression +# + +CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); +SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; +SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); +SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; +SELECT a + 1 AS num FROM t1 HAVING 30 - num; +--error 1054 +SELECT a + 1 AS num, num + 1 FROM t1; +SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; +--error 1054 +SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; +DROP TABLE t1; + # End of 4.1 tests +create table t1 (a int not null, b int not null, c int not null); +insert t1 values (1,1,1),(1,1,2),(1,2,1); +select a, b from t1 group by a, b order by sum(c); +drop table t1; # # Bug#21302: Result not properly sorted when using an ORDER BY on a second diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 55a638e18e9..ad7fd90ed63 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -879,3 +879,32 @@ select * from t1 where a > 3; show status like 'last_query_cost'; drop table t1; SET GLOBAL query_cache_size=0; + +# +# Bug #20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view +# +set global query_cache_size=1024*1024; +flush status; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3); +select * from t1; +show status like 'Qcache_hits'; +select * from t1; +show status like 'Qcache_hits'; +create table t2 like t1; +select * from t1; +show status like 'Qcache_hits'; +insert into t2 select * from t1; +select * from t1; +show status like 'Qcache_hits'; +drop table t1, t2; + +create table t1(c1 int); +create table t2(c1 int); +create table t3(c1 int); +create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1; +start transaction; +insert into t1(c1) select c1 from v1; +drop table t1, t2, t3; +drop view v1; +set global query_cache_size=0; diff --git a/mysql-test/t/rename.test b/mysql-test/t/rename.test index 09a02344203..fce37d8466d 100644 --- a/mysql-test/t/rename.test +++ b/mysql-test/t/rename.test @@ -2,10 +2,6 @@ # Test of rename table # -# Test requires concurrent connections, which can't be tested on embedded -# server --- source include/not_embedded.inc - --disable_warnings drop table if exists t0,t1,t2,t3,t4; # Clear up from other tests (to ensure that SHOW TABLES below is right) diff --git a/mysql-test/t/rpl_000015.test b/mysql-test/t/rpl_000015.test index cf93beb2bfe..ffe7ab87632 100644 --- a/mysql-test/t/rpl_000015.test +++ b/mysql-test/t/rpl_000015.test @@ -18,7 +18,7 @@ show slave status; change master to master_host='127.0.0.1'; # The following needs to be cleaned up when change master is fixed ---replace_result $MYSQL_TCP_PORT MASTER_PORT +--replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # show slave status; --replace_result $MASTER_MYPORT MASTER_PORT diff --git a/mysql-test/t/rpl_ndb_bank.test b/mysql-test/t/rpl_ndb_bank.test index d6a10e4ccac..9174d09484b 100644 --- a/mysql-test/t/rpl_ndb_bank.test +++ b/mysql-test/t/rpl_ndb_bank.test @@ -118,12 +118,12 @@ RESET MASTER; # there is no neat way to find the backupid, this is a hack to find it... --exec $NDB_TOOLS_DIR/ndb_select_all --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -d sys --delimiter=',' SYSTAB_0 | grep 520093696 > $MYSQLTEST_VARDIR/tmp.dat -CREATE TABLE IF NOT EXISTS cluster.backup_info (id INT, backup_id INT) ENGINE = HEAP; -DELETE FROM cluster.backup_info; -LOAD DATA INFILE '../tmp.dat' INTO TABLE cluster.backup_info FIELDS TERMINATED BY ','; +CREATE TABLE IF NOT EXISTS mysql.backup_info (id INT, backup_id INT) ENGINE = HEAP; +DELETE FROM mysql.backup_info; +LOAD DATA INFILE '../tmp.dat' INTO TABLE mysql.backup_info FIELDS TERMINATED BY ','; --exec rm $MYSQLTEST_VARDIR/tmp.dat || true --replace_column 1 <the_backup_id> -SELECT @the_backup_id:=backup_id FROM cluster.backup_info; +SELECT @the_backup_id:=backup_id FROM mysql.backup_info; let the_backup_id=`select @the_backup_id`; # restore on slave, first check that nothing is there diff --git a/mysql-test/t/rpl_ndb_dd_advance.test b/mysql-test/t/rpl_ndb_dd_advance.test index a77521f3a82..4730951cb47 100644 --- a/mysql-test/t/rpl_ndb_dd_advance.test +++ b/mysql-test/t/rpl_ndb_dd_advance.test @@ -436,19 +436,19 @@ SELECT COUNT(*) FROM history; --exec $NDB_TOOLS_DIR/ndb_select_all --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -d sys --delimiter=',' SYSTAB_0 | grep 520093696 > $MYSQLTEST_VARDIR/tmp.dat -CREATE TEMPORARY TABLE IF NOT EXISTS cluster.backup_info (id INT, backup_id INT) ENGINE = HEAP; +CREATE TEMPORARY TABLE IF NOT EXISTS mysql.backup_info (id INT, backup_id INT) ENGINE = HEAP; -DELETE FROM cluster.backup_info; +DELETE FROM mysql.backup_info; -LOAD DATA INFILE '../tmp.dat' INTO TABLE cluster.backup_info FIELDS TERMINATED BY ','; +LOAD DATA INFILE '../tmp.dat' INTO TABLE mysql.backup_info FIELDS TERMINATED BY ','; --exec rm $MYSQLTEST_VARDIR/tmp.dat || true --replace_column 1 <the_backup_id> -SELECT @the_backup_id:=backup_id FROM cluster.backup_info; +SELECT @the_backup_id:=backup_id FROM mysql.backup_info; let the_backup_id=`select @the_backup_id`; -DROP TABLE IF EXISTS cluster.backup_info; +DROP TABLE IF EXISTS mysql.backup_info; #RESET MASTER; --echo ************ Restore the slave ************************ diff --git a/mysql-test/t/rpl_ndb_idempotent.test b/mysql-test/t/rpl_ndb_idempotent.test index eb47ec08695..477e7ff02e5 100644 --- a/mysql-test/t/rpl_ndb_idempotent.test +++ b/mysql-test/t/rpl_ndb_idempotent.test @@ -4,7 +4,7 @@ # # Currently test only works with ndb since it retrieves "old" -# binlog positions with cluster.binlog_index and apply_status; +# binlog positions with mysql.binlog_index and apply_status; # # create a table with one row @@ -15,7 +15,7 @@ SELECT * FROM t1 ORDER BY c3; # sync slave and retrieve epoch sync_slave_with_master; --replace_column 1 <the_epoch> -SELECT @the_epoch:=MAX(epoch) FROM cluster.apply_status; +SELECT @the_epoch:=MAX(epoch) FROM mysql.apply_status; let $the_epoch= `select @the_epoch` ; SELECT * FROM t1 ORDER BY c3; @@ -24,7 +24,7 @@ connection master; --replace_result $the_epoch <the_epoch> --replace_column 1 <the_pos> eval SELECT @the_pos:=Position,@the_file:=SUBSTRING_INDEX(FILE, '/', -1) - FROM cluster.binlog_index WHERE epoch = $the_epoch ; + FROM mysql.binlog_index WHERE epoch = $the_epoch ; let $the_pos= `SELECT @the_pos` ; let $the_file= `SELECT @the_file` ; diff --git a/mysql-test/t/rpl_ndb_multi.test b/mysql-test/t/rpl_ndb_multi.test index fc7ecab00ac..630668ad369 100644 --- a/mysql-test/t/rpl_ndb_multi.test +++ b/mysql-test/t/rpl_ndb_multi.test @@ -7,7 +7,7 @@ # # Currently test only works with ndb since it retrieves "old" -# binlog positions with cluster.binlog_index and apply_status; +# binlog positions with mysql.binlog_index and apply_status; # # create a table with one row, and make sure the other "master" gets it @@ -25,7 +25,7 @@ SELECT * FROM t1 ORDER BY c3; connection master; sync_slave_with_master; --replace_column 1 <the_epoch> -SELECT @the_epoch:=MAX(epoch) FROM cluster.apply_status; +SELECT @the_epoch:=MAX(epoch) FROM mysql.apply_status; let $the_epoch= `select @the_epoch` ; SELECT * FROM t1 ORDER BY c3; stop slave; @@ -34,7 +34,7 @@ stop slave; connection server2; --replace_result $the_epoch <the_epoch> eval SELECT @the_pos:=Position,@the_file:=SUBSTRING_INDEX(FILE, '/', -1) - FROM cluster.binlog_index WHERE epoch = $the_epoch ; + FROM mysql.binlog_index WHERE epoch = $the_epoch ; let $the_pos= `SELECT @the_pos` ; let $the_file= `SELECT @the_file` ; diff --git a/mysql-test/t/rpl_ndb_sync.test b/mysql-test/t/rpl_ndb_sync.test index 20d4f5707f8..10f7dd534a3 100644 --- a/mysql-test/t/rpl_ndb_sync.test +++ b/mysql-test/t/rpl_ndb_sync.test @@ -6,7 +6,7 @@ # # Currently test only works with ndb since it retrieves "old" -# binlog positions with cluster.binlog_index and apply_status; +# binlog positions with mysql.binlog_index and apply_status; # # stop the save @@ -94,11 +94,11 @@ STOP SLAVE; --connection master reset master; # should now contain nothing -select * from cluster.binlog_index; +select * from mysql.binlog_index; --connection slave reset slave; # should now contain nothing -select * from cluster.apply_status; +select * from mysql.apply_status; # End 5.1 Test 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/show_check.test b/mysql-test/t/show_check.test index 7d4ad099e6f..be803a74c7b 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -1,5 +1,4 @@ -# Requires use of multiple simultaneous connections, not supported with -# embedded server testing +# Uses GRANT commands that usually disabled in embedded server -- source include/not_embedded.inc # check that CSV engine was compiled in, as the result of the test 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/status.test b/mysql-test/t/status.test index 2afcd49962c..099c2e062bb 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -1,10 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# ---source include/not_embedded.inc # PS causes different statistics --disable_ps_protocol 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..0bbbc5a793e 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); @@ -1955,6 +1955,39 @@ SELECT field1, field2 DROP TABLE t1, t2; +# +# Bug #23478: not top-level IN subquery returning a non-empty result set +# with possible NULL values by index access from the outer query +# + +CREATE TABLE t1(a int, INDEX (a)); +INSERT INTO t1 VALUES (1), (3), (5), (7); +INSERT INTO t1 VALUES (NULL); + +CREATE TABLE t2(a int); +INSERT INTO t2 VALUES (1),(2),(3); + +EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; +SELECT a, a IN (SELECT a FROM t1) FROM t2; + +DROP TABLE t1,t2; + +# +# Bug #11302: getObject() returns a String for a sub-query of type datetime +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25'); + +CREATE TABLE t2 AS SELECT + (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a + FROM t1 WHERE a > '2000-01-01'; +SHOW CREATE TABLE t2; + +CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); +SHOW CREATE TABLE t3; + +DROP TABLE t1,t2,t3; + # End of 4.1 tests # @@ -2247,11 +2280,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 */ @@ -2426,3 +2459,40 @@ SELECT ( FROM t1 t2 GROUP BY t2.a; DROP TABLE t1,t2; + +# +# Bug #21727: Correlated subquery that requires filesort: +# slow with big sort_buffer_size +# + +CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); +CREATE TABLE t2 (x int auto_increment, y int, z int, + PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); + +disable_query_log; +let $1=3000; +while ($1) +{ + eval INSERT INTO t1(a) VALUES(RAND()*1000); + eval SELECT MAX(b) FROM t1 INTO @id; + let $2=10; + while ($2) + { + eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000); + dec $2; + } + dec $1; +} +enable_query_log; + +SET SESSION sort_buffer_size = 32 * 1024; +SELECT SQL_NO_CACHE COUNT(*) + FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c + FROM t1) t; + +SET SESSION sort_buffer_size = 8 * 1024 * 1024; +SELECT SQL_NO_CACHE COUNT(*) + FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c + FROM t1) t; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test new file mode 100644 index 00000000000..f7fbafdd17f --- /dev/null +++ b/mysql-test/t/subselect3.test @@ -0,0 +1,137 @@ +--disable_warnings +drop table if exists t0, t1, t2, t3, t4; +--enable_warnings + +# +# 1. Subquery with GROUP/HAVING +# +create table t1 (oref int, grp int, ie int) ; +insert into t1 (oref, grp, ie) values + (1, 1, 1), + (1, 1, 1), + (1, 2, NULL), + + (2, 1, 3), + + (3, 1, 4), + (3, 2, NULL); + +# Ok, for +# select max(ie) from t1 where oref=PARAM group by grp +# we'll have: +# 1 -> (1, NULL) matching + NULL +# 2 -> (3) non-matching +# 3 -> (3, NULL) non-matching + NULL +# 4 -> () nothing. + +create table t2 (oref int, a int); +insert into t2 values + (1, 1), + (2, 2), + (3, 3), + (4, NULL), + (2, NULL); + +# true, false, null, false, null +select a, oref, a in (select max(ie) + from t1 where oref=t2.oref group by grp) from t2; + +# This must have a trigcond +explain extended +select a, oref, a in (select max(ie) + from t1 where oref=t2.oref group by grp) from t2; + +# This must not have a trigcond: +explain extended +select a, oref from t2 +where a in (select max(ie) from t1 where oref=t2.oref group by grp); + + +# Non-correlated subquery, 2 NULL evaluations +create table t3 (a int); +insert into t3 values (NULL), (NULL); +flush status; +select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +show status like 'Handler_read_rnd_next'; +select ' ^ This must show 11' Z; + +# This must show trigcond: +explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; + +drop table t1, t2, t3; + +# +# 2. Subquery handled with 'index_subquery': +# +create table t1 (a int, oref int, key(a)); +insert into t1 values + (1, 1), + (1, NULL), + (2, 3), + (2, NULL), + (3, NULL); + +create table t2 (a int, oref int); +insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); + +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; + +# The next explain shows "using index" but that is just incorrect display +# (there is a bug filed about this). +explain extended +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; + +flush status; +select oref, a from t2 where a in (select a from t1 where oref=t2.oref); +# This will only show access to t2: +show status like '%Handler_read_rnd_next'; + +# Check that repeated NULL-scans are not cached (subq. is not correlated): +delete from t2; +insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); + +flush status; +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +show status like '%Handler_read%'; +select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; + +drop table t1, t2; + +# +# 3. Subquery handled with 'unique_index_subquery': +# +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); + +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); + +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; + +drop table t1, t2; + +# +# 4. Subquery that is a join, with ref access +# +create table t1 (a int, b int, key(a)); +insert into t1 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; + +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +# This must have trigcond in WHERE and HAVING: +explain extended +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +drop table t1, t2, t3; + diff --git a/mysql-test/t/synchronization.test b/mysql-test/t/synchronization.test index c7696195ee0..71e13a65ec3 100644 --- a/mysql-test/t/synchronization.test +++ b/mysql-test/t/synchronization.test @@ -4,7 +4,7 @@ # --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings connect (con1,localhost,root,,); diff --git a/mysql-test/t/system_mysql_db_fix30020.test b/mysql-test/t/system_mysql_db_fix30020.test index d5c82faaa06..3a3af085629 100644 --- a/mysql-test/t/system_mysql_db_fix30020.test +++ b/mysql-test/t/system_mysql_db_fix30020.test @@ -98,7 +98,7 @@ INSERT INTO user VALUES ('localhost','', '','N','N','N','N','N','N','N','N',' DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, -time_zone_transition_type, general_log, slow_log, event; +time_zone_transition_type, general_log, slow_log, event, binlog_index; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test index 5adefc2e966..2d4fd1e920f 100644 --- a/mysql-test/t/system_mysql_db_fix40123.test +++ b/mysql-test/t/system_mysql_db_fix40123.test @@ -69,7 +69,7 @@ CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, binlog_index; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50030.test b/mysql-test/t/system_mysql_db_fix50030.test index 724786febaf..78ecf1e573c 100644 --- a/mysql-test/t/system_mysql_db_fix50030.test +++ b/mysql-test/t/system_mysql_db_fix50030.test @@ -72,7 +72,7 @@ CREATE TABLE procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char( -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, binlog_index; -- enable_query_log diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 09576d415e5..3aa162b4700 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -125,3 +125,13 @@ PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="2006060 EXECUTE s; DROP PREPARE s; DROP TABLE t1; + + +# +# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result +# +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); + diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 1b80a15e4ff..f16e5dec40c 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'; # @@ -1120,6 +1120,12 @@ select i, count(distinct j) from t1 group by i; select i+0.0 as i2, count(distinct j) from t1 group by i2; drop table t1; +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +drop table t1; + # # Bug#16172 DECIMAL data type processed incorrectly # @@ -1130,3 +1136,8 @@ select cast(-3.4 as decimal(2,1)); select cast(99.6 as decimal(2,0)); select cast(-13.4 as decimal(2,1)); select cast(98.6 as decimal(2,0)); + +# Bug #8663 (cant use bigint as input to CAST) +# +select cast(19999999999999999999 as unsigned); + diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index c62d7829b05..52ae424e423 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -127,6 +127,50 @@ create table t1(f1 int); insert into t1 values(1),(2); explain select myfunc_int(f1) from t1 order by 1; drop table t1; + +# +# Bug #21809: Error 1356 while selecting from view with grouping though +# underlying select OK. +# +CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2); + +DELIMITER ||; +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN + RETURN a; +END +|| +DELIMITER ;|| + +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +SELECT a,c FROM v1; + +--error ER_PARSE_ERROR +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; + +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/upgrade.test b/mysql-test/t/upgrade.test index 5c5046cf7e9..f517c7787f8 100644 --- a/mysql-test/t/upgrade.test +++ b/mysql-test/t/upgrade.test @@ -47,3 +47,12 @@ select * from `txu@0023p@0023p1`; select * from `txu#p#p1`; drop table `txu@0023p@0023p1`; drop table `txu#p#p1`; + +# +# Check if old tables work +# + +system cp $MYSQL_TEST_DIR/std_data/old_table-323.frm $MYSQLTEST_VARDIR/master-data/test/t1.frm; +truncate t1; +drop table t1; + diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index ecd10384870..eb03ff3ea52 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; diff --git a/mysql-test/t/wait_for_socket.sh b/mysql-test/t/wait_for_socket.sh index 3b900fa2208..b6526b7d19c 100755 --- a/mysql-test/t/wait_for_socket.sh +++ b/mysql-test/t/wait_for_socket.sh @@ -33,7 +33,7 @@ fi ########################################################################### -client_args="--silent --socket=$socket_path " +client_args="--silent --socket=$socket_path --connect_timeout=1 " [ -n "$username" ] && client_args="$client_args --user=$username " [ -n "$password" ] && client_args="$client_args --password=$password " |