diff options
Diffstat (limited to 'mysql-test')
33 files changed, 1047 insertions, 55 deletions
diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index ecbd66d54a0..abf9cd066b1 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -89,10 +89,14 @@ sub mtr_report_test_skipped ($) { { print "[ disabled ] $tinfo->{'comment'}\n"; } - else + elsif ( $tinfo->{'comment'} ) { print "[ skipped ] $tinfo->{'comment'}\n"; } + else + { + print "[ skipped ]\n"; + } } sub mtr_report_tests_not_skipped_though_disabled ($) { diff --git a/mysql-test/lib/mtr_unique.pl b/mysql-test/lib/mtr_unique.pl new file mode 100644 index 00000000000..a8fb320c773 --- /dev/null +++ b/mysql-test/lib/mtr_unique.pl @@ -0,0 +1,156 @@ +# +# This file is used from mysql-test-run.pl when choosing +# port numbers and directories to use for running mysqld. +# + +use strict; +use Fcntl ':flock'; + +# +# Requested IDs are stored in a hash and released upon END. +# +my %mtr_unique_assigned_ids = (); +END { + while(my ($id,$file) = each(%mtr_unique_assigned_ids)) { + print "Autoreleasing $file:$id\n"; + mtr_release_unique_id($file, $id); + } +} + +# +# Require a unique, numerical ID, given a file name (where all +# requested IDs are stored), a minimum and a maximum value. +# +# We use flock to implement locking for the ID file and ignore +# possible problems arising from lack of support for it on +# some platforms (it should work on most, and the possible +# race condition would occur rarely). The proper solution for +# this is a daemon that manages IDs, of course. +# +# If no unique ID within the specified parameters can be +# obtained, return undef. +# +sub mtr_require_unique_id($$$) { + my $file = shift; + my $min = shift; + my $max = shift; + my $ret = undef; + my $changed = 0; + + my $can_use_ps = `ps -e | grep '^[ ]*$$ '`; + + if(eval("readlink '$file'") || eval("readlink '$file.sem'")) { + die 'lock file is a symbolic link'; + } + + chmod 0777, "$file.sem"; + open SEM, ">", "$file.sem" or die "can't write to $file.sem"; + flock SEM, LOCK_EX or die "can't lock $file.sem"; + if(! -e $file) { + open FILE, ">", $file or die "can't create $file"; + close FILE; + } + + if(eval("readlink '$file'") || eval("readlink '$file.sem'")) { + die 'lock file is a symbolic link'; + } + + chmod 0777, $file; + open FILE, "+<", $file or die "can't open $file"; + select undef,undef,undef,0.2; + seek FILE, 0, 0; + my %taken = (); + while(<FILE>) { + chomp; + my ($id, $pid) = split / /; + $taken{$id} = $pid; + if($can_use_ps) { + my $res = `ps -e | grep '^[ ]*$pid '`; + if(!$res) { + print "Ignoring slot $id used by missing process $pid.\n"; + delete $taken{$id}; + ++$changed; + } + } + } + for(my $i=$min; $i<=$max; ++$i) { + if(! exists $taken{$i}) { + $ret = $i; + $taken{$i} = $$; + ++$changed; + last; + } + } + if($changed) { + seek FILE, 0, 0; + truncate FILE, 0 or die "can't truncate $file"; + for my $k (keys %taken) { + print FILE $k . ' ' . $taken{$k} . "\n"; + } + } + close FILE; + flock SEM, LOCK_UN or warn "can't unlock $file.sem"; + close SEM; + $mtr_unique_assigned_ids{$ret} = $file if defined $ret; + return $ret; +} + +# +# Require a unique ID like above, but sleep if no ID can be +# obtained immediately. +# +sub mtr_require_unique_id_and_wait($$$) { + my $ret = mtr_require_unique_id($_[0],$_[1],$_[2]); + while(! defined $ret) { + sleep 30; + $ret = mtr_require_unique_id($_[0],$_[1],$_[2]); + print "Waiting for unique id to become available...\n" unless $ret; + } + return $ret; +} + +# +# Release a unique ID. +# +sub mtr_release_unique_id($$) { + my $file = shift; + my $myid = shift; + + if(eval("readlink '$file'") || eval("readlink '$file.sem'")) { + die 'lock file is a symbolic link'; + } + + open SEM, ">", "$file.sem" or die "can't write to $file.sem"; + flock SEM, LOCK_EX or die "can't lock $file.sem"; + + if(eval("readlink '$file'") || eval("readlink '$file.sem'")) { + die 'lock file is a symbolic link'; + } + + if(! -e $file) { + open FILE, ">", $file or die "can't create $file"; + close FILE; + } + open FILE, "+<", $file or die "can't open $file"; + select undef,undef,undef,0.2; + seek FILE, 0, 0; + my %taken = (); + while(<FILE>) { + chomp; + my ($id, $pid) = split / /; + $taken{$id} = $pid; + } + delete $taken{$myid}; + seek FILE, 0, 0; + truncate FILE, 0 or die "can't truncate $file"; + for my $k (keys %taken) { + print FILE $k . ' ' . $taken{$k} . "\n"; + } + close FILE; + flock SEM, LOCK_UN or warn "can't unlock $file.sem"; + close SEM; + delete $mtr_unique_assigned_ids{$myid}; +} + +1; + diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index b8ae774629c..6ec55c5f4d6 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -63,10 +63,8 @@ use Getopt::Long; use Sys::Hostname; use IO::Socket; use IO::Socket::INET; -use Data::Dumper; use strict; use warnings; -use diagnostics; select(STDOUT); $| = 1; # Automatically flush STDOUT @@ -88,6 +86,7 @@ require "lib/mtr_diff.pl"; require "lib/mtr_match.pl"; require "lib/mtr_misc.pl"; require "lib/mtr_stress.pl"; +require "lib/mtr_unique.pl"; $Devel::Trace::TRACE= 1; @@ -448,7 +447,6 @@ sub main () { mtr_exit(0); } - ############################################################################## # # Default settings diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 40ca0a38db2..8158cab4323 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1020,3 +1020,29 @@ t1 CREATE TABLE `t1` ( `stddev(0)` double(8,4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +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; +a cnt +1 65536 +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +a sumation +1 2147516416 +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; +a average +1 32768.5000 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index dbd6783fe76..95211ad9133 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -873,3 +873,11 @@ SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); 1 1 DROP TABLE t1; +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1(foo) VALUES (NULL); +ERROR 23000: Column 'foo' cannot be null +INSERT INTO t1() VALUES (); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +INSERT INTO t1(foo) VALUES (''); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +DROP TABLE t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index b78aa0e77b5..d910be98cf4 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1326,6 +1326,21 @@ COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) COLUMN_DEFAULT=get_value( fld1 7cf7a6782be951a1f2464a350da926a5 65532 1 DROP TABLE bug23037; DROP FUNCTION get_value; +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +2 DERIVED tables ALL NULL NULL NULL NULL 0 Using filesort +explain select * from (select table_name from information_schema.tables) as a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +2 DERIVED tables ALL NULL NULL NULL NULL 2 +drop view v1; End of 5.0 tests. select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 50b0b6ae294..34e961395c4 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -96,3 +96,37 @@ i 2 affected rows: 1 affected rows: 0 +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; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 1 Y 32768 0 63 +id +1 +select * from v1 group by id limit 0; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 0 Y 32768 0 63 +id +select * from v1 where id=1000 group by id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 0 Y 32768 0 63 +id +select * from v1 where id=1 group by id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 1 Y 32768 0 63 +id +1 +select * from v2 where renamed=1 group by renamed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v2 id renamed 3 10 1 Y 32768 0 63 +renamed +1 +select * from v3 where renamed=1 group by renamed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def v3 renamed 8 12 0 Y 32896 0 63 +renamed +drop table t1; +drop view v1,v2,v3; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 6461696b0d0..e5cc47fd9d2 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -760,13 +760,6 @@ xxxxxxxxxxxxxxxxxxxaa xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxz 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); -a b -1 2 -1 1 -drop table t1; create table t1 ( `sid` decimal(8,0) default null, `wnid` varchar(11) not null default '', @@ -854,6 +847,40 @@ b a 20 1 10 2 DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; +num +3 +2 +SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); +str +test1 +test2 +SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; +num +3 +2 +SELECT a + 1 AS num FROM t1 HAVING 30 - num; +num +2 +3 +SELECT a + 1 AS num, num + 1 FROM t1; +ERROR 42S22: Unknown column 'num' in 'field list' +SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; +num (select num + 2 FROM t1 LIMIT 1) +2 4 +3 5 +SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; +ERROR 42S22: Unknown column 'num' in 'on clause' +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); +a b +1 2 +1 1 +drop table t1; CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); INSERT INTO t1 VALUES (1,1), (2,2), (3,3); explain SELECT t1.b as a, t2.b as c FROM diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index d5996ee8163..fac026047de 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1276,3 +1276,51 @@ Variable_name Value Last_query_cost 0.000000 drop table t1; SET GLOBAL query_cache_size=0; +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; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 1 +create table t2 like t1; +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 2 +insert into t2 select * from t1; +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 3 +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/r/subselect.result b/mysql-test/r/subselect.result index ecd9762dd24..a4c666dd876 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -744,7 +744,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -907,7 +907,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -2816,19 +2816,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having (<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); @@ -3001,6 +3001,38 @@ field1 field2 1 1 1 3 DROP TABLE t1, t2; +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index +SELECT a, a IN (SELECT a FROM t1) FROM t2; +a a IN (SELECT a FROM t1) +1 1 +2 NULL +3 1 +DROP TABLE t1,t2; +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; +Table Create Table +t2 CREATE TABLE `t2` ( + `sub_a` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2,t3; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); @@ -3544,3 +3576,19 @@ FROM t1 GROUP BY t1.a LIMIT 1) 2 2 DROP TABLE t1,t2; +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)); +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; +COUNT(*) +3000 +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; +COUNT(*) +3000 +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result new file mode 100644 index 00000000000..10cc2729f3e --- /dev/null +++ b/mysql-test/r/subselect3.result @@ -0,0 +1,153 @@ +drop table if exists t0, t1, t2, t3, t4; +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); +create table t2 (oref int, a int); +insert into t2 values +(1, 1), +(2, 2), +(3, 3), +(4, NULL), +(2, NULL); +select a, oref, a in (select max(ie) +from t1 where oref=t2.oref group by grp) from t2; +a oref a in (select max(ie) +from t1 where oref=t2.oref group by grp) +1 1 1 +2 2 0 +3 3 NULL +NULL 4 0 +NULL 2 NULL +explain extended +select a, oref, a in (select max(ie) +from t1 where oref=t2.oref group by grp) from t2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) +from t1 where oref=t2.oref group by grp)` from `test`.`t2` +explain extended +select a, oref from t2 +where a in (select max(ie) from t1 where oref=t2.oref group by grp); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) +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; +a in (select max(ie) from t1 where oref=4 group by grp) +0 +0 +show status like 'Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 11 +select ' ^ This must show 11' Z; +Z + ^ This must show 11 +explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +drop table t1, t2, t3; +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; +oref a Z +1 1 1 +2 2 0 +3 NULL NULL +4 NULL 0 +explain extended +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using index; Using where +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2` +flush status; +select oref, a from t2 where a in (select a from t1 where oref=t2.oref); +oref a +1 1 +show status like '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 5 +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; +oref a Z +0 NULL 0 +0 NULL 0 +0 NULL 0 +0 NULL 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 29 +select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; +Z +No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +drop table t1, t2; +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 ; +a b Z +1 1 1 +2 1 0 +NULL 1 NULL +NULL 0 0 +drop table t1, t2; +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; +a oref Z +1 1 1 +NULL 1 NULL +NULL 0 0 +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where +2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where +Warnings: +Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` +drop table t1, t2, t3; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 04b37f7ae86..a8d5388097d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -179,3 +179,15 @@ a 2006-06-06 15:55:55 DROP PREPARE s; DROP TABLE t1; +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)) +20060810.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)) +20060810101112.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) +20060810101112.000014 +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); +CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)) +101112.098700 diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 595c15afcc2..901297288af 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1420,6 +1420,14 @@ i2 count(distinct j) 1.0 2 2.0 2 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; +f1 +20101112000000.000014 +101112.000000 +drop table t1; select cast(143.481 as decimal(4,1)); cast(143.481 as decimal(4,1)) 143.5 @@ -1447,3 +1455,8 @@ Error 1264 Out of range value for column 'cast(-13.4 as decimal(2,1))' at row 1 select cast(98.6 as decimal(2,0)); cast(98.6 as decimal(2,0)) 99 +select cast(19999999999999999999 as unsigned); +cast(19999999999999999999 as unsigned) +18446744073709551615 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 851818e7a79..1308980ca96 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -105,6 +105,85 @@ explain select myfunc_int(f1) from t1 order by 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort drop table t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 values (1,1),(2,2); +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN +RETURN a; +END +|| +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; +myfunc_int(a AS attr_name) +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1` +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1` +SELECT a,c FROM v1; +a c +1 1 +2 2 +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; End of 5.0 tests. drop function if exists pi; CREATE FUNCTION pi RETURNS STRING SONAME "should_not_parse.so"; 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/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/information_schema.test b/mysql-test/t/information_schema.test index 53fd79fcdf0..cec140a5e63 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -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/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 c06e52e2d78..cb4a5593de7 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/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 67c4c4cb20b..cb4e5f97c3a 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -877,3 +877,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/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/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/subselect.test b/mysql-test/t/subselect.test index b68cd225a08..0bbbc5a793e 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -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 # @@ -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/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 e704014c0ab..f16e5dec40c 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -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 cab55fdb32a..01aac24ded4 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. # |