diff options
Diffstat (limited to 'mysql-test')
113 files changed, 4127 insertions, 182 deletions
diff --git a/mysql-test/include/innodb_page_size.combinations b/mysql-test/include/innodb_page_size.combinations new file mode 100644 index 00000000000..a9b7c7ce14d --- /dev/null +++ b/mysql-test/include/innodb_page_size.combinations @@ -0,0 +1,8 @@ +[16k] +innodb-page-size=16K + +[8k] +innodb-page-size=8K + +[4k] +innodb-page-size=4K diff --git a/mysql-test/include/innodb_page_size.inc b/mysql-test/include/innodb_page_size.inc new file mode 100644 index 00000000000..754b640da86 --- /dev/null +++ b/mysql-test/include/innodb_page_size.inc @@ -0,0 +1,4 @@ +# The goal of including this file is to enable innodb_page_size combinations +# (see include/innodb_page_size.combinations) + +--source include/have_innodb.inc diff --git a/mysql-test/include/innodb_page_size_small.combinations b/mysql-test/include/innodb_page_size_small.combinations new file mode 100644 index 00000000000..a9b7c7ce14d --- /dev/null +++ b/mysql-test/include/innodb_page_size_small.combinations @@ -0,0 +1,8 @@ +[16k] +innodb-page-size=16K + +[8k] +innodb-page-size=8K + +[4k] +innodb-page-size=4K diff --git a/mysql-test/include/innodb_page_size_small.inc b/mysql-test/include/innodb_page_size_small.inc new file mode 100644 index 00000000000..754b640da86 --- /dev/null +++ b/mysql-test/include/innodb_page_size_small.inc @@ -0,0 +1,4 @@ +# The goal of including this file is to enable innodb_page_size combinations +# (see include/innodb_page_size.combinations) + +--source include/have_innodb.inc diff --git a/mysql-test/include/wait_for_slave_sql_error_and_skip.inc b/mysql-test/include/wait_for_slave_sql_error_and_skip.inc index 9246c1839af..d68faaf04e7 100644 --- a/mysql-test/include/wait_for_slave_sql_error_and_skip.inc +++ b/mysql-test/include/wait_for_slave_sql_error_and_skip.inc @@ -66,6 +66,13 @@ if (!$slave_skip_counter) { } source include/start_slave.inc; +# start_slave.inc returns when Slave_SQL_Running=Yes. But the slave +# thread sets it before clearing Last_SQL_Errno. So we have to wait +# for Last_SQL_Errno=0 separately. + +let $slave_param= Last_SQL_Errno; +let $slave_param_value= 0; +source include/wait_for_slave_param.inc; --let $include_filename= wait_for_slave_sql_error_and_skip.inc [errno=$slave_sql_errno] --source include/end_include_file.inc diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 3cc5126f4b3..ed7dd020e5b 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2,7 +2,7 @@ # -*- cperl -*- # Copyright (c) 2004, 2014, Oracle and/or its affiliates. -# Copyright (c) 2009, 2014, Monty Program Ab +# Copyright (c) 2009, 2017, MariaDB Corporation # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -1296,10 +1296,6 @@ sub command_line_setup { fix_vs_config_dir(); - # Respect MTR_BINDIR variable, which is typically set in to the - # build directory in out-of-source builds. - $bindir=$ENV{MTR_BINDIR}||$basedir; - # Look for the client binaries directory if ($path_client_bindir) { @@ -3222,15 +3218,26 @@ sub mysql_server_start($) { } my $mysqld_basedir= $mysqld->value('basedir'); + my $extra_opts= get_extra_opts($mysqld, $tinfo); + if ( $basedir eq $mysqld_basedir ) { if (! $opt_start_dirty) # If dirty, keep possibly grown system db { - # Copy datadir from installed system db - my $path= ($opt_parallel == 1) ? "$opt_vardir" : "$opt_vardir/.."; - my $install_db= "$path/install.db"; - copytree($install_db, $datadir) if -d $install_db; - mtr_error("Failed to copy system db to '$datadir'") unless -d $datadir; + # Some InnoDB options are incompatible with the default bootstrap. + # If they are used, re-bootstrap + if ( $extra_opts and + "@$extra_opts" =~ /--innodb[-_](?:page[-_]size|checksum[-_]algorithm|undo[-_]tablespaces|log[-_]group[-_]home[-_]dir|data[-_]home[-_]dir)/ ) + { + mysql_install_db($mysqld, undef, $extra_opts); + } + else { + # Copy datadir from installed system db + my $path= ($opt_parallel == 1) ? "$opt_vardir" : "$opt_vardir/.."; + my $install_db= "$path/install.db"; + copytree($install_db, $datadir) if -d $install_db; + mtr_error("Failed to copy system db to '$datadir'") unless -d $datadir; + } } } else @@ -3269,7 +3276,6 @@ sub mysql_server_start($) { if (!$opt_embedded_server) { - my $extra_opts= get_extra_opts($mysqld, $tinfo); mysqld_start($mysqld,$extra_opts); # Save this test case information, so next can examine it @@ -3493,7 +3499,7 @@ sub default_mysqld { sub mysql_install_db { - my ($mysqld, $datadir)= @_; + my ($mysqld, $datadir, $extra_opts)= @_; my $install_datadir= $datadir || $mysqld->value('datadir'); my $install_basedir= $mysqld->value('basedir'); @@ -3534,6 +3540,13 @@ sub mysql_install_db { mtr_add_arg($args, $extra_opt); } } + # InnoDB options can come not only from the command line, but also + # from option files or combinations + foreach my $extra_opt ( @$extra_opts ) { + if ($extra_opt =~ /--innodb/) { + mtr_add_arg($args, $extra_opt); + } + } # If DISABLE_GRANT_OPTIONS is defined when the server is compiled (e.g., # configure --disable-grant-options), mysqld will not recognize the diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result index f416c53f42c..d1fc4a3bd93 100644 --- a/mysql-test/r/alter_table_online.result +++ b/mysql-test/r/alter_table_online.result @@ -73,3 +73,114 @@ drop table t1; create table t1 (a int) partition by hash(a) partitions 2; alter online table t1 modify a int comment 'test'; drop table t1; +# +# MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +# +CREATE TABLE t1 (a BINARY(10)); +ALTER TABLE t1 MODIFY a BINARY(10), ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a VARBINARY(10)); +ALTER TABLE t1 MODIFY a VARBINARY(10), ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a TINYBLOB); +ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMBLOB); +ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a BLOB); +ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB); +ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(10)); +ALTER TABLE t1 MODIFY a CHAR(10), ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10)); +ALTER TABLE t1 MODIFY a VARCHAR(10), ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT); +ALTER TABLE t1 MODIFY a TINYTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT); +ALTER TABLE t1 MODIFY a MEDIUMTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a TEXT); +ALTER TABLE t1 MODIFY a TEXT, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT); +ALTER TABLE t1 MODIFY a LONGTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(10)); +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10)); +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT); +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT); +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a TEXT); +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT); +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(10) COLLATE latin1_bin); +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin); +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COLLATE latin1_bin); +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_bin); +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COLLATE latin1_bin); +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COLLATE latin1_bin); +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(10) COLLATE latin1_general_ci); +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_general_ci); +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a TINYTEXT COLLATE latin1_general_ci); +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_general_ci); +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COLLATE latin1_general_ci); +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a LONGTEXT COLLATE latin1_general_ci); +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; diff --git a/mysql-test/r/binary_to_hex.result b/mysql-test/r/binary_to_hex.result new file mode 100644 index 00000000000..a6b68834da8 --- /dev/null +++ b/mysql-test/r/binary_to_hex.result @@ -0,0 +1,117 @@ +USE test; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (c1 TINYBLOB, +c2 BLOB, +c3 MEDIUMBLOB, +c4 LONGBLOB, +c5 TEXT, +c6 BIT(1), +c7 CHAR, +c8 VARCHAR(10), +c9 GEOMETRY) CHARACTER SET = binary; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyblob, + `c2` blob, + `c3` mediumblob, + `c4` longblob, + `c5` blob, + `c6` bit(1) DEFAULT NULL, + `c7` binary(1) DEFAULT NULL, + `c8` varbinary(10) DEFAULT NULL, + `c9` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=binary +INSERT INTO t1 VALUES ('tinyblob-text readable', 'blob-text readable', +'mediumblob-text readable', 'longblob-text readable', +'text readable', b'1', 'c', 'variable', +POINT(1, 1)); +CREATE TABLE t2(id int, `col1` binary(10),`col2` blob); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) DEFAULT NULL, + `col1` binary(10) DEFAULT NULL, + `col2` blob +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t2 VALUES (1, X'AB1234', X'123ABC'), (2, X'DE1234', X'123DEF'); +#Print the table contents when binary-as-hex option is off. +SELECT * FROM t1; +c1 c2 c3 c4 c5 c6 c7 c8 c9 +tinyblob-text readable blob-text readable mediumblob-text readable longblob-text readable text readable # c variable # +SELECT * FROM t2; +id col1 col2 +1 # # +2 # # +#Print the table contents after turning on the binary-as-hex option + +#Print the table contents in tab format + +c1 c2 c3 c4 c5 c6 c7 c8 c9 +0x74696E79626C6F622D74657874207265616461626C65 0x626C6F622D74657874207265616461626C65 0x6D656469756D626C6F622D74657874207265616461626C65 0x6C6F6E67626C6F622D74657874207265616461626C65 0x74657874207265616461626C65 0x01 0x63 0x7661726961626C65 0x000000000101000000000000000000F03F000000000000F03F +id col1 col2 +1 0xAB123400000000000000 0x123ABC +2 0xDE123400000000000000 0x123DEF + +#Print the table contents in table format + ++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+ +| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | ++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+ +| 0x74696E79626C6F622D74657874207265616461626C65 | 0x626C6F622D74657874207265616461626C65 | 0x6D656469756D626C6F622D74657874207265616461626C65 | 0x6C6F6E67626C6F622D74657874207265616461626C65 | 0x74657874207265616461626C65 | 0x01 | 0x63 | 0x7661726961626C65 | 0x000000000101000000000000000000F03F000000000000F03F | ++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+ ++------+------------------------+------------+ +| id | col1 | col2 | ++------+------------------------+------------+ +| 1 | 0xAB123400000000000000 | 0x123ABC | ++------+------------------------+------------+ + +#Print the table contents vertically + +*************************** 1. row *************************** +c1: 0x74696E79626C6F622D74657874207265616461626C65 +c2: 0x626C6F622D74657874207265616461626C65 +c3: 0x6D656469756D626C6F622D74657874207265616461626C65 +c4: 0x6C6F6E67626C6F622D74657874207265616461626C65 +c5: 0x74657874207265616461626C65 +c6: 0x01 +c7: 0x63 +c8: 0x7661726961626C65 +c9: 0x000000000101000000000000000000F03F000000000000F03F + +#Print the table contents in xml format + +<?xml version="1.0"?> + +<resultset statement="SELECT * FROM t1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + <row> + <field name="c1">0x74696E79626C6F622D74657874207265616461626C65</field> + <field name="c2">0x626C6F622D74657874207265616461626C65</field> + <field name="c3">0x6D656469756D626C6F622D74657874207265616461626C65</field> + <field name="c4">0x6C6F6E67626C6F622D74657874207265616461626C65</field> + <field name="c5">0x74657874207265616461626C65</field> + <field name="c6">0x01</field> + <field name="c7">0x63</field> + <field name="c8">0x7661726961626C65</field> + <field name="c9">0x000000000101000000000000000000F03F000000000000F03F</field> + </row> +</resultset> +<?xml version="1.0"?> + +<resultset statement="SELECT * FROM t2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + <row> + <field name="id">1</field> + <field name="col1">0xAB123400000000000000</field> + <field name="col2">0x123ABC</field> + </row> + + <row> + <field name="id">2</field> + <field name="col1">0xDE123400000000000000</field> + <field name="col2">0x123DEF</field> + </row> +</resultset> + +#Print the table contents in html format + +<TABLE BORDER=1><TR><TH>c1</TH><TH>c2</TH><TH>c3</TH><TH>c4</TH><TH>c5</TH><TH>c6</TH><TH>c7</TH><TH>c8</TH><TH>c9</TH></TR><TR><TD>0x74696E79626C6F622D74657874207265616461626C65</TD><TD>0x626C6F622D74657874207265616461626C65</TD><TD>0x6D656469756D626C6F622D74657874207265616461626C65</TD><TD>0x6C6F6E67626C6F622D74657874207265616461626C65</TD><TD>0x74657874207265616461626C65</TD><TD>0x01</TD><TD>0x63</TD><TD>0x7661726961626C65</TD><TD>0x000000000101000000000000000000F03F000000000000F03F</TD></TR></TABLE><TABLE BORDER=1><TR><TH>id</TH><TH>col1</TH><TH>col2</TH></TR><TR><TD>1</TD><TD>0xAB123400000000000000</TD><TD>0x123ABC</TD></TR><TR><TD>2</TD><TD>0xDE123400000000000000</TD><TD>0x123DEF</TD></TR></TABLE>DROP TABLE t1, t2; diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result index 4a26d0f19dd..5d92184f191 100644 --- a/mysql-test/r/contributors.result +++ b/mysql-test/r/contributors.result @@ -2,9 +2,11 @@ SHOW CONTRIBUTORS; Name Location Comment Booking.com https://www.booking.com Founding member, Platinum Sponsor of the MariaDB Foundation Alibaba Cloud https://intl.aliyun.com Platinum Sponsor of the MariaDB Foundation +Tencent Cloud https://cloud.tencent.com Platinum Sponsor of the MariaDB Foundation MariaDB Corporation https://mariadb.com Founding member, Gold Sponsor of the MariaDB Foundation Visma https://visma.com Gold Sponsor of the MariaDB Foundation DBS https://dbs.com Gold Sponsor of the MariaDB Foundation +IBM https://www.ibm.com Gold Sponsor of the MariaDB Foundation Nexedi https://www.nexedi.com Silver Sponsor of the MariaDB Foundation Acronis http://www.acronis.com Silver Sponsor of the MariaDB Foundation Auttomattic https://automattic.com Bronze Sponsor of the MariaDB Foundation diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result index 3b65dd0e608..d55a232c715 100644 --- a/mysql-test/r/count_distinct.result +++ b/mysql-test/r/count_distinct.result @@ -94,3 +94,15 @@ count(distinct i) 2 drop table t1; drop view v1; +create table t1 (user_id char(64) character set utf8); +insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17); +set @@tmp_table_size = 1024; +select count(distinct user_id) from t1; +count(distinct user_id) +17 +alter table t1 modify user_id char(128) character set utf8; +select count(distinct user_id) from t1; +count(distinct user_id) +17 +drop table t1; +set @@tmp_table_size = default; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 843b49c1f75..8c69745b12c 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -5552,5 +5552,22 @@ SELECT 'a','aa'; a aa a aa # +# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +# +SET NAMES utf8, character_set_connection=ucs2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_utf8'derived_merge=on'; +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SET NAMES utf8, character_set_connection=ucs2; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; +c2 +616263646566676869-616263646566676869 +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; +c2 +YWJjZGVmZ2hp-YWJjZGVmZ2hp +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# # End of 10.0 tests # diff --git a/mysql-test/r/engine_error_in_alter-8453.result b/mysql-test/r/engine_error_in_alter-8453.result new file mode 100644 index 00000000000..c5a3375f33c --- /dev/null +++ b/mysql-test/r/engine_error_in_alter-8453.result @@ -0,0 +1,6 @@ +create table t1 (a int, b int); +set debug_dbug='+d,external_lock_failure'; +alter table t1 add column c int; +ERROR HY000: Got error 168 'KABOOM!' from MyISAM +set debug_dbug=''; +drop table t1; diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result index 925158ab129..b87ee7bfc52 100644 --- a/mysql-test/r/func_concat.result +++ b/mysql-test/r/func_concat.result @@ -149,3 +149,116 @@ CALL p1(); ########################################40100.000 DROP PROCEDURE p1; # End of 5.1 tests +# +# Start of 10.0 tests +# +# +# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('1234567'); +SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1, +CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2 +FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +c1 c2 +123567 123---567 +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +c2 +1234567-1234567 +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('1234567'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +c2 +1234567-1234567 +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT REVERSE(t) t2 FROM t1) sub; +c2 +7654321-7654321 +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SOUNDEX(t) t2 FROM t1) sub; +c2 +- +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; +c2 +MTIzNDU2Nw==-MTIzNDU2Nw== +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT WEIGHT_STRING(t) t2 FROM t1) sub; +c2 +1234567-1234567 +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; +c2 +31323334353637-31323334353637 +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT QUOTE(t) t2 FROM t1) sub; +c2 +'1234567'-'1234567' +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES(TO_BASE64('abcdefghi')); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FROM_BASE64(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES(HEX('abcdefghi')); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UNHEX(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(30) CHARSET latin1); +INSERT INTO t1 VALUES('test'); +SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +c2 +16 +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +c2 +33 +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +c2 +34 +SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +c2 +4 +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +c2 +9 +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +c2 +10 +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(64) CHARSET latin1); +INSERT INTO t1 VALUES('123456789'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MD5(t) t2 FROM t1) sub; +c2 +25f9e794323b453885f5181f1b624d0b-25f9e794323b453885f5181f1b624d0b +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FORMAT(t,2) t2 FROM t1) sub; +c2 +123,456,789.00-123,456,789.00 +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT INSERT(t,3,4,'xxx') t2 FROM t1) sub; +c2 +abxxxghi-abxxxghi +DROP TABLE t1; +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LEFT(t,10) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RIGHT(t,10) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SUBSTR(t,1,10) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LTRIM(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RTRIM(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TRIM(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result index 1eda56ac114..707e767ddf5 100644 --- a/mysql-test/r/func_crypt.result +++ b/mysql-test/r/func_crypt.result @@ -106,3 +106,21 @@ OLD_PASSWORD(c1) PASSWORD(c1) 77023ffe214c04ff *82E58A2C08AAFE72C8EB523069CD8ADB33F78F58 DROP TABLE t1; End of 5.0 tests +# +# Start of 10.0 tests +# +# +# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT ENCRYPT(t,'aa') t2 FROM t1) sub; +c2 +aaHHlPHAM4sjs-aaHHlPHAM4sjs +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# +# End of 10.0 tests +# diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result index af7230d2ad0..4089966a7d7 100644 --- a/mysql-test/r/func_regexp_pcre.result +++ b/mysql-test/r/func_regexp_pcre.result @@ -879,3 +879,35 @@ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral, 1 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +1 +SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +1 +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +535 +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +0 +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +803 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp diff --git a/mysql-test/r/gis-alter_table_online.result b/mysql-test/r/gis-alter_table_online.result new file mode 100644 index 00000000000..ea7ab3b66bd --- /dev/null +++ b/mysql-test/r/gis-alter_table_online.result @@ -0,0 +1,51 @@ +# +# Start of 10.1 tests +# +# +# MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +# +CREATE TABLE t1 (a TINYBLOB); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMBLOB); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a BLOB); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY); +ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY); +ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY); +ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY); +ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY); +ALTER TABLE t1 MODIFY a POLYGON, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +DROP TABLE t1; +CREATE TABLE t1 (a POLYGON); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a POLYGON); +ALTER TABLE t1 MODIFY a POLYGON, ALGORITHM=INPLACE; +DROP TABLE t1; +# +# End of 10.1 tests +# diff --git a/mysql-test/r/gis-rt-precise.result b/mysql-test/r/gis-rt-precise.result index 3f4f014fb78..65583a0ce0d 100644 --- a/mysql-test/r/gis-rt-precise.result +++ b/mysql-test/r/gis-rt-precise.result @@ -60,3 +60,25 @@ fid AsText(g) 45 LINESTRING(51 51,60 60) DROP TABLE t1; End of 5.5 tests. +CREATE TABLE t1 ( +coordinate point NOT NULL, +SPATIAL KEY coordinate (coordinate) +) ENGINE=Aria DEFAULT CHARSET=ascii PAGE_CHECKSUM=1; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +coordinate point NO MUL NULL +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(0 0)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(10 0)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(10 10)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(0 10)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(5 5)")); +SELECT astext(coordinate) FROM t1 WHERE ST_Intersects(ST_LineFromText("LINESTRING(0 0, 10 0, 10 10, 0 10)"), coordinate); +astext(coordinate) +POINT(0 0) +POINT(10 0) +POINT(10 10) +POINT(0 10) +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +coordinate point NO MUL NULL +DROP TABLE t1; diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 2bd492008bc..f77cd4d14bf 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -333,8 +333,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon; fid Area(g) @@ -684,11 +684,11 @@ insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363 select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85998; object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) -85998 MULTIPOLYGON 1 POINT(115.31877315203187 -36.23747282102153) +85998 MULTIPOLYGON 1 POINT(115.2970604672862 -36.23335610879993) select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85984; object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) -85984 MULTIPOLYGON 1 POINT(-114.87787186923313 36.33101763469059) +85984 MULTIPOLYGON 1 POINT(-114.86854472054372 36.34725218253213) drop table t1; create table t1 (fl geometry not null); insert into t1 values (1); @@ -1641,5 +1641,23 @@ AsText(g) NULL POINT(1 1) # +# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 (x INT, y INT); +INSERT INTO t1 VALUES(0,0); +SELECT LENGTH(t2) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +c2 +25 +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +c2 +51 +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +c2 +52 +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# # End 10.0 tests # diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 7a105fa6172..aac2d833233 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2531,6 +2531,33 @@ select a from t1 group by a having a > 1; a drop table t1; set sql_mode= @save_sql_mode; +create table t1 (f1 int); +insert into t1 values (5),(9); +create table t2 (f2 int); +insert into t2 values (0),(6); +create table t3 (f3 int); +insert into t3 values (6),(3); +create table t4 (f4 int); +insert into t4 values (1),(0); +select +(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7, +(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7)) +from t4; +ERROR 42S22: Reference 'field7' not supported (reference to group function) +drop table t1, t2, t3, t4; +create table t1 (i1 int); +insert into t1 values (1); +create table t2 (i int); +insert into t2 values (2); +select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2); +1 +1 +drop table t1, t2; +create table t1 (c1 int, c2 int); +create table t2 (c1 int, c2 int); +select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3; +c1 c1 +drop table t1, t2; # # Bug #58782 # Missing rows with SELECT .. WHERE .. IN subquery diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 4ea6f4ba192..13a8afd9455 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2248,4 +2248,99 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a WHERE b IN (1,2,3) OR b = d; a b c d DROP TABLE t1,t2,t3; +# +# MDEV-11958: LEFT JOIN with stored routine produces incorrect result +# +CREATE TABLE t (x INT); +INSERT INTO t VALUES(1),(NULL); +CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (ifnull(`test`.`t2`.`x`,0) = 0) +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (`f`(`test`.`t2`.`x`,0) = 0) +drop function f; +drop table t; +CREATE TABLE t1 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL +); +CREATE TABLE t2 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL, +col3 DECIMAL(33,5) NULL DEFAULT NULL +); +INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); +INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); +CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) +RETURNS decimal(33,5) +LANGUAGE SQL +DETERMINISTIC +CONTAINS SQL +SQL SECURITY INVOKER +BEGIN +IF p_num IS NULL THEN +RETURN p_return; +ELSE +RETURN p_num; +END IF; +END | +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (ifnull(`test`.`t2`.`col3`,0) = 0) +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (`f1`(`test`.`t2`.`col3`,0) = 0) +DROP FUNCTION f1; +DROP TABLE t1,t2; +# end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index c8305ccfd2d..2eb1527da37 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2259,6 +2259,101 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a WHERE b IN (1,2,3) OR b = d; a b c d DROP TABLE t1,t2,t3; +# +# MDEV-11958: LEFT JOIN with stored routine produces incorrect result +# +CREATE TABLE t (x INT); +INSERT INTO t VALUES(1),(NULL); +CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (ifnull(`test`.`t2`.`x`,0) = 0) +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (`f`(`test`.`t2`.`x`,0) = 0) +drop function f; +drop table t; +CREATE TABLE t1 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL +); +CREATE TABLE t2 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL, +col3 DECIMAL(33,5) NULL DEFAULT NULL +); +INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); +INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); +CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) +RETURNS decimal(33,5) +LANGUAGE SQL +DETERMINISTIC +CONTAINS SQL +SQL SECURITY INVOKER +BEGIN +IF p_num IS NULL THEN +RETURN p_return; +ELSE +RETURN p_num; +END IF; +END | +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (ifnull(`test`.`t2`.`col3`,0) = 0) +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (`f1`(`test`.`t2`.`col3`,0) = 0) +DROP FUNCTION f1; +DROP TABLE t1,t2; +# end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result index 1128caf9122..250037f60b8 100644 --- a/mysql-test/r/loadxml.result +++ b/mysql-test/r/loadxml.result @@ -5,10 +5,10 @@ load xml infile '../../std_data/loadxml.dat' into table t1 rows identified by '<row>'; select * from t1 order by a; a b -1 b1 -2 b2 -3 b3 -11 b11 +1 b1 +2 b2 +3 b3 +11 b11 111 b111 112 b112 & < > " ' &unknown; -- check entities 212 b212 @@ -81,17 +81,17 @@ LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1 ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b); SELECT * FROM t1 ORDER BY a; a b -1 !b1 -11 !b11 +1 ! b1 +11 ! b11 111 !b111 112 !b112 & < > " ' &unknown; -- check entities -2 !b2 +2 ! b2 212 !b212 213 !b213 214 !b214 215 !b215 216 !&bb b; -3 !b3 +3 ! b3 DROP TABLE t1; # # Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS diff --git a/mysql-test/r/myisam_debug.result b/mysql-test/r/myisam_debug.result index 77de991bafd..ca45ce1fc9f 100644 --- a/mysql-test/r/myisam_debug.result +++ b/mysql-test/r/myisam_debug.result @@ -36,6 +36,6 @@ set debug_dbug='+d,mi_lock_database_failure'; unlock tables; Warnings: Error 126 Incorrect key file for table './test/t1.MYI'; try to repair it -Error 1015 Can't lock file (errno: 22 "Invalid argument") +Error 1030 Got error 22 "Invalid argument" from storage engine MyISAM set debug_dbug=@old_dbug; drop table t1; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index dd0129df0d9..8a24128daa2 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -529,3 +529,61 @@ a +-------------------+ End of tests +create table `a1\``b1` (a int); +show tables; +Tables_in_test +a1\`b1 +insert `a1\``b1` values (1),(2); +show create table `a1\``b1`; +Table Create Table +a1\`b1 CREATE TABLE `a1\``b1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `a1\``b1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +INSERT INTO `a1\``b1` VALUES (1),(2); +insert `a1\``b1` values (4),(5); +show create table `a1\``b1`; +Table Create Table +a1\`b1 CREATE TABLE `a1\``b1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from `a1\``b1`; +a +1 +2 +drop table `a1\``b1`; +set sql_mode=ansi_quotes; +create table "a1\""b1" (a int); +show tables; +Tables_in_test +a1\"b1 +insert "a1\""b1" values (1),(2); +show create table "a1\""b1"; +Table Create Table +a1\"b1 CREATE TABLE "a1\""b1" ( + "a" int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE "a1\""b1" ( + "a" int(11) DEFAULT NULL +); +/*!40101 SET character_set_client = @saved_cs_client */; +INSERT INTO "a1\""b1" VALUES (1),(2); +insert "a1\""b1" values (4),(5); +show create table "a1\""b1"; +Table Create Table +a1\"b1 CREATE TABLE "a1\""b1" ( + "a" int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from "a1\""b1"; +a +1 +2 +drop table "a1\""b1"; +set sql_mode=default; diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 9ba5344fad0..565423be624 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -464,4 +464,25 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; -End of tests +use test; +alter table mysql.innodb_table_stats modify last_update int not null; +create table extralongname_extralongname_extralongname_extralongname_ext ( +id int(10) unsigned not null, +created_date date not null, +created timestamp not null, +primary key (created,id,created_date) +) engine=innodb stats_persistent=1 default charset=latin1 +partition by range (year(created_date)) +subpartition by hash (month(created_date)) +subpartitions 2 ( +partition p2007 values less than (2008), +partition p2008 values less than (2009) +); +select length(table_name) from mysql.innodb_table_stats; +length(table_name) +79 +79 +79 +79 +drop table extralongname_extralongname_extralongname_extralongname_ext; +End of 10.0 tests diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index fa054d457f9..b3d8fb93e1f 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -960,4 +960,9 @@ con1 con2 con2 -closed_connection- +set sql_mode=no_backslash_escapes; +select "foo\""bar"; +foo\"bar +foo\"bar +set sql_mode=default; End of tests diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index c9c569137b2..6e2e394caec 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -30,6 +30,8 @@ create temporary table t3 (a int); create temporary table t4 (a int) select * from t3; insert into t3 values(1); insert into t4 select * from t3; +create table t3 (a int); +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 623c28ff7ca..3fa7fef89b2 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7121,9 +7121,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7131,9 +7132,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # @@ -7147,3 +7149,40 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 09223d36429..db51bbb8186 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2274,6 +2274,94 @@ pk f1 sq 5 3 5 set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2; +# +# mdev-12838: scan of materialized of semi-join subquery in join +# +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 ( +dispatch_group varchar(32), +assignment_group varchar(32), +sys_id char(32), +PRIMARY KEY (sys_id), +KEY idx1 (dispatch_group), +KEY idx2 (assignment_group) +) ENGINE=MyISAM; +CREATE TABLE t2 ( +ugroup varchar(32), +user varchar(32), +sys_id char(32), +PRIMARY KEY (sys_id), +KEY idx3 (ugroup), +KEY idx4 (user) +) ENGINE=MyISAM; +CREATE TABLE t3 ( +type mediumtext, +sys_id char(32), +PRIMARY KEY (sys_id) +) ENGINE=MyISAM; +set optimizer_switch='materialization=off'; +explain SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary +1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where +1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index +SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +assignment_group +df50316637232000158bbfc8bcbe5d23 +e08fad2637232000158bbfc8bcbe5d39 +ec70316637232000158bbfc8bcbe5d60 +7b10fd2637232000158bbfc8bcbe5d30 +ebb4620037332000158bbfc8bcbe5d89 +set optimizer_switch='materialization=on'; +explain SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index +2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where +2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where +SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +assignment_group +df50316637232000158bbfc8bcbe5d23 +e08fad2637232000158bbfc8bcbe5d39 +ec70316637232000158bbfc8bcbe5d60 +7b10fd2637232000158bbfc8bcbe5d30 +ebb4620037332000158bbfc8bcbe5d89 +DROP TABLE t1,t2,t3; +set optimizer_switch=@save_optimizer_switch; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index dba4d049da3..57b0526c6a3 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -485,3 +485,20 @@ FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) ); f1 DROP TABLE t1,t2,t3; +# +# MDEV-12963: min/max optimization optimizing away all tables employed +# for uncorrelated IN subquery used in a disjunct of WHERE +# +create table t1 (a int, index idx(a)) engine=myisam; +insert into t1 values (4),(7),(1),(3),(9); +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; +a +3 +7 +9 +explain +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index idx idx 5 NULL 5 Using where; Using index +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1; diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 8c0e6041a90..818fdf6c335 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7121,9 +7121,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7131,9 +7132,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # @@ -7147,6 +7149,43 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; @@optimizer_switch like '%exists_to_in=off%' diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 5e2b4f2d374..51e3c7066d4 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7114,9 +7114,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7124,9 +7125,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # @@ -7140,6 +7142,43 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index afe79f80015..4a26d641aa6 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7112,9 +7112,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7122,9 +7123,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # @@ -7138,4 +7140,41 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 15f7b977c24..39e6a8c26d1 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7127,9 +7127,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7137,9 +7138,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # @@ -7153,6 +7155,43 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 1c5578b3575..6980969bb79 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7112,9 +7112,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7122,9 +7123,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # @@ -7138,5 +7140,42 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/subselect_nulls.result b/mysql-test/r/subselect_nulls.result index 584c184870d..08982371269 100644 --- a/mysql-test/r/subselect_nulls.result +++ b/mysql-test/r/subselect_nulls.result @@ -115,3 +115,9 @@ k d1 d2 set optimizer_switch= @tmp_subselect_nulls; drop table x1; drop table x2; +select (select 1, 2) in (select 3, 4); +(select 1, 2) in (select 3, 4) +0 +select (select NULL, NULL) in (select 3, 4); +(select NULL, NULL) in (select 3, 4) +NULL diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index c00ce7ec227..5d0c64e9ddb 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1612,3 +1612,25 @@ Warnings: Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where ((rand() < 0)) drop table t1,t2; set optimizer_switch=@save_optimizer_switch; +# +# mdev-12855: materialization of a semi-join subquery + ORDER BY +# +CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('qux'),('foo'); +CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); +SELECT f1 FROM t1 +WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) +HAVING f1 != 'foo' +ORDER BY f1; +f1 +qux +explain SELECT f1 FROM t1 +WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) +HAVING f1 != 'foo' +ORDER BY f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index f1 f1 11 NULL 2 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 3f91b510c5b..4feab0c78b0 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2314,6 +2314,94 @@ pk f1 sq 5 3 5 set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2; +# +# mdev-12838: scan of materialized of semi-join subquery in join +# +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 ( +dispatch_group varchar(32), +assignment_group varchar(32), +sys_id char(32), +PRIMARY KEY (sys_id), +KEY idx1 (dispatch_group), +KEY idx2 (assignment_group) +) ENGINE=MyISAM; +CREATE TABLE t2 ( +ugroup varchar(32), +user varchar(32), +sys_id char(32), +PRIMARY KEY (sys_id), +KEY idx3 (ugroup), +KEY idx4 (user) +) ENGINE=MyISAM; +CREATE TABLE t3 ( +type mediumtext, +sys_id char(32), +PRIMARY KEY (sys_id) +) ENGINE=MyISAM; +set optimizer_switch='materialization=off'; +explain SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary +1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where +1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index +SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +assignment_group +df50316637232000158bbfc8bcbe5d23 +e08fad2637232000158bbfc8bcbe5d39 +ec70316637232000158bbfc8bcbe5d60 +7b10fd2637232000158bbfc8bcbe5d30 +ebb4620037332000158bbfc8bcbe5d89 +set optimizer_switch='materialization=on'; +explain SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index +2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where +2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where +SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND +t1.dispatch_group IN +(SELECT t2.ugroup +FROM t2, t3 t3_i +WHERE t2.ugroup = t3_i.sys_id AND +t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND +t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +assignment_group +df50316637232000158bbfc8bcbe5d23 +e08fad2637232000158bbfc8bcbe5d39 +ec70316637232000158bbfc8bcbe5d60 +7b10fd2637232000158bbfc8bcbe5d30 +ebb4620037332000158bbfc8bcbe5d89 +DROP TABLE t1,t2,t3; +set optimizer_switch=@save_optimizer_switch; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 5a6cd8907e9..fe2339db471 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1978,3 +1978,21 @@ d 2016-06-04 00:00:00 drop table t1; End of 5.0 tests +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 (c varchar(30), d varchar(30)); +insert into t1 values ('1','1'),('2','2'),('4','4'); +create table t3 (e int, f int); +insert into t3 values (1,1),(2,2),(31,31),(32,32); +select e,f, (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3; +e f sub +1 1 1 +2 2 1 +31 31 0 +32 32 0 +select avg(f), (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3 group by sub; +avg(f) sub +31.5000 0 +1.5000 1 +drop table t1,t2,t3; +End of 5.5 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index d6da2a03b46..1cff9f378f8 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5619,6 +5619,20 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5) drop view v1; drop table t1,t2,t3; +# +# MDEV-11240: Server crashes in check_view_single_update or +# Assertion `derived->table' failed in mysql_derived_merge_for_insert +# +CREATE TABLE t3 (a INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2; +CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; +PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; +EXECUTE stmt; +ERROR HY000: Can not insert into join view 'test.v2' without fields list +EXECUTE stmt; +ERROR HY000: Can not insert into join view 'test.v2' without fields list +drop view v1,v2; +drop table t3; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- @@ -5944,6 +5958,68 @@ use_case_id InitialDeadline 10 2015-12-18 drop view v1; drop table t1; +# +# MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view +# +# DATABASE() fails only when the initial view creation features a NULL +# default database. +# +# CREATE, USE and DROP database so that we have no "default" database. +# +CREATE DATABASE temporary; +USE temporary; +DROP DATABASE temporary; +SELECT DATABASE(); +DATABASE() +NULL +CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two'; +SHOW CREATE VIEW test.v_no_db; +View Create View character_set_client collation_connection +v_no_db CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v_no_db` AS select (database() = 'temporary_two') AS `DATABASE() = 'temporary_two'` latin1 latin1_swedish_ci +PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'"; +# +# All statements should return NULL +# +EXECUTE prepared_no_database; +DATABASE() = 'temporary_two' +NULL +SELECT DATABASE() = 'temporary_two'; +DATABASE() = 'temporary_two' +NULL +SELECT * FROM test.v_no_db; +DATABASE() = 'temporary_two' +NULL +CREATE DATABASE temporary_two; +USE temporary_two; +CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two'; +PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'"; +# +# All statements should return 1; +# +SELECT DATABASE() = 'temporary_two'; +DATABASE() = 'temporary_two' +1 +SELECT * FROM test.v_no_db; +DATABASE() = 'temporary_two' +1 +SELECT * FROM test.v_with_db; +DATABASE() = 'temporary_two' +1 +EXECUTE prepared_with_database; +DATABASE() = 'temporary_two' +1 +# +# Prepared statements maintain default database to be the same +# during on creation so this should return NULL still. +# See MySQL bug #25843 +# +EXECUTE prepared_no_database; +DATABASE() = 'temporary_two' +NULL +DROP DATABASE temporary_two; +DROP VIEW test.v_no_db; +DROP VIEW test.v_with_db; +USE test; # ----------------------------------------------------------------- # -- End of 10.0 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/std_data/loadxml.dat b/mysql-test/std_data/loadxml.dat index b72fac9da74..d5ccd4a1b13 100644 --- a/mysql-test/std_data/loadxml.dat +++ b/mysql-test/std_data/loadxml.dat @@ -9,19 +9,19 @@ <table_data name="t1"> <row> <field name="a">1</field> - <field name="b">b1</field> + <field name="b"> b1</field> </row> <row> <field name="a">2</field> - <field name="b">b2</field> + <field name="b"> b2</field> </row> <row> <field name="a">3</field> - <field name="b">b3</field> + <field name="b"> b3</field> </row> <row> <field name="a">11</field> - <field name="b">b11</field> + <field name="b"> b11</field> </row> <!-- Check field values as tags --> diff --git a/mysql-test/suite/archive/archive_gis.result b/mysql-test/suite/archive/archive_gis.result index 97f48407db2..5593b53f7c3 100644 --- a/mysql-test/suite/archive/archive_gis.result +++ b/mysql-test/suite/archive/archive_gis.result @@ -326,8 +326,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid; fid Area(g) diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 5b6adf5600e..48d9db44b05 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -1,14 +1,14 @@ #### Setup tables #### -CREATE TABLE t0 (a CHAR(100)); -CREATE TABLE t1 (a CHAR(100)); -CREATE TABLE t2 (a CHAR(100)); -CREATE TABLE t3 (a CHAR(100)); -CREATE TABLE ta0 (a CHAR(100)); -CREATE TABLE ta1 (a CHAR(100)); -CREATE TABLE ta2 (a CHAR(100)); -CREATE TABLE ta3 (a CHAR(100)); +CREATE TABLE t0 (a CHAR(200)); +CREATE TABLE t1 (a CHAR(200)); +CREATE TABLE t2 (a CHAR(200)); +CREATE TABLE t3 (a CHAR(200)); +CREATE TABLE ta0 (a CHAR(200)); +CREATE TABLE ta1 (a CHAR(200)); +CREATE TABLE ta2 (a CHAR(200)); +CREATE TABLE ta3 (a CHAR(200)); CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT); -CREATE TABLE data_table (a CHAR(100)); +CREATE TABLE data_table (a CHAR(200)); INSERT INTO data_table VALUES ('foo'); CREATE TABLE trigger_table_1 (a INT); CREATE TABLE trigger_table_2 (a INT); @@ -2392,7 +2392,7 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc DROP PROCEDURE p1; DROP TABLE t1; DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (a VARCHAR(100), b VARCHAR(100)); +CREATE TABLE t1 (a VARCHAR(200), b VARCHAR(200)); INSERT INTO t1 VALUES ('a','b'); UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1; Warnings: @@ -2423,7 +2423,7 @@ CREATE FUNCTION fun_check_log_bin() RETURNS INT BEGIN SET @@SQL_LOG_BIN = 0; INSERT INTO t1 VALUES(@@global.sync_binlog); -RETURN 100; +RETURN 200; END| "One unsafe warning should be issued in the following statement" SELECT fun_check_log_bin(); diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test index 2de84a58875..aaf4697b91f 100644 --- a/mysql-test/suite/binlog/t/binlog_unsafe.test +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test @@ -110,16 +110,16 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state --echo #### Setup tables #### -CREATE TABLE t0 (a CHAR(100)); -CREATE TABLE t1 (a CHAR(100)); -CREATE TABLE t2 (a CHAR(100)); -CREATE TABLE t3 (a CHAR(100)); -CREATE TABLE ta0 (a CHAR(100)); -CREATE TABLE ta1 (a CHAR(100)); -CREATE TABLE ta2 (a CHAR(100)); -CREATE TABLE ta3 (a CHAR(100)); +CREATE TABLE t0 (a CHAR(200)); +CREATE TABLE t1 (a CHAR(200)); +CREATE TABLE t2 (a CHAR(200)); +CREATE TABLE t3 (a CHAR(200)); +CREATE TABLE ta0 (a CHAR(200)); +CREATE TABLE ta1 (a CHAR(200)); +CREATE TABLE ta2 (a CHAR(200)); +CREATE TABLE ta3 (a CHAR(200)); CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT); -CREATE TABLE data_table (a CHAR(100)); +CREATE TABLE data_table (a CHAR(200)); INSERT INTO data_table VALUES ('foo'); CREATE TABLE trigger_table_1 (a INT); CREATE TABLE trigger_table_2 (a INT); @@ -433,7 +433,7 @@ DROP TABLE t1; DROP TABLE IF EXISTS t1; --enable_warnings -CREATE TABLE t1 (a VARCHAR(100), b VARCHAR(100)); +CREATE TABLE t1 (a VARCHAR(200), b VARCHAR(200)); INSERT INTO t1 VALUES ('a','b'); UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1; DROP TABLE t1; @@ -471,7 +471,7 @@ CREATE FUNCTION fun_check_log_bin() RETURNS INT BEGIN SET @@SQL_LOG_BIN = 0; INSERT INTO t1 VALUES(@@global.sync_binlog); - RETURN 100; + RETURN 200; END| DELIMITER ;| --echo "One unsafe warning should be issued in the following statement" diff --git a/mysql-test/suite/innodb/r/drop_table_background.result b/mysql-test/suite/innodb/r/drop_table_background.result new file mode 100644 index 00000000000..a6f5672ba7f --- /dev/null +++ b/mysql-test/suite/innodb/r/drop_table_background.result @@ -0,0 +1,9 @@ +CREATE TABLE t(c0 SERIAL, c1 INT, c2 INT, c3 INT, c4 INT, +KEY(c1), KEY(c2), KEY(c2,c1), +KEY(c3), KEY(c3,c1), KEY(c3,c2), KEY(c3,c2,c1), +KEY(c4), KEY(c4,c1), KEY(c4,c2), KEY(c4,c2,c1), +KEY(c4,c3), KEY(c4,c3,c1), KEY(c4,c3,c2), KEY(c4,c3,c2,c1)) ENGINE=InnoDB; +SET DEBUG_DBUG='+d,row_drop_table_add_to_background'; +DROP TABLE t; +CREATE TABLE t (a INT) ENGINE=InnoDB; +DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/innodb-get-fk.result b/mysql-test/suite/innodb/r/innodb-get-fk.result index f34fb8bcb67..ee17f262854 100644 --- a/mysql-test/suite/innodb/r/innodb-get-fk.result +++ b/mysql-test/suite/innodb/r/innodb-get-fk.result @@ -27,7 +27,7 @@ CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `repr CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB COMMENT="This is a comment about tables"; ALTER TABLE `repro`.`crew_role_assigned` COMMENT = 'innodb_read_only'; -ERROR HY000: Can't lock file (errno: 165 "Table is read only") +ERROR HY000: Table 'crew_role_assigned' is read only SHOW CREATE TABLE `repro`.`crew_role_assigned`; Table Create Table crew_role_assigned CREATE TABLE `crew_role_assigned` ( diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result index 21c1234d7d3..f683195ac15 100644 --- a/mysql-test/suite/innodb/r/innodb_gis.result +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -326,8 +326,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid; fid Area(g) diff --git a/mysql-test/suite/innodb/r/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result new file mode 100644 index 00000000000..db31c32559f --- /dev/null +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@ -0,0 +1,48 @@ +create table t1 (a int not null, d varchar(15) not null, b +varchar(198) not null, c char(156), +fulltext ftsic(c)) engine=InnoDB +row_format=redundant; +insert into t1 values(123, 'abcdef', 'jghikl', 'mnop'); +insert into t1 values(456, 'abcdef', 'jghikl', 'mnop'); +insert into t1 values(789, 'abcdef', 'jghikl', 'mnop'); +insert into t1 values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +SET GLOBAL innodb_file_per_table=OFF; +create table t2 (a int not null, d varchar(15) not null, b +varchar(198) not null, c char(156), fulltext ftsic(c)) engine=InnoDB +row_format=redundant; +insert into t2 select * from t1; +create table t3 (a int not null, d varchar(15) not null, b varchar(198), +c varchar(150), index k1(c(99), b(56)), index k2(b(5), c(10))) engine=InnoDB +row_format=redundant; +insert into t3 values(444, 'dddd', 'bbbbb', 'aaaaa'); +insert into t3 values(555, 'eeee', 'ccccc', 'aaaaa'); +SET GLOBAL innodb_fast_shutdown=0; +SELECT COUNT(*) FROM t1; +COUNT(*) +4096 +SELECT COUNT(*) FROM t2; +COUNT(*) +4096 +SELECT COUNT(*) FROM t3; +COUNT(*) +2 +TRUNCATE TABLE t1; +ERROR HY000: Table 't1' is read only +TRUNCATE TABLE t2; +ERROR HY000: Table 't2' is read only +TRUNCATE TABLE t3; +ERROR HY000: Table 't3' is read only +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/suite/innodb/r/table_flags,4k.rdiff b/mysql-test/suite/innodb/r/table_flags,4k.rdiff new file mode 100644 index 00000000000..faa8a408c65 --- /dev/null +++ b/mysql-test/suite/innodb/r/table_flags,4k.rdiff @@ -0,0 +1,83 @@ +--- suite/innodb/r/table_flags.result ++++ suite/innodb/r/table_flags,4k.reject +@@ -13,7 +13,7 @@ + header=0x01000003016e (NAME=0x696e66696d756d00) + header=0x00002815008d (NAME='SYS_DATAFILES', + DB_TRX_ID=0x000000000301, +- DB_ROLL_PTR=0x81000001320194, ++ DB_ROLL_PTR=0x81000003260194, + ID=0x000000000000000e, + N_COLS=0x00000002, + TYPE=0x00000001, +@@ -23,7 +23,7 @@ + SPACE=0x00000000) + header=0x0000101500d5 (NAME='SYS_FOREIGN', + DB_TRX_ID=0x000000000300, +- DB_ROLL_PTR=0x800000012d0110, ++ DB_ROLL_PTR=0x80000003200110, + ID=0x000000000000000b, + N_COLS=0x00000004, + TYPE=0x00000001, +@@ -33,7 +33,7 @@ + SPACE=0x00000000) + header=0x000018150122 (NAME='SYS_FOREIGN_COLS', + DB_TRX_ID=0x000000000300, +- DB_ROLL_PTR=0x800000012d0201, ++ DB_ROLL_PTR=0x80000003200201, + ID=0x000000000000000c, + N_COLS=0x00000004, + TYPE=0x00000001, +@@ -43,7 +43,7 @@ + SPACE=0x00000000) + header=0x0400201501fc (NAME='SYS_TABLESPACES', + DB_TRX_ID=0x000000000301, +- DB_ROLL_PTR=0x81000001320110, ++ DB_ROLL_PTR=0x81000003260110, + ID=0x000000000000000d, + N_COLS=0x00000003, + TYPE=0x00000001, +@@ -53,7 +53,7 @@ + SPACE=0x00000000) + header=0x000038150240 (NAME='test/tc', + DB_TRX_ID=0x000000000303, +- DB_ROLL_PTR=0x83000001360110, ++ DB_ROLL_PTR=0x830000032b0110, + ID=0x0000000000000010, + N_COLS=0x80000001, + TYPE=0x00000001, +@@ -63,7 +63,7 @@ + SPACE=0x00000002) + header=0x0000401502c8 (NAME='test/td', + DB_TRX_ID=0x000000000304, +- DB_ROLL_PTR=0x84000001370110, ++ DB_ROLL_PTR=0x840000032c0110, + ID=0x0000000000000011, + N_COLS=0x80000001, + TYPE=0x00000021, +@@ -73,7 +73,7 @@ + SPACE=0x00000003) + header=0x0000501501b8 (NAME='test/tp', + DB_TRX_ID=0x000000000306, +- DB_ROLL_PTR=0x86000001390110, ++ DB_ROLL_PTR=0x860000032e0110, + ID=0x0000000000000013, + N_COLS=0x80000001, + TYPE=0x00000021, +@@ -83,7 +83,7 @@ + SPACE=0x00000005) + header=0x000030150284 (NAME='test/tr', + DB_TRX_ID=0x000000000302, +- DB_ROLL_PTR=0x82000001350110, ++ DB_ROLL_PTR=0x820000032a0110, + ID=0x000000000000000f, + N_COLS=0x00000001, + TYPE=0x00000001, +@@ -93,7 +93,7 @@ + SPACE=0x00000001) + header=0x000048150074 (NAME='test/tz', + DB_TRX_ID=0x000000000305, +- DB_ROLL_PTR=0x85000001380110, ++ DB_ROLL_PTR=0x850000032d0110, + ID=0x0000000000000012, + N_COLS=0x80000001, + TYPE=0x00000023, diff --git a/mysql-test/suite/innodb/r/table_flags,8k.rdiff b/mysql-test/suite/innodb/r/table_flags,8k.rdiff new file mode 100644 index 00000000000..ba3439fc6cf --- /dev/null +++ b/mysql-test/suite/innodb/r/table_flags,8k.rdiff @@ -0,0 +1,83 @@ +--- suite/innodb/r/table_flags.result ++++ suite/innodb/r/table_flags,8k.reject +@@ -13,7 +13,7 @@ + header=0x01000003016e (NAME=0x696e66696d756d00) + header=0x00002815008d (NAME='SYS_DATAFILES', + DB_TRX_ID=0x000000000301, +- DB_ROLL_PTR=0x81000001320194, ++ DB_ROLL_PTR=0x81000001d70194, + ID=0x000000000000000e, + N_COLS=0x00000002, + TYPE=0x00000001, +@@ -23,7 +23,7 @@ + SPACE=0x00000000) + header=0x0000101500d5 (NAME='SYS_FOREIGN', + DB_TRX_ID=0x000000000300, +- DB_ROLL_PTR=0x800000012d0110, ++ DB_ROLL_PTR=0x80000001d10110, + ID=0x000000000000000b, + N_COLS=0x00000004, + TYPE=0x00000001, +@@ -33,7 +33,7 @@ + SPACE=0x00000000) + header=0x000018150122 (NAME='SYS_FOREIGN_COLS', + DB_TRX_ID=0x000000000300, +- DB_ROLL_PTR=0x800000012d0201, ++ DB_ROLL_PTR=0x80000001d10201, + ID=0x000000000000000c, + N_COLS=0x00000004, + TYPE=0x00000001, +@@ -43,7 +43,7 @@ + SPACE=0x00000000) + header=0x0400201501fc (NAME='SYS_TABLESPACES', + DB_TRX_ID=0x000000000301, +- DB_ROLL_PTR=0x81000001320110, ++ DB_ROLL_PTR=0x81000001d70110, + ID=0x000000000000000d, + N_COLS=0x00000003, + TYPE=0x00000001, +@@ -53,7 +53,7 @@ + SPACE=0x00000000) + header=0x000038150240 (NAME='test/tc', + DB_TRX_ID=0x000000000303, +- DB_ROLL_PTR=0x83000001360110, ++ DB_ROLL_PTR=0x83000001db0110, + ID=0x0000000000000010, + N_COLS=0x80000001, + TYPE=0x00000001, +@@ -63,7 +63,7 @@ + SPACE=0x00000002) + header=0x0000401502c8 (NAME='test/td', + DB_TRX_ID=0x000000000304, +- DB_ROLL_PTR=0x84000001370110, ++ DB_ROLL_PTR=0x84000001dc0110, + ID=0x0000000000000011, + N_COLS=0x80000001, + TYPE=0x00000021, +@@ -73,7 +73,7 @@ + SPACE=0x00000003) + header=0x0000501501b8 (NAME='test/tp', + DB_TRX_ID=0x000000000306, +- DB_ROLL_PTR=0x86000001390110, ++ DB_ROLL_PTR=0x86000001de0110, + ID=0x0000000000000013, + N_COLS=0x80000001, + TYPE=0x00000021, +@@ -83,7 +83,7 @@ + SPACE=0x00000005) + header=0x000030150284 (NAME='test/tr', + DB_TRX_ID=0x000000000302, +- DB_ROLL_PTR=0x82000001350110, ++ DB_ROLL_PTR=0x82000001da0110, + ID=0x000000000000000f, + N_COLS=0x00000001, + TYPE=0x00000001, +@@ -93,7 +93,7 @@ + SPACE=0x00000001) + header=0x000048150074 (NAME='test/tz', + DB_TRX_ID=0x000000000305, +- DB_ROLL_PTR=0x85000001380110, ++ DB_ROLL_PTR=0x85000001dd0110, + ID=0x0000000000000012, + N_COLS=0x80000001, + TYPE=0x00000023, diff --git a/mysql-test/suite/innodb/r/table_flags.result b/mysql-test/suite/innodb/r/table_flags.result new file mode 100644 index 00000000000..8d79439764c --- /dev/null +++ b/mysql-test/suite/innodb/r/table_flags.result @@ -0,0 +1,175 @@ +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE tr(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +CREATE TABLE tc(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPACT; +CREATE TABLE td(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +CREATE TABLE tz(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPRESSED +KEY_BLOCK_SIZE=1; +CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC +PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9; +ERROR HY000: Unknown option 'PAGE_COMPRESSED' +CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SYS_TABLES clustered index root page (8): +N_RECS=9; LEVEL=0; INDEX_ID=0x0000000000000001 +header=0x01000003016e (NAME=0x696e66696d756d00) +header=0x00002815008d (NAME='SYS_DATAFILES', + DB_TRX_ID=0x000000000301, + DB_ROLL_PTR=0x81000001320194, + ID=0x000000000000000e, + N_COLS=0x00000002, + TYPE=0x00000001, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000040, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000000) +header=0x0000101500d5 (NAME='SYS_FOREIGN', + DB_TRX_ID=0x000000000300, + DB_ROLL_PTR=0x800000012d0110, + ID=0x000000000000000b, + N_COLS=0x00000004, + TYPE=0x00000001, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000040, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000000) +header=0x000018150122 (NAME='SYS_FOREIGN_COLS', + DB_TRX_ID=0x000000000300, + DB_ROLL_PTR=0x800000012d0201, + ID=0x000000000000000c, + N_COLS=0x00000004, + TYPE=0x00000001, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000040, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000000) +header=0x0400201501fc (NAME='SYS_TABLESPACES', + DB_TRX_ID=0x000000000301, + DB_ROLL_PTR=0x81000001320110, + ID=0x000000000000000d, + N_COLS=0x00000003, + TYPE=0x00000001, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000040, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000000) +header=0x000038150240 (NAME='test/tc', + DB_TRX_ID=0x000000000303, + DB_ROLL_PTR=0x83000001360110, + ID=0x0000000000000010, + N_COLS=0x80000001, + TYPE=0x00000001, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000050, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000002) +header=0x0000401502c8 (NAME='test/td', + DB_TRX_ID=0x000000000304, + DB_ROLL_PTR=0x84000001370110, + ID=0x0000000000000011, + N_COLS=0x80000001, + TYPE=0x00000021, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000050, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000003) +header=0x0000501501b8 (NAME='test/tp', + DB_TRX_ID=0x000000000306, + DB_ROLL_PTR=0x86000001390110, + ID=0x0000000000000013, + N_COLS=0x80000001, + TYPE=0x00000021, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000050, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000005) +header=0x000030150284 (NAME='test/tr', + DB_TRX_ID=0x000000000302, + DB_ROLL_PTR=0x82000001350110, + ID=0x000000000000000f, + N_COLS=0x00000001, + TYPE=0x00000001, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000050, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000001) +header=0x000048150074 (NAME='test/tz', + DB_TRX_ID=0x000000000305, + DB_ROLL_PTR=0x85000001380110, + ID=0x0000000000000012, + N_COLS=0x80000001, + TYPE=0x00000023, + MIX_ID=0x0000000000000000, + MIX_LEN=0x00000050, + CLUSTER_NAME=NULL(0 bytes), + SPACE=0x00000004) +header=0x060008030000 (NAME=0x73757072656d756d00) +SHOW CREATE TABLE tr; +Table Create Table +tr CREATE TABLE `tr` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT +INSERT INTO tr VALUES(42); +SHOW CREATE TABLE tc; +ERROR 42S02: Table 'test.tc' doesn't exist in engine +SHOW CREATE TABLE td; +ERROR 42S02: Table 'test.td' doesn't exist in engine +SHOW CREATE TABLE tz; +ERROR 42S02: Table 'test.tz' doesn't exist in engine +SHOW CREATE TABLE tp; +ERROR 42S02: Table 'test.tp' doesn't exist in engine +FOUND /InnoDB: Table '.test.\..t[cdzp].' in InnoDB data dictionary has unknown type (81|f21|8a1|3023)/ in mysqld.1.err +FOUND /InnoDB: Cannot open table test/t[cdzp] from the internal data dictionary of InnoDB/ in mysqld.1.err +Restoring SYS_TABLES clustered index root page (8) +SHOW CREATE TABLE tr; +Table Create Table +tr CREATE TABLE `tr` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT +SHOW CREATE TABLE tc; +Table Create Table +tc CREATE TABLE `tc` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT +SHOW CREATE TABLE td; +Table Create Table +td CREATE TABLE `td` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +SHOW CREATE TABLE tz; +Table Create Table +tz CREATE TABLE `tz` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 +SHOW CREATE TABLE tp; +Table Create Table +tp CREATE TABLE `tp` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +BEGIN; +INSERT INTO tr VALUES(1); +INSERT INTO tc VALUES(1); +INSERT INTO td VALUES(1); +INSERT INTO tz VALUES(1); +INSERT INTO tp VALUES(1); +ROLLBACK; +SELECT * FROM tr; +a +42 +SELECT * FROM tc; +a +SELECT * FROM td; +a +SELECT * FROM tz; +a +SELECT * FROM tp; +a +DROP TABLE tr,tc,td,tz,tp; +ib_logfile0 +ib_logfile1 +ibdata1 +sys_tables.bin diff --git a/mysql-test/suite/innodb/t/drop_table_background.test b/mysql-test/suite/innodb/t/drop_table_background.test new file mode 100644 index 00000000000..0f596dec574 --- /dev/null +++ b/mysql-test/suite/innodb/t/drop_table_background.test @@ -0,0 +1,30 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +# Embedded server does not support restarting +--source include/not_embedded.inc + +CREATE TABLE t(c0 SERIAL, c1 INT, c2 INT, c3 INT, c4 INT, +KEY(c1), KEY(c2), KEY(c2,c1), +KEY(c3), KEY(c3,c1), KEY(c3,c2), KEY(c3,c2,c1), +KEY(c4), KEY(c4,c1), KEY(c4,c2), KEY(c4,c2,c1), +KEY(c4,c3), KEY(c4,c3,c1), KEY(c4,c3,c2), KEY(c4,c3,c2,c1)) ENGINE=InnoDB; + +let $n= 10; + +SET DEBUG_DBUG='+d,row_drop_table_add_to_background'; +--disable_query_log +let $i= $n; +while ($i) { + eval CREATE TABLE t$i LIKE t; + dec $i; +} +let $i= $n; +while ($i) { + eval DROP TABLE t$i; + dec $i; +} +--enable_query_log +DROP TABLE t; +--source include/restart_mysqld.inc +CREATE TABLE t (a INT) ENGINE=InnoDB; +DROP TABLE t; diff --git a/mysql-test/suite/innodb/t/innodb-alter-debug.test b/mysql-test/suite/innodb/t/innodb-alter-debug.test index 70017ffba35..f4996916e9f 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-debug.test +++ b/mysql-test/suite/innodb/t/innodb-alter-debug.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size.inc --source include/have_debug.inc --source include/have_debug_sync.inc diff --git a/mysql-test/suite/innodb/t/innodb-alter-discard.test b/mysql-test/suite/innodb/t/innodb-alter-discard.test index 80678cef0a6..c90992e58a3 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-discard.test +++ b/mysql-test/suite/innodb/t/innodb-alter-discard.test @@ -1,7 +1,7 @@ #Bug#13955083 ALLOW IN-PLACE DDL OPERATIONS ON MISSING OR DISCARDED TABLESPACES --source include/not_embedded.inc ---source include/have_innodb.inc +--source include/innodb_page_size.inc let $MYSQLD_DATADIR=`select @@datadir`; SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb/t/innodb-alter-nullable.test b/mysql-test/suite/innodb/t/innodb-alter-nullable.test index 3f1e82b3183..bb5cdee000a 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test +++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size.inc # Save the initial number of concurrent sessions. --source include/count_sessions.inc diff --git a/mysql-test/suite/innodb/t/innodb-alter-table.test b/mysql-test/suite/innodb/t/innodb-alter-table.test index 45342b4a218..97f0075f344 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-table.test +++ b/mysql-test/suite/innodb/t/innodb-alter-table.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size.inc # # MMDEV-8386: MariaDB creates very big tmp file and hangs on xtradb diff --git a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test index e1e736fc678..8813e50f24a 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test +++ b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test @@ -10,8 +10,7 @@ # Avoid CrashReporter popup on Mac --source include/not_crashrep.inc -# InnoDB is required ---source include/have_innodb.inc +--source include/innodb_page_size.inc --echo # --echo # Bug #18734396 INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS diff --git a/mysql-test/suite/innodb/t/innodb-get-fk.test b/mysql-test/suite/innodb/t/innodb-get-fk.test index c1ab54fab45..339a7968623 100644 --- a/mysql-test/suite/innodb/t/innodb-get-fk.test +++ b/mysql-test/suite/innodb/t/innodb-get-fk.test @@ -36,7 +36,7 @@ CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `rep -- let $restart_parameters=--innodb-read-only -- source include/restart_mysqld.inc ---error ER_CANT_LOCK +--error ER_OPEN_AS_READONLY ALTER TABLE `repro`.`crew_role_assigned` COMMENT = 'innodb_read_only'; SHOW CREATE TABLE `repro`.`crew_role_assigned`; diff --git a/mysql-test/suite/innodb/t/log_file_size.test b/mysql-test/suite/innodb/t/log_file_size.test index 4bae93957e8..6c1003881cf 100644 --- a/mysql-test/suite/innodb/t/log_file_size.test +++ b/mysql-test/suite/innodb/t/log_file_size.test @@ -1,6 +1,6 @@ # Test resizing the InnoDB redo log. ---source include/have_innodb.inc +--source include/innodb_page_size.inc # Embedded server does not support crashing --source include/not_embedded.inc @@ -37,6 +37,12 @@ call mtr.add_suppression("Attempting backtrace"); FLUSH TABLES; --enable_query_log +--let $restart_parameters= --innodb-thread-concurrency=1 --innodb-log-file-size=1m --innodb-log-files-in-group=2 +--source include/restart_mysqld.inc + +--let $restart_parameters= --innodb-thread-concurrency=100 --innodb-log-file-size=10M --innodb-log-files-in-group=2 +--source include/restart_mysqld.inc + CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; BEGIN; INSERT INTO t1 VALUES (42); diff --git a/mysql-test/suite/innodb/t/row_format_redundant.test b/mysql-test/suite/innodb/t/row_format_redundant.test new file mode 100644 index 00000000000..d10121c75c6 --- /dev/null +++ b/mysql-test/suite/innodb/t/row_format_redundant.test @@ -0,0 +1,63 @@ +--source include/have_innodb.inc +# Embedded mode doesn't allow restarting +--source include/not_embedded.inc + +create table t1 (a int not null, d varchar(15) not null, b +varchar(198) not null, c char(156), +fulltext ftsic(c)) engine=InnoDB +row_format=redundant; + +insert into t1 values(123, 'abcdef', 'jghikl', 'mnop'); +insert into t1 values(456, 'abcdef', 'jghikl', 'mnop'); +insert into t1 values(789, 'abcdef', 'jghikl', 'mnop'); +insert into t1 values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; + +SET GLOBAL innodb_file_per_table=OFF; +create table t2 (a int not null, d varchar(15) not null, b +varchar(198) not null, c char(156), fulltext ftsic(c)) engine=InnoDB +row_format=redundant; + +insert into t2 select * from t1; + +create table t3 (a int not null, d varchar(15) not null, b varchar(198), +c varchar(150), index k1(c(99), b(56)), index k2(b(5), c(10))) engine=InnoDB +row_format=redundant; + +insert into t3 values(444, 'dddd', 'bbbbb', 'aaaaa'); +insert into t3 values(555, 'eeee', 'ccccc', 'aaaaa'); + +# read-only restart requires the change buffer to be empty; therefore we +# do a slow shutdown. +SET GLOBAL innodb_fast_shutdown=0; +--let $restart_parameters = --innodb-read-only +--source include/restart_mysqld.inc + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t3; + +--error ER_OPEN_AS_READONLY +TRUNCATE TABLE t1; +--error ER_OPEN_AS_READONLY +TRUNCATE TABLE t2; +--error ER_OPEN_AS_READONLY +TRUNCATE TABLE t3; + +--let $restart_parameters = +--source include/restart_mysqld.inc + +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/suite/innodb/t/table_flags.test b/mysql-test/suite/innodb/t/table_flags.test new file mode 100644 index 00000000000..4d3afcb2dec --- /dev/null +++ b/mysql-test/suite/innodb/t/table_flags.test @@ -0,0 +1,190 @@ +--source include/innodb_page_size.inc +# Embedded server tests do not support restarting +--source include/not_embedded.inc + +--disable_query_log +call mtr.add_suppression("InnoDB: New log files created, LSN="); +call mtr.add_suppression("InnoDB: Creating foreign key constraint system tables"); +call mtr.add_suppression("InnoDB: Error: .*innodb_table_stats. not found"); +call mtr.add_suppression("InnoDB: Table '.test.\\..t[cdzp].' in InnoDB data dictionary has unknown type (81|f21|8a1|3023)"); +call mtr.add_suppression("InnoDB: Cannot open table test/t[cdzp] from the internal data dictionary of InnoDB"); +call mtr.add_suppression("InnoDB: Error: table 'test/t[cdzp]'"); +FLUSH TABLES; +--enable_query_log + +let INNODB_PAGE_SIZE=`select @@innodb_page_size`; +let MYSQLD_DATADIR=`select @@datadir`; + +let bugdir= $MYSQLTEST_VARDIR/tmp/table_flags; +--mkdir $bugdir +--let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err + +--let $d=--innodb-data-home-dir=$bugdir --innodb-log-group-home-dir=$bugdir +--let $d=$d --innodb-data-file-path=ibdata1:10M:autoextend +--let $d=$d --innodb-undo-tablespaces=0 +--let $restart_parameters=$d --innodb-stats-persistent=0 --innodb-file-format=1 +--source include/restart_mysqld.inc + +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE tr(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +CREATE TABLE tc(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPACT; +CREATE TABLE td(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +CREATE TABLE tz(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPRESSED +KEY_BLOCK_SIZE=1; +# PAGE_COMPRESSED is supported starting with MariaDB 10.1.0 +--error ER_UNKNOWN_OPTION +CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC +PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9; +CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC; + +--source include/shutdown_mysqld.inc +--perl +use strict; +my $ps= $ENV{INNODB_PAGE_SIZE}; +my $file= "$ENV{bugdir}/ibdata1"; +open(FILE, "+<", $file) || die "Unable to open $file\n"; +# Read DICT_HDR_TABLES, the root page number of CLUST_IND (SYS_TABLES.NAME). +sysseek(FILE, 7*$ps+38+32, 0) || die "Unable to seek $file"; +die "Unable to read $file" unless sysread(FILE, $_, 4) == 4; +my $sys_tables_root = unpack("N", $_); +my $page; +print "SYS_TABLES clustered index root page ($sys_tables_root):\n"; +sysseek(FILE, $sys_tables_root*$ps, 0) || die "Unable to seek $file"; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +open(BACKUP, ">$ENV{bugdir}/sys_tables.bin") || die "Unable to open backup\n"; +syswrite(BACKUP, $page, $ps)==$ps || die "Unable to write backup\n"; +close(BACKUP) || die "Unable to close backup\n"; +print "N_RECS=", unpack("n", substr($page,38+16,2)); +print "; LEVEL=", unpack("n", substr($page,38+26,2)); +print "; INDEX_ID=0x", unpack("H*", substr($page,38+28,8)), "\n"; +my @fields=("NAME","DB_TRX_ID","DB_ROLL_PTR", + "ID","N_COLS","TYPE","MIX_ID","MIX_LEN","CLUSTER_NAME","SPACE"); +for (my $offset= 0x65; $offset; + $offset= unpack("n", substr($page,$offset-2,2))) +{ + print "header=0x", unpack("H*",substr($page,$offset-6,6)), " ("; + my $n_fields= unpack("n", substr($page,$offset-4,2)) >> 1 & 0x3ff; + my $start= 0; + my $name; + for (my $i= 0; $i < $n_fields; $i++) { + my $end= unpack("C", substr($page, $offset-7-$i, 1)); + print ",\n " if $i; + print "$fields[$i]="; + if ($end & 0x80) { + print "NULL(", ($end & 0x7f) - $start, " bytes)" + } elsif ($n_fields > 1 && $i == 0) { + $name= substr($page,$offset+$start,$end-$start); + print "'$name'" + } else { + print "0x", unpack("H*", substr($page,$offset+$start,$end-$start)) + } + # Corrupt SYS_TABLES.TYPE + if ($i == 5) + { + my $flags= 0; + if ($name eq 'test/tr') { +# $flags= 0x40 # DATA_DIR mismatch causes 10.0 crash! + } elsif ($name eq 'test/tc') { + $flags= 0x80 # 10.1 PAGE_COMPRESSED + } elsif ($name eq 'test/td') { + $flags= 0xf00 # 10.1 PAGE_COMPRESSION_LEVEL=15 (0..9 is valid) + } elsif ($name eq 'test/tz') { + $flags= 0x3000 # 10.1 ATOMIC_WRITES=3 (0..2 is valid) + } elsif ($name eq 'test/tp') { + $flags= 0x880 # 10.1 PAGE_COMPRESSED, PAGE_COMPRESSION_LEVEL=8 + # (in 10.1, this leaves PAGE_COMPRESSION_LEVEL=1 + # without PAGE_COMPRESSED, which should be invalid) + } + + substr($page,$offset+$start,$end-$start)= pack( + "N", $flags ^ + unpack("N", substr($page,$offset+$start,$end-$start))) + if $flags; + } + # Corrupt SYS_TABLES.MIX_LEN (ignored for ROW_FORMAT=REDUNDANT) + if ($i == 7 && $name eq 'test/tr') + { + substr($page,$offset+$start,$end-$start)= chr(255) x 4; + } + $start= $end & 0x7f; + } + print ")\n"; +} +substr($page,0,4)=pack("N",0xdeadbeef); +substr($page,$ps-8,4)=pack("N",0xdeadbeef); +sysseek(FILE, $sys_tables_root*$ps, 0) || die "Unable to seek $file"; +syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; +close(FILE) || die "Unable to close $file\n"; +EOF +--source include/start_mysqld.inc + +SHOW CREATE TABLE tr; +INSERT INTO tr VALUES(42); +--error ER_NO_SUCH_TABLE_IN_ENGINE +SHOW CREATE TABLE tc; +--error ER_NO_SUCH_TABLE_IN_ENGINE +SHOW CREATE TABLE td; +--error ER_NO_SUCH_TABLE_IN_ENGINE +SHOW CREATE TABLE tz; +--error ER_NO_SUCH_TABLE_IN_ENGINE +SHOW CREATE TABLE tp; + +--source include/shutdown_mysqld.inc + +let SEARCH_RANGE= -50000; +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +--let SEARCH_PATTERN= InnoDB: Table '.test.\..t[cdzp].' in InnoDB data dictionary has unknown type (81|f21|8a1|3023) +--source include/search_pattern_in_file.inc +--let SEARCH_PATTERN= InnoDB: Cannot open table test/t[cdzp] from the internal data dictionary of InnoDB +--source include/search_pattern_in_file.inc + +# Restore the backup of the corrupted SYS_TABLES clustered index root page +--perl +use strict; +my $ps= $ENV{INNODB_PAGE_SIZE}; +my $file= "$ENV{bugdir}/ibdata1"; +open(FILE, "+<", $file) || die "Unable to open $file\n"; +open(BACKUP, "<$ENV{bugdir}/sys_tables.bin") || die "Unable to open backup\n"; +# Read DICT_HDR_TABLES, the root page number of CLUST_IND (SYS_TABLES.NAME). +sysseek(FILE, 7*$ps+38+32, 0) || die "Unable to seek $file"; +die "Unable to read $file\n" unless sysread(FILE, $_, 4) == 4; +my $sys_tables_root = unpack("N", $_); +print "Restoring SYS_TABLES clustered index root page ($sys_tables_root)\n"; +sysseek(FILE, $sys_tables_root*$ps, 0) || die "Unable to seek $file"; +die "Unable to read backup\n" unless sysread(BACKUP, $_, $ps) == $ps; +die "Unable to restore backup\n" unless syswrite(FILE, $_, $ps) == $ps; +close(BACKUP); +close(FILE) || die "Unable to close $file\n"; +EOF +--source include/start_mysqld.inc + +SHOW CREATE TABLE tr; +SHOW CREATE TABLE tc; +SHOW CREATE TABLE td; +SHOW CREATE TABLE tz; +SHOW CREATE TABLE tp; + +BEGIN; +INSERT INTO tr VALUES(1); +INSERT INTO tc VALUES(1); +INSERT INTO td VALUES(1); +INSERT INTO tz VALUES(1); +INSERT INTO tp VALUES(1); +ROLLBACK; + +SELECT * FROM tr; +SELECT * FROM tc; +SELECT * FROM td; +SELECT * FROM tz; +SELECT * FROM tp; + +DROP TABLE tr,tc,td,tz,tp; + +--source include/shutdown_mysqld.inc + +--let $restart_parameters= +--source include/start_mysqld.inc + +--list_files $bugdir +--remove_files_wildcard $bugdir +--rmdir $bugdir diff --git a/mysql-test/suite/innodb_zip/r/innodb-zip.result b/mysql-test/suite/innodb_zip/r/innodb-zip.result index dbfae3c0630..584a8a5d647 100644 --- a/mysql-test/suite/innodb_zip/r/innodb-zip.result +++ b/mysql-test/suite/innodb_zip/r/innodb-zip.result @@ -1,4 +1,3 @@ -DROP DATABASE IF EXISTS mysqltest_innodb_zip; CREATE DATABASE mysqltest_innodb_zip; USE mysqltest_innodb_zip; SELECT table_name, row_format, data_length, index_length @@ -379,5 +378,4 @@ select @@innodb_file_format_max; @@innodb_file_format_max Barracuda drop table normal_table, zip_table; -USE test; DROP DATABASE mysqltest_innodb_zip; diff --git a/mysql-test/suite/innodb_zip/t/innodb-create-options.test b/mysql-test/suite/innodb_zip/t/innodb-create-options.test index aeb22514bf6..53d739feccb 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-create-options.test +++ b/mysql-test/suite/innodb_zip/t/innodb-create-options.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size_small.inc # Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE # Related bugs; # Bug#54679: ALTER TABLE causes compressed row_format to revert to compact diff --git a/mysql-test/suite/innodb_zip/t/innodb-zip.test b/mysql-test/suite/innodb_zip/t/innodb-zip.test index 1c7c7c8c419..e8ba98f14ce 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-zip.test +++ b/mysql-test/suite/innodb_zip/t/innodb-zip.test @@ -1,9 +1,4 @@ --- source include/have_innodb.inc - - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest_innodb_zip; ---enable_warnings +--source include/innodb_page_size_small.inc CREATE DATABASE mysqltest_innodb_zip; USE mysqltest_innodb_zip; @@ -13,7 +8,6 @@ SELECT table_name, row_format, data_length, index_length let $per_table=`select @@innodb_file_per_table`; let $format=`select @@innodb_file_format`; -let $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; @@ -363,12 +357,9 @@ drop table normal_table, zip_table; -- disable_query_log eval set global innodb_file_format=$format; eval set global innodb_file_per_table=$per_table; -eval set session innodb_strict_mode=$innodb_strict_mode_orig; eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; --enable_query_log -USE test; DROP DATABASE mysqltest_innodb_zip; - diff --git a/mysql-test/suite/innodb_zip/t/innodb_bug36169.test b/mysql-test/suite/innodb_zip/t/innodb_bug36169.test index 6426bd683ae..ac1f0a3fe57 100644 --- a/mysql-test/suite/innodb_zip/t/innodb_bug36169.test +++ b/mysql-test/suite/innodb_zip/t/innodb_bug36169.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size_small.inc # # Bug#36169 create innodb compressed table with too large row size crashed # http://bugs.mysql.com/36169 diff --git a/mysql-test/suite/innodb_zip/t/innodb_bug36172.test b/mysql-test/suite/innodb_zip/t/innodb_bug36172.test index 015f461e532..7b82a7aba33 100644 --- a/mysql-test/suite/innodb_zip/t/innodb_bug36172.test +++ b/mysql-test/suite/innodb_zip/t/innodb_bug36172.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size_small.inc # # Test case for bug 36172 # diff --git a/mysql-test/suite/innodb_zip/t/innodb_bug52745.test b/mysql-test/suite/innodb_zip/t/innodb_bug52745.test index 3c5d79826f0..d84feb3b22d 100644 --- a/mysql-test/suite/innodb_zip/t/innodb_bug52745.test +++ b/mysql-test/suite/innodb_zip/t/innodb_bug52745.test @@ -1,4 +1,4 @@ --- source include/have_innodb.inc +--source include/innodb_page_size_small.inc let $file_format=`select @@innodb_file_format`; let $file_per_table=`select @@innodb_file_per_table`; diff --git a/mysql-test/suite/innodb_zip/t/innodb_bug53591.test b/mysql-test/suite/innodb_zip/t/innodb_bug53591.test index 8bc461719b8..ea1221dd711 100644 --- a/mysql-test/suite/innodb_zip/t/innodb_bug53591.test +++ b/mysql-test/suite/innodb_zip/t/innodb_bug53591.test @@ -1,4 +1,4 @@ --- source include/have_innodb.inc +--source include/innodb_page_size_small.inc let $file_format=`select @@innodb_file_format`; let $file_per_table=`select @@innodb_file_per_table`; diff --git a/mysql-test/suite/innodb_zip/t/innodb_bug56680.test b/mysql-test/suite/innodb_zip/t/innodb_bug56680.test index f592bd16942..700d7e6f0a8 100644 --- a/mysql-test/suite/innodb_zip/t/innodb_bug56680.test +++ b/mysql-test/suite/innodb_zip/t/innodb_bug56680.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size_small.inc # # Bug #56680 InnoDB may return wrong results from a case-insensitive index # diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result new file mode 100644 index 00000000000..6424ba28297 --- /dev/null +++ b/mysql-test/suite/parts/r/longname.result @@ -0,0 +1,37 @@ +set names utf8; +create database mysqltest1; +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +database_name table_name length(table_name) +CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 ( +id int(10) unsigned NOT NULL, +id2 int(10) unsigned NOT NULL, +PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) +SUBPARTITION BY HASH ( id2 ) +SUBPARTITIONS 2 ( +PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB, +PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); +Warnings: +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +database_name table_name length(table_name) +CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé ( +id int(10) unsigned NOT NULL, +id2 int(10) unsigned NOT NULL, +PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) +SUBPARTITION BY HASH ( id2 ) +SUBPARTITIONS 2 ( +PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB, +PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); +ERROR HY000: The path specified for @0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@ is too long. +drop database mysqltest1; diff --git a/mysql-test/suite/parts/r/quoting.result b/mysql-test/suite/parts/r/quoting.result new file mode 100644 index 00000000000..ba6a155e6ac --- /dev/null +++ b/mysql-test/suite/parts/r/quoting.result @@ -0,0 +1,6 @@ +set sql_mode=ansi_quotes; +create table t1 (i int) partition by range (i) (partition flush values less than maxvalue); +set sql_mode=default; +lock tables t1 read local; +unlock tables; +drop table t1; diff --git a/mysql-test/suite/parts/t/longname.test b/mysql-test/suite/parts/t/longname.test new file mode 100644 index 00000000000..0f7378ef8e3 --- /dev/null +++ b/mysql-test/suite/parts/t/longname.test @@ -0,0 +1,32 @@ +source include/have_innodb.inc; +source include/have_partition.inc; +set names utf8; + +create database mysqltest1; +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 ( + id int(10) unsigned NOT NULL, + id2 int(10) unsigned NOT NULL, + PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) + SUBPARTITION BY HASH ( id2 ) + SUBPARTITIONS 2 ( + PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); + +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; + +--error ER_PATH_LENGTH +CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé ( + id int(10) unsigned NOT NULL, + id2 int(10) unsigned NOT NULL, + PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) + SUBPARTITION BY HASH ( id2 ) + SUBPARTITIONS 2 ( + PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); + +drop database mysqltest1; diff --git a/mysql-test/suite/parts/t/quoting.test b/mysql-test/suite/parts/t/quoting.test new file mode 100644 index 00000000000..297896fd9cf --- /dev/null +++ b/mysql-test/suite/parts/t/quoting.test @@ -0,0 +1,10 @@ +# +# MDEV-13017 LOCK TABLE fails with irrelevant error while working with tables affected by ANSI_QUOTES +# +--source include/have_partition.inc +set sql_mode=ansi_quotes; +create table t1 (i int) partition by range (i) (partition flush values less than maxvalue); +set sql_mode=default; +lock tables t1 read local; +unlock tables; +drop table t1; diff --git a/mysql-test/suite/perfschema/t/privilege.test b/mysql-test/suite/perfschema/t/privilege.test index 926d90d6edc..47a68602fa4 100644 --- a/mysql-test/suite/perfschema/t/privilege.test +++ b/mysql-test/suite/perfschema/t/privilege.test @@ -333,4 +333,3 @@ UPDATE performance_schema.setup_consumers SET enabled = 'YES'; UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; - diff --git a/mysql-test/suite/roles/current_role_view-12666.result b/mysql-test/suite/roles/current_role_view-12666.result new file mode 100644 index 00000000000..1d14593be4b --- /dev/null +++ b/mysql-test/suite/roles/current_role_view-12666.result @@ -0,0 +1,103 @@ +CREATE USER has_role@'localhost'; +GRANT ALL PRIVILEGES ON *.* TO has_role@'localhost'; +CREATE ROLE test_role; +GRANT test_role TO has_role@'localhost'; +CREATE USER no_role@'localhost'; +GRANT ALL PRIVILEGES ON *.* TO no_role@'localhost'; +CREATE TABLE view_role_test ( +id int primary key, +role_name varchar(50) +); +INSERT INTO view_role_test VALUES (1, 'test_role'); +# +# Use the same logic for stored procedures. +# +PREPARE prepared_no_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()"; +# +# Creating a view with no CURRENT_ROLE() set and one with CURRENT_ROLE() +# set. Both should produce the same SHOW CREATE VIEW output. +# +CREATE +DEFINER = no_role@localhost +SQL SECURITY INVOKER +VIEW v_view_role_test_no_current_role +AS +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +SHOW CREATE VIEW v_view_role_test_no_current_role; +View Create View character_set_client collation_connection +v_view_role_test_no_current_role CREATE ALGORITHM=UNDEFINED DEFINER=`no_role`@`localhost` SQL SECURITY INVOKER VIEW `v_view_role_test_no_current_role` AS select `view_role_test`.`id` AS `id`,`view_role_test`.`role_name` AS `role_name` from `view_role_test` where (`view_role_test`.`role_name` = current_role()) latin1 latin1_swedish_ci +# +# No values should be returned +# +EXECUTE prepared_no_current_role; +id role_name +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +id role_name +SELECT * FROM v_view_role_test_no_current_role; +id role_name +# +# Now let's set the role. Create identical views as before. See if +# their behaviour is different. It should not be. +# +SET ROLE test_role; +SELECT CURRENT_USER(); +CURRENT_USER() +root@localhost +SELECT CURRENT_ROLE(); +CURRENT_ROLE() +test_role +# +# Create the VIEW and prepared Statement with a CURRENT_ROLE() set. +# +CREATE +DEFINER = no_role@localhost +SQL SECURITY INVOKER +VIEW v_view_role_test_with_current_role +AS +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +PREPARE prepared_with_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()"; +SHOW CREATE VIEW v_view_role_test_with_current_role; +View Create View character_set_client collation_connection +v_view_role_test_with_current_role CREATE ALGORITHM=UNDEFINED DEFINER=`no_role`@`localhost` SQL SECURITY INVOKER VIEW `v_view_role_test_with_current_role` AS select `view_role_test`.`id` AS `id`,`view_role_test`.`role_name` AS `role_name` from `view_role_test` where (`view_role_test`.`role_name` = current_role()) latin1 latin1_swedish_ci +# +# Values should be returned for all select statements as we do have +# a CURRENT_ROLE() active; +# +EXECUTE prepared_no_current_role; +id role_name +1 test_role +EXECUTE prepared_with_current_role; +id role_name +1 test_role +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +id role_name +1 test_role +SELECT * FROM v_view_role_test_no_current_role; +id role_name +1 test_role +SELECT * FROM v_view_role_test_with_current_role; +id role_name +1 test_role +SET ROLE NONE; +# +# No values should be returned for all select statements as we do not have +# a CURRENT_ROLE() active; +# +EXECUTE prepared_no_current_role; +id role_name +EXECUTE prepared_with_current_role; +id role_name +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +id role_name +SELECT * FROM v_view_role_test_no_current_role; +id role_name +SELECT * FROM v_view_role_test_with_current_role; +id role_name +DROP USER has_role@'localhost'; +DROP USER no_role@'localhost'; +DROP ROLE test_role; +DROP table view_role_test; +DROP VIEW v_view_role_test_no_current_role; +DROP VIEW v_view_role_test_with_current_role; +DROP PREPARE prepared_no_current_role; +DROP PREPARE prepared_with_current_role; diff --git a/mysql-test/suite/roles/current_role_view-12666.test b/mysql-test/suite/roles/current_role_view-12666.test new file mode 100644 index 00000000000..32039ffef07 --- /dev/null +++ b/mysql-test/suite/roles/current_role_view-12666.test @@ -0,0 +1,102 @@ +# +# MDEV-12666 CURRENT_ROLE() does not work in a view +# +--source include/not_embedded.inc + +CREATE USER has_role@'localhost'; +GRANT ALL PRIVILEGES ON *.* TO has_role@'localhost'; + +CREATE ROLE test_role; +GRANT test_role TO has_role@'localhost'; + +CREATE USER no_role@'localhost'; +GRANT ALL PRIVILEGES ON *.* TO no_role@'localhost'; + +CREATE TABLE view_role_test ( + id int primary key, + role_name varchar(50) + ); + +INSERT INTO view_role_test VALUES (1, 'test_role'); + +--echo # +--echo # Use the same logic for stored procedures. +--echo # +PREPARE prepared_no_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()"; + +--echo # +--echo # Creating a view with no CURRENT_ROLE() set and one with CURRENT_ROLE() +--echo # set. Both should produce the same SHOW CREATE VIEW output. +--echo # +CREATE +DEFINER = no_role@localhost +SQL SECURITY INVOKER +VIEW v_view_role_test_no_current_role +AS +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); + +SHOW CREATE VIEW v_view_role_test_no_current_role; + + +--echo # +--echo # No values should be returned +--echo # +EXECUTE prepared_no_current_role; +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +SELECT * FROM v_view_role_test_no_current_role; + +--echo # +--echo # Now let's set the role. Create identical views as before. See if +--echo # their behaviour is different. It should not be. +--echo # +SET ROLE test_role; + +SELECT CURRENT_USER(); +SELECT CURRENT_ROLE(); + +--echo # +--echo # Create the VIEW and prepared Statement with a CURRENT_ROLE() set. +--echo # +CREATE +DEFINER = no_role@localhost +SQL SECURITY INVOKER +VIEW v_view_role_test_with_current_role +AS +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); + +PREPARE prepared_with_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()"; + +SHOW CREATE VIEW v_view_role_test_with_current_role; + + +--echo # +--echo # Values should be returned for all select statements as we do have +--echo # a CURRENT_ROLE() active; +--echo # +EXECUTE prepared_no_current_role; +EXECUTE prepared_with_current_role; +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +SELECT * FROM v_view_role_test_no_current_role; +SELECT * FROM v_view_role_test_with_current_role; + +SET ROLE NONE; +--echo # +--echo # No values should be returned for all select statements as we do not have +--echo # a CURRENT_ROLE() active; +--echo # +EXECUTE prepared_no_current_role; +EXECUTE prepared_with_current_role; +SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); +SELECT * FROM v_view_role_test_no_current_role; +SELECT * FROM v_view_role_test_with_current_role; + + +DROP USER has_role@'localhost'; +DROP USER no_role@'localhost'; +DROP ROLE test_role; + +DROP table view_role_test; +DROP VIEW v_view_role_test_no_current_role; +DROP VIEW v_view_role_test_with_current_role; +DROP PREPARE prepared_no_current_role; +DROP PREPARE prepared_with_current_role; diff --git a/mysql-test/suite/roles/show_create_database-10463.result b/mysql-test/suite/roles/show_create_database-10463.result new file mode 100644 index 00000000000..1bf14933966 --- /dev/null +++ b/mysql-test/suite/roles/show_create_database-10463.result @@ -0,0 +1,65 @@ +drop database if exists db; +Warnings: +Note 1008 Can't drop database 'db'; database doesn't exist +create role r1; +create user beep@'%'; +create database db; +create table db.t1 (i int); +create table db.t2 (b int); +grant select on db.* to r1; +grant r1 to beep@'%'; +show databases; +Database +information_schema +test +show create database db; +ERROR 42000: Access denied for user 'beep'@'localhost' to database 'db' +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; +table_schema table_name +set role r1; +show databases; +Database +db +information_schema +test +show create database db; +Database Create Database +db CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; +table_schema table_name +db t1 +db t2 +create role r2; +create user beep2@'%'; +grant update on db.* to r2; +grant r2 to beep2; +show databases; +Database +information_schema +test +show create database db; +ERROR 42000: Access denied for user 'beep2'@'localhost' to database 'db' +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; +table_schema table_name +set role r2; +show databases; +Database +db +information_schema +test +show create database db; +Database Create Database +db CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; +table_schema table_name +db t1 +db t2 +drop database db; +drop role r1; +drop user beep; +drop role r2; +drop user beep2; diff --git a/mysql-test/suite/roles/show_create_database-10463.test b/mysql-test/suite/roles/show_create_database-10463.test new file mode 100644 index 00000000000..2d921629c10 --- /dev/null +++ b/mysql-test/suite/roles/show_create_database-10463.test @@ -0,0 +1,55 @@ +source include/not_embedded.inc; + +drop database if exists db; + +create role r1; +create user beep@'%'; + +create database db; +create table db.t1 (i int); +create table db.t2 (b int); +grant select on db.* to r1; +grant r1 to beep@'%'; + +--connect (con1,localhost,beep,,) +show databases; +--error ER_DBACCESS_DENIED_ERROR +show create database db; +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; + +set role r1; +show databases; +show create database db; +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; + + +connection default; +create role r2; +create user beep2@'%'; + +grant update on db.* to r2; +grant r2 to beep2; +--connect (con2,localhost,beep2,,) +show databases; +--error ER_DBACCESS_DENIED_ERROR +show create database db; +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; + +set role r2; +show databases; + +show create database db; +select table_schema, table_name from information_schema.tables +where table_schema = 'db'; + + +connection default; + +drop database db; +drop role r1; +drop user beep; +drop role r2; +drop user beep2; diff --git a/mysql-test/suite/rpl/r/circular_serverid0.result b/mysql-test/suite/rpl/r/circular_serverid0.result new file mode 100644 index 00000000000..899b4bd343b --- /dev/null +++ b/mysql-test/suite/rpl/r/circular_serverid0.result @@ -0,0 +1,30 @@ +include/rpl_init.inc [topology=1->2->1] +include/rpl_connect.inc [creating M4] +include/rpl_connect.inc [creating M2] +SET @old_debug= @@global.debug; +STOP SLAVE; +SET GLOBAL debug_dbug= "+d,dbug.rows_events_to_delay_relay_logging"; +START SLAVE IO_THREAD; +include/wait_for_slave_io_to_start.inc +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb; +INSERT INTO `t1` VALUES (null, repeat('a', 1024)), (null, repeat('b', 1024)); +SET debug_sync='now WAIT_FOR start_sql_thread'; +START SLAVE SQL_THREAD; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb +SET debug_sync='now SIGNAL go_on_relay_logging'; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Xid # # COMMIT /* XID */ +drop table t1; +SET GLOBAL debug_dbug= @old_debug; +SET debug_sync='RESET'; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_reset_slave_fail.result b/mysql-test/suite/rpl/r/rpl_reset_slave_fail.result new file mode 100644 index 00000000000..205e8fe428b --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_reset_slave_fail.result @@ -0,0 +1,29 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (c1 INT); +INSERT INTO t1 (c1) VALUES (1); +include/stop_slave_sql.inc +FLUSH LOGS; +FLUSH LOGS; +INSERT INTO t1 (c1) VALUES (2); +include/sync_slave_io_with_master.inc +call mtr.add_suppression("File '.*slave-relay-bin."); +call mtr.add_suppression("Could not open log file"); +call mtr.add_suppression("Failed to open the relay log"); +call mtr.add_suppression("Failed to initialize the master info structure"); +include/rpl_stop_server.inc [server_number=2] +# Removing file(s) +include/rpl_start_server.inc [server_number=2] +START SLAVE; +ERROR HY000: Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log +START SLAVE; +ERROR HY000: Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log +RESET SLAVE; +DROP TABLE t1; +START SLAVE UNTIL MASTER_LOG_FILE= 'MASTER_LOG_FILE', MASTER_LOG_POS= MASTER_LOG_POS;; +include/wait_for_slave_sql_to_stop.inc +include/stop_slave_io.inc +include/start_slave.inc +include/diff_tables.inc [master:t1, slave:t1] +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/circular_serverid0.cnf b/mysql-test/suite/rpl/t/circular_serverid0.cnf new file mode 100644 index 00000000000..277aac2869b --- /dev/null +++ b/mysql-test/suite/rpl/t/circular_serverid0.cnf @@ -0,0 +1,30 @@ +!include ../my.cnf + +[mysqld.1] +gtid-domain-id=4 +server-id=4 +# +log-slave-updates +slave-parallel-threads=0 +gtid-strict-mode=1 +gtid-ignore-duplicates=1 + +# +# Max-size row events to minimum with the idea to create +# a number of Rows_log_event per Query. +# +binlog-row-event-max-size=1024 + +[mysqld.2] +gtid-domain-id=2 +server-id=2 +# +log-slave-updates +slave-parallel-threads=0 +gtid-strict-mode=1 +gtid-ignore-duplicates=1 +binlog-row-event-max-size=1024 +# The slave will be initialized with a @@global.dbug-var value +skip-slave-start=1 + + diff --git a/mysql-test/suite/rpl/t/circular_serverid0.test b/mysql-test/suite/rpl/t/circular_serverid0.test new file mode 100644 index 00000000000..20ad58e2c52 --- /dev/null +++ b/mysql-test/suite/rpl/t/circular_serverid0.test @@ -0,0 +1,104 @@ +# +# Testing chain/circular replication scenario of MDEV-9670 +# The effect of the bug was that we got a commit with a GTID with server_id +# + +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--let $rpl_topology= 1->2->1 +--source include/rpl_init.inc + +--let $rpl_connection_name= M4 +--let $rpl_server_number= 1 +--source include/rpl_connect.inc + +--let $rpl_connection_name= M2 +--let $rpl_server_number= 2 +--source include/rpl_connect.inc + +# The parameter reflects binlog-row-event-max-size @cnf. +--let $row_size=1024 + +SET @old_debug= @@global.debug; + +--connection M2 +STOP SLAVE; +SET GLOBAL debug_dbug= "+d,dbug.rows_events_to_delay_relay_logging"; +START SLAVE IO_THREAD; +--source include/wait_for_slave_io_to_start.inc + +--connection M2 +# This query also creates a Gtid event group whose Gtid will remain in +# ignored status for too long causing a following group split. + +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb; +--sync_slave_with_master M4 + +# This INSERT will be logged as two Write_log events which the buggy +# slave applier would split. + +--connection M4 +eval INSERT INTO `t1` VALUES (null, repeat('a', $row_size)), (null, repeat('b', $row_size)); + +# START M2 IO thread and wait for its signal to follow with the SQL +# thread start. At this moment the SQL thread shall be having 2 and +# "half" groups to execute. The "hafl" one would be committed by the +# buggy applier after which the IO is released to queue the rest of +# the 3rd group which the SQL thread commits separately to complete +# the split. + +--connection M2 + +# wait for IO signal to start the SQL thread. IO will be hanging upon that. +SET debug_sync='now WAIT_FOR start_sql_thread'; + +# Now the slave server has relay log whose last group is incomplete. +# An unfixed slave server would go to "insert" a "fake" +# Gtid_list_log_event event which actually would commit the incomplete +# group. However before to actual commit do_apply_event() hits some assert. +# In the fixed server the fake Gtid_list_log_event is *not* inserted +# in the middle of a group. +START SLAVE SQL_THREAD; + +# Sleep for a little time to give SQL thread a chance to commit while +# the IO thread is hanging (see +# DBUG_EXECUTE_IF("dbug.rows_events_to_delay_relay_logging"...) in +# queue_event). Alternatively to reproduce the case when buggy slave +# wait for the 1st group commit + +#--let $count= 1 +#--let $table= t1 +#--source include/wait_until_rows_count.inc + +--sleep 2 + +# Demonstrate either no split group in the correct slave or the 1nd +# group in the buggy one +--source include/show_binlog_events.inc + +# Release the IO thread +SET debug_sync='now SIGNAL go_on_relay_logging'; + +# Sync servers +--sync_slave_with_master M4 +--connection M4 +--sync_slave_with_master M2 +--connection M2 + +# Demonstrate replication goes correctly not to create any split, or +# the 2nd group in the buggy slave +--source include/show_binlog_events.inc + +# +# Cleanup +# +--connection M4 +drop table t1; + +--connection M2 +SET GLOBAL debug_dbug= @old_debug; +SET debug_sync='RESET'; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_reset_slave_fail.test b/mysql-test/suite/rpl/t/rpl_reset_slave_fail.test new file mode 100644 index 00000000000..021dc76d50c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_reset_slave_fail.test @@ -0,0 +1,91 @@ +############################################################################### +# Bug#24901077: RESET SLAVE ALL DOES NOT ALWAYS RESET SLAVE +# +# Problem: +# ======= +# If you have a relay log index file that has ended up with +# some relay log files that do not exists, then RESET SLAVE +# ALL is not enough to get back to a clean state. +############################################################################### +# Remove all slave-relay-bin.0* files (do not remove slave-relay-bin.index) +# During server restart rli initialization will fail as there are no +# relay logs. In case of bug RESET SLAVE will not do the required clean up +# as rli is not inited and subsequent START SLAVE will fail. +# Disable "Warning 1612 Being purged log ./slave-relay-bin.0* was not found" +# because it is different on Unix and Windows systems. + +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +--connection master +CREATE TABLE t1 (c1 INT); +INSERT INTO t1 (c1) VALUES (1); +--sync_slave_with_master + +--connection slave +--source include/stop_slave_sql.inc +--let $MYSQLD_SLAVE_DATADIR= `select @@datadir` + +--connection master +# Generate more relay logs on slave. +FLUSH LOGS; +FLUSH LOGS; +INSERT INTO t1 (c1) VALUES (2); + +--source include/sync_slave_io_with_master.inc +call mtr.add_suppression("File '.*slave-relay-bin."); +call mtr.add_suppression("Could not open log file"); +call mtr.add_suppression("Failed to open the relay log"); +call mtr.add_suppression("Failed to initialize the master info structure"); + +# Stop slave +--let $rpl_server_number= 2 +--source include/rpl_stop_server.inc + +# Delete file(s) +--echo # Removing $remove_pattern file(s) +--let $remove_pattern= slave-relay-bin.0* +--remove_files_wildcard $MYSQLD_SLAVE_DATADIR $remove_pattern + +# Start slave +--let $rpl_server_number= 2 +--source include/rpl_start_server.inc + +# Start slave must fail because of the removed file(s). +--error ER_MASTER_INFO +START SLAVE; + +# Try a second time, it must fail again. +--error ER_MASTER_INFO +START SLAVE; + +# Retrieve master executed position before reset slave. +--let $master_exec_file= query_get_value("SHOW SLAVE STATUS", Relay_Master_Log_File, 1) +--let $master_exec_pos= query_get_value("SHOW SLAVE STATUS", Exec_Master_Log_Pos, 1) + +# Reset slave. +# Disable "Warning 1612 Being purged log ./slave-relay-bin.0* was not found" +# because it is different on Unix and Windows systems. +--disable_warnings +RESET SLAVE; +--enable_warnings +DROP TABLE t1; +--replace_result $master_exec_file MASTER_LOG_FILE $master_exec_pos MASTER_LOG_POS +--eval START SLAVE UNTIL MASTER_LOG_FILE= '$master_exec_file', MASTER_LOG_POS= $master_exec_pos; +--source include/wait_for_slave_sql_to_stop.inc +--source include/stop_slave_io.inc + +# Start slave. +--source include/start_slave.inc + +--connection master +--sync_slave_with_master +# Check consistency. +--let $diff_tables= master:t1, slave:t1 +--source include/diff_tables.inc + +# Cleanup +--connection master +DROP TABLE t1; +--sync_slave_with_master +--source include/rpl_end.inc diff --git a/mysql-test/suite/storage_engine/type_spatial.result b/mysql-test/suite/storage_engine/type_spatial.result index 6c2f5a4d50f..71211bd2057 100644 --- a/mysql-test/suite/storage_engine/type_spatial.result +++ b/mysql-test/suite/storage_engine/type_spatial.result @@ -350,8 +350,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon; fid Area(g) diff --git a/mysql-test/suite/storage_engine/type_spatial_indexes.result b/mysql-test/suite/storage_engine/type_spatial_indexes.result index 93bb4c617e5..b2e00463606 100644 --- a/mysql-test/suite/storage_engine/type_spatial_indexes.result +++ b/mysql-test/suite/storage_engine/type_spatial_indexes.result @@ -350,8 +350,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon; fid Area(g) @@ -1050,8 +1050,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon; fid Area(g) diff --git a/mysql-test/suite/sys_vars/r/innodb_sched_priority_cleaner_basic.result b/mysql-test/suite/sys_vars/r/innodb_sched_priority_cleaner_basic.result index 1183fb27732..f2bfaf2ed61 100644 --- a/mysql-test/suite/sys_vars/r/innodb_sched_priority_cleaner_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_sched_priority_cleaner_basic.result @@ -1,4 +1,4 @@ -SET @start_value = @@GLOBAL.innodb_sched_priority_cleaner; +SET GLOBAL innodb_sched_priority_cleaner=39; SELECT @@GLOBAL.innodb_sched_priority_cleaner; @@GLOBAL.innodb_sched_priority_cleaner 19 diff --git a/mysql-test/suite/sys_vars/t/innodb_sched_priority_cleaner_basic.test b/mysql-test/suite/sys_vars/t/innodb_sched_priority_cleaner_basic.test index b2382fd7844..2c2037f167f 100644 --- a/mysql-test/suite/sys_vars/t/innodb_sched_priority_cleaner_basic.test +++ b/mysql-test/suite/sys_vars/t/innodb_sched_priority_cleaner_basic.test @@ -4,7 +4,15 @@ # A dynamic, global variable -SET @start_value = @@GLOBAL.innodb_sched_priority_cleaner; +# Test in read-only mode +--let $restart_parameters= --innodb-read-only +--source include/restart_mysqld.inc +--let $restart_parameters= + +# This has no actual effect in innodb_read_only mode +SET GLOBAL innodb_sched_priority_cleaner=39; + +--source include/restart_mysqld.inc # Default value SELECT @@GLOBAL.innodb_sched_priority_cleaner; diff --git a/mysql-test/t/alter_table_online.test b/mysql-test/t/alter_table_online.test index d9c2a2c4d4f..9062ecba35a 100644 --- a/mysql-test/t/alter_table_online.test +++ b/mysql-test/t/alter_table_online.test @@ -121,3 +121,168 @@ drop table t1; create table t1 (a int) partition by hash(a) partitions 2; alter online table t1 modify a int comment 'test'; drop table t1; + +--echo # +--echo # MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +--echo # +# +# ALTER to the same [VAR]BINARY type +# +CREATE TABLE t1 (a BINARY(10)); +ALTER TABLE t1 MODIFY a BINARY(10), ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARBINARY(10)); +ALTER TABLE t1 MODIFY a VARBINARY(10), ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER to the same BLOB variant +# +CREATE TABLE t1 (a TINYBLOB); +ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGBLOB); +ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER to the same [VAR]CHAR type +# +CREATE TABLE t1 (a CHAR(10)); +ALTER TABLE t1 MODIFY a CHAR(10), ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10)); +ALTER TABLE t1 MODIFY a VARCHAR(10), ALGORITHM=INPLACE; +DROP TABLE t1; + + +# +# ALTER to the same TEXT variant +# +CREATE TABLE t1 (a TINYTEXT); +ALTER TABLE t1 MODIFY a TINYTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT); +ALTER TABLE t1 MODIFY a MEDIUMTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +ALTER TABLE t1 MODIFY a TEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT); +ALTER TABLE t1 MODIFY a LONGTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER from a non-binary to a binary collation +# +CREATE TABLE t1 (a CHAR(10)); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10)); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYTEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER from a binary to a non-binary collation +# +CREATE TABLE t1 (a CHAR(10) COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYTEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER from a non-binary collation to another non-binary collation +# +CREATE TABLE t1 (a CHAR(10) COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYTEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +# End of 10.0 tests diff --git a/mysql-test/t/binary_to_hex.test b/mysql-test/t/binary_to_hex.test new file mode 100644 index 00000000000..8312a246d0c --- /dev/null +++ b/mysql-test/t/binary_to_hex.test @@ -0,0 +1,76 @@ +# === Purpose === +# The purpose of this test case is to make +# sure that the binary data in tables is printed +# as hex when the option binary-as-hex is enabled. +# +# === Related bugs and/or worklogs === +# Bug #25340722 - PRINT BINARY DATA AS HEX IN THE MYSQL +# CLIENT (CONTRIBUTION) +# + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc +--source include/not_embedded.inc + +USE test; +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (c1 TINYBLOB, + c2 BLOB, + c3 MEDIUMBLOB, + c4 LONGBLOB, + c5 TEXT, + c6 BIT(1), + c7 CHAR, + c8 VARCHAR(10), + c9 GEOMETRY) CHARACTER SET = binary; + +SHOW CREATE TABLE t1; + +INSERT INTO t1 VALUES ('tinyblob-text readable', 'blob-text readable', + 'mediumblob-text readable', 'longblob-text readable', + 'text readable', b'1', 'c', 'variable', + POINT(1, 1)); + +CREATE TABLE t2(id int, `col1` binary(10),`col2` blob); + +SHOW CREATE TABLE t2; + +INSERT INTO t2 VALUES (1, X'AB1234', X'123ABC'), (2, X'DE1234', X'123DEF'); + +--echo #Print the table contents when binary-as-hex option is off. +--replace_column 6 # 9 # +SELECT * FROM t1; + +--replace_column 2 # 3 # +SELECT * FROM t2; + +--echo #Print the table contents after turning on the binary-as-hex option +--echo +--echo #Print the table contents in tab format +--echo +--exec $MYSQL test --binary-as-hex -e "SELECT * FROM t1; SELECT * FROM t2;" +--echo +--echo #Print the table contents in table format +--echo +--exec $MYSQL test --binary-as-hex --table -e "SELECT * FROM t1; SELECT * FROM t2 WHERE col2=0x123ABC;" +--echo +--echo #Print the table contents vertically +--echo +--exec $MYSQL test --binary-as-hex --vertical -e "SELECT * FROM t1;" +--echo +--echo #Print the table contents in xml format +--echo +--exec $MYSQL test --binary-as-hex --xml -e "SELECT * FROM t1; SELECT * FROM t2;" +--echo +--echo #Print the table contents in html format +--echo +--exec $MYSQL test --binary-as-hex --html -e "SELECT * FROM t1; SELECT * FROM t2;" + +#Cleanup +DROP TABLE t1, t2; + +# Wait till all disconnects are completed + --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test index f92b7c5b148..5ab736cee15 100644 --- a/mysql-test/t/bootstrap.test +++ b/mysql-test/t/bootstrap.test @@ -59,6 +59,12 @@ drop table t1; SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' and SUPPORT='YES'; +# +# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init +# +--error 1 +--exec $MYSQLD_BOOTSTRAP_CMD --myisam_recover_options=NONE + --echo End of 5.5 tests --source include/not_windows_embedded.inc diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test index 10b4ac6f0e7..a00574b6cba 100644 --- a/mysql-test/t/count_distinct.test +++ b/mysql-test/t/count_distinct.test @@ -107,3 +107,19 @@ create view v1 as select * from t1; select count(distinct i) from v1; drop table t1; drop view v1; + +# +# MDEV-12136 SELECT COUNT(DISTINCT) returns the wrong value when tmp_table_size is limited +# +create table t1 (user_id char(64) character set utf8); +insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17); +set @@tmp_table_size = 1024; +select count(distinct user_id) from t1; +alter table t1 modify user_id char(128) character set utf8; +select count(distinct user_id) from t1; +drop table t1; +set @@tmp_table_size = default; + +# +# End of 5.5 tests +# diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 165d945944e..04156622ab2 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -924,5 +924,21 @@ SELECT 'a','aa'; --echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET NAMES utf8, character_set_connection=ucs2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_utf8'derived_merge=on'; +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SET NAMES utf8, character_set_connection=ucs2; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; + + +--echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/t/engine_error_in_alter-8453.test b/mysql-test/t/engine_error_in_alter-8453.test new file mode 100644 index 00000000000..c4600ec07fe --- /dev/null +++ b/mysql-test/t/engine_error_in_alter-8453.test @@ -0,0 +1,11 @@ +# +# MDEV-8453 Alter table not returning engine errors +# +--source include/have_debug.inc + +create table t1 (a int, b int); +set debug_dbug='+d,external_lock_failure'; +--error ER_GET_ERRMSG +alter table t1 add column c int; +set debug_dbug=''; +drop table t1; diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test index e56d1121808..be573f494a2 100644 --- a/mysql-test/t/func_concat.test +++ b/mysql-test/t/func_concat.test @@ -145,3 +145,94 @@ CALL p1(); DROP PROCEDURE p1; --echo # End of 5.1 tests + + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; + +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('1234567'); +SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1, + CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2 + FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +DROP TABLE t1; + +# Other functions affected by MDEV-10306 + +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('1234567'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT REVERSE(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SOUNDEX(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT WEIGHT_STRING(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT QUOTE(t) t2 FROM t1) sub; +DROP TABLE t1; + +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES(TO_BASE64('abcdefghi')); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FROM_BASE64(t) t2 FROM t1) sub; +DROP TABLE t1; + +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES(HEX('abcdefghi')); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UNHEX(t) t2 FROM t1) sub; +DROP TABLE t1; + +CREATE TABLE t1 (t VARCHAR(30) CHARSET latin1); +INSERT INTO t1 VALUES('test'); +SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +DROP TABLE t1; + + +# Functions not affected by MDEV-10306 +# They only had an unused tmp_value, which was removed. + +CREATE TABLE t1 (t VARCHAR(64) CHARSET latin1); +INSERT INTO t1 VALUES('123456789'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MD5(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FORMAT(t,2) t2 FROM t1) sub; +DROP TABLE t1; + +# Functions not affected by MDEV-10306 +# They already use tmp_value only for internal purposes and +# return the result in the String passed to val_str() + +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT INSERT(t,3,4,'xxx') t2 FROM t1) sub; +DROP TABLE t1; + + +# Functions not affected by MDEV-10306 +# They use this code style: +# String *res= args[0]->val_str(str); +# tmp_value.set(*res, start, end); +# return &tmp_value; + +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LEFT(t,10) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RIGHT(t,10) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SUBSTR(t,1,10) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LTRIM(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RTRIM(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TRIM(t) t2 FROM t1) sub; +DROP TABLE t1; + +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test index ca6e712f45c..785cc63d3f1 100644 --- a/mysql-test/t/func_crypt.test +++ b/mysql-test/t/func_crypt.test @@ -70,3 +70,27 @@ SELECT OLD_PASSWORD(c1), PASSWORD(c1) FROM t1; DROP TABLE t1; --echo End of 5.0 tests + + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +# ENCRYPT() is not affected by MDEV-10306 +# It already uses tmp_value only for internal purposes and +# returns the result in the String passed to val_str() +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT ENCRYPT(t,'aa') t2 FROM t1) sub; +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test index 8e3adad0037..07de4b33271 100644 --- a/mysql-test/t/func_regexp_pcre.test +++ b/mysql-test/t/func_regexp_pcre.test @@ -430,3 +430,22 @@ SELECT CAST(0xE001 AS BINARY) REGEXP @regCheck; --echo # MDEV-12420: Testing recursion overflow --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral,Golf,Hotel,India,Juliet,Kilo,Lima,Mike,StrataL3,November,Oscar,StrataL2,Sand,P3,P4SwitchTest,Arsys,Poppa,ExtensionMgr,Arp,Quebec,Romeo,StrataApiV2,PtReyes,Sierra,SandAcl,Arrow,Artools,BridgeTest,Tango,SandT,PAlaska,Namespace,Agent,Qos,PatchPanel,ProjectReport,Ark,Gimp,Agent,SliceAgent,Arnet,Bgp,Ale,Tommy,Central,AsicPktTestLib,Hsc,SandL3,Abuild,Pca9555,Standby,ControllerDut,CalSys,SandLib,Sb820,PointV2,BfnLib,Evpn,BfnSdk,Sflow,ManagementActive,AutoTest,GatedTest,Bgp,Sand,xinetd,BfnAgentLib,bf-utils,Hello,BfnState,Eos,Artest,Qos,Scd,ThermoMgr,Uniform,EosUtils,Eb,FanController,Central,BfnL3,BfnL2,tcp_wrappers,Victor,Environment,Route,Failover,Whiskey,Xray,Gimp,BfnFixed,Strata,SoCal,XApi,Msrp,XpProfile,tcpdump,PatchPanel,ArosTest,FhTest,Arbus,XpAcl,MacConc,XpApi,telnet,QosTest,Alpha2,BfnVlan,Stp,VxlanControllerTest,MplsAgent,Bravo2,Lanz,BfnMbb,Intf,XCtrl,Unicast,SandTunnel,L3Unicast,Ipsec,MplsTest,Rsvp,EthIntf,StageMgr,Sol,MplsUtils,Nat,Ira,P4NamespaceDut,Counters,Charlie2,Aqlc,Mlag,Power,OpenFlow,Lag,RestApi,BfdTest,strongs,Sfa,CEosUtils,Adt746,MaintenanceMode,MlagDut,EosImage,IpEth,MultiProtocol,Launcher,Max3179,Snmp,Acl,IpEthTest,PhyEee,bf-syslibs,tacc,XpL2,p4-ar-switch,p4-bf-switch,LdpTest,BfnPhy,Mirroring,Phy6,Ptp' REGEXP '^((?!\b(Strata|StrataApi|StrataApiV2)\b).)*$'); + +# +# MDEV-13173 An RLIKE that previously worked on 10.0 now returns "Got error 'pcre_exec: recursion limit of 100 exceeded' from regexp" +# +SELECT CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; + +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); + +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); + +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); diff --git a/mysql-test/t/gis-alter_table_online.test b/mysql-test/t/gis-alter_table_online.test new file mode 100644 index 00000000000..be6d2137e77 --- /dev/null +++ b/mysql-test/t/gis-alter_table_online.test @@ -0,0 +1,82 @@ +--source include/have_innodb.inc +--source include/have_geometry.inc + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +--echo # + +# +# Blob variants to GEOMETRY +# +CREATE TABLE t1 (a TINYBLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGBLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# GEOMETRY to BLOB variants +# + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# Different GEOMETRY types +# + +# Can't do INPLACE from a supertype to a subtype +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a POLYGON, ALGORITHM=INPLACE; +DROP TABLE t1; + +# Ok to do INPLACE from a subtype to a supertype +CREATE TABLE t1 (a POLYGON); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +# Ok to do INPLACE for two equal geometry subtypes +CREATE TABLE t1 (a POLYGON); +ALTER TABLE t1 MODIFY a POLYGON, ALGORITHM=INPLACE; +DROP TABLE t1; + + +--echo # +--echo # End of 10.1 tests +--echo # diff --git a/mysql-test/t/gis-rt-precise.test b/mysql-test/t/gis-rt-precise.test index 4cae10a9076..9c26aa05598 100644 --- a/mysql-test/t/gis-rt-precise.test +++ b/mysql-test/t/gis-rt-precise.test @@ -62,3 +62,25 @@ SELECT fid, AsText(g) FROM t1 WHERE ST_Within(g, DROP TABLE t1; --echo End of 5.5 tests. + +# +# MDEV-12078 Using spatial index changes type from point to geometry. +# +CREATE TABLE t1 ( + coordinate point NOT NULL, + SPATIAL KEY coordinate (coordinate) +) ENGINE=Aria DEFAULT CHARSET=ascii PAGE_CHECKSUM=1; + +SHOW COLUMNS FROM t1; + +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(0 0)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(10 0)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(10 10)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(0 10)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(5 5)")); + +SELECT astext(coordinate) FROM t1 WHERE ST_Intersects(ST_LineFromText("LINESTRING(0 0, 10 0, 10 10, 0 10)"), coordinate); + +SHOW COLUMNS FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index f689902533a..4192a3284b7 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -356,15 +356,15 @@ insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363 36.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439 36.252666,-115.261439 36.247366,-115.247239 36.247066)))')); -# Expected result is 115.31877315203187, but IA64 returns 115.31877315203188 -# due to fused multiply-add instructions. ---replace_result 115.31877315203188 115.31877315203187 +# Expected results are 115.2970604672862 and 36.23335610879993, but IA64 returns +# slightly different values due to fused multiply-add instructions. +--replace_result 115.29706047613604 115.2970604672862 36.23335611157958 36.23335610879993 select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85998; -# Expected result is 36.3310176346905, but IA64 returns 36.3310176346904 +# Expected result is 36.34725218253213, but IA64 returns 36.34725217627852 # due to fused multiply-add instructions. ---replace_result 36.3310176346904 36.3310176346905 -114.87787186923326 -114.87787186923313 36.33101763469053 36.33101763469059 36.33101763469043 36.33101763469059 +--replace_result 36.34725217627852 36.34725218253213 -114.86854470090232 -114.86854472054372 select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85984; @@ -1503,5 +1503,20 @@ DROP VIEW v1; SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; --echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 (x INT, y INT); +INSERT INTO t1 VALUES(0,0); +SELECT LENGTH(t2) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; + + +--echo # --echo # End 10.0 tests --echo # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 8ea2f923eb1..da8d3016942 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1702,6 +1702,44 @@ select a as x from t1 group by x having x > 1; select a from t1 group by a having a > 1; drop table t1; set sql_mode= @save_sql_mode; + +# +# MDEV-7826 Server crashes in Item_subselect::enumerate_field_refs_processor +# +create table t1 (f1 int); +insert into t1 values (5),(9); +create table t2 (f2 int); +insert into t2 values (0),(6); +create table t3 (f3 int); +insert into t3 values (6),(3); +create table t4 (f4 int); +insert into t4 values (1),(0); +--error ER_ILLEGAL_REFERENCE +select +(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7, +(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7)) +from t4; +drop table t1, t2, t3, t4; + +# +# MDEV-13180 Unused left join causes server crash +# +create table t1 (i1 int); +insert into t1 values (1); +create table t2 (i int); +insert into t2 values (2); +select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2); +drop table t1, t2; + + +# +# MDEV-12489 The select stmt may fail due to "having clause is ambiguous" unexpected +# +create table t1 (c1 int, c2 int); +create table t2 (c1 int, c2 int); +select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3; +drop table t1, t2; + # # End of MariaDB 5.5 tests # diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 19c5f64de78..9645d8ad82a 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1801,4 +1801,86 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-11958: LEFT JOIN with stored routine produces incorrect result +--echo # + +CREATE TABLE t (x INT); +INSERT INTO t VALUES(1),(NULL); +CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); + +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE IFNULL(t2.x,0)=0; +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE IFNULL(t2.x,0)=0; +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE f(t2.x,0)=0; +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE f(t2.x,0)=0; + +drop function f; +drop table t; +CREATE TABLE t1 ( + col1 DECIMAL(33,5) NULL DEFAULT NULL, + col2 DECIMAL(33,5) NULL DEFAULT NULL +); + +CREATE TABLE t2 ( + col1 DECIMAL(33,5) NULL DEFAULT NULL, + col2 DECIMAL(33,5) NULL DEFAULT NULL, + col3 DECIMAL(33,5) NULL DEFAULT NULL +); + +INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); +INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); + +DELIMITER |; + +CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) +RETURNS decimal(33,5) +LANGUAGE SQL +DETERMINISTIC +CONTAINS SQL +SQL SECURITY INVOKER +BEGIN + IF p_num IS NULL THEN + RETURN p_return; + ELSE + RETURN p_num; + END IF; +END | + +DELIMITER ;| + +let $q1= +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; + +eval $q1; +eval EXPLAIN EXTENDED $q1; + +let $q2= +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +eval $q2; +eval EXPLAIN EXTENDED $q2; + +DROP FUNCTION f1; + +DROP TABLE t1,t2; + +--echo # end of 5.5 tests + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 263e1103e8b..4403a9d668f 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -618,3 +618,33 @@ EOF --echo --echo End of tests + +# +# MDEV-13187 incorrect backslash parsing in clients +# +create table `a1\``b1` (a int); +show tables; +insert `a1\``b1` values (1),(2); +show create table `a1\``b1`; +--exec $MYSQL_DUMP --compact test +--exec $MYSQL_DUMP test > $MYSQLTEST_VARDIR/tmp/bug.sql +insert `a1\``b1` values (4),(5); +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql +show create table `a1\``b1`; +select * from `a1\``b1`; +drop table `a1\``b1`; + +# same with ansi_quotes +set sql_mode=ansi_quotes; +create table "a1\""b1" (a int); +show tables; +insert "a1\""b1" values (1),(2); +show create table "a1\""b1"; +--exec $MYSQL_DUMP --compact --compatible=postgres test +--exec $MYSQL_DUMP --compatible=postgres test > $MYSQLTEST_VARDIR/tmp/bug.sql +insert "a1\""b1" values (4),(5); +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql +show create table "a1\""b1"; +select * from "a1\""b1"; +drop table "a1\""b1"; +set sql_mode=default; diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 2ef469ba7c5..f8026df8770 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -1,6 +1,7 @@ -- source include/mysql_upgrade_preparation.inc -- source include/have_working_dns.inc -- source include/have_innodb.inc +-- source include/have_partition.inc # # Basic test that we can run mysql_upgrde and that it finds the @@ -166,4 +167,26 @@ SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user' DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; ---echo End of tests +# +# MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars +# +use test; +alter table mysql.innodb_table_stats modify last_update int not null; + +create table extralongname_extralongname_extralongname_extralongname_ext ( + id int(10) unsigned not null, + created_date date not null, + created timestamp not null, + primary key (created,id,created_date) +) engine=innodb stats_persistent=1 default charset=latin1 + partition by range (year(created_date)) + subpartition by hash (month(created_date)) + subpartitions 2 ( + partition p2007 values less than (2008), + partition p2008 values less than (2009) + ); +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +select length(table_name) from mysql.innodb_table_stats; +drop table extralongname_extralongname_extralongname_extralongname_ext; + +--echo End of 10.0 tests diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index e85d793b628..aea4ba6432d 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -2948,11 +2948,17 @@ disconnect $x; # Disconnect the selected connection disconnect $y; --echo $CURRENT_CONNECTION +connection default; +# +# MDEV-13187 incorrect backslash parsing in clients +# +set sql_mode=no_backslash_escapes; +select "foo\""bar"; +set sql_mode=default; --echo End of tests -connection default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 8ea0f2db9b7..7862580d3b0 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -80,6 +80,9 @@ insert into t3 values(1); insert into t4 select * from t3; +--error ER_OPTION_PREVENTS_STATEMENT +create table t3 (a int); + # a non-temp table updated: --error ER_OPTION_PREVENTS_STATEMENT update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ab864803864..43373248834 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6002,11 +6002,17 @@ INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +--error ER_WRONG_GROUP_FIELD +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +--error ER_WRONG_GROUP_FIELD SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); delete from t1; +--error ER_WRONG_GROUP_FIELD +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +--error ER_WRONG_GROUP_FIELD SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; drop view v2; @@ -6021,3 +6027,45 @@ INSERT INTO t1 VALUES ('foo','bar'); SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); DROP TABLE t1; + +--echo # +--echo # MDEV-10146: Wrong result (or questionable result and behavior) +--echo # with aggregate function in uncorrelated SELECT subquery +--echo # +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (f2 int); + +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; + +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; + +INSERT INTO t2 VALUES (4); + +--error ER_SUBQUERY_NO_1_ROW +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +--error ER_SUBQUERY_NO_1_ROW +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; + +drop view v1; +drop table t1,t2; + +# +# MDEV-7828 Assertion `key_read == 0' failed in TABLE::enable_keyread with SELECT SQ and WHERE SQ +# +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +--echo # +--echo # Disable this query till MDEV-13399 is resolved +--echo # +--echo # INSERT t2 VALUES (9); +--echo # --error ER_SUBQUERY_NO_1_ROW +--echo # SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +--echo # +drop table t1, t2; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 9e3ac603ec6..35f2b9588fe 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -507,3 +507,19 @@ SELECT * FROM t1 ON (f3 = t2b.f2) ); DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-12963: min/max optimization optimizing away all tables employed +--echo # for uncorrelated IN subquery used in a disjunct of WHERE +--echo # + +create table t1 (a int, index idx(a)) engine=myisam; +insert into t1 values (4),(7),(1),(3),(9); + +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; +explain +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; + +drop table t1; + + diff --git a/mysql-test/t/subselect_nulls.test b/mysql-test/t/subselect_nulls.test index 4b08e773b17..3e7b2189ed5 100644 --- a/mysql-test/t/subselect_nulls.test +++ b/mysql-test/t/subselect_nulls.test @@ -97,3 +97,9 @@ set optimizer_switch= @tmp_subselect_nulls; drop table x1; drop table x2; + +# +# MDEV-7339 Server crashes in Item_func_trig_cond::val_int +# +select (select 1, 2) in (select 3, 4); +select (select NULL, NULL) in (select 3, 4); diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index 0f2892ae2dc..cfb6c8c2819 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -283,3 +283,23 @@ select * from t1 where (rand() < 0) and i in (select i from t2); drop table t1,t2; set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # mdev-12855: materialization of a semi-join subquery + ORDER BY +--echo # + +CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('qux'),('foo'); +CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); + +let $q= +SELECT f1 FROM t1 +WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) +HAVING f1 != 'foo' +ORDER BY f1; + +eval $q; +eval explain $q; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 7b6c5818a04..c626d88e6ff 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1950,6 +1950,213 @@ set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2; +--echo # +--echo # mdev-12838: scan of materialized of semi-join subquery in join +--echo # + +set @save_optimizer_switch=@@optimizer_switch; + +CREATE TABLE t1 ( + dispatch_group varchar(32), + assignment_group varchar(32), + sys_id char(32), + PRIMARY KEY (sys_id), + KEY idx1 (dispatch_group), + KEY idx2 (assignment_group) +) ENGINE=MyISAM; + +CREATE TABLE t2 ( + ugroup varchar(32), + user varchar(32), + sys_id char(32), + PRIMARY KEY (sys_id), + KEY idx3 (ugroup), + KEY idx4 (user) +) ENGINE=MyISAM; + +CREATE TABLE t3 ( + type mediumtext, + sys_id char(32), + PRIMARY KEY (sys_id) +) ENGINE=MyISAM; + +--disable_query_log + +INSERT INTO t1 VALUES +('e5d9f63237232000158bbfc8bcbe5dbf','f304ae0037332000158bbfc8bcbe5d4f', +'5398c0e037003000158bbfc8bcbe5dbb'), +('69d9f63237232000158bbfc8bcbe5dcb','7172ea0037332000158bbfc8bcbe5db6', +'5c188ca037003000158bbfc8bcbe5dbc'), +('577ed708d773020058c92cf65e61037a','699708d4d773020058c92cf65e61037c', +'623a8cd4d773020058c92cf65e6103ea'), +('96fb652637232000158bbfc8bcbe5db4','df50316637232000158bbfc8bcbe5d23', +'6835bd6637232000158bbfc8bcbe5d21'), +('e1d9f63237232000158bbfc8bcbe5db8','96346e0037332000158bbfc8bcbe5daa', +'697880e037003000158bbfc8bcbe5dcd'), +('25d9f63237232000158bbfc8bcbe5dbe','f304ae0037332000158bbfc8bcbe5d4f', +'6a9804e037003000158bbfc8bcbe5d09'), +('96fb652637232000158bbfc8bcbe5db4','e08fad2637232000158bbfc8bcbe5d39', +'6d25f96637232000158bbfc8bcbe5d79'), +('e9d9f63237232000158bbfc8bcbe5dc6','7172ea0037332000158bbfc8bcbe5db6', +'702880e037003000158bbfc8bcbe5d94'), +('a5d9f63237232000158bbfc8bcbe5dca','f304ae0037332000158bbfc8bcbe5d4f', +'7188c0e037003000158bbfc8bcbe5d75'), +('65d9f63237232000158bbfc8bcbe5dc4','f304ae0037332000158bbfc8bcbe5d4f', +'778880e037003000158bbfc8bcbe5d9e'), +('a1d9f63237232000158bbfc8bcbe5dc3','7172ea0037332000158bbfc8bcbe5db6', +'7d0840e037003000158bbfc8bcbe5dde'), +('21d9f63237232000158bbfc8bcbe5db7','96346e0037332000158bbfc8bcbe5daa', +'7f6880e037003000158bbfc8bcbe5da7'), +('96fb652637232000158bbfc8bcbe5db4','ec70316637232000158bbfc8bcbe5d60', +'8025f96637232000158bbfc8bcbe5dd0'), +('3dd9f63237232000158bbfc8bcbe5dcc','7172ea0037332000158bbfc8bcbe5db6', +'823880e037003000158bbfc8bcbe5ded'), +('96fb652637232000158bbfc8bcbe5db4','7b10fd2637232000158bbfc8bcbe5d30', +'9a353d6637232000158bbfc8bcbe5dee'), +('75d9f63237232000158bbfc8bcbe5dd0','ebb4620037332000158bbfc8bcbe5d89', +'a558c0e037003000158bbfc8bcbe5d36'), +('6dd9f63237232000158bbfc8bcbe5db5','96346e0037332000158bbfc8bcbe5daa', +'bc78cca037003000158bbfc8bcbe5d74'), +('add9f63237232000158bbfc8bcbe5dc7','7172ea0037332000158bbfc8bcbe5db6', +'c53804a037003000158bbfc8bcbe5db8'), +('fdd9f63237232000158bbfc8bcbe5dcd','7864ae0037332000158bbfc8bcbe5db8', +'cfe740e037003000158bbfc8bcbe5de8'), +('96fb652637232000158bbfc8bcbe5db4','3120fd2637232000158bbfc8bcbe5d42', +'e2257d6637232000158bbfc8bcbe5ded'), +('3c3725e237232000158bbfc8bcbe5da1','96346e0037332000158bbfc8bcbe5daa', +'ee78c0e037003000158bbfc8bcbe5db5'), +('a9d9f63237232000158bbfc8bcbe5dc0','7172ea0037332000158bbfc8bcbe5db6', +'f00888a037003000158bbfc8bcbe5dd3'), +('29d9f63237232000158bbfc8bcbe5db9','7172ea0037332000158bbfc8bcbe5db6', +'fa0880e037003000158bbfc8bcbe5d70'), +('b1d9f63237232000158bbfc8bcbe5dcf','ebb4620037332000158bbfc8bcbe5d89', +'fa48c0e037003000158bbfc8bcbe5d28'); + +INSERT INTO t2 VALUES +('17801ac21b13200050fdfbcd2c0713e8','8e826bf03710200044e0bfc8bcbe5d86', +'14c19a061b13200050fdfbcd2c07134b'), +('577ed708d773020058c92cf65e61037a','931644d4d773020058c92cf65e61034c', +'339888d4d773020058c92cf65e6103aa'), +('df50316637232000158bbfc8bcbe5d23','92826bf03710200044e0bfc8bcbe5da9', +'3682f56637232000158bbfc8bcbe5d44'), +('b4f342b237232000158bbfc8bcbe5def','86826bf03710200044e0bfc8bcbe5d70', +'38e4c2b237232000158bbfc8bcbe5dea'), +('7b10fd2637232000158bbfc8bcbe5d30','8a826bf03710200044e0bfc8bcbe5d72', +'4442b56637232000158bbfc8bcbe5d43'), +('3120fd2637232000158bbfc8bcbe5d42','82826bf03710200044e0bfc8bcbe5d89', +'49d2396637232000158bbfc8bcbe5d12'), +('96fb652637232000158bbfc8bcbe5db4','86826bf03710200044e0bfc8bcbe5d79', +'4e3ca52637232000158bbfc8bcbe5d3e'), +('17801ac21b13200050fdfbcd2c0713e8','824fd523bf4320007a6d257b3f073963', +'58c19a061b13200050fdfbcd2c07134e'), +('699708d4d773020058c92cf65e61037c','901784d4d773020058c92cf65e6103da', +'5bc708d4d773020058c92cf65e6103d5'), +('75d9f63237232000158bbfc8bcbe5dd0','86826bf03710200044e0bfc8bcbe5d79', +'6b52cb7237232000158bbfc8bcbe5ded'), +('f253da061b13200050fdfbcd2c0713ab','8e826bf03710200044e0bfc8bcbe5d86', +'81045e061b13200050fdfbcd2c071373'), +('7b10fd2637232000158bbfc8bcbe5d30','8e826bf03710200044e0bfc8bcbe5d74', +'8c42b56637232000158bbfc8bcbe5d3f'), +('e5d9f63237232000158bbfc8bcbe5dbf','7a826bf03710200044e0bfc8bcbe5df5', +'a7acfe3237232000158bbfc8bcbe5d78'), +('8a5055c9c61122780043563ef53438e3','9ee1b13dc6112271007f9d0efdb69cd0', +'a9aff553c6112276015a8006174bee21'), +('8a4dde73c6112278017a6a4baf547aa7','9ee1b13dc6112271007f9d0efdb69cd0', +'a9b2f526c61122760003ae07349d294f'), +('aaccc971c0a8001500fe1ff4302de101','9ee1b13dc6112271007f9d0efdb69cd0', +'aacceed3c0a80015009069bba51c4e21'), +('65d9f63237232000158bbfc8bcbe5dc4','8d56406a0a0a0a6b004070b354aada28', +'ac1bfa3237232000158bbfc8bcbe5dc3'), +('b85d44954a3623120004689b2d5dd60a','97000fcc0a0a0a6e0104ca999f619e5b', +'b77bc032cbb00200d71cb9c0c24c9c45'), +('220f8e71c61122840197e57c33464f70','8d56406a0a0a0a6b004070b354aada28', +'b9b74f080a0a0b343ba75b95bdb27056'), +('e08fad2637232000158bbfc8bcbe5d39','82826bf03710200044e0bfc8bcbe5d80', +'be02756637232000158bbfc8bcbe5d8b'), +('ebb4620037332000158bbfc8bcbe5d89','7682abf03710200044e0bfc8bcbe5d25', +'c0122f4437732000158bbfc8bcbe5d7d'), +('96fb652637232000158bbfc8bcbe5db4','7a82abf03710200044e0bfc8bcbe5d27', +'c23ca52637232000158bbfc8bcbe5d3b'), +('22122b37c611228400f9ff91c857581d','9ee1b13dc6112271007f9d0efdb69cd0', +'d23bbf5dac14641866947512bde59dc5'), +('db53a9290a0a0a650091abebccf833c6','9ee1b13dc6112271007f9d0efdb69cd0', +'db54a0f60a0a0a65002c54dcb72b4f41'), +('e08fad2637232000158bbfc8bcbe5d39','8e826bf03710200044e0bfc8bcbe5d86', +'f602756637232000158bbfc8bcbe5d88'), +('699708d4d773020058c92cf65e61037c','8d59d601d7b3020058c92cf65e6103c2', +'f718a241d7b3020058c92cf65e610332'), +('df50316637232000158bbfc8bcbe5d23','9e826bf03710200044e0bfc8bcbe5da6', +'fe82f56637232000158bbfc8bcbe5d4e'), +('f972d6061b13200050fdfbcd2c0713e5','780395f0df031100a9e78b6c3df2631f', +'ff4395f0df031100a9e78b6c3df2637e'); + +INSERT INTO t3 VALUES +('87245e061b13200050fdfbcd2c0713cc','7172ea0037332000158bbfc8bcbe5db6'), +('74af88c6c611227d0066386e74dc853d','74ad1ff3c611227d01d25feac2af603f'), +('59e22fb137032000158bbfc8bcbe5d52','75d9f63237232000158bbfc8bcbe5dd0'), +('98906fb137032000158bbfc8bcbe5d65','781da52637232000158bbfc8bcbe5db8'), +('87245e061b13200050fdfbcd2c0713cc','7864ae0037332000158bbfc8bcbe5db8'), +('87245e061b13200050fdfbcd2c0713cc','7b10fd2637232000158bbfc8bcbe5d30'), +('59e22fb137032000158bbfc8bcbe5d52','81a880e037003000158bbfc8bcbe5df8'), +('74af88c6c611227d0066386e74dc853d','8a4cb6d4c61122780043b1642efcd52b'), +('1cb8ab9bff500200158bffffffffff62','8a4dde73c6112278017a6a4baf547aa7'), +('1cb8ab9bff500200158bffffffffff62','8a5055c9c61122780043563ef53438e3'), +('87245e061b13200050fdfbcd2c0713cc','96346e0037332000158bbfc8bcbe5daa'), +('59e22fb137032000158bbfc8bcbe5d52','96fb652637232000158bbfc8bcbe5db4'), +('59e22fb137032000158bbfc8bcbe5d52','a1d9f63237232000158bbfc8bcbe5dc3'), +('59e22fb137032000158bbfc8bcbe5d52','a5d9f63237232000158bbfc8bcbe5dca'), +('1cb8ab9bff500200158bffffffffff62','a715cd759f2002002920bde8132e7018'), +('59e22fb137032000158bbfc8bcbe5d52','a9d9f63237232000158bbfc8bcbe5dc0'), +('74af88c6c611227d0066386e74dc853d','aacb62e2c0a80015007f67f752c2b12c'), +('74af88c6c611227d0066386e74dc853d','aaccc971c0a8001500fe1ff4302de101'), +('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dbb'), +('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dc7'), +('59e22fb137032000158bbfc8bcbe5d52','b1d9f63237232000158bbfc8bcbe5dcf'), +('1cb8ab9bff500200158bffffffffff62','b85d44954a3623120004689b2d5dd60a'), +('1cb8ab9bff500200158bffffffffff62','b97e89b94a36231201676b73322a0311'), +('1cb8ab9bff500200158bffffffffff62','cfcbad03d711110050f5edcb9e61038f'), +('1cb8ab9bff500200158bffffffffff62','d625dccec0a8016700a222a0f7900d06'), +('1cb8ab9bff500200158bffffffffff62','db53580b0a0a0a6501aa37c294a2ba6b'), +('1cb8ab9bff500200158bffffffffff62','db53a9290a0a0a650091abebccf833c6'), +('1cb8ab9bff500200158bffffffffff62','dc0db135c332010016194ffe5bba8f23'), +('87245e061b13200050fdfbcd2c0713cc','df50316637232000158bbfc8bcbe5d23'), +('87245e061b13200050fdfbcd2c0713cc','e08fad2637232000158bbfc8bcbe5d39'), +('59e22fb137032000158bbfc8bcbe5d52','e1d9f63237232000158bbfc8bcbe5db8'), +('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5db4'), +('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5dbf'), +('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dba'), +('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dc6'), +('87245e061b13200050fdfbcd2c0713cc','ebb4620037332000158bbfc8bcbe5d89'), +('87245e061b13200050fdfbcd2c0713cc','ec70316637232000158bbfc8bcbe5d60'), +('87245e061b13200050fdfbcd2c0713cc','f253da061b13200050fdfbcd2c0713ab'), +('87245e061b13200050fdfbcd2c0713cc','f304ae0037332000158bbfc8bcbe5d4f'), +('98906fb137032000158bbfc8bcbe5d65','f972d6061b13200050fdfbcd2c0713e5'), +('59e22fb137032000158bbfc8bcbe5d52','fdd9f63237232000158bbfc8bcbe5dcd'); + +--enable_query_log + +let $q= +SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND + t1.dispatch_group IN + (SELECT t2.ugroup + FROM t2, t3 t3_i + WHERE t2.ugroup = t3_i.sys_id AND + t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND + t2.user = '86826bf03710200044e0bfc8bcbe5d79'); + +set optimizer_switch='materialization=off'; +eval explain $q; +eval $q; + +set optimizer_switch='materialization=on'; +eval explain $q; +eval $q; + +DROP TABLE t1,t2,t3; +set optimizer_switch=@save_optimizer_switch; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index f4dc6a5d449..4a3c19b49ab 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1370,3 +1370,18 @@ order by d; drop table t1; --echo End of 5.0 tests + +# +# Bug #24595639: INCORRECT BEHAVIOR IN QUERY WITH UNION AND GROUP BY +# +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 (c varchar(30), d varchar(30)); +insert into t1 values ('1','1'),('2','2'),('4','4'); +create table t3 (e int, f int); +insert into t3 values (1,1),(2,2),(31,31),(32,32); +select e,f, (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3; +select avg(f), (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3 group by sub; +drop table t1,t2,t3; + +--echo End of 5.5 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 89a8e0c9ffc..89f9c744a5f 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5565,6 +5565,22 @@ select * drop view v1; drop table t1,t2,t3; +--echo # +--echo # MDEV-11240: Server crashes in check_view_single_update or +--echo # Assertion `derived->table' failed in mysql_derived_merge_for_insert +--echo # + +CREATE TABLE t3 (a INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2; +CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; +PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; +--error ER_VIEW_NO_INSERT_FIELD_LIST +EXECUTE stmt; +--error ER_VIEW_NO_INSERT_FIELD_LIST +EXECUTE stmt; +drop view v1,v2; +drop table t3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- @@ -5814,6 +5830,55 @@ SELECT * FROM v1 where use_case_id = 10; drop view v1; drop table t1; +--echo # +--echo # MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view +--echo # +--echo # DATABASE() fails only when the initial view creation features a NULL +--echo # default database. +--echo # +--echo # CREATE, USE and DROP database so that we have no "default" database. +--echo # +CREATE DATABASE temporary; +USE temporary; +DROP DATABASE temporary; +SELECT DATABASE(); + +CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two'; +SHOW CREATE VIEW test.v_no_db; +PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'"; + +--echo # +--echo # All statements should return NULL +--echo # +EXECUTE prepared_no_database; +SELECT DATABASE() = 'temporary_two'; +SELECT * FROM test.v_no_db; + +CREATE DATABASE temporary_two; +USE temporary_two; +CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two'; +PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'"; + +--echo # +--echo # All statements should return 1; +--echo # +SELECT DATABASE() = 'temporary_two'; +SELECT * FROM test.v_no_db; +SELECT * FROM test.v_with_db; +EXECUTE prepared_with_database; + +--echo # +--echo # Prepared statements maintain default database to be the same +--echo # during on creation so this should return NULL still. +--echo # See MySQL bug #25843 +--echo # +EXECUTE prepared_no_database; + +DROP DATABASE temporary_two; +DROP VIEW test.v_no_db; +DROP VIEW test.v_with_db; +USE test; + --echo # ----------------------------------------------------------------- --echo # -- End of 10.0 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests index 0a2d5a85e51..520567a0e9b 100644 --- a/mysql-test/unstable-tests +++ b/mysql-test/unstable-tests @@ -23,47 +23,57 @@ # ############################################################################## +main.alter_table_online : Modified in 10.0.32 +main.binary_to_hex : Modified in 10.0.32 +main.bootstrap : Modified in 10.0.32 +main.count_distinct : Modified in 10.0.32 main.count_distinct2 : MDEV-11768 - timeout main.create_delayed : MDEV-10605 - failed with timeout +main.ctype_ucs : Modified in 10.0.32 main.debug_sync : MDEV-10607 - internal error main.derived_opt : MDEV-11768 - timeout -main.derived_view : Modified in 10.0.31 -main.events_2 : Modified in 10.0.31 +main.engine_error_in_alter-8453 : Added in 10.0.32 main.events_slowlog : MDEV-12821 - wrong result -main.func_regexp_pcre : Modified in 10.0.31 -main.gis : Modified in 10.0.31 +main.func_concat : Modified in 10.0.32 +main.func_crypt : Modified in 10.0.32 +main.func_regexp_pcre : MDEV-13412 - crash, wrong result; modified in 10.0.32 +main.gis : MDEV-13411 - wrong result on P8; modified in 10.0.32 +main.gis-alter_table_online : Added in 10.0.32 +main.gis-rt-precise : Modified in 10.0.32 +main.group_by : Modified in 10.0.32 main.host_cache_size_functionality : MDEV-10606 - sporadic failure on shutdown main.index_intersect_innodb : MDEV-10643 - failed with timeout main.index_merge_innodb : MDEV-7142 - wrong result main.innodb_mysql_lock : MDEV-7861 - sporadic lock detection failure -main.loaddata : Modified in 10.0.31 -main.log_tables-big : Modified in 10.0.31 +main.join_outer : Modified in 10.0.32 +main.loadxml : Data file modified in 10.0.32 +main.log_tables-big : MDEV-13408 - wrong result main.mdev-504 : MDEV-10607 - sporadic "can't connect" main.mdev375 : MDEV-10607 - sporadic "can't connect" main.merge : MDEV-10607 - sporadic "can't connect" -main.mysqld--help : Modified in 10.0.31 -main.mysqldump : Modified in 10.0.31 +main.mysql : Modified in 10.0.32 main.mysqlhotcopy_myisam : MDEV-10995 - test hangs on debug build -main.mysqltest : MDEV-9269 - fails on Alpha +main.mysqltest : MDEV-9269 - fails on Alpha; modified in 10.0.32 +main.mysql_upgrade : Modified in 10.0.32 main.ps : MDEV-11017 - sporadic wrong Prepared_stmt_count +main.read_only : Modified in 10.0.32 main.show_explain : MDEV-10674 - wrong result main.sp_notembedded : MDEV-10607 - internal error main.sp-security : MDEV-10607 - sporadic "can't connect" -main.subselect4 : Modified in 10.0.31 -main.subselect_exists2in : Modified in 10.0.31 -main.subselect_innodb : MDEV-10614 - sporadic wrong results; modified in 10.0.31 -main.subselect_mat_cost_bugs : Modified in 10.0.31 -main.subselect_sj : Modified in 10.0.31 -main.subselect_sj_mat : Modified in 10.0.31 -main.subselect_sj2_mat : Modified in 10.0.31 -main.symlink : Modified in 10.0.31 -main.symlink-myisam-11902 : Modified in 10.0.31 -main.view : Modified in 10.0.31 +main.subselect : Modified in 10.0.32 +main.subselect_innodb : MDEV-10614 - sporadic wrong results +main.subselect_mat_cost_bugs : Modified in 10.0.32 +main.subselect_nulls : Modified in 10.0.32 +main.subselect_sj2_mat : Modified in 10.0.32 +main.subselect_sj_mat : MOdified in 10.0.32 +main.symlink-aria-11902 : MDEV-12215 - Wrong error number +main.symlink-myisam-11902 : MDEV-12215 - Wrong error number +main.union : Modified in 10.0.32 +main.view : Modified in 10.0.32 main.xa : MDEV-11769 - lock wait timeout #---------------------------------------------------------------- -archive.archive-big : MDEV-10615 - table is marked as crashed archive.archive_bitfield : MDEV-11771 - table is marked as crashed archive.archive_symlink : MDEV-12170 - unexpected error on rmdir archive.discover : MDEV-10510 - table is marked as crashed @@ -71,17 +81,21 @@ archive.mysqlhotcopy_archive : MDEV-10995 - test hangs on debug build #---------------------------------------------------------------- -binlog.binlog_commit_wait : MDEV-10150 - Error: too much time elapsed -binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint +binlog.binlog_commit_wait : MDEV-10150 - Error: too much time elapsed +binlog.binlog_unsafe : Modified in 10.0.32 +binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint #---------------------------------------------------------------- -connect.jdbc_new : Modified in 10.0.31 -connect.tbl : MDEV-9844, MDEV-10179 - sporadic crashes, valgrind warnings, wrong results - -#---------------------------------------------------------------- - -csv.read_only : Added in 10.0.31 +connect.drop-open-error : Added in 10.0.32 +connect.grant2 : Modified in 10.0.32 +connect.infoschema-9739 : Added in 10.0.32 +connect.json : Sporadic wrong result, tentative fix in 10.0.32 +connect.mysql_new : Modified in 10.0.32 +connect.odbc_firebird : Added in 10.0.32 +connect.secure_file_priv : Modified in 10.0.32 +connect.tbl : MDEV-9844, MDEV-10179 - sporadic crashes, valgrind warnings, wrong results +connect.zip : Sporadic wrong result, tentative fix expected in 10.0.32 #---------------------------------------------------------------- @@ -90,6 +104,7 @@ engines/rr_trx.* : MDEV-10998 - tests not maintained #---------------------------------------------------------------- federated.federatedx : MDEV-10617 - Wrong checksum, timeouts +federated.federated_bug_35333 : MDEV-13410 - Wrong result federated.federated_innodb : MDEV-10617, MDEV-10417 - Wrong checksum, timeouts, fails on Mips federated.federated_partition : MDEV-10417 - Fails on Mips federated.federated_transactions : MDEV-10617, MDEV-10417 - Wrong checksum, timeouts, fails on Mips @@ -103,19 +118,28 @@ funcs_2/charset.* : MDEV-10999 - test not maintained #---------------------------------------------------------------- -innodb.autoinc_debug : Added in 10.0.31 innodb.binlog_consistent : MDEV-10618 - Server fails to start +innodb.drop_table_background : MDEV-13407 - Tablespace exists; added in 10.0.32 innodb.group_commit_crash_no_optimize_thread : MDEV-11770 - checksum mismatch -innodb.innodb-alter-debug : Added in 10.0.31 -innodb.innodb-alter-nullable : Added in 10.0.31 -innodb.innodb-alter-table : MDEV-10619 - Testcase timeout -innodb.innodb_bug14676111 : Modified in 10.0.31 +innodb.innodb-alter-debug : Modified in 10.0.32 +innodb.innodb-alter-discard : Modified in 10.0.32 +innodb.innodb-alter-nullable : Modified in 10.0.32 +innodb.innodb-alter-table : MDEV-10619 - Testcase timeout; modified in 10.0.32 +innodb.innodb-alter-tempfile : Modified in 10.0.32 innodb.innodb_bug30423 : MDEV-7311 - Wrong number of rows in the plan -innodb.innodb-index-online-norebuild : Added in 10.0.31 -innodb.log_file_size : Modified in 10.0.31 +innodb.innodb-get-fk : Modified in 10.0.32 innodb.innodb_monitor : MDEV-10939 - Testcase timeout - -innodb_fts.innodb_fts_stopword_charset : MDEV-12052 - Crash on shutdown; modified in 10.0.31 +innodb.log_file_size : Modified in 10.0.32 +innodb.row_format_redundant : Added in 10.0.32 +innodb.table_flags : Added in 10.0.32 + +innodb_zip.innodb_bug36169 : Modified in 10.0.32 +innodb_zip.innodb_bug36172 : Modified in 10.0.32 +innodb_zip.innodb_bug52745 : Modified in 10.0.32 +innodb_zip.innodb_bug53591 : Modified in 10.0.32 +innodb_zip.innodb_bug56680 : Modified in 10.0.32 +innodb_zip.innodb-create-options : Modified in 10.0.32 +innodb_zip.innodb-zip : Modified in 10.0.32 #---------------------------------------------------------------- @@ -129,17 +153,16 @@ mroonga/storage.index_multiple_column_unique_date_order_32bit_desc : Wrong resul #---------------------------------------------------------------- multi_source.gtid : MDEV-10417 - Fails on Mips +multi_source.info_logs : MDEV-10042 - Wrong result multi_source.multisource : MDEV-10417 - Fails on Mips multi_source.simple : MDEV-4633 - Wrong slave status output multi_source.status_vars : MDEV-4632 - failed while waiting for Slave_received_heartbeats #---------------------------------------------------------------- -myisam_debug : Modified in 10.0.31 - -#---------------------------------------------------------------- - +parts.longname : Added in 10.0.32 parts.partition_exch_qa_10 : MDEV-11765 - wrong result +parts.quoting : Added in 10.0.32 #---------------------------------------------------------------- @@ -148,7 +171,6 @@ perfschema.func_mutex : MDEV-5708 - fails for s390x perfschema.hostcache_ipv6_ssl : MDEV-10696 - crash on shutdown perfschema.socket_summary_by_event_name_func : MDEV-10622 - Socket summary tables do not match perfschema.stage_mdl_procedure : MDEV-11545 - Wrong result -perfschema.start_server_1_digest : Added in 10.0.31 perfschema_stress.* : MDEV-10996 - tests not maintained @@ -161,9 +183,12 @@ plugins.thread_pool_server_audit : MDEV-9562 - crashes on sol10-sparc #---------------------------------------------------------------- roles.create_and_grant_role : MDEV-11772 - wrong result +roles.current_role_view-12666 : Added in 10.0.32 +roles.show_create_database-10463 : Added in 10.0.32 #---------------------------------------------------------------- +rpl.circular_serverid0 : Added in 10.0.32 rpl.last_insert_id : MDEV-10625 - warnings in error log rpl.rpl_auto_increment : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_bug45679 : MDEV-10417 - Fails on Mips @@ -180,17 +205,23 @@ rpl.rpl_insert_delayed : MDEV-9329 - Fails on Ubuntu/s390x rpl.rpl_invoked_features : MDEV-10417 - Fails on Mips rpl.rpl_mdev6020 : MDEV-10630, MDEV-10417 - Timeouts, fails on Mips rpl.rpl_parallel : MDEV-10653 - Timeouts +rpl.rpl_parallel_mdev6589 : MDEV-12979 - Assertion failure rpl.rpl_parallel_temptable : MDEV-10356 - Crash in close_thread_tables +rpl.rpl_parallel_tokudb_delete_pk : Opt file modified in 10.0.32 +rpl.rpl_parallel_tokudb_update_pk_uc0_lookup0 : Modified in 10.0.32 +rpl.rpl_parallel_tokudb_write_pk : Modified in 10.0.32 rpl.rpl_partition_innodb : MDEV-10417 - Fails on Mips +rpl.rpl_reset_slave_fail : Added in 10.0.32 rpl.rpl_row_basic_11bugs : MDEV-12171 - Server failed to start +rpl.rpl_row_index_choice : MDEV-13409 - Server crash rpl.rpl_row_sp001 : MDEV-9329 - Fails on Ubuntu/s390x rpl.rpl_semi_sync : MDEV-11220 - Wrong result rpl.rpl_semi_sync_uninstall_plugin : MDEV-7140 - Wrong plugin status rpl.rpl_show_slave_hosts : MDEV-12171 - Server failed to start +rpl.rpl_skip_replication : MDEV-9268 - Fails with timeout in sync_slave_with_master on Alpha rpl.rpl_slave_grp_exec : MDEV-10514 - Unexpected deadlock rpl.rpl_sync : MDEV-10633 - Database page corruption rpl.rpl_temporary_error2 : MDEV-10634 - Wrong number of retries -rpl.rpl_skip_replication : MDEV-9268 - Fails with timeout in sync_slave_with_master on Alpha rpl/extra/rpl_tests.* : MDEV-10994 - tests not maintained @@ -219,46 +250,67 @@ stress.ddl_innodb : MDEV-10635 - Testcase timeout sys_vars.autocommit_func2 : MDEV-9329 - Fails on Ubuntu/s390x sys_vars.innodb_buffer_pool_dump_pct_basic : MDEV-10651 - sporadic failure on file_exists -sys_vars.innodb_status_output_basic : MDEV-12174 - Timeout +sys_vars.innodb_sched_priority_cleaner_basic : Modified in 10.0.32 sys_vars.thread_cache_size_func : MDEV-11775 - wrong result #---------------------------------------------------------------- -tokudb.background_job_manager : Modified in 10.0.31 -tokudb.bug-1657908 : Added in 10.0.31 -tokudb.card_auto_analyze_lots : Modified in 10.0.31 tokudb.change_column_all_1000_10 : MDEV-12640 - Lost connection during query tokudb.change_column_bin : MDEV-12640 - Lost connection during query tokudb.change_column_char : MDEV-12822 - Lost connection during query tokudb.cluster_filter_unpack_varchar : MDEV-10636 - Wrong execution plan -tokudb.dir_cmd : Added in 10.0.31 tokudb.dir_per_db : MDEV-11537 - wrong result tokudb.dir_per_db_rename_to_nonexisting_schema : MDEV-12823 - Valgrind tokudb.hotindex-update-1 : MDEV-12640 - Lost connection during query -tokudb.i_s_tokudb_lock_waits_timeout : Modified in 10.0.31 -tokudb.i_s_tokudb_locks : Modified in 10.0.31 +tokudb.kill_query_blocked_in_lt : Added in 10.0.32 +tokudb.locks-select-update-1 : MDEV-13406 - Lock wait timeout +tokudb.locks-select-update-3 : Modified in 10.0.32 tokudb.rows-32m-rand-insert : MDEV-12640 - Lost connection during query tokudb.rows-32m-seq-insert : MDEV-12640 - Lost connection during query tokudb_backup.* : MDEV-11001 - tests don't work -tokudb_backup.backup_master_info : Added in 10.0.31 -tokudb_backup.backup_master_state : Added in 10.0.31 -tokudb_backup.empty_slave_info_file : Added in 10.0.31 -tokudb_backup.innodb_use_native_aio_enabled : Added in 10.0.31 -tokudb_backup.rpl_safe_slave : Added in 10.0.31 -tokudb_backup.rpl_tokudb_commit_sync : Added in 10.0.31 - tokudb_bugs.checkpoint_lock : MDEV-10637 - Wrong processlist output tokudb_bugs.checkpoint_lock_3 : MDEV-10637 - Wrong processlist output -tokudb_bugs.db233 : Modified in 10.0.31 +tokudb_bugs.frm_store : MDEV-12823 - Valgrind tokudb_bugs.frm_store2 : MDEV-12823 - Valgrind -tokudb_bugs.leak172 : Modified in 10.0.31 +tokudb_bugs.frm_store3 : MDEV-12823 - Valgrind tokudb_bugs.xa : MDEV-11804 - Lock wait timeout +tokudb_mariadb.mdev12972 : Added in 10.0.32 + tokudb_rpl.* : MDEV-11001 - tests don't work tokudb_sys_vars.* : MDEV-11001 - tests don't work +rpl-tokudb.rpl_deadlock_tokudb : Modified in 10.0.32 +rpl-tokudb.rpl_not_null_tokudb : Modified in 10.0.32 +rpl-tokudb.rpl_rfr_disable_on_expl_pk_absence : Modified in 10.0.32 +rpl-tokudb.rpl_row_basic_3tokudb : Modified in 10.0.32 +rpl-tokudb.rpl_stm_tokudb : Modified in 10.0.32 +rpl-tokudb.rpl_tokudb_commit_after_flush : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_insert_id : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_insert_id_pk : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_multi_update : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_multi_update2 : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_multi_update3 : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_rfr_partition_table : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_crash_safe : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_blobs : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_eng_full : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_eng_min : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_eng_noblob : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_idx_full : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_idx_min : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_img_idx_noblob : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_log : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_lower_case_table_names : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_sp003 : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_sp006 : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_row_trig004 : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_stm_log : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_stm_mixed_crash_safe : Added in 10.0.32 +rpl-tokudb.rpl_tokudb_stm_mixed_lower_case_table_names : Added in 10.0.32 + #---------------------------------------------------------------- unit.ma_test_loghandler : MDEV-10638 - record read not ok |