diff options
| author | Sergey Petrunya <psergey@askmonty.org> | 2009-09-15 14:46:35 +0400 |
|---|---|---|
| committer | Sergey Petrunya <psergey@askmonty.org> | 2009-09-15 14:46:35 +0400 |
| commit | 151e5d586cc59afb3c664d56d47265c964fe7fb1 (patch) | |
| tree | 1972ed60ad937e632cf111e7beafb2148a87efeb /mysql-test | |
| parent | 2083b7ddfd85b2bab837b73cb8357afcc2e893d8 (diff) | |
| parent | b495b3db2346a98650946ad5907ba3fe164eadc7 (diff) | |
| download | mariadb-git-151e5d586cc59afb3c664d56d47265c964fe7fb1.tar.gz | |
Merge lp:maria -> lp:~maria-captains/maria/maria-5.1-merge
Diffstat (limited to 'mysql-test')
65 files changed, 1576 insertions, 300 deletions
diff --git a/mysql-test/lib/mtr_gcov.pl b/mysql-test/lib/mtr_gcov.pl index f531889b08d..ef1067bfd27 100644 --- a/mysql-test/lib/mtr_gcov.pl +++ b/mysql-test/lib/mtr_gcov.pl @@ -20,6 +20,8 @@ use strict; +our $basedir; + sub gcov_prepare ($) { my ($dir)= @_; print "Purging gcov information from '$dir'...\n"; @@ -42,7 +44,7 @@ sub gcov_collect ($$$) { # Get current directory to return to later. my $start_dir= cwd(); - print "Collecting source coverage info using '$gcov'...\n"; + print "Collecting source coverage info using '$gcov'...$basedir\n"; -f "$start_dir/$gcov_msg" and unlink("$start_dir/$gcov_msg"); -f "$start_dir/$gcov_err" and unlink("$start_dir/$gcov_err"); @@ -62,6 +64,7 @@ sub gcov_collect ($$$) { $dir_reported= 1; } system("$gcov $f 2>>$start_dir/$gcov_err >>$start_dir/$gcov_msg"); + system("perl $basedir/mysql-test/lib/process-purecov-annotations.pl $f.gcov"); } chdir($start_dir); } diff --git a/mysql-test/lib/process-purecov-annotations.pl b/mysql-test/lib/process-purecov-annotations.pl new file mode 100755 index 00000000000..843d1d2f130 --- /dev/null +++ b/mysql-test/lib/process-purecov-annotations.pl @@ -0,0 +1,64 @@ +#!/usr/bin/perl +# -*- cperl -*- + +# This script processes a .gcov coverage report to honor purecov +# annotations: lines marked as inspected or as deadcode are changed +# from looking like lines with code that was never executed to look +# like lines that have no executable code. + +use strict; +use warnings; + +foreach my $in_file_name ( @ARGV ) +{ + my $out_file_name=$in_file_name . ".tmp"; + my $skipping=0; + + open(IN, "<", $in_file_name) || next; + open(OUT, ">", $out_file_name); + while(<IN>) + { + my $line= $_; + my $check= $line; + + # process purecov: start/end multi-blocks + my $started=0; + my $ended= 0; + while (($started=($check =~ s/purecov: *begin *(deadcode|inspected)//)) || + ($ended=($check =~ s/purecov: *end//))) + { + $skipping= $skipping + $started - $ended; + } + if ($skipping < 0) + { + print OUT "WARNING: #####: incorrect order of purecov begin/end annotations\n"; + $skipping= 0; + } + + # Besides purecov annotations, also remove uncovered code mark from cases + # like the following: + # + # -: 211:*/ + # -: 212:class Field_value : public Value_dep + # #####: 213:{ + # -: 214:public: + # + # I have no idea why would gcov think there is uncovered code there + # + my @arr= split(/:/, $line); + if ($skipping || $line =~ /purecov: *(inspected|deadcode)/ || + $arr[2] =~ m/^{ */) + { + # Change '####' to '-'. + $arr[0] =~ s/#####/ -/g; + $line= join(":", @arr); + } + print OUT $line; + } + close(IN); + close(OUT); + system("cat $out_file_name > $in_file_name"); + system("rm $out_file_name"); +} + + diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index e48bc1954cb..812e25ef351 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -170,6 +170,7 @@ our $opt_force; our $opt_mem= $ENV{'MTR_MEM'}; our $opt_gcov; +our $opt_gcov_src_dir; our $opt_gcov_exe= "gcov"; our $opt_gcov_err= "mysql-test-gcov.msg"; our $opt_gcov_msg= "mysql-test-gcov.err"; @@ -272,7 +273,7 @@ sub main { command_line_setup(); if ( $opt_gcov ) { - gcov_prepare($basedir); + gcov_prepare($basedir . "/" . $opt_gcov_src_dir); } if (!$opt_suites) { @@ -418,7 +419,7 @@ sub main { mtr_print_line(); if ( $opt_gcov ) { - gcov_collect($basedir, $opt_gcov_exe, + gcov_collect($basedir . "/" . $opt_gcov_src_dir, $opt_gcov_exe, $opt_gcov_msg, $opt_gcov_err); } @@ -890,6 +891,7 @@ sub command_line_setup { # Coverage, profiling etc 'gcov' => \$opt_gcov, + 'gcov-src-dir=s' => \$opt_gcov_src_dir, 'valgrind|valgrind-all' => \$opt_valgrind, 'valgrind-mysqltest' => \$opt_valgrind_mysqltest, 'valgrind-mysqld' => \$opt_valgrind_mysqld, @@ -1799,6 +1801,20 @@ sub tool_arguments ($$) { return mtr_args2str($exe, @$args); } +# This is not used to actually start a mysqld server, just to allow test +# scripts to run the mysqld binary to test invalid server startup options. +sub mysqld_client_arguments () { + my $default_mysqld= default_mysqld(); + my $exe = find_mysqld($basedir); + my $args; + mtr_init_args(\$args); + mtr_add_arg($args, "--no-defaults"); + mtr_add_arg($args, "--basedir=%s", $basedir); + mtr_add_arg($args, "--character-sets-dir=%s", $default_mysqld->value("character-sets-dir")); + mtr_add_arg($args, "--language=%s", $default_mysqld->value("language")); + return mtr_args2str($exe, @$args); +} + sub have_maria_support () { my $maria_var= $mysqld_variables{'maria'}; @@ -2008,6 +2024,7 @@ sub environment_setup { $ENV{'MYSQLADMIN'}= native_path($exe_mysqladmin); $ENV{'MYSQL_CLIENT_TEST'}= mysql_client_test_arguments(); $ENV{'MYSQL_FIX_SYSTEM_TABLES'}= mysql_fix_arguments(); + $ENV{'MYSQLD'}= mysqld_client_arguments(); $ENV{'EXE_MYSQL'}= $exe_mysql; # ---------------------------------------------------- @@ -5516,6 +5533,9 @@ Misc options actions. Disable facility with NUM=0. gcov Collect coverage information after the test. The result is a gcov file per source and header file. + gcov-src-dir=subdir Colllect coverage only within the given subdirectory. + For example, if you're only developing the SQL layer, + it makes sense to use --gcov-src-dir=sql experimental=<file> Refer to list of tests considered experimental; failures will be marked exp-fail instead of fail. report-features First run a "test" that reports mysql features diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result new file mode 100644 index 00000000000..57d12a64f5a --- /dev/null +++ b/mysql-test/r/log_slow.result @@ -0,0 +1,60 @@ +select @@log_slow_filter; +@@log_slow_filter + +select @@log_slow_rate_limit; +@@log_slow_rate_limit +1 +select @@log_slow_verbosity; +@@log_slow_verbosity + +show variables like "log_slow%"; +Variable_name Value +log_slow_filter +log_slow_queries ON +log_slow_rate_limit 1 +log_slow_time 10.000000 +log_slow_verbosity +set @@log_slow_filter= "filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk,admin"; +select @@log_slow_filter; +@@log_slow_filter +admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +set @@log_slow_filter="admin,admin"; +select @@log_slow_filter; +@@log_slow_filter +admin +set @@log_slow_filter=7; +select @@log_slow_filter; +@@log_slow_filter +admin,filesort,filesort_on_disk +set @@log_slow_filter= "filesort,impossible,impossible2,admin"; +ERROR 42000: Variable 'log_slow_filter' can't be set to the value of 'impossible' +set @@log_slow_filter= "filesort, admin"; +ERROR 42000: Variable 'log_slow_filter' can't be set to the value of ' admin' +set @@log_slow_filter= 1<<31; +ERROR 42000: Variable 'log_slow_filter' can't be set to the value of '2147483648' +select @@log_slow_filter; +@@log_slow_filter +admin,filesort,filesort_on_disk +set @@log_slow_verbosity= "query_plan,innodb"; +select @@log_slow_verbosity; +@@log_slow_verbosity +innodb,query_plan +set @@log_slow_verbosity=1; +select @@log_slow_verbosity; +@@log_slow_verbosity +innodb +show fields from mysql.slow_log; +Field Type Null Key Default Extra +start_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP +user_host mediumtext NO NULL +query_time time NO NULL +lock_time time NO NULL +rows_sent int(11) NO NULL +rows_examined int(11) NO NULL +db varchar(512) NO NULL +last_insert_id int(11) NO NULL +insert_id int(11) NO NULL +server_id int(10) unsigned NO NULL +sql_text mediumtext NO NULL +set @@log_slow_filter=default; +set @@log_slow_verbosity=default; diff --git a/mysql-test/r/mysql-bug41486.result b/mysql-test/r/mysql-bug41486.result index 02777ab587f..62a6712eae1 100644 --- a/mysql-test/r/mysql-bug41486.result +++ b/mysql-test/r/mysql-bug41486.result @@ -3,6 +3,9 @@ SET @old_max_allowed_packet= @@global.max_allowed_packet; SET @@global.max_allowed_packet = 2 * 1024 * 1024 + 1024; CREATE TABLE t1(data LONGBLOB); INSERT INTO t1 SELECT REPEAT('1', 2*1024*1024); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 SET @old_general_log = @@global.general_log; SET @@global.general_log = 0; SET @@global.general_log = @old_general_log; diff --git a/mysql-test/r/mysqld_option_err.result b/mysql-test/r/mysqld_option_err.result new file mode 100644 index 00000000000..255f109b788 --- /dev/null +++ b/mysql-test/r/mysqld_option_err.result @@ -0,0 +1,6 @@ +Test that unknown option is not silently ignored. +Test bad binlog format. +Test bad default storage engine. +Test non-numeric value passed to number option. +Test that bad value for plugin enum option is rejected correctly. +Done. diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result index a298c552806..5c11163ab9e 100644 --- a/mysql-test/r/ps_11bugs.result +++ b/mysql-test/r/ps_11bugs.result @@ -121,8 +121,8 @@ insert into t1 values (1); explain select * from t1 where 3 in (select (1+1) union select 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used -3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL Impossible HAVING NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL select * from t1 where 3 in (select (1+1) union select 1); a diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 50b5c3c13fb..6b9a6b7c185 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3585,7 +3585,6 @@ INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 const b b 22 const 1 Using index DROP TABLE t1,t2; CREATE TABLE t1(id int PRIMARY KEY, b int, e int); CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cd4844456eb..1b8e31ebf78 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4353,13 +4353,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1)) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result new file mode 100644 index 00000000000..ae117af3e32 --- /dev/null +++ b/mysql-test/r/table_elim.result @@ -0,0 +1,420 @@ +drop table if exists t0, t1, t2, t3; +drop view if exists v1, v2; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); +create table t0 as select * from t1; +create table t2 (a int primary key, b int) +as select a, a as b from t1 where a in (1,2); +create table t3 (a int primary key, b int) +as select a, a as b from t1 where a in (1,3); +# This will be eliminated: +explain select t1.a from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain extended select t1.a from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +select t1.a from t1 left join t2 on t2.a=t1.a; +a +0 +1 +2 +3 +# This will not be eliminated as t2.b is in in select list: +explain select * from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# This will not be eliminated as t2.b is in in order list: +explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# This will not be eliminated as t2.b is in group list: +explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# This will not be eliminated as t2.b is in the WHERE +explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where +# Elimination of multiple tables: +explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +# Elimination of multiple tables (2): +explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +# Elimination when done within an outer join nest: +explain extended +select t0.* +from +t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and +t3.a=t1.a) on t0.a=t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t0`.`a` = `test`.`t1`.`a`)) where 1 +# Elimination with aggregate functions +explain select count(*) from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select count(1) from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +This must not use elimination: +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +drop table t0, t1, t2, t3; +create table t0 ( id integer, primary key (id)); +create table t1 ( +id integer, +attr1 integer, +primary key (id), +key (attr1) +); +create table t2 ( +id integer, +attr2 integer, +fromdate date, +primary key (id, fromdate), +key (attr2,fromdate) +); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0; +insert into t1 select id, id from t0; +insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0; +insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0; +create view v1 as +select +F.id, A1.attr1, A2.attr2 +from +t0 F +left join t1 A1 on A1.id=F.id +left join t2 A2 on A2.id=F.id and +A2.fromdate=(select MAX(fromdate) from +t2 where id=A2.id); +create view v2 as +select +F.id, A1.attr1, A2.attr2 +from +t0 F +left join t1 A1 on A1.id=F.id +left join t2 A2 on A2.id=F.id and +A2.fromdate=(select MAX(fromdate) from +t2 where id=F.id); +This should use one table: +explain select id from v1 where id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY F const PRIMARY PRIMARY 4 const 1 Using index +This should use one table: +explain extended select id from v1 where id in (1,2,3,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY F range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +Warnings: +Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` where (`F`.`id` in (1,2,3,4)) +This should use facts and A1 tables: +explain extended select id from v1 where attr1 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A1.id 1 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t1` `A1` where ((`F`.`id` = `A1`.`id`) and (`A1`.`attr1` between 12 and 14)) +This should use facts, A2 and its subquery: +explain extended select id from v1 where attr2 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A2.id 1 100.00 Using index +3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.A2.id 2 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t2` `A2` where ((`F`.`id` = `A2`.`id`) and (`A2`.`attr2` between 12 and 14) and (`A2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `A2`.`id`)))) +This should use one table: +explain select id from v2 where id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY F const PRIMARY PRIMARY 4 const 1 Using index +This should use one table: +explain extended select id from v2 where id in (1,2,3,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY F range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +Warnings: +Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` where (`F`.`id` in (1,2,3,4)) +This should use facts and A1 tables: +explain extended select id from v2 where attr1 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A1.id 1 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t1` `A1` where ((`F`.`id` = `A1`.`id`) and (`A1`.`attr1` between 12 and 14)) +This should use facts, A2 and its subquery: +explain extended select id from v2 where attr2 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A2.id 1 100.00 Using where; Using index +3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.F.id 2 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t2` `A2` where ((`F`.`id` = `A2`.`id`) and (`A2`.`attr2` between 12 and 14) and (`A2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `F`.`id`)))) +drop view v1, v2; +drop table t0, t1, t2; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); +create table t2 (pk1 int, pk2 int, pk3 int, col int, primary key(pk1, pk2, pk3)); +insert into t2 select a,a,a,a from t1; +This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and +t2.pk2=t2.pk1+1 and +t2.pk3=t2.pk2+1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and +t2.pk3=t2.pk1+1 and +t2.pk2=t2.pk3+1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +This must use both: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and +t2.pk3=t2.pk1+1 and +t2.pk2=t2.pk3+t2.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 +This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk2=t1.a and +t2.pk1=t2.pk2+1 and +t2.pk3=t2.pk1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +drop table t1, t2; +create table t1 (pk int primary key, col int); +insert into t1 values (1,1),(2,2); +create table t2 like t1; +insert into t2 select * from t1; +create table t3 like t1; +insert into t3 select * from t1; +explain +select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +explain +select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +explain select t1.* +from +t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) +on t2.col=t1.col or t2.col=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +explain select t1.*, t2.* +from +t1 left join +(t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) +on t2.pk=t1.col or t2.pk=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +drop table t1, t2, t3; +# +# Check things that look like functional dependencies but really are not +# +create table t1 (a char(10) character set latin1 collate latin1_general_ci primary key); +insert into t1 values ('foo'); +insert into t1 values ('bar'); +create table t2 (a char(10) character set latin1 collate latin1_general_cs primary key); +insert into t2 values ('foo'); +insert into t2 values ('FOO'); +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +drop table t1,t2; +create table t1 (a int primary key); +insert into t1 values (1),(2); +create table t2 (a char(10) primary key); +insert into t2 values ('1'),('1.0'); +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +drop table t1, t2; +create table t1 (a char(10) primary key); +insert into t1 values ('foo'),('bar'); +create table t2 (a char(10), unique key(a(2))); +insert into t2 values ('foo'),('bar'); +explain select t1.* from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 ref a a 3 test.t1.a 2 +drop table t1, t2; +# +# check UPDATE/DELETE that look like they could be eliminated +# +create table t1 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 like t1; +insert into t2 select * from t1; +update t1 left join t2 using (a) set t2.a=t2.a+100; +select * from t1; +a b +1 1 +2 2 +3 3 +select * from t2; +a b +101 1 +102 2 +103 3 +delete from t2; +insert into t2 select * from t1; +delete t2 from t1 left join t2 using (a); +select * from t1; +a b +1 1 +2 2 +3 3 +select * from t2; +a b +drop table t1, t2; +# +# Tests with various edge-case ON expressions +# +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); +create table t2 (pk int primary key, b int) +as select a as pk, a as b from t1 where a in (1,2); +create table t3 (pk1 int, pk2 int, b int, unique(pk1,pk2)); +insert into t3 select a as pk1, a as pk2, a as b from t1 where a in (1,3); +explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on t2.pk in (10); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on t2.pk in (t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index +explain select t1.a from t1 left join t3 on t3.pk1=t1.a and t3.pk2 IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +drop table t1,t2,t3; +# +# Multi-equality tests +# +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); +create table t2 (pk int primary key, b int, c int); +insert into t2 select a,a,a from t1 where a in (1,2); +explain +select t1.* +from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b +where t1.d=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +explain +select t1.* +from +t1 +left join +t2 +on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or +(t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) +where t1.d=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +#This can't be eliminated: +explain +select t1.* +from +t1 +left join +t2 +on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or +(t2.pk=t2.c and t1.a=t1.b and t2.c=t1.b) +where t1.d=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +explain +select t1.* +from +t1 +left join +t2 +on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or +(t2.pk=t2.c and t2.c=t1.b) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain +select t1.* +from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where +drop table t1, t2; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 44a3812725a..d81e80c96f9 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -522,7 +522,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 UNION t2 const PRIMARY PRIMARY 4 const 1 100.00 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select '1' AS `a`,'1' AS `b` from `test`.`t1` where ('1' = 1)) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where ('1' = 1)) +Note 1003 (select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where 1) (select * from t1 where a=5) union (select * from t2 where a=1); a b 1 10 diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 558e6d98339..4582ccddd81 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -865,7 +865,7 @@ select @@query_prealloc_size = @test; @@query_prealloc_size = @test 1 set global sql_mode=repeat('a',80); -ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' End of 4.1 tests create table t1 (a int); select a into @x from t1; diff --git a/mysql-test/suite/pbxt/r/alter_table.result b/mysql-test/suite/pbxt/r/alter_table.result index 467bb54a2a9..7f9ad9665fe 100644 --- a/mysql-test/suite/pbxt/r/alter_table.result +++ b/mysql-test/suite/pbxt/r/alter_table.result @@ -126,23 +126,23 @@ key (n4, n1, n2, n3) ); alter table t1 disable keys; show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 n1 1 n1 NULL 0 NULL NULL BTREE -t1 1 n1_2 1 n1 NULL NULL NULL NULL BTREE -t1 1 n1_2 2 n2 NULL NULL NULL NULL YES BTREE -t1 1 n1_2 3 n3 NULL NULL NULL NULL YES BTREE -t1 1 n1_2 4 n4 NULL NULL NULL NULL YES BTREE -t1 1 n2 1 n2 NULL NULL NULL NULL YES BTREE -t1 1 n2 2 n3 NULL NULL NULL NULL YES BTREE -t1 1 n2 3 n4 NULL NULL NULL NULL YES BTREE -t1 1 n2 4 n1 NULL NULL NULL NULL BTREE -t1 1 n3 1 n3 NULL NULL NULL NULL YES BTREE -t1 1 n3 2 n4 NULL NULL NULL NULL YES BTREE -t1 1 n3 3 n1 NULL NULL NULL NULL BTREE -t1 1 n3 4 n2 NULL NULL NULL NULL YES BTREE -t1 1 n4 1 n4 NULL NULL NULL NULL YES BTREE -t1 1 n4 2 n1 NULL NULL NULL NULL BTREE -t1 1 n4 3 n2 NULL NULL NULL NULL YES BTREE -t1 1 n4 4 n3 NULL NULL NULL NULL YES BTREE +t1 0 n1 1 n1 A 0 NULL NULL BTREE +t1 1 n1_2 1 n1 A 0 NULL NULL BTREE +t1 1 n1_2 2 n2 A 0 NULL NULL YES BTREE +t1 1 n1_2 3 n3 A 0 NULL NULL YES BTREE +t1 1 n1_2 4 n4 A 0 NULL NULL YES BTREE +t1 1 n2 1 n2 A 0 NULL NULL YES BTREE +t1 1 n2 2 n3 A 0 NULL NULL YES BTREE +t1 1 n2 3 n4 A 0 NULL NULL YES BTREE +t1 1 n2 4 n1 A 0 NULL NULL BTREE +t1 1 n3 1 n3 A 0 NULL NULL YES BTREE +t1 1 n3 2 n4 A 0 NULL NULL YES BTREE +t1 1 n3 3 n1 A 0 NULL NULL BTREE +t1 1 n3 4 n2 A 0 NULL NULL YES BTREE +t1 1 n4 1 n4 A 0 NULL NULL YES BTREE +t1 1 n4 2 n1 A 0 NULL NULL BTREE +t1 1 n4 3 n2 A 0 NULL NULL YES BTREE +t1 1 n4 4 n3 A 0 NULL NULL YES BTREE insert into t1 values(10,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(9,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(8,RAND()*1000,RAND()*1000,RAND()); @@ -156,23 +156,23 @@ insert into t1 values(1,RAND()*1000,RAND()*1000,RAND()); alter table t1 enable keys; show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 n1 1 n1 NULL 10 NULL NULL BTREE -t1 1 n1_2 1 n1 NULL NULL NULL NULL BTREE -t1 1 n1_2 2 n2 NULL NULL NULL NULL YES BTREE -t1 1 n1_2 3 n3 NULL NULL NULL NULL YES BTREE -t1 1 n1_2 4 n4 NULL NULL NULL NULL YES BTREE -t1 1 n2 1 n2 NULL NULL NULL NULL YES BTREE -t1 1 n2 2 n3 NULL NULL NULL NULL YES BTREE -t1 1 n2 3 n4 NULL NULL NULL NULL YES BTREE -t1 1 n2 4 n1 NULL NULL NULL NULL BTREE -t1 1 n3 1 n3 NULL NULL NULL NULL YES BTREE -t1 1 n3 2 n4 NULL NULL NULL NULL YES BTREE -t1 1 n3 3 n1 NULL NULL NULL NULL BTREE -t1 1 n3 4 n2 NULL NULL NULL NULL YES BTREE -t1 1 n4 1 n4 NULL NULL NULL NULL YES BTREE -t1 1 n4 2 n1 NULL NULL NULL NULL BTREE -t1 1 n4 3 n2 NULL NULL NULL NULL YES BTREE -t1 1 n4 4 n3 NULL NULL NULL NULL YES BTREE +t1 0 n1 1 n1 A 10 NULL NULL BTREE +t1 1 n1_2 1 n1 A 10 NULL NULL BTREE +t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE +t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE +t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE +t1 1 n2 1 n2 A 10 NULL NULL YES BTREE +t1 1 n2 2 n3 A 10 NULL NULL YES BTREE +t1 1 n2 3 n4 A 10 NULL NULL YES BTREE +t1 1 n2 4 n1 A 10 NULL NULL BTREE +t1 1 n3 1 n3 A 10 NULL NULL YES BTREE +t1 1 n3 2 n4 A 10 NULL NULL YES BTREE +t1 1 n3 3 n1 A 10 NULL NULL BTREE +t1 1 n3 4 n2 A 10 NULL NULL YES BTREE +t1 1 n4 1 n4 A 10 NULL NULL YES BTREE +t1 1 n4 2 n1 A 10 NULL NULL BTREE +t1 1 n4 3 n2 A 10 NULL NULL YES BTREE +t1 1 n4 4 n3 A 10 NULL NULL YES BTREE drop table t1; create table t1 (i int unsigned not null auto_increment primary key); alter table t1 rename t2; @@ -286,17 +286,17 @@ insert into t1 values(1,1), (2,1), (3, 1); alter table t1 add unique (a,b), add key (b); show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 a 2 b A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 0 a 1 a A 300 NULL NULL YES BTREE +t1 0 a 2 b A 300 NULL NULL YES BTREE +t1 1 b 1 b A 300 NULL NULL YES BTREE analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 a 2 b A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 0 a 1 a A 300 NULL NULL YES BTREE +t1 0 a 2 b A 300 NULL NULL YES BTREE +t1 1 b 1 b A 300 NULL NULL YES BTREE drop table t1; CREATE TABLE t1 (i int(10), index(i) ); ALTER TABLE t1 DISABLE KEYS; @@ -545,37 +545,37 @@ drop table if exists t1; create table t1 (a int, key(a)); show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE "this used not to disable the index" alter table t1 modify a int, disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE alter table t1 enable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a NULL NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE alter table t1 modify a bigint, disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE alter table t1 enable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a NULL NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE alter table t1 add b char(10), disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE alter table t1 add c decimal(10,2), enable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE "this however did" alter table t1 disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a NULL NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE desc t1; Field Type Null Key Default Extra a bigint(20) YES MUL NULL @@ -585,7 +585,7 @@ alter table t1 add d decimal(15,5); "The key should still be disabled" show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 0 NULL NULL YES BTREE drop table t1; "Now will test with one unique index" create table t1(a int, b char(10), unique(a)); @@ -595,7 +595,7 @@ t1 0 a 1 a A 0 NULL NULL YES BTREE alter table t1 disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 a 1 a NULL 0 NULL NULL YES BTREE +t1 0 a 1 a A 0 NULL NULL YES BTREE alter table t1 enable keys; "If no copy on noop change, this won't touch the data file" "Unique index, no change" @@ -623,12 +623,12 @@ create table t1(a int, b char(10), unique(a), key(b)); show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 a 1 a A 0 NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE alter table t1 disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 a 1 a NULL 0 NULL NULL YES BTREE -t1 1 b 1 b NULL NULL NULL NULL YES BTREE +t1 0 a 1 a A 0 NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE alter table t1 enable keys; "If no copy on noop change, this won't touch the data file" "The non-unique index will be disabled" @@ -636,31 +636,31 @@ alter table t1 modify a int, disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 a 1 a A 0 NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE alter table t1 enable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 a 1 a NULL 0 NULL NULL YES BTREE -t1 1 b 1 b NULL NULL NULL NULL YES BTREE +t1 0 a 1 a A 0 NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE "Change the type implying data copy" "The non-unique index will be disabled" alter table t1 modify a bigint, disable keys; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 a 1 a A 0 NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE "Change again the type, but leave the indexes as_is" alter table t1 modify a int; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 a 1 a A 0 NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE "Try the same. When data is no copied on similar tables, this is noop" alter table t1 modify a int; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 a 1 a A 0 NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 0 NULL NULL YES BTREE drop table t1; create database mysqltest; create table t1 (c1 int); @@ -697,11 +697,11 @@ DROP TABLE IF EXISTS bug24219_2; CREATE TABLE bug24219 (a INT, INDEX(a)); SHOW INDEX FROM bug24219; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -bug24219 1 a 1 a A NULL NULL NULL YES BTREE +bug24219 1 a 1 a A 0 NULL NULL YES BTREE ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; SHOW INDEX FROM bug24219_2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE +bug24219_2 1 a 1 a A 0 NULL NULL YES BTREE DROP TABLE bug24219_2; create table t1 (mycol int(10) not null); alter table t1 alter column mycol set default 0; @@ -882,7 +882,7 @@ int_field int(10) unsigned NO MUL NULL char_field char(10) YES NULL SHOW INDEXES FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 int_field 1 int_field A NULL NULL NULL BTREE +t1 1 int_field 1 int_field A 0 NULL NULL BTREE INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); "Non-copy data change - new frm, but old data and index files" ALTER TABLE t1 diff --git a/mysql-test/suite/pbxt/r/analyze.result b/mysql-test/suite/pbxt/r/analyze.result index 4e769b6c5b5..d2e7fc29d3a 100644 --- a/mysql-test/suite/pbxt/r/analyze.result +++ b/mysql-test/suite/pbxt/r/analyze.result @@ -56,5 +56,5 @@ Table Op Msg_type Msg_text test.t1 analyze status OK show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 a 1 a A 5 NULL NULL YES BTREE drop table t1; diff --git a/mysql-test/suite/pbxt/r/auto_increment.result b/mysql-test/suite/pbxt/r/auto_increment.result index a945ecebbcc..51c272a4414 100644 --- a/mysql-test/suite/pbxt/r/auto_increment.result +++ b/mysql-test/suite/pbxt/r/auto_increment.result @@ -229,7 +229,8 @@ a b 204 7 delete from t1 where a=0; update t1 set a=NULL where b=6; -ERROR 23000: Column 'a' cannot be null +Warnings: +Warning 1048 Column 'a' cannot be null update t1 set a=300 where b=7; SET SQL_MODE=''; insert into t1(a,b)values(NULL,8); @@ -244,7 +245,7 @@ a b 1 1 200 2 201 4 -203 6 +0 6 300 7 301 8 400 9 @@ -260,7 +261,6 @@ a b 1 1 200 2 201 4 -203 6 300 7 301 8 400 9 @@ -271,20 +271,20 @@ a b 405 14 delete from t1 where a=0; update t1 set a=NULL where b=13; -ERROR 23000: Column 'a' cannot be null +Warnings: +Warning 1048 Column 'a' cannot be null update t1 set a=500 where b=14; select * from t1 order by b; a b 1 1 200 2 201 4 -203 6 300 7 301 8 400 9 401 10 402 11 -404 13 +0 13 500 14 drop table t1; create table t1 (a bigint); diff --git a/mysql-test/suite/pbxt/r/delete.result b/mysql-test/suite/pbxt/r/delete.result index 9d337a1ed34..eb4a4ae78d5 100644 --- a/mysql-test/suite/pbxt/r/delete.result +++ b/mysql-test/suite/pbxt/r/delete.result @@ -125,18 +125,19 @@ a b 0 11 2 12 delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); -Warnings: -Error 1242 Subquery returns more than 1 row -Error 1242 Subquery returns more than 1 row +ERROR 21000: Subquery returns more than 1 row select * from t11; a b 0 10 1 11 +2 12 select * from t12; a b 33 10 0 11 +2 12 insert into t11 values (2, 12); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); ERROR 21000: Subquery returns more than 1 row select * from t11; @@ -145,13 +146,12 @@ a b 1 11 2 12 delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); -Warnings: -Error 1242 Subquery returns more than 1 row -Error 1242 Subquery returns more than 1 row +ERROR 21000: Subquery returns more than 1 row select * from t11; a b 0 10 1 11 +2 12 drop table t11, t12, t2; create table t1 (a int, b int, unique key (a), key (b)); insert into t1 values (3, 3), (7, 7); diff --git a/mysql-test/suite/pbxt/r/distinct.result b/mysql-test/suite/pbxt/r/distinct.result index 7da52ea4bd1..c3e8342a6e1 100644 --- a/mysql-test/suite/pbxt/r/distinct.result +++ b/mysql-test/suite/pbxt/r/distinct.result @@ -174,8 +174,8 @@ INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary -1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index -1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer +1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index +1 SIMPLE t3 ref a a 5 test.t1.b 1 Using where; Using index SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 @@ -190,7 +190,7 @@ insert into t3 select * from t4; explain select distinct t1.a from t1,t3 where t1.a=t3.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary -1 SIMPLE t3 ref a a 5 test.t1.a 11 Using where; Using index; Distinct +1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a 1 @@ -212,7 +212,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 40 Using index +1 SIMPLE t3 index NULL a 5 NULL 2 Using index explain SELECT distinct a,b from t3 order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort diff --git a/mysql-test/suite/pbxt/r/func_group.result b/mysql-test/suite/pbxt/r/func_group.result index d5f804dee03..d1a0d09ad09 100644 --- a/mysql-test/suite/pbxt/r/func_group.result +++ b/mysql-test/suite/pbxt/r/func_group.result @@ -61,7 +61,7 @@ grp sum NULL NULL 1 7 2 20.25 -3 45.483163247594 +3 45.4831632475944 create table t2 (grp int, a bigint unsigned, c char(10)); insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; replace into t2 select grp, a, c from t1 limit 2,1; @@ -613,8 +613,8 @@ id select_type table type possible_keys key key_len ref rows Extra explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k1 k1 3 NULL 1 Using where; Using index -1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index; Using join buffer +1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index +1 SIMPLE t2 range k1 k1 3 NULL 1 Using where; Using index; Using join buffer explain select min(a4 - 0.01) from t1; id select_type table type possible_keys key key_len ref rows Extra @@ -1186,7 +1186,7 @@ std(s1/s2) 0.21325764 select std(o1/o2) from bug22555; std(o1/o2) -0.21325763586649 +0.213257635866493 select std(e1/e2) from bug22555; std(e1/e2) 0.21325764 @@ -1212,7 +1212,7 @@ round(std(s1/s2), 17) 0.21325763586649341 select std(o1/o2) from bug22555; std(o1/o2) -0.21325763586649 +0.213257635866493 select round(std(e1/e2), 17) from bug22555; round(std(e1/e2), 17) 0.21325763586649341 @@ -1237,7 +1237,7 @@ round(std(s1/s2), 17) 0.21325763586649341 select std(o1/o2) from bug22555; std(o1/o2) -0.21325763586649 +0.213257635866493 select round(std(e1/e2), 17) from bug22555; round(std(e1/e2), 17) 0.21325763586649341 diff --git a/mysql-test/suite/pbxt/r/func_math.result b/mysql-test/suite/pbxt/r/func_math.result index d1cdb7cb76e..d4ce452ef51 100644 --- a/mysql-test/suite/pbxt/r/func_math.result +++ b/mysql-test/suite/pbxt/r/func_math.result @@ -60,7 +60,7 @@ Warnings: Note 1003 select ln(exp(10)) AS `ln(exp(10))`,exp((ln(sqrt(10)) * 2)) AS `exp(ln(sqrt(10))*2)`,ln(-(1)) AS `ln(-1)`,ln(0) AS `ln(0)`,ln(NULL) AS `ln(NULL)` select log2(8),log2(15),log2(-2),log2(0),log2(NULL); log2(8) log2(15) log2(-2) log2(0) log2(NULL) -3 3.9068905956085 NULL NULL NULL +3 3.90689059560852 NULL NULL NULL explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -68,7 +68,7 @@ Warnings: Note 1003 select log2(8) AS `log2(8)`,log2(15) AS `log2(15)`,log2(-(2)) AS `log2(-2)`,log2(0) AS `log2(0)`,log2(NULL) AS `log2(NULL)` select log10(100),log10(18),log10(-4),log10(0),log10(NULL); log10(100) log10(18) log10(-4) log10(0) log10(NULL) -2 1.2552725051033 NULL NULL NULL +2 1.25527250510331 NULL NULL NULL explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -85,7 +85,7 @@ Note 1003 select pow(10,log10(10)) AS `pow(10,log10(10))`,pow(2,4) AS `power(2,4 set @@rand_seed1=10000000,@@rand_seed2=1000000; select rand(999999),rand(); rand(999999) rand() -0.014231365187309 0.028870999839968 +0.0142313651873091 0.028870999839968 explain extended select rand(999999),rand(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -101,7 +101,7 @@ Warnings: Note 1003 select pi() AS `pi()`,format(sin((pi() / 2)),6) AS `format(sin(pi()/2),6)`,format(cos((pi() / 2)),6) AS `format(cos(pi()/2),6)`,format(abs(tan(pi())),6) AS `format(abs(tan(pi())),6)`,format((1 / tan(1)),6) AS `format(cot(1),6)`,format(asin(1),6) AS `format(asin(1),6)`,format(acos(0),6) AS `format(acos(0),6)`,format(atan(1),6) AS `format(atan(1),6)` select degrees(pi()),radians(360); degrees(pi()) radians(360) -180 6.2831853071796 +180 6.28318530717959 select format(atan(-2, 2), 6); format(atan(-2, 2), 6) -0.785398 diff --git a/mysql-test/suite/pbxt/r/func_str.result b/mysql-test/suite/pbxt/r/func_str.result index 59d7b23f9df..b783abb466f 100644 --- a/mysql-test/suite/pbxt/r/func_str.result +++ b/mysql-test/suite/pbxt/r/func_str.result @@ -1327,10 +1327,10 @@ cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)) 20.06 select conv("18383815659218730760",10,10) + 0; conv("18383815659218730760",10,10) + 0 -1.8383815659219e+19 +1.83838156592187e+19 select "18383815659218730760" + 0; "18383815659218730760" + 0 -1.8383815659219e+19 +1.83838156592187e+19 CREATE TABLE t1 (code varchar(10)); INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13'); SELECT ASCII(code), code FROM t1 WHERE code='A12'; diff --git a/mysql-test/suite/pbxt/r/grant.result b/mysql-test/suite/pbxt/r/grant.result index 24a2b9b4d55..94f89f2fd87 100644 --- a/mysql-test/suite/pbxt/r/grant.result +++ b/mysql-test/suite/pbxt/r/grant.result @@ -457,7 +457,7 @@ Privilege Context Comment Alter Tables To alter the table Alter routine Functions,Procedures To alter or drop stored functions/procedures Create Databases,Tables,Indexes To create new databases and tables -Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE +Create routine Databases To use CREATE FUNCTION/PROCEDURE Create temporary tables Databases To use CREATE TEMPORARY TABLE Create view Tables To create new views Create user Server Admin To create new users diff --git a/mysql-test/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result index e8bd3d98834..a1e2ce15743 100644 --- a/mysql-test/suite/pbxt/r/group_min_max.result +++ b/mysql-test/suite/pbxt/r/group_min_max.result @@ -133,34 +133,34 @@ Table Op Msg_type Msg_text test.t3 analyze status OK explain select a1, min(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by explain select a1, max(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 65 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 65 NULL 129 Using index for group-by explain select a1, min(a2), max(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 # NULL # Using index for group-by explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by explain select min(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by explain select a2, min(c), max(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by select a1, min(a2) from t1 group by a1; a1 min(a2) a a @@ -293,13 +293,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1 Using where explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 2 Using where; Using index for group-by explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 2 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 65 NULL 3 Using where; Using index @@ -669,40 +669,40 @@ d l421 d p422 explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range NULL idx_t3_1 6 NULL 10 Using where; Using index for group-by +1 SIMPLE t3 range NULL idx_t3_1 6 NULL 193 Using where; Using index for group-by explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range NULL idx_t3_1 6 NULL 10 Using where; Using index for group-by +1 SIMPLE t3 range NULL idx_t3_1 6 NULL 193 Using where; Using index for group-by select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; a1 a2 b max(c) min(c) a a b h112 e112 @@ -804,22 +804,22 @@ b h212 e212 c h312 e312 explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; a1 a2 b min(c) a a NULL a777 @@ -849,49 +849,49 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by @@ -1364,29 +1364,29 @@ 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; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 PRIMARY t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by 2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 129 Using where; Using index for group-by 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 129 Using where; Using index for group-by 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 2 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 2 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1 Using where explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by @@ -1491,13 +1491,13 @@ select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') grou a1 a2 b min(c) explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1 Using where @@ -1554,13 +1554,13 @@ select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1 a1 a2 b explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 100.00 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 99.22 Using where; Using index for group-by Warnings: Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`b` = 'a') and (`test`.`t1`.`a2` >= 'b')) explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); @@ -1577,7 +1577,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 100.00 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 99.39 Using where; Using index for group-by Warnings: Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`b` = 'a') and (`test`.`t2`.`a2` >= 'b')) explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); @@ -1702,19 +1702,19 @@ c e d e explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1 Using where explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by; Using temporary; Using filesort +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by; Using temporary; Using filesort explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using index for group-by @@ -1846,7 +1846,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1 Using where explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; a1 a2 b concat(min(c), max(c)) a a a a111d111 @@ -1985,7 +1985,7 @@ c d explain select a1 from t1 where a2 = 'b' group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using where; Using index for group-by select a1 from t1 where a2 = 'b' group by a1; a1 a @@ -1994,7 +1994,7 @@ c d explain select distinct a1 from t1 where a2 = 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using where; Using index for group-by select distinct a1 from t1 where a2 = 'b'; a1 a @@ -2188,7 +2188,7 @@ INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by +1 SIMPLE t1 index NULL a 10 NULL 15 Using index FLUSH STATUS; SELECT max(b), a FROM t1 GROUP BY a; max(b) a @@ -2202,7 +2202,7 @@ Handler_read_key 0 Handler_read_next 0 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by +1 SIMPLE t1 index NULL a 10 NULL 15 Using index FLUSH STATUS; CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; SHOW STATUS LIKE 'handler_read__e%'; @@ -2235,14 +2235,14 @@ Handler_read_next 0 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL a 5 NULL 8 Using index for group-by -2 UNION t1 range NULL a 5 NULL 8 Using index for group-by +1 PRIMARY t1 index NULL a 10 NULL 15 Using index +2 UNION t1 index NULL a 10 NULL 15 Using index NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra @@ -2252,7 +2252,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra @@ -2261,21 +2261,21 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer range NULL a 5 NULL 8 Using index for group-by -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index 2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index -3 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +3 SUBQUERY t1 index NULL a 10 NULL 15 Using index CREATE TABLE t3 LIKE t1; FLUSH STATUS; INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; @@ -2312,7 +2312,7 @@ INSERT INTO t1 VALUES (4), (2), (1), (2), (2), (4), (1), (4); EXPLAIN SELECT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by +1 SIMPLE t1 index NULL idx 5 NULL 16 Using index SELECT DISTINCT(a) FROM t1; a 1 @@ -2320,7 +2320,7 @@ a 4 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by +1 SIMPLE t1 index NULL idx 5 NULL 16 Using index SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; a 1 @@ -2345,7 +2345,7 @@ CREATE INDEX break_it ON t1 (a, b); EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by +1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; a MIN(b) MAX(b) 1 1 3 @@ -2355,7 +2355,7 @@ a MIN(b) MAX(b) EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by; Using temporary; Using filesort +1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; a MIN(b) MAX(b) 4 1 3 diff --git a/mysql-test/suite/pbxt/r/join.result b/mysql-test/suite/pbxt/r/join.result index 3adcb4fd27a..a74ee3d3b35 100644 --- a/mysql-test/suite/pbxt/r/join.result +++ b/mysql-test/suite/pbxt/r/join.result @@ -774,7 +774,7 @@ insert into t3 select * from t2 where a < 800; explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a,b a 5 NULL 1 Using where -1 SIMPLE t3 ref b b 5 test.t2.b 11 Using where +1 SIMPLE t3 ref b b 5 test.t2.b 1 Using where drop table t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index a2a2bc6299f..92dc4f8acf2 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -851,7 +851,7 @@ ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer -1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 +1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 @@ -958,15 +958,15 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1008,14 +1008,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); EXPLAIN EXTENDED @@ -1055,17 +1055,17 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using where +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1102,21 +1102,21 @@ t0.b=t1.b AND (t9.a=1); a b a b a b a b a b a b a b a b a b a b 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 -1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 -1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 -1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 +1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 +1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 +1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 -1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 +1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 +1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 SELECT t2.a,t2.b FROM t2; @@ -1203,7 +1203,7 @@ EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index 1 SIMPLE t3 index c c 5 NULL 6 Using index -1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index +1 SIMPLE t2 ref b b 5 test.t3.c 1 Using index EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index @@ -1484,8 +1484,8 @@ explain select * from t1 left join on (t1.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ref a a 5 test.t1.a 11 -1 SIMPLE t3 ref a a 5 test.t2.a 11 +1 SIMPLE t2 ref a a 5 test.t1.a 1 +1 SIMPLE t3 ref a a 5 test.t2.a 1 drop table t1, t2, t3; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); diff --git a/mysql-test/suite/pbxt/r/key.result b/mysql-test/suite/pbxt/r/key.result index 0b964a84a4b..d727394f616 100644 --- a/mysql-test/suite/pbxt/r/key.result +++ b/mysql-test/suite/pbxt/r/key.result @@ -153,7 +153,7 @@ t1 0 PRIMARY 1 d A 0 NULL NULL BTREE t1 0 a 1 a A 0 NULL NULL BTREE t1 0 e 1 e A 0 NULL NULL BTREE t1 0 b 1 b A 0 NULL NULL YES BTREE -t1 1 c 1 c A NULL NULL NULL YES BTREE +t1 1 c 1 c A 0 NULL NULL YES BTREE drop table t1; CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, UNIQUE (c,i)); diff --git a/mysql-test/suite/pbxt/r/key_cache.result b/mysql-test/suite/pbxt/r/key_cache.result index 8d71c6ce930..5ff41bd29d7 100644 --- a/mysql-test/suite/pbxt/r/key_cache.result +++ b/mysql-test/suite/pbxt/r/key_cache.result @@ -122,7 +122,7 @@ i explain select count(*) from t1, t2 where t1.p = t2.i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using where; Using index +1 SIMPLE t2 ref k1 k1 5 test.t1.p 1 Using where; Using index select count(*) from t1, t2 where t1.p = t2.i; count(*) 3 @@ -257,8 +257,6 @@ test.t2 assign_to_keycache note The storage engine for the table doesn't support drop table t1,t2,t3; set global keycache2.key_buffer_size=0; set global keycache3.key_buffer_size=100; -Warnings: -Warning 1292 Truncated incorrect key_buffer_size value: '100' set global keycache3.key_buffer_size=0; create table t1 (mytext text, FULLTEXT (mytext)) engine=myisam; insert t1 values ('aaabbb'); diff --git a/mysql-test/suite/pbxt/r/key_diff.result b/mysql-test/suite/pbxt/r/key_diff.result index 9d26bee4557..33bedfcc39e 100644 --- a/mysql-test/suite/pbxt/r/key_diff.result +++ b/mysql-test/suite/pbxt/r/key_diff.result @@ -36,7 +36,7 @@ a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 5 -1 SIMPLE t2 ALL b NULL NULL NULL 5 Using where; Using join buffer +1 SIMPLE t2 ref b b 4 test.t1.a 1 Using where select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b A B a a diff --git a/mysql-test/suite/pbxt/r/lowercase_view.result b/mysql-test/suite/pbxt/r/lowercase_view.result index 0debf20108a..c37dc41c495 100644 --- a/mysql-test/suite/pbxt/r/lowercase_view.result +++ b/mysql-test/suite/pbxt/r/lowercase_view.result @@ -119,7 +119,7 @@ create table t1Aa (col1 int); create view v1Aa as select col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `AaA`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; select Aaa.col1 from t1Aa as AaA; col1 @@ -128,7 +128,7 @@ drop view v1AA; create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `AaA`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; drop table t1Aa; CREATE TABLE t1 (a int, b int); @@ -142,7 +142,7 @@ CREATE OR REPLACE VIEW v1 AS select X.a from t1 AS X group by X.b having (X.a = 1); SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `X`.`a` AS `a` from `t1` `X` group by `X`.`b` having (`X`.`a` = 1) latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `x`.`a` AS `a` from `t1` `x` group by `x`.`b` having (`x`.`a` = 1) latin1 latin1_swedish_ci SELECT * FROM v1; a DROP VIEW v1; diff --git a/mysql-test/suite/pbxt/r/mysqlshow.result b/mysql-test/suite/pbxt/r/mysqlshow.result index 0e1915dc47a..56b5d125ef3 100644 --- a/mysql-test/suite/pbxt/r/mysqlshow.result +++ b/mysql-test/suite/pbxt/r/mysqlshow.result @@ -107,7 +107,21 @@ Database: information_schema | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +| INNODB_BUFFER_POOL_PAGES | | PBXT_STATISTICS | +| INNODB_CMP | +| INNODB_RSEG | +| XTRADB_ENHANCEMENTS | +| INNODB_BUFFER_POOL_PAGES_INDEX | +| INNODB_INDEX_STATS | +| INNODB_TRX | +| INNODB_CMP_RESET | +| INNODB_LOCK_WAITS | +| INNODB_CMPMEM_RESET | +| INNODB_LOCKS | +| INNODB_CMPMEM | +| INNODB_TABLE_STATS | +| INNODB_BUFFER_POOL_PAGES_BLOB | +---------------------------------------+ Database: INFORMATION_SCHEMA +---------------------------------------+ @@ -141,7 +155,21 @@ Database: INFORMATION_SCHEMA | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +| INNODB_BUFFER_POOL_PAGES | | PBXT_STATISTICS | +| INNODB_CMP | +| INNODB_RSEG | +| XTRADB_ENHANCEMENTS | +| INNODB_BUFFER_POOL_PAGES_INDEX | +| INNODB_INDEX_STATS | +| INNODB_TRX | +| INNODB_CMP_RESET | +| INNODB_LOCK_WAITS | +| INNODB_CMPMEM_RESET | +| INNODB_LOCKS | +| INNODB_CMPMEM | +| INNODB_TABLE_STATS | +| INNODB_BUFFER_POOL_PAGES_BLOB | +---------------------------------------+ Wildcard: inf_rmation_schema +--------------------+ diff --git a/mysql-test/suite/pbxt/r/null.result b/mysql-test/suite/pbxt/r/null.result index 775d169a39a..036ba01ed1d 100644 --- a/mysql-test/suite/pbxt/r/null.result +++ b/mysql-test/suite/pbxt/r/null.result @@ -93,9 +93,11 @@ INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55"; Warnings: Warning 1265 Data truncated for column 'd' at row 1 UPDATE t1 SET d=1/NULL; -ERROR 23000: Column 'd' cannot be null +Warnings: +Warning 1265 Data truncated for column 'd' at row 1 UPDATE t1 SET d=NULL; -ERROR 23000: Column 'd' cannot be null +Warnings: +Warning 1048 Column 'd' cannot be null INSERT INTO t1 (a) values (null); ERROR 23000: Column 'a' cannot be null INSERT INTO t1 (a) values (1/null); @@ -130,7 +132,7 @@ Warning 1048 Column 'd' cannot be null Warning 1048 Column 'd' cannot be null select * from t1; a b c d - 0 0000-00-00 00:00:00 2003 + 0 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0 diff --git a/mysql-test/suite/pbxt/r/null_key.result b/mysql-test/suite/pbxt/r/null_key.result index c01e0337b32..753ebf31f1c 100644 --- a/mysql-test/suite/pbxt/r/null_key.result +++ b/mysql-test/suite/pbxt/r/null_key.result @@ -407,8 +407,8 @@ EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ref idx idx 5 test.t1.a 2 -1 SIMPLE t3 ref idx idx 5 test.t2.b 186 Using index +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index FLUSH STATUS ; SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; diff --git a/mysql-test/suite/pbxt/r/partition_pruning.result b/mysql-test/suite/pbxt/r/partition_pruning.result index f003f62c163..9938d352ff7 100644 --- a/mysql-test/suite/pbxt/r/partition_pruning.result +++ b/mysql-test/suite/pbxt/r/partition_pruning.result @@ -338,12 +338,12 @@ select * from t1 X, t1 Y where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE X p1,p2 ALL a,b NULL NULL NULL 2 Using where -1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 2 Using where +1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 1 Using where explain partitions select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE X p1,p2 ALL a NULL NULL NULL 4 Using where -1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2 +1 SIMPLE Y p1,p2 ref a a 4 test.X.a 1 drop table t1; create table t1 (a int) partition by hash(a) partitions 20; insert into t1 values (1),(2),(3); diff --git a/mysql-test/suite/pbxt/r/pbxt_bugs.result b/mysql-test/suite/pbxt/r/pbxt_bugs.result index 6ebb8459c75..a6db895d3d2 100644 --- a/mysql-test/suite/pbxt/r/pbxt_bugs.result +++ b/mysql-test/suite/pbxt/r/pbxt_bugs.result @@ -1218,3 +1218,59 @@ c1 c2 0 opq 1 jkl DROP TABLE t1; +create table parent (id int primary key); +create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id)); +insert into parent values (2), (3), (4); +insert into child values (3), (4); +delete ignore from parent; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`) +select * from parent; +id +2 +3 +4 +drop table child, parent; +create schema test378222; +use test378222; +create table t1 (id int primary key); +create table t2 (id int primary key); +alter table t1 add constraint foreign key (id) references t2 (id); +alter table t2 add constraint foreign key (id) references t1 (id); +drop schema test378222; +create schema test378222a; +create schema test378222b; +create table test378222a.t1 (id int primary key); +create table test378222b.t2 (id int primary key); +alter table test378222a.t1 add constraint foreign key (id) references test378222b.t2 (id); +alter table test378222b.t2 add constraint foreign key (id) references test378222a.t1 (id); +set foreign_key_checks = 1; +drop schema test378222a; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +drop schema test378222b; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +set foreign_key_checks = 0; +drop schema test378222a; +drop schema test378222b; +set foreign_key_checks = 1; +use test; +CREATE TABLE t1(c1 TINYINT AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyint(4) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=PBXT AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES(null); +INSERT INTO t1 VALUES(null); +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +c1 +10 +11 +12 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +c1 +1 +DROP TABLE t1; diff --git a/mysql-test/suite/pbxt/r/pbxt_ref_int.result b/mysql-test/suite/pbxt/r/pbxt_ref_int.result index cd86d122452..6f096f064ee 100644 --- a/mysql-test/suite/pbxt/r/pbxt_ref_int.result +++ b/mysql-test/suite/pbxt/r/pbxt_ref_int.result @@ -166,7 +166,7 @@ child CREATE TABLE `child` ( `parent_id` int(11) DEFAULT NULL, KEY `par_ind` (`parent_id`), KEY `child_ind` (`id`), - CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) + CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent` (`id`) ) ENGINE=PBXT DEFAULT CHARSET=latin1 drop index child_ind on child; show create table child; @@ -175,7 +175,7 @@ child CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, KEY `par_ind` (`parent_id`), - CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) + CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent` (`id`) ) ENGINE=PBXT DEFAULT CHARSET=latin1 alter table parent add column c1 varchar(40); insert child values(2000, 2); @@ -243,7 +243,7 @@ child CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, KEY `par_ind` (`parent_id`), - CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) + CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent` (`id`) ) ENGINE=PBXT DEFAULT CHARSET=latin1 alter table child add column c1 varchar(40); insert child values(400, 1, "asd"); @@ -284,7 +284,7 @@ child CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, KEY `par_ind` (`parent_id`), - CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE + CONSTRAINT `FOREIGN_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent` (`id`) ON DELETE CASCADE ) ENGINE=PBXT DEFAULT CHARSET=latin1 insert parent values(1); insert child values(100, 1); diff --git a/mysql-test/suite/pbxt/r/preload.result b/mysql-test/suite/pbxt/r/preload.result index 285b58e210e..cdd19dec861 100644 --- a/mysql-test/suite/pbxt/r/preload.result +++ b/mysql-test/suite/pbxt/r/preload.result @@ -144,7 +144,7 @@ Key_reads 0 load index into cache t3, t2 key (primary,b) ; Table Op Msg_type Msg_text test.t3 preload_keys Error Table 'test.t3' doesn't exist -test.t3 preload_keys error Corrupt +test.t3 preload_keys status Operation failed test.t2 preload_keys note The storage engine for the table doesn't support preload_keys show status like "key_read%"; Variable_name Value @@ -159,7 +159,7 @@ Key_reads 0 load index into cache t3 key (b), t2 key (c) ; Table Op Msg_type Msg_text test.t3 preload_keys Error Table 'test.t3' doesn't exist -test.t3 preload_keys error Corrupt +test.t3 preload_keys status Operation failed test.t2 preload_keys note The storage engine for the table doesn't support preload_keys show status like "key_read%"; Variable_name Value diff --git a/mysql-test/suite/pbxt/r/ps_1general.result b/mysql-test/suite/pbxt/r/ps_1general.result index 6584274ecf6..baa944eebab 100644 --- a/mysql-test/suite/pbxt/r/ps_1general.result +++ b/mysql-test/suite/pbxt/r/ps_1general.result @@ -289,11 +289,11 @@ prepare stmt4 from ' show index from t2 from test '; execute stmt4; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t2 0 PRIMARY 1 a A 0 NULL NULL BTREE -t2 1 t2_idx 1 b A NULL NULL NULL YES BTREE +t2 1 t2_idx 1 b A 0 NULL NULL YES BTREE prepare stmt4 from ' show table status from test like ''t2%'' '; execute stmt4; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t2 PBXT 10 Fixed 0 29 1 # 4096 0 NULL # # # latin1_swedish_ci NULL +t2 PBXT 10 Fixed 0 29 1024 # 4096 0 NULL # # # latin1_swedish_ci NULL prepare stmt4 from ' show table status from test like ''t9%'' '; execute stmt4; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment @@ -447,7 +447,7 @@ def type 253 10 3 Y 0 31 8 def possible_keys 253 4096 0 Y 0 31 8 def key 253 64 0 Y 0 31 8 def key_len 253 4096 0 Y 0 31 8 -def ref 253 1024 0 Y 0 31 8 +def ref 253 2048 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 14 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra @@ -463,7 +463,7 @@ def type 253 10 5 Y 0 31 8 def possible_keys 253 4096 7 Y 0 31 8 def key 253 64 7 Y 0 31 8 def key_len 253 4096 1 Y 0 31 8 -def ref 253 1024 0 Y 0 31 8 +def ref 253 2048 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 def Extra 253 255 27 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/suite/pbxt/r/range.result b/mysql-test/suite/pbxt/r/range.result index 758c2a064de..d9cf8ac704f 100644 --- a/mysql-test/suite/pbxt/r/range.result +++ b/mysql-test/suite/pbxt/r/range.result @@ -423,19 +423,19 @@ test.t2 analyze status OK explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uid_index uid_index 4 NULL 1 Using where -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uid_index uid_index 4 NULL 1 Using where -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uid_index uid_index 4 NULL 2 Using where -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uid_index uid_index 4 NULL 2 Using where -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id name uid id name uid 1001 A 1 1001 A 1 diff --git a/mysql-test/suite/pbxt/r/schema.result b/mysql-test/suite/pbxt/r/schema.result index 564fb3626df..4167119d932 100644 --- a/mysql-test/suite/pbxt/r/schema.result +++ b/mysql-test/suite/pbxt/r/schema.result @@ -3,11 +3,13 @@ create schema foo; show create schema foo; Database Create Database foo CREATE DATABASE `foo` /*!40100 DEFAULT CHARACTER SET latin1 */ +create table t1 (id int) engine=pbxt; show schemas; Database information_schema foo mtr mysql +pbxt test drop schema foo; diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result index 53127fb0dab..41137e9e8dd 100644 --- a/mysql-test/suite/pbxt/r/select.result +++ b/mysql-test/suite/pbxt/r/select.result @@ -604,15 +604,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort -1 SIMPLE t3 ref period period 4 test.t1.period 18 +1 SIMPLE t3 ref period period 4 test.t1.period 1 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 18 +1 SIMPLE t3 index period period 4 NULL 10 +1 SIMPLE t1 ref period period 4 test.t3.period 1 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index period period 4 NULL 1 -1 SIMPLE t3 ref period period 4 test.t1.period 18 +1 SIMPLE t1 index period period 4 NULL 10 +1 SIMPLE t3 ref period period 4 test.t1.period 1 select period from t1; period 9410 @@ -2095,7 +2095,7 @@ show keys from t2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +t2 1 fld3 1 fld3 A 1199 NULL NULL BTREE drop table t4, t3, t2, t1; DO 1; DO benchmark(100,1+1),1,1; @@ -2369,7 +2369,7 @@ insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref c,d d 5 const 1 Using where -1 SIMPLE t1 ref a a 5 test.t2.c 2 Using where +1 SIMPLE t1 ref a a 5 test.t2.c 1 Using where select * from t1 left join t2 on a=c where d in (4); a b c d 3 2 3 4 @@ -2377,7 +2377,7 @@ a b c d explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref c,d d 5 const 1 Using where -1 SIMPLE t1 ref a a 5 test.t2.c 2 Using where +1 SIMPLE t1 ref a a 5 test.t2.c 1 Using where select * from t1 left join t2 on a=c where d = 4; a b c d 3 2 3 4 @@ -2403,11 +2403,11 @@ INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 +1 SIMPLE t2 ref a a 23 test.t1.a 1 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 +1 SIMPLE t2 ref a a 23 test.t1.a 1 DROP TABLE t1, t2; CREATE TABLE t1 ( city char(30) ); INSERT INTO t1 VALUES ('London'); @@ -2792,26 +2792,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away select max(key1) from t1 where key1 <= 0.6158; max(key1) -0.61580002307892 +0.615800023078918 select max(key2) from t2 where key2 <= 1.6158; max(key2) -1.6158000230789 +1.61580002307892 select min(key1) from t1 where key1 >= 0.3762; min(key1) -0.37619999051094 +0.376199990510941 select min(key2) from t2 where key2 >= 1.3762; min(key2) -1.3761999607086 +1.37619996070862 select max(key1), min(key2) from t1, t2 where key1 <= 0.6158 and key2 >= 1.3762; max(key1) min(key2) -0.61580002307892 1.3761999607086 +0.615800023078918 1.37619996070862 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; max(key1) -0.61580002307892 +0.615800023078918 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; min(key1) -0.37619999051094 +0.376199990510941 DROP TABLE t1,t2; CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); INSERT INTO t1 VALUES (10); @@ -3454,7 +3454,7 @@ explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE A range PRIMARY PRIMARY 12 NULL 1 Using where -1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 11 +1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 1 drop table t1, t2; CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), @@ -3468,12 +3468,12 @@ EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,b b 5 NULL 1 Using where -1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where +1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,b PRIMARY 4 NULL 1 Using where -1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where +1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, diff --git a/mysql-test/suite/pbxt/r/select_safe.result b/mysql-test/suite/pbxt/r/select_safe.result index 468eb6cc5a9..ea0c2156d55 100644 --- a/mysql-test/suite/pbxt/r/select_safe.result +++ b/mysql-test/suite/pbxt/r/select_safe.result @@ -70,12 +70,12 @@ insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(nu explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL b NULL NULL NULL 21 -1 SIMPLE t2 ref b b 21 test.t1.b 2 Using where +1 SIMPLE t2 ref b b 21 test.t1.b 1 Using where set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL b NULL NULL NULL 21 -1 SIMPLE t2 ref b b 21 test.t1.b 2 Using where +1 SIMPLE t2 ref b b 21 test.t1.b 1 Using where SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; create table t1 (a int); diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 395770111ff..bae93f76b2c 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -1333,7 +1333,7 @@ a explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1343,7 +1343,7 @@ a explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1353,8 +1353,8 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) insert into t1 values (3,31); @@ -1370,7 +1370,7 @@ a explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) drop table t1, t2, t3; @@ -3546,7 +3546,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer -2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 2 Using where; Using index; Using filesort +2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 1 Using where; Using index; Using filesort SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4214,7 +4214,7 @@ CREATE INDEX I2 ON t1 (b); EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4224,14 +4224,14 @@ CREATE INDEX I2 ON t2 (b); EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; diff --git a/mysql-test/suite/pbxt/r/type_enum.result b/mysql-test/suite/pbxt/r/type_enum.result index 7cfb227e6c5..a84cf0d4edd 100644 --- a/mysql-test/suite/pbxt/r/type_enum.result +++ b/mysql-test/suite/pbxt/r/type_enum.result @@ -1776,8 +1776,14 @@ t1 CREATE TABLE `t1` ( `russian_deviant` enum('E','F','EF','F,E') NOT NULL DEFAULT 'E' ) ENGINE=PBXT DEFAULT CHARSET=latin1 drop table t1; +select @@SESSION.sql_mode; +@@SESSION.sql_mode + +select @@GLOBAL.sql_mode; +@@GLOBAL.sql_mode + create table t1(exhausting_charset enum('ABCDEFGHIJKLMNOPQRSTUVWXYZ',' !"','#$%&\'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~','xx\','yy\','zz')); -ERROR HY000: Can't create table 'test.t1' (errno: -1) +drop table t1; End of 5.1 tests diff --git a/mysql-test/suite/pbxt/r/type_ranges.result b/mysql-test/suite/pbxt/r/type_ranges.result index 34cabd64bcf..0674dcb4d28 100644 --- a/mysql-test/suite/pbxt/r/type_ranges.result +++ b/mysql-test/suite/pbxt/r/type_ranges.result @@ -70,19 +70,19 @@ flags set('one','two','tree') latin1_swedish_ci NO # show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE -t1 1 utiny 1 utiny A NULL NULL NULL BTREE -t1 1 tiny 1 tiny A NULL NULL NULL BTREE -t1 1 short 1 short A NULL NULL NULL BTREE -t1 1 any_name 1 medium A NULL NULL NULL BTREE -t1 1 longlong 1 longlong A NULL NULL NULL BTREE -t1 1 real_float 1 real_float A NULL NULL NULL BTREE -t1 1 ushort 1 ushort A NULL NULL NULL BTREE -t1 1 umedium 1 umedium A NULL NULL NULL BTREE -t1 1 ulong 1 ulong A NULL NULL NULL BTREE -t1 1 ulonglong 1 ulonglong A NULL NULL NULL BTREE -t1 1 ulonglong 2 ulong A NULL NULL NULL BTREE -t1 1 options 1 options A NULL NULL NULL BTREE -t1 1 options 2 flags A NULL NULL NULL BTREE +t1 1 utiny 1 utiny A 0 NULL NULL BTREE +t1 1 tiny 1 tiny A 0 NULL NULL BTREE +t1 1 short 1 short A 0 NULL NULL BTREE +t1 1 any_name 1 medium A 0 NULL NULL BTREE +t1 1 longlong 1 longlong A 0 NULL NULL BTREE +t1 1 real_float 1 real_float A 0 NULL NULL BTREE +t1 1 ushort 1 ushort A 0 NULL NULL BTREE +t1 1 umedium 1 umedium A 0 NULL NULL BTREE +t1 1 ulong 1 ulong A 0 NULL NULL BTREE +t1 1 ulonglong 1 ulonglong A 0 NULL NULL BTREE +t1 1 ulonglong 2 ulong A 0 NULL NULL BTREE +t1 1 options 1 options A 0 NULL NULL BTREE +t1 1 options 2 flags A 0 NULL NULL BTREE CREATE UNIQUE INDEX test on t1 ( auto ) ; CREATE INDEX test2 on t1 ( ulonglong,ulong) ; CREATE INDEX test3 on t1 ( medium ) ; diff --git a/mysql-test/suite/pbxt/r/type_timestamp.result b/mysql-test/suite/pbxt/r/type_timestamp.result index 2e5a6f46276..47d2a996afd 100644 --- a/mysql-test/suite/pbxt/r/type_timestamp.result +++ b/mysql-test/suite/pbxt/r/type_timestamp.result @@ -101,13 +101,13 @@ create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6), t8 timestamp(8), t10 timestamp(10), t12 timestamp(12), t14 timestamp(14)); Warnings: -Warning 1287 The syntax 'TIMESTAMP(2)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(4)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(6)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(8)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(10)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(12)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 5.2. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(2)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(4)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(6)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(8)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(10)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(12)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead insert t1 values (0,0,0,0,0,0,0), ("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", diff --git a/mysql-test/suite/pbxt/r/union.result b/mysql-test/suite/pbxt/r/union.result index 85fd7203488..04e5aaf6298 100644 --- a/mysql-test/suite/pbxt/r/union.result +++ b/mysql-test/suite/pbxt/r/union.result @@ -1301,12 +1301,14 @@ t3 CREATE TABLE `t3` ( `left(a,100000000)` longtext ) ENGINE=PBXT DEFAULT CHARSET=latin1 drop tables t1,t2,t3; +SELECT @tmp_max:= @@global.max_allowed_packet; +@tmp_max:= @@global.max_allowed_packet +1048576 +SET @@global.max_allowed_packet=25000000; CREATE TABLE t1 (a mediumtext); CREATE TABLE t2 (b varchar(20)); INSERT INTO t1 VALUES ('a'); CREATE TABLE t3 SELECT REPEAT(a,20000000) AS a FROM t1 UNION SELECT b FROM t2; -Warnings: -Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( @@ -1340,6 +1342,7 @@ t3 CREATE TABLE `t3` ( `a` varbinary(510) DEFAULT NULL ) ENGINE=PBXT DEFAULT CHARSET=latin1 DROP TABLES t1,t2,t3; +SET @@global.max_allowed_packet:= @tmp_max; create table t1 ( id int not null auto_increment, primary key (id), col1 int); insert into t1 (col1) values (2),(3),(4),(5),(6); select 99 union all select id from t1 order by 1; diff --git a/mysql-test/suite/pbxt/r/view_grant.result b/mysql-test/suite/pbxt/r/view_grant.result index 0847967eb87..5e2744c2933 100644 --- a/mysql-test/suite/pbxt/r/view_grant.result +++ b/mysql-test/suite/pbxt/r/view_grant.result @@ -28,7 +28,7 @@ create view v2 as select * from mysqltest.t2; ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci grant create view,drop,select on test.* to mysqltest_1@localhost; use test; alter view v1 as select * from mysqltest.t1; @@ -309,7 +309,7 @@ grant create view,select on test.* to mysqltest_1@localhost; create view v1 as select * from mysqltest.t1; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci revoke select on mysqltest.t1 from mysqltest_1@localhost; select * from v1; ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them diff --git a/mysql-test/suite/pbxt/t/auto_increment.test b/mysql-test/suite/pbxt/t/auto_increment.test index 1819e0cba1f..453d4d7658d 100644 --- a/mysql-test/suite/pbxt/t/auto_increment.test +++ b/mysql-test/suite/pbxt/t/auto_increment.test @@ -150,7 +150,6 @@ delete from t1 where a=0; update t1 set a=0 where b=5; select * from t1 order by b; delete from t1 where a=0; ---error 1048 update t1 set a=NULL where b=6; update t1 set a=300 where b=7; SET SQL_MODE=''; @@ -166,7 +165,6 @@ delete from t1 where a=0; update t1 set a=0 where b=12; select * from t1 order by b; delete from t1 where a=0; ---error 1048 update t1 set a=NULL where b=13; update t1 set a=500 where b=14; select * from t1 order by b; diff --git a/mysql-test/suite/pbxt/t/delete.test b/mysql-test/suite/pbxt/t/delete.test index d774ed50cd1..913e7df3d3a 100644 --- a/mysql-test/suite/pbxt/t/delete.test +++ b/mysql-test/suite/pbxt/t/delete.test @@ -120,13 +120,16 @@ select * from t2; delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; select * from t12; +--error 1242 delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; select * from t12; +--error 1062 insert into t11 values (2, 12); -- error 1242 delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; +--error 1242 delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; drop table t11, t12, t2; diff --git a/mysql-test/suite/pbxt/t/join_nested.test b/mysql-test/suite/pbxt/t/join_nested.test index 9a678b5c0f6..e90aa843042 100644 --- a/mysql-test/suite/pbxt/t/join_nested.test +++ b/mysql-test/suite/pbxt/t/join_nested.test @@ -546,6 +546,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, CREATE INDEX idx_b ON t8(b); +--sorted_result EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b @@ -585,6 +586,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); +--sorted_result EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b @@ -621,6 +623,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--sorted_result SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 diff --git a/mysql-test/suite/pbxt/t/lowercase_table_grant-master.opt b/mysql-test/suite/pbxt/t/lowercase_table_grant-master.opt new file mode 100644 index 00000000000..c718e2feb1b --- /dev/null +++ b/mysql-test/suite/pbxt/t/lowercase_table_grant-master.opt @@ -0,0 +1 @@ +--lower_case_table_names diff --git a/mysql-test/suite/pbxt/t/lowercase_table_qcache-master.opt b/mysql-test/suite/pbxt/t/lowercase_table_qcache-master.opt new file mode 100644 index 00000000000..c718e2feb1b --- /dev/null +++ b/mysql-test/suite/pbxt/t/lowercase_table_qcache-master.opt @@ -0,0 +1 @@ +--lower_case_table_names diff --git a/mysql-test/suite/pbxt/t/lowercase_view-master.opt b/mysql-test/suite/pbxt/t/lowercase_view-master.opt new file mode 100644 index 00000000000..62ab6dad1e0 --- /dev/null +++ b/mysql-test/suite/pbxt/t/lowercase_view-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/pbxt/t/null.test b/mysql-test/suite/pbxt/t/null.test index a52e9d85b6c..63281133388 100644 --- a/mysql-test/suite/pbxt/t/null.test +++ b/mysql-test/suite/pbxt/t/null.test @@ -61,9 +61,7 @@ drop table t1; # CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0); INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55"; ---error 1048 UPDATE t1 SET d=1/NULL; ---error 1048 UPDATE t1 SET d=NULL; --error 1048 INSERT INTO t1 (a) values (null); diff --git a/mysql-test/suite/pbxt/t/pbxt_bugs.test b/mysql-test/suite/pbxt/t/pbxt_bugs.test index 3976f44267c..b774e9e3034 100644 --- a/mysql-test/suite/pbxt/t/pbxt_bugs.test +++ b/mysql-test/suite/pbxt/t/pbxt_bugs.test @@ -926,7 +926,59 @@ LOAD DATA LOCAL INFILE 'suite/pbxt/t/load_unique_error1.inc' REPLACE INTO TABLE SELECT * FROM t1 ORDER BY c1; DROP TABLE t1; +create table parent (id int primary key); +create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id)); +insert into parent values (2), (3), (4); +insert into child values (3), (4); + +--error 1451 +delete ignore from parent; +--sorted_result +select * from parent; + +drop table child, parent; + +# bug 378222: Drop sakila causes error: Cannot delete or update a parent row: a foreign key constraint fails + +create schema test378222; +use test378222; +create table t1 (id int primary key); +create table t2 (id int primary key); +alter table t1 add constraint foreign key (id) references t2 (id); +alter table t2 add constraint foreign key (id) references t1 (id); +drop schema test378222; + +create schema test378222a; +create schema test378222b; +create table test378222a.t1 (id int primary key); +create table test378222b.t2 (id int primary key); +alter table test378222a.t1 add constraint foreign key (id) references test378222b.t2 (id); +alter table test378222b.t2 add constraint foreign key (id) references test378222a.t1 (id); +set foreign_key_checks = 1; +--error 1217 +drop schema test378222a; +--error 1217 +drop schema test378222b; +set foreign_key_checks = 0; +drop schema test378222a; +drop schema test378222b; +set foreign_key_checks = 1; +use test; + +# bug 369086: Incosistent/Incorrect Truncate behavior +CREATE TABLE t1(c1 TINYINT AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES(null); +INSERT INTO t1 VALUES(null); +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +DROP TABLE t1; + --disable_query_log + DROP TABLE t2, t5; drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/rename.test b/mysql-test/suite/pbxt/t/rename.test index 1cd02ef8a65..670c68f7965 100644 --- a/mysql-test/suite/pbxt/t/rename.test +++ b/mysql-test/suite/pbxt/t/rename.test @@ -63,7 +63,28 @@ connection con2; # Wait for the the tables to be renamed # i.e the query below succeds let $query= select * from t2, t4; -source include/wait_for_query_to_suceed.inc; +# source include/wait_for_query_to_suceed.inc; +let $counter= 100; + +disable_abort_on_error; +disable_query_log; +disable_result_log; +eval $query; +while ($mysql_errno) +{ + eval $query; + sleep 0.1; + dec $counter; + + if (!$counter) + { + die("Waited too long for query to suceed"); + } +} +enable_abort_on_error; +enable_query_log; +enable_result_log; + show tables; diff --git a/mysql-test/suite/pbxt/t/schema.test b/mysql-test/suite/pbxt/t/schema.test index a08d9b38935..41e5231f690 100644 --- a/mysql-test/suite/pbxt/t/schema.test +++ b/mysql-test/suite/pbxt/t/schema.test @@ -10,5 +10,12 @@ drop database if exists mysqltest1; create schema foo; show create schema foo; +# force PBXT schema to be created +create table t1 (id int) engine=pbxt; show schemas; drop schema foo; + +--disable_query_log +drop table if exists t1; +drop database pbxt; +--enable_query_log diff --git a/mysql-test/suite/pbxt/t/type_enum.test b/mysql-test/suite/pbxt/t/type_enum.test index 10beff8fdc3..ec562f377f6 100644 --- a/mysql-test/suite/pbxt/t/type_enum.test +++ b/mysql-test/suite/pbxt/t/type_enum.test @@ -153,12 +153,19 @@ create table t1(russian_deviant enum('E','F','EF','F,E') NOT NULL DEFAULT'E'); show create table t1; drop table t1; -# ER_WRONG_FIELD_TERMINATORS ---error 1005 +# the following create statement sometimes fails like it would if NO_BACKSLASH_ESCAPES sql mode was on, +# we check sql mode here +select @@SESSION.sql_mode; +select @@GLOBAL.sql_mode; + +## ER_WRONG_FIELD_TERMINATORS +#--error 1005 create table t1(exhausting_charset enum('ABCDEFGHIJKLMNOPQRSTUVWXYZ',' !"','#$%&\'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~','xx\','yy\','zz')); +drop table t1; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/udf-master.opt b/mysql-test/suite/pbxt/t/udf-master.opt new file mode 100644 index 00000000000..7d8786c156a --- /dev/null +++ b/mysql-test/suite/pbxt/t/udf-master.opt @@ -0,0 +1 @@ +$UDF_EXAMPLE_LIB_OPT diff --git a/mysql-test/suite/pbxt/t/union.test b/mysql-test/suite/pbxt/t/union.test index 07add11fe5f..02c73d4bb57 100644 --- a/mysql-test/suite/pbxt/t/union.test +++ b/mysql-test/suite/pbxt/t/union.test @@ -802,6 +802,10 @@ drop tables t1,t2,t3; # exceeds mediumtext maximum length # +SELECT @tmp_max:= @@global.max_allowed_packet; +SET @@global.max_allowed_packet=25000000; +# switching connection to allow the new max_allowed_packet take effect +--connect (newconn, localhost, root,,) CREATE TABLE t1 (a mediumtext); CREATE TABLE t2 (b varchar(20)); INSERT INTO t1 VALUES ('a'); @@ -823,6 +827,9 @@ INSERT INTO t1 VALUES ('a'); CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2; SHOW CREATE TABLE t3; DROP TABLES t1,t2,t3; +--connection default +SET @@global.max_allowed_packet:= @tmp_max; +--disconnect newconn # # Bug #10032 Bug in parsing UNION with ORDER BY when one node does not use FROM diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index dccaecef20a..0c4b9c6886c 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -25,15 +25,19 @@ let $merge_table_support= 1; --echo # we get another @@optimizer_switch user) --echo # +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='index_merge=off,index_merge_union=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='index_merge_union=on'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,index_merge_sort_union=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; --error ER_WRONG_VALUE_FOR_VAR @@ -71,17 +75,21 @@ set optimizer_switch='default,index_merge=on,index_merge=off,default'; set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch=default; # Check setting defaults for global vars +--replace_regex /,table_elimination=on// select @@global.optimizer_switch; set @@global.optimizer_switch=default; +--replace_regex /,table_elimination=on// select @@global.optimizer_switch; --echo # --echo # Check index_merge's @@optimizer_switch flags --echo # +--replace_regex /,table_elimination.on// select @@optimizer_switch; create table t0 (a int); @@ -182,6 +190,7 @@ set optimizer_switch='default,index_merge_union=off'; explain select * from t1 where a=10 and b=10 or c=10; set optimizer_switch=default; +--replace_regex /,table_elimination.on// show variables like 'optimizer_switch'; drop table t0, t1; diff --git a/mysql-test/t/log_slow.test b/mysql-test/t/log_slow.test new file mode 100644 index 00000000000..303d5bf8deb --- /dev/null +++ b/mysql-test/t/log_slow.test @@ -0,0 +1,42 @@ +# +# Testing of slow log query options +# + +select @@log_slow_filter; +select @@log_slow_rate_limit; +select @@log_slow_verbosity; +show variables like "log_slow%"; + +# Some simple test to set log_slow_filter +set @@log_slow_filter= "filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk,admin"; +select @@log_slow_filter; +set @@log_slow_filter="admin,admin"; +select @@log_slow_filter; +set @@log_slow_filter=7; +select @@log_slow_filter; + +# Test of wrong values +--error 1231 +set @@log_slow_filter= "filesort,impossible,impossible2,admin"; +--error 1231 +set @@log_slow_filter= "filesort, admin"; +--error 1231 +set @@log_slow_filter= 1<<31; +select @@log_slow_filter; + +# Some simple test to set log_slow_verbosity +set @@log_slow_verbosity= "query_plan,innodb"; +select @@log_slow_verbosity; +set @@log_slow_verbosity=1; +select @@log_slow_verbosity; + +# +# Check which fields are in slow_log table +# + +show fields from mysql.slow_log; + +# Reset used variables + +set @@log_slow_filter=default; +set @@log_slow_verbosity=default; diff --git a/mysql-test/t/mysql-bug41486.test b/mysql-test/t/mysql-bug41486.test index 6e014bca7d1..e7b0acc1935 100644 --- a/mysql-test/t/mysql-bug41486.test +++ b/mysql-test/t/mysql-bug41486.test @@ -27,7 +27,8 @@ connect (con1, localhost, root,,); CREATE TABLE t1(data LONGBLOB); INSERT INTO t1 SELECT REPEAT('1', 2*1024*1024); - +# The following is to remove the race between end of insert and start of MYSQL_DUMP: +SELECT COUNT(*) FROM t1; let $outfile= $MYSQLTEST_VARDIR/tmp/bug41486.sql; --error 0,1 remove_file $outfile; diff --git a/mysql-test/t/mysqld_option_err.test b/mysql-test/t/mysqld_option_err.test new file mode 100644 index 00000000000..2b35a2eb38e --- /dev/null +++ b/mysql-test/t/mysqld_option_err.test @@ -0,0 +1,47 @@ +# +# Test error checks on mysqld command line option parsing. +# +# Call mysqld with different invalid options, and check that it fails in each case. +# +# This means that a test failure results in mysqld starting up, which is only +# caught when the test case times out. This is not ideal, but I did not find an +# easy way to have the server shut down after a successful startup. +# + +--source include/not_embedded.inc + +# We have not run (and do not need) bootstrap of the server. We just +# give it a dummy data directory (for log files etc). + +mkdir $MYSQLTEST_VARDIR/tmp/mysqld_option_err; + + +--echo Test that unknown option is not silently ignored. +--error 2 +--exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --nonexistentoption >$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 + + +--echo Test bad binlog format. +--error 1 +--exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --log-bin --binlog-format=badformat >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 + + +--echo Test bad default storage engine. +--error 1 +--exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --default-storage-engine=nonexistentengine >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 + + +--echo Test non-numeric value passed to number option. +--error 1 +--exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --min-examined-row-limit=notanumber >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 + + +# Test for MBug#423035: error in parsing enum value for plugin +# variable in mysqld command-line option. +# See also Bug#32034. +--echo Test that bad value for plugin enum option is rejected correctly. +--error 7 +--exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables $EXAMPLE_PLUGIN_OPT --plugin-load=EXAMPLE=ha_example.so --plugin-example-enum-var=noexist >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 + + +--echo Done. diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test new file mode 100644 index 00000000000..642c5d51d62 --- /dev/null +++ b/mysql-test/t/table_elim.test @@ -0,0 +1,338 @@ +# +# Table elimination (MWL#17) tests +# +--disable_warnings +drop table if exists t0, t1, t2, t3; +drop view if exists v1, v2; +--enable_warnings + +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); +create table t0 as select * from t1; + +create table t2 (a int primary key, b int) + as select a, a as b from t1 where a in (1,2); + +create table t3 (a int primary key, b int) + as select a, a as b from t1 where a in (1,3); + +--echo # This will be eliminated: +explain select t1.a from t1 left join t2 on t2.a=t1.a; +explain extended select t1.a from t1 left join t2 on t2.a=t1.a; + +select t1.a from t1 left join t2 on t2.a=t1.a; + +--echo # This will not be eliminated as t2.b is in in select list: +explain select * from t1 left join t2 on t2.a=t1.a; + +--echo # This will not be eliminated as t2.b is in in order list: +explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b; + +--echo # This will not be eliminated as t2.b is in group list: +explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b; + +--echo # This will not be eliminated as t2.b is in the WHERE +explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null; + +--echo # Elimination of multiple tables: +explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; + +--echo # Elimination of multiple tables (2): +explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; + +--echo # Elimination when done within an outer join nest: +explain extended +select t0.* +from + t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and + t3.a=t1.a) on t0.a=t1.a; + +--echo # Elimination with aggregate functions +explain select count(*) from t1 left join t2 on t2.a=t1.a; +explain select count(1) from t1 left join t2 on t2.a=t1.a; +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a; + +--echo This must not use elimination: +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a; + +drop table t0, t1, t2, t3; + +# This will stand for elim_facts +create table t0 ( id integer, primary key (id)); + +# Attribute1, non-versioned +create table t1 ( + id integer, + attr1 integer, + primary key (id), + key (attr1) +); + +# Attribute2, time-versioned +create table t2 ( + id integer, + attr2 integer, + fromdate date, + primary key (id, fromdate), + key (attr2,fromdate) +); + +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0; + +insert into t1 select id, id from t0; +insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0; +insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0; + +create view v1 as +select + F.id, A1.attr1, A2.attr2 +from + t0 F + left join t1 A1 on A1.id=F.id + left join t2 A2 on A2.id=F.id and + A2.fromdate=(select MAX(fromdate) from + t2 where id=A2.id); +create view v2 as +select + F.id, A1.attr1, A2.attr2 +from + t0 F + left join t1 A1 on A1.id=F.id + left join t2 A2 on A2.id=F.id and + A2.fromdate=(select MAX(fromdate) from + t2 where id=F.id); + +--echo This should use one table: +explain select id from v1 where id=2; +--echo This should use one table: +explain extended select id from v1 where id in (1,2,3,4); +--echo This should use facts and A1 tables: +explain extended select id from v1 where attr1 between 12 and 14; +--echo This should use facts, A2 and its subquery: +explain extended select id from v1 where attr2 between 12 and 14; + +# Repeat for v2: + +--echo This should use one table: +explain select id from v2 where id=2; +--echo This should use one table: +explain extended select id from v2 where id in (1,2,3,4); +--echo This should use facts and A1 tables: +explain extended select id from v2 where attr1 between 12 and 14; +--echo This should use facts, A2 and its subquery: +explain extended select id from v2 where attr2 between 12 and 14; + +drop view v1, v2; +drop table t0, t1, t2; + +# +# Tests for the code that uses t.keypartX=func(t.keypartY) equalities to +# make table elimination inferences +# +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); + +create table t2 (pk1 int, pk2 int, pk3 int, col int, primary key(pk1, pk2, pk3)); +insert into t2 select a,a,a,a from t1; + +--echo This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and + t2.pk2=t2.pk1+1 and + t2.pk3=t2.pk2+1; + +--echo This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and + t2.pk3=t2.pk1+1 and + t2.pk2=t2.pk3+1; + +--echo This must use both: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and + t2.pk3=t2.pk1+1 and + t2.pk2=t2.pk3+t2.col; + +--echo This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk2=t1.a and + t2.pk1=t2.pk2+1 and + t2.pk3=t2.pk1; + +drop table t1, t2; +# +# Check that equality propagation is taken into account +# +create table t1 (pk int primary key, col int); +insert into t1 values (1,1),(2,2); + +create table t2 like t1; +insert into t2 select * from t1; + +create table t3 like t1; +insert into t3 select * from t1; + +explain +select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; + +explain +select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; + +explain select t1.* +from + t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) + on t2.col=t1.col or t2.col=t1.col; + +explain select t1.*, t2.* +from + t1 left join + (t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) + on t2.pk=t1.col or t2.pk=t1.col; + +drop table t1, t2, t3; + +--echo # +--echo # Check things that look like functional dependencies but really are not +--echo # + +create table t1 (a char(10) character set latin1 collate latin1_general_ci primary key); +insert into t1 values ('foo'); +insert into t1 values ('bar'); + +create table t2 (a char(10) character set latin1 collate latin1_general_cs primary key); +insert into t2 values ('foo'); +insert into t2 values ('FOO'); + +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; + +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; +drop table t1,t2; + +create table t1 (a int primary key); +insert into t1 values (1),(2); +create table t2 (a char(10) primary key); +insert into t2 values ('1'),('1.0'); +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=1; +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a; + +drop table t1, t2; +# partial unique keys do not work at the moment, although they are able to +# provide one-match guarantees: +create table t1 (a char(10) primary key); +insert into t1 values ('foo'),('bar'); + +create table t2 (a char(10), unique key(a(2))); +insert into t2 values ('foo'),('bar'); + +explain select t1.* from t1 left join t2 on t2.a=t1.a; + +drop table t1, t2; + +--echo # +--echo # check UPDATE/DELETE that look like they could be eliminated +--echo # +create table t1 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); + +create table t2 like t1; +insert into t2 select * from t1; +update t1 left join t2 using (a) set t2.a=t2.a+100; +select * from t1; +select * from t2; + +delete from t2; +insert into t2 select * from t1; + +delete t2 from t1 left join t2 using (a); +select * from t1; +select * from t2; +drop table t1, t2; + +--echo # +--echo # Tests with various edge-case ON expressions +--echo # +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); + +create table t2 (pk int primary key, b int) + as select a as pk, a as b from t1 where a in (1,2); + +create table t3 (pk1 int, pk2 int, b int, unique(pk1,pk2)); +insert into t3 select a as pk1, a as pk2, a as b from t1 where a in (1,3); + +explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b; +explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; +explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; + +explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; +explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; +explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; + +explain select t1.a from t1 left join t2 on t2.pk in (10); +explain select t1.a from t1 left join t2 on t2.pk in (t1.a); + +explain select t1.a from t1 left join t2 on TRUE; + +explain select t1.a from t1 left join t3 on t3.pk1=t1.a and t3.pk2 IS NULL; + +drop table t1,t2,t3; + +--echo # +--echo # Multi-equality tests +--echo # +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); + +create table t2 (pk int primary key, b int, c int); +insert into t2 select a,a,a from t1 where a in (1,2); + +explain +select t1.* +from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b +where t1.d=1; + +explain +select t1.* +from + t1 + left join + t2 + on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or + (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) +where t1.d=1; + +--echo #This can't be eliminated: +explain +select t1.* +from + t1 + left join + t2 + on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or + (t2.pk=t2.c and t1.a=t1.b and t2.c=t1.b) +where t1.d=1; + +explain +select t1.* +from + t1 + left join + t2 + on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or + (t2.pk=t2.c and t2.c=t1.b) +; + +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 4; + +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 3; + +explain +select t1.* +from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); + +drop table t1, t2; diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index efc40f8b942..57eee78bdaa 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -704,6 +704,75 @@ fun:inet_ntoa } + +# +# Some problem inside glibc on Ubuntu 9.04, x86 (but not amd64): +# +# ==5985== 19 bytes in 1 blocks are still reachable in loss record 1 of 6 +# ==5985== at 0x7AF3FDE: malloc (vg_replace_malloc.c:207) +# ... 11,12, or 13 functions w/o symbols ... +# ==5985== by 0x8717185: nptl_pthread_exit_hack_handler (my_thr_init.c:55) +# +# Since valgrind 3.3.0 doesn't support '...' multi-function pattern, using +# multiple suppressions: +# +{ + Mem loss inside nptl_pthread_exit_hack_handler + Memcheck:Leak + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:nptl_pthread_exit_hack_handler +} + +{ + Mem loss inside nptl_pthread_exit_hack_handler + Memcheck:Leak + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:nptl_pthread_exit_hack_handler +} + +{ + Mem loss inside nptl_pthread_exit_hack_handler + Memcheck:Leak + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:* + fun:nptl_pthread_exit_hack_handler +} + # # BUG#45630 # Suppress valgrind failures within nptl_pthread_exit_hack_handler on Ubuntu 9.04, x86 (but not amd64) |