diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2014-06-06 00:07:27 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2014-06-06 00:07:27 +0200 |
| commit | e27c338634739ef56a6888e7948e04c0fa0ba677 (patch) | |
| tree | ad63ccae614f3dd77509825d1905fd815ef322cb /mysql-test | |
| parent | 2a5905141a3c509a7c34c3d370fb146dbc1c965f (diff) | |
| parent | 6d75570e99fbf070cdbeefdfbcfc94d1c7b3ad1f (diff) | |
| download | mariadb-git-e27c338634739ef56a6888e7948e04c0fa0ba677.tar.gz | |
5.5.38 merge
Diffstat (limited to 'mysql-test')
42 files changed, 1352 insertions, 64 deletions
diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index ff4bc960acb..18cd4748687 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -13,7 +13,6 @@ main.signal_demo3 @solaris # Bug#11753919 2010-01-20 alik Several main.sp @solaris # Bug#11753919 2010-01-20 alik Several test cases fail on Solaris with error Thread stack overrun main.wait_timeout @solaris # Bug#11758972 2010-04-26 alik wait_timeout fails on OpenSolaris -rpl.rpl_innodb_bug28430 # Bug#11754425 rpl.rpl_row_sp011 @solaris # Bug#11753919 2011-07-25 sven Several test cases fail on Solaris with error Thread stack overrun rpl.rpl_spec_variables @solaris # Bug #17337114 2013-08-20 Luis Soares failing on pb2 with timeout for 'CHECK WARNINGS' diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 8ae61881c07..c81af134add 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -78,7 +78,7 @@ cast(cast(20010203101112.121314 as double) as datetime) 2001-02-03 10:11:12 select cast(cast(010203101112.12 as double) as datetime); cast(cast(010203101112.12 as double) as datetime) -0001-02-03 10:11:12 +2001-02-03 10:11:12 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime); cast(cast(20010203101112.121314 as decimal(32,6)) as datetime) 2001-02-03 10:11:12 @@ -87,10 +87,10 @@ cast(20010203101112.121314 as datetime) 2001-02-03 10:11:12 select cast(110203101112.121314 as datetime); cast(110203101112.121314 as datetime) -0011-02-03 10:11:12 +2011-02-03 10:11:12 select cast(cast(010203101112.12 as double) as datetime); cast(cast(010203101112.12 as double) as datetime) -0001-02-03 10:11:12 +2001-02-03 10:11:12 select cast("2011-02-03 10:11:12.123456" as datetime); cast("2011-02-03 10:11:12.123456" as datetime) 2011-02-03 10:11:12 @@ -111,7 +111,7 @@ cast(cast(20010203101112.5 as double) as datetime(1)) 2001-02-03 10:11:12.5 select cast(cast(010203101112.12 as double) as datetime(2)); cast(cast(010203101112.12 as double) as datetime(2)) -0001-02-03 10:11:12.12 +2001-02-03 10:11:12.12 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)); cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)) 2001-02-03 10:11:12.121314 @@ -120,10 +120,10 @@ cast(20010203101112.121314 as datetime(6)) 2001-02-03 10:11:12.121314 select cast(110203101112.121314 as datetime(6)); cast(110203101112.121314 as datetime(6)) -0011-02-03 10:11:12.121314 +2011-02-03 10:11:12.121314 select cast(cast(010203101112.12 as double) as datetime(6)); cast(cast(010203101112.12 as double) as datetime(6)) -0001-02-03 10:11:12.120000 +2001-02-03 10:11:12.120000 select cast("2011-02-03 10:11:12.123456" as time); cast("2011-02-03 10:11:12.123456" as time) 10:11:12 @@ -266,7 +266,7 @@ Warnings: Warning 1264 Out of range value for column 'cast(-1000 as double(5,2))' at row 1 select cast(010203101112.121314 as datetime); cast(010203101112.121314 as datetime) -0001-02-03 10:11:12 +2001-02-03 10:11:12 select cast(120010203101112.121314 as datetime); cast(120010203101112.121314 as datetime) NULL @@ -327,7 +327,7 @@ cast(cast(120010203101112.121314 as double) as datetime) NULL select cast(cast(1.1 as double) as datetime); cast(cast(1.1 as double) as datetime) -0000-00-00 00:00:01 +NULL select cast(cast(-1.1 as double) as datetime); cast(cast(-1.1 as double) as datetime) NULL diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 3a3b69f1fc7..d0f42c3d3ec 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -539,6 +539,7 @@ n d1 d2 result 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 drop table t1; +set @save_derived_optimizer_switch_bug=@@optimizer_switch; SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (8); @@ -553,4 +554,17 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1249 Select 4 was reduced during optimization DROP TABLE t1, t2; +set optimizer_switch=@save_derived_optimizer_switch_bug; +# +# MDEV-6163: Error while executing an update query that has the +# same table in a sub-query +# +set @save_derived_optimizer_switch_bug=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +create table t1 (balance float, accountId varchar(64), primary key (accountId)); +insert into t1 (accountId,balance) values +('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0); +update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; +set optimizer_switch=@save_derived_optimizer_switch_bug; +drop table t1; set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 5646de88d26..4753728793a 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1001,29 +1001,29 @@ Warnings: Warning 1292 Truncated incorrect time value: '1223.5aa' select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time); column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time) -NULL +838:59:59 Warnings: -Warning 1292 Incorrect datetime value: '1.8446744073709552e19' +Warning 1292 Truncated incorrect time value: '1.8446744073709552e19' select column_get(column_create(1, 9223372036854775807 AS int), 1 as time); column_get(column_create(1, 9223372036854775807 AS int), 1 as time) -NULL +838:59:59 Warnings: -Warning 1292 Incorrect datetime value: '9223372036854775807' +Warning 1292 Truncated incorrect time value: '9223372036854775807' select column_get(column_create(1, -9223372036854775808 AS int), 1 as time); column_get(column_create(1, -9223372036854775808 AS int), 1 as time) -NULL +-838:59:59 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' +Warning 1292 Truncated incorrect time value: '-9223372036854775808' select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time); column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time) -NULL +838:59:59 Warnings: -Warning 1292 Incorrect datetime value: '99999999999999999999999999999' +Warning 1292 Truncated incorrect time value: '99999999999999999999999999999' select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time); column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time) -NULL +838:59:59 Warnings: -Warning 1292 Incorrect datetime value: '1e29' +Warning 1292 Truncated incorrect time value: '1e29' select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time); column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time) NULL @@ -1435,6 +1435,17 @@ Warning 1265 Data truncated for column 'dyncol' at row 1 SELECT COLUMN_ADD( dyncol, 'a', '22:22:22', 'c', REPEAT('x',270) AS CHAR ) FROM t1; DROP table t1; # +# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column +# +SELECT +column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1, +column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2; +t1 t2 +-838:59:59 -838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '-999999999999999' +Warning 1292 Truncated incorrect time value: '-9223372036854775808' +# # end of 5.3 tests # select column_get(column_create(1, "18446744073709552001" as char), 1 as int); diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index cebba082ea4..c7643f79779 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1091,9 +1091,9 @@ NULL select isnull(week(now() + 0)), isnull(week(now() + 0.2)), week(20061108), week(20061108.01), week(20061108085411.000002); isnull(week(now() + 0)) isnull(week(now() + 0.2)) week(20061108) week(20061108.01) week(20061108085411.000002) -0 0 45 NULL 45 +0 0 45 45 45 Warnings: -Warning 1292 Incorrect datetime value: '20061108.01' +Warning 1292 Truncated incorrect datetime value: '20061108.01' End of 4.1 tests select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') @@ -2483,6 +2483,14 @@ SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP; 1 DROP TABLE t1; # +# MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND) +# +SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2; +c1 c2 +9923-03-10 22:47:10.0 NULL +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2000000000000000000.0' +# # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL) # SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE); diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 932c1c76027..2d67d24bedd 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -520,3 +520,15 @@ LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug11735141.txt' INTO TABLE t1; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; End of 5.1 tests +# +# Bug#11759519 INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA LOCAL AND IMPORT ERRORS +# +SET @old_mode= @@sql_mode; +CREATE TABLE t1 (fld1 INT); +SET sql_mode='strict_all_tables'; +# Without fix, load data hangs forever. +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/mysql' REPLACE INTO TABLE t1 +FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; +Got one of the listed errors +SET @@sql_mode= @old_mode; +DROP TABLE t1; diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result index a2ce12bffc1..13321695360 100644 --- a/mysql-test/r/myisampack.result +++ b/mysql-test/r/myisampack.result @@ -150,6 +150,60 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +create table `t1` (`id` varchar(15) DEFAULT NULL) ENGINE=MyISAM ROW_FORMAT=FIXED; +insert into t1 values ('aaa'),('bbb'),('ccc'),('ddd'),('eee'); +insert into t1 (select * from t1); +insert into t1 (select * from t1); +insert into t1 (select * from t1); +insert into t1 (select * from t1); +checksum table t1; +Table Checksum +test.t1 2696656816 +insert into t1 values(NULL); +checksum table t1; +Table Checksum +test.t1 2679879600 +flush table t1; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +checksum table t1; +Table Checksum +test.t1 2679879600 +alter table t1 checksum=1 row_format=fixed; +checksum table t1; +Table Checksum +test.t1 2679879600 +flush table t1; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +checksum table t1; +Table Checksum +test.t1 2679879600 +alter table t1 row_format=dynamic checksum=0; +checksum table t1; +Table Checksum +test.t1 2330021136 +flush table t1; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +checksum table t1; +Table Checksum +test.t1 2330021136 +alter table t1 checksum=1 row_format=dynamic; +checksum table t1; +Table Checksum +test.t1 2330021136 +flush table t1; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +checksum table t1; +Table Checksum +test.t1 2330021136 +drop table t1; # # BUG#11751736: DROP DATABASE STATEMENT SHOULD REMOVE .OLD SUFFIX FROM # DATABASE DIRECTORY diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result index e91855b8dcd..ca3a42c087c 100644 --- a/mysql-test/r/outfile_loaddata.result +++ b/mysql-test/r/outfile_loaddata.result @@ -147,6 +147,14 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; a b c 1 ABC-АБВ DEF-ÂÃÄ 2 NULL NULL +SELECT * FROM t1; +a b c +1 ABC-АБВ DEF-ÂÃÄ +2 NULL NULL +SELECT * FROM t2; +a b c +1 ABC-АБВ DEF-ÂÃÄ +2 NULL NULL SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY 'ъ'; Warnings: Warning 1638 Non-ASCII separator arguments are not fully supported @@ -175,6 +183,14 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; a b c 1 ABC-АБВ DEF-ÂÃÄ 2 NULL NULL +SELECT * FROM t1; +a b c +1 ABC-АБВ DEF-ÂÃÄ +2 NULL NULL +SELECT * FROM t2; +a b c +1 ABC-АБВ DEF-ÂÃÄ +2 NULL NULL # Default (binary) charset: SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1; ################################################## diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index ec7fd798d4c..0a4cf9932c0 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -3301,3 +3301,55 @@ explain partitions select * from t1 where a between 10 and 10+33; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where drop table t0, t1; +# +# MDEV-6239: Partition pruning is not working as expected in an inner query +# +create table t1 +( +company_id bigint(5), +dept_id bigint(5), +emp_id bigint(5), +emp_name varchar(100), +primary key (company_id, emp_id) +) partition by list (company_id) ( +partition p_1000 values in (1000), +partition p_2000 values in (2000), +partition p_3000 values in (3000) +); +create table t2 +( +company_id bigint(5), +dept_id bigint(5), +dept_name varchar(100), +primary key (company_id, dept_id) +) partition by list (company_id) ( +partition p_1000 values in (1000), +partition p_2000 values in (2000), +partition p_3000 values in (3000) +); +insert into t2 values +(1000, 10, 'Engineering'), +(1000, 20, 'Product Management'), +(1000, 30, 'QA'), +(2000, 40, 'Support'), +(2000, 50, 'Professional Services'); +insert into t1 values +(1000, 10, 1, 'John'), +(1000, 10, 2, 'Smith'), +(1000, 20, 3, 'Jacob'), +(1000, 20, 4, 'Brian'), +(1000, 30, 5, 'Chris'), +(1000, 30, 6, 'Ryan'), +(2000, 40, 7, 'Karin'), +(2000, 40, 8, 'Jay'), +(2000, 50, 9, 'Ana'), +(2000, 50, 10, 'Jessica'); +# Table t2 should have only partition p_1000. +explain partitions +select * from t1 +where company_id = 1000 +and dept_id in (select dept_id from t2 where COMPANY_ID = 1000); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t2 p_1000 ref PRIMARY PRIMARY 8 const 2 Using index +1 PRIMARY t1 p_1000 ALL PRIMARY NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +drop table t1,t2; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 01be3cbfe2c..f2ad42ebc8d 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2047,6 +2047,60 @@ f1 f2 f3 f4 DROP TABLE t1; DROP VIEW v3; # +# MDEV-6105: Emoji unicode character string search query makes mariadb performance down +# +SET NAMES utf8; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 'test.t1' +CREATE TABLE t1( +id int AUTO_INCREMENT, +fd varchar(20), +primary key(id), +index ix_fd(fd) +)engine=innodb default charset=UTF8; +INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim'); +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +# The following should show "Impossible WHERE" : +explain +SELECT * FROM t1 WHERE fd='😁'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +SELECT * FROM t1 WHERE fd='😁'; +id fd +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +# The following must not use range access: +explain select count(*) from t1 where fd <'😁'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index ix_fd ix_fd 63 NULL # Using where; Using index +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +select count(*) from t1 where fd <'😁'; +count(*) +40960 +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +select count(*) from t1 ignore index (ix_fd) where fd <'😁'; +count(*) +40960 +drop table t1; +set names default; +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index dc6bed5fd98..16b35448c50 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -2049,6 +2049,60 @@ f1 f2 f3 f4 DROP TABLE t1; DROP VIEW v3; # +# MDEV-6105: Emoji unicode character string search query makes mariadb performance down +# +SET NAMES utf8; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 'test.t1' +CREATE TABLE t1( +id int AUTO_INCREMENT, +fd varchar(20), +primary key(id), +index ix_fd(fd) +)engine=innodb default charset=UTF8; +INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim'); +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +# The following should show "Impossible WHERE" : +explain +SELECT * FROM t1 WHERE fd='😁'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +SELECT * FROM t1 WHERE fd='😁'; +id fd +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +# The following must not use range access: +explain select count(*) from t1 where fd <'😁'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index ix_fd ix_fd 63 NULL # Using where; Using index +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +select count(*) from t1 where fd <'😁'; +count(*) +40960 +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +select count(*) from t1 ignore index (ix_fd) where fd <'😁'; +count(*) +40960 +drop table t1; +set names default; +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 2975176c64a..43ba6ead575 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1148,5 +1148,35 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t4 index NULL PRIMARY 59 NULL 2 Using where; Using index; End temporary DROP TABLE t1,t2,t3,t4; +# +# MDEV-6263: Wrong result when using IN subquery with order by +# +CREATE TABLE t1 ( +id int(11) NOT NULL, +nombre varchar(255) NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 (id, nombre) VALUES +(1, 'row 1'),(2, 'row 2'),(3, 'row 3'), +(4, 'row 4'),(5, 'row 5'),(6, 'row 6'); +CREATE TABLE t2 ( +id_algo int(11) NOT NULL, +id_agente int(11) NOT NULL, +PRIMARY KEY (id_algo,id_agente), +KEY another_data (id_agente) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 (id_algo, id_agente) VALUES +(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1); +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC; +id nombre +1 row 1 +2 row 2 +3 row 3 +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); +id nombre +1 row 1 +2 row 2 +3 row 3 +DROP TABLE t1, t2; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index ff97882c469..c45ab02a646 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1163,6 +1163,36 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY t4 hash_index NULL #hash#$hj:PRIMARY 55:59 test.t3.t3_c 2 Using where; End temporary; Using join buffer (incremental, BNLH join) DROP TABLE t1,t2,t3,t4; +# +# MDEV-6263: Wrong result when using IN subquery with order by +# +CREATE TABLE t1 ( +id int(11) NOT NULL, +nombre varchar(255) NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 (id, nombre) VALUES +(1, 'row 1'),(2, 'row 2'),(3, 'row 3'), +(4, 'row 4'),(5, 'row 5'),(6, 'row 6'); +CREATE TABLE t2 ( +id_algo int(11) NOT NULL, +id_agente int(11) NOT NULL, +PRIMARY KEY (id_algo,id_agente), +KEY another_data (id_agente) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 (id_algo, id_agente) VALUES +(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1); +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC; +id nombre +1 row 1 +2 row 2 +3 row 3 +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); +id nombre +1 row 1 +2 row 2 +3 row 3 +DROP TABLE t1, t2; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index f4d7d986a9d..0ff366b0d7a 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1150,6 +1150,36 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t4 index NULL PRIMARY 59 NULL 2 Using where; Using index; End temporary DROP TABLE t1,t2,t3,t4; +# +# MDEV-6263: Wrong result when using IN subquery with order by +# +CREATE TABLE t1 ( +id int(11) NOT NULL, +nombre varchar(255) NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 (id, nombre) VALUES +(1, 'row 1'),(2, 'row 2'),(3, 'row 3'), +(4, 'row 4'),(5, 'row 5'),(6, 'row 6'); +CREATE TABLE t2 ( +id_algo int(11) NOT NULL, +id_agente int(11) NOT NULL, +PRIMARY KEY (id_algo,id_agente), +KEY another_data (id_agente) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 (id_algo, id_agente) VALUES +(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1); +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC; +id nombre +1 row 1 +2 row 2 +3 row 3 +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); +id nombre +1 row 1 +2 row 2 +3 row 3 +DROP TABLE t1, t2; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set optimizer_switch=default; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 5e5b37f480a..2ba7606b663 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -722,6 +722,15 @@ dt 2013-09-21 08:20:30 drop procedure test5041; SET @@timestamp=DEFAULT; +# +# MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME) +# +SELECT +CAST(010203101112 AS DATETIME(1)) AS c1, +CAST(010203101112.2 AS DATETIME(1)) AS c2, +CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3; +c1 c2 c3 +2001-02-03 10:11:12.0 2001-02-03 10:11:12.2 2001-02-03 10:11:12.2 End of 5.3 tests # # Start of 5.5 tests diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 00a7a9b783e..aaf9819d79a 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -301,6 +301,34 @@ Field Type Null Key Default Extra MAX(t1)+1 decimal(22,1) YES NULL DROP TABLE t2,t1; # +# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column +# +SET sql_mode=traditional; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); +ERROR 22007: Incorrect time value: '18446744069414584320' for column 'a' at row 1 +SET sql_mode=DEFAULT; +INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +838:59:59.999999 +DROP TABLE t1; +SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED); +TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED) +0 +Warnings: +Warning 1292 Truncated incorrect time value: '18446744069414584320' +# +# MDEV-6100 No warning on CAST(9000000 AS TIME) +# +SELECT CAST(9000000 AS TIME); +CAST(9000000 AS TIME) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '9000000' +# # End of 5.3 tests # CREATE TABLE t1 (f1 TIME); diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result index 9c96889c7a0..8096785dcc1 100644 --- a/mysql-test/r/type_time_hires.result +++ b/mysql-test/r/type_time_hires.result @@ -17,46 +17,46 @@ Warnings: Note 1265 Data truncated for column 'a' at row 1 insert t1 values (99991231235959e1); Warnings: -Warning 1265 Data truncated for column 'a' at row 1 +Warning 1264 Out of range value for column 'a' at row 1 select * from t1; a -00:00:00.000 00:20:03.123 01:02:03.456 03:04:05.789 15:47:11.123 +838:59:59.999 select truncate(a, 6) from t1; truncate(a, 6) -0.000000 2003.123000 10203.456000 30405.789062 154711.123000 +8385959.999000 select a DIV 1 from t1; a DIV 1 -0 2003 10203 30405 154711 +8385959 select group_concat(distinct a) from t1; group_concat(distinct a) -00:00:00.000,00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123 +00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999 alter table t1 engine=innodb; select * from t1 order by a; a -00:00:00.000 00:20:03.123 01:02:03.456 03:04:05.789 15:47:11.123 +838:59:59.999 select * from t1 order by a+0; a -00:00:00.000 00:20:03.123 01:02:03.456 03:04:05.789 15:47:11.123 +838:59:59.999 drop table t1; create table t1 (a time(4)) engine=innodb; insert t1 values ('2010-12-11 01:02:03.456789'); diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index a3ad63c035a..6d99cad30f0 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1876,8 +1876,8 @@ SELECT(SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev; dev 1 # -# LP bug#1010729: Unexpected syntax error from UNION -# (bug #54382) with single-table join nest +# lp:1010729: Unexpected syntax error from UNION +# (bug #54382) with single-table join nest # CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); @@ -1892,3 +1892,12 @@ UNION SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); pk DROP TABLE t1,t2; +create table t1 (a int); +insert t1 values (1),(2),(3),(1); +explain select 1 from dual where exists (select max(a) from t1 group by a union select a+2 from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +3 UNION t1 ALL NULL NULL NULL NULL 4 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 1ea30658561..64b329e9e01 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1,5 +1,5 @@ -drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; -drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6; +drop table if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; +drop view if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; drop database if exists mysqltest; use test; SET @save_optimizer_switch=@@optimizer_switch; @@ -4114,6 +4114,114 @@ a 20 DROP VIEW v1,v2,v3; DROP TABLE t1,t2; +# +# MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized +# with MERGE view) +# +CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE OR REPLACE view v1 AS +SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +; +SELECT 1 +FROM (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t1) +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t8) ON 1=1 +; +1 +SELECT 1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 +; +1 +drop view v1; +drop table t1,t2,t3,t4,t5,t6; # ----------------------------------------------------------------- # -- End of 5.2 tests. # ----------------------------------------------------------------- @@ -5052,6 +5160,114 @@ execute stmt1; deallocate prepare stmt1; drop view v1,v2; drop table t1,t2; +# +# MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized +# with MERGE view) +# +CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE OR REPLACE view v1 AS +SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +; +SELECT 1 +FROM (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t1) +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 +FROM t1 a_alias_1 +LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 +LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 +LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 +LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 +LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t8) ON 1=1 +; +1 +SELECT 1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 +; +1 +drop view v1; +drop table t1,t2,t3,t4,t5,t6; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/suite/binlog/r/binlog_truncate_kill.result b/mysql-test/suite/binlog/r/binlog_truncate_kill.result new file mode 100644 index 00000000000..9161f3dc10d --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_truncate_kill.result @@ -0,0 +1,38 @@ +RESET MASTER; +connection default; +CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT) ENGINE=INNODB; +INSERT INTO t1(a, b) VALUES(1,2),(2,4),(3,6),(4,8),(5,10); +SET DEBUG_SYNC = "open_and_process_table signal truncate_before_lock wait_for forever"; +TRUNCATE t1; +connect con1,localhost,root,,; +SET DEBUG_SYNC = "now wait_for truncate_before_lock"; +SELECT ((@id := id) - id) FROM information_schema.processlist WHERE processlist.info LIKE '%TRUNCATE t1%' AND state LIKE '%open_and_process_table%'; +((@id := id) - id) +0 +KILL QUERY @id; +connection default; +ERROR 70100: Query execution was interrupted +connection con1; +include/show_binlog_events.inc +connection con1; +connection con1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT) ENGINE=INNODB +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; INSERT INTO t1(a, b) VALUES(1,2),(2,4),(3,6),(4,8),(5,10) +master-bin.000001 # Xid # # COMMIT /* XID */ +connection con1; +connection con1; +disconnect con1; +connection default; +SELECT * FROM t1; +id a b +1 1 2 +2 2 4 +3 3 6 +4 4 8 +5 5 10 +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/suite/binlog/t/binlog_truncate_kill.test b/mysql-test/suite/binlog/t/binlog_truncate_kill.test new file mode 100644 index 00000000000..92c23f18cb4 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_truncate_kill.test @@ -0,0 +1,57 @@ +############################################################################### +# Bug#17942050:KILL OF TRUNCATE TABLE WILL LEAD TO BINARY LOG WRITTEN WHILE +# ROWS REMAINS +# +# Problem: +# ======== +# When truncate table fails while using transactional based engines even +# though the operation errors out we still continue and log it to binlog. +# Because of this master has data but the truncate will be written to binary +# log which will cause inconsistency. +# +# Test: +# ===== +# Make master to wait in "open_table" call during the execution of truncate +# table command and kill the truncate table from other connection. This causes +# open table to return an error saying truncate failed during open table. This +# statement should not be binlogged. +############################################################################### +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/have_binlog_format_statement.inc +RESET MASTER; +--enable_connect_log +--connection default +CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT) ENGINE=INNODB; +INSERT INTO t1(a, b) VALUES(1,2),(2,4),(3,6),(4,8),(5,10); +SET DEBUG_SYNC = "open_and_process_table signal truncate_before_lock wait_for forever"; +--send TRUNCATE t1 + +connect(con1,localhost,root,,); +SET DEBUG_SYNC = "now wait_for truncate_before_lock"; +# Wait for one connection to reach open_and_process_table. +--let $show_statement= SHOW PROCESSLIST +--let $field= State +--let $condition= 'debug sync point: open_and_process_table'; +--source include/wait_show_condition.inc + +SELECT ((@id := id) - id) FROM information_schema.processlist WHERE processlist.info LIKE '%TRUNCATE t1%' AND state LIKE '%open_and_process_table%'; +# Test killing from mysql server +KILL QUERY @id; + +connection default; +--ERROR ER_QUERY_INTERRUPTED +--reap + +connection con1; +--source include/show_binlog_events.inc + +disconnect con1; +--source include/wait_until_disconnected.inc +connection default; + +SELECT * FROM t1; + +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; +--disable_connect_log diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result index 2b98bc704d5..ae9440859df 100644 --- a/mysql-test/suite/funcs_1/r/innodb_func_view.result +++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result @@ -3695,14 +3695,14 @@ my_double, id FROM t1_values WHERE select_id = 53 OR select_id IS NULL order by id; CAST(my_double AS TIME) my_double id NULL NULL 1 -NULL -1.7976931348623e308 2 -NULL 1.7976931348623e308 3 +-838:59:59 -1.7976931348623e308 2 +838:59:59 1.7976931348623e308 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 +Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2 +Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -3711,14 +3711,14 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 53 OR select_id IS NULL) order by id; CAST(my_double AS TIME) my_double id NULL NULL 1 -NULL -1.7976931348623e308 2 -NULL 1.7976931348623e308 3 +-838:59:59 -1.7976931348623e308 2 +838:59:59 1.7976931348623e308 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result index 43516edc70a..81f7a0584d3 100644 --- a/mysql-test/suite/funcs_1/r/memory_func_view.result +++ b/mysql-test/suite/funcs_1/r/memory_func_view.result @@ -3696,14 +3696,14 @@ my_double, id FROM t1_values WHERE select_id = 53 OR select_id IS NULL order by id; CAST(my_double AS TIME) my_double id NULL NULL 1 -NULL -1.7976931348623e308 2 -NULL 1.7976931348623e308 3 +-838:59:59 -1.7976931348623e308 2 +838:59:59 1.7976931348623e308 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 +Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2 +Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -3712,14 +3712,14 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 53 OR select_id IS NULL) order by id; CAST(my_double AS TIME) my_double id NULL NULL 1 -NULL -1.7976931348623e308 2 -NULL 1.7976931348623e308 3 +-838:59:59 -1.7976931348623e308 2 +838:59:59 1.7976931348623e308 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result index 43516edc70a..81f7a0584d3 100644 --- a/mysql-test/suite/funcs_1/r/myisam_func_view.result +++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result @@ -3696,14 +3696,14 @@ my_double, id FROM t1_values WHERE select_id = 53 OR select_id IS NULL order by id; CAST(my_double AS TIME) my_double id NULL NULL 1 -NULL -1.7976931348623e308 2 -NULL 1.7976931348623e308 3 +-838:59:59 -1.7976931348623e308 2 +838:59:59 1.7976931348623e308 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 +Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2 +Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -3712,14 +3712,14 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 53 OR select_id IS NULL) order by id; CAST(my_double AS TIME) my_double id NULL NULL 1 -NULL -1.7976931348623e308 2 -NULL 1.7976931348623e308 3 +-838:59:59 -1.7976931348623e308 2 +838:59:59 1.7976931348623e308 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/innodb/r/count_distinct.result b/mysql-test/suite/innodb/r/count_distinct.result new file mode 100644 index 00000000000..201767b8f42 --- /dev/null +++ b/mysql-test/suite/innodb/r/count_distinct.result @@ -0,0 +1,12 @@ +CREATE TABLE t1 ( +id int NOT NULL AUTO_INCREMENT, +a int NOT NULL, +b int NOT NULL, +PRIMARY KEY (id), +UNIQUE KEY ba (b, a) +) ENGINE=InnoDB; +INSERT INTO t1 (a, b) VALUES(1,101),(1,102),(1,103),(1,104),(1,105),(1,106),(1,107),(1,108),(1,109),(1,110); +SELECT COUNT(DISTINCT b) FROM t1 WHERE a = 1; +COUNT(DISTINCT b) +10 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/count_distinct.test b/mysql-test/suite/innodb/t/count_distinct.test new file mode 100644 index 00000000000..94a70260e23 --- /dev/null +++ b/mysql-test/suite/innodb/t/count_distinct.test @@ -0,0 +1,15 @@ +--source include/have_innodb.inc + +# +# MDEV-4925 Wrong result - count(distinct), Using index for group-by (scanning) +# +CREATE TABLE t1 ( + id int NOT NULL AUTO_INCREMENT, + a int NOT NULL, + b int NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY ba (b, a) +) ENGINE=InnoDB; +INSERT INTO t1 (a, b) VALUES(1,101),(1,102),(1,103),(1,104),(1,105),(1,106),(1,107),(1,108),(1,109),(1,110); +SELECT COUNT(DISTINCT b) FROM t1 WHERE a = 1; +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_row_create_select.result b/mysql-test/suite/rpl/r/rpl_row_create_select.result new file mode 100644 index 00000000000..ac95d50df9d --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_create_select.result @@ -0,0 +1,28 @@ +include/master-slave.inc +[connection master] +# +# BUG#17994219: CREATE TABLE .. SELECT PRODUCES INVALID STRUCTURE, +# BREAKS RBR +# +#After the patch, the display width is set to a default +#value of 21. +CREATE TABLE t1 AS SELECT REPEAT('A', 1000) DIV 1 AS a; +Warnings: +Warning 1918 Encountered illegal value '' when converting to DECIMAL +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(21) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t2 AS SELECT CONVERT(REPEAT('A', 255) USING UCS2) DIV 1 AS a; +Warnings: +Warning 1918 Encountered illegal value '' when converting to DECIMAL +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` bigint(21) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +#After the patch, no error is reported. +DROP TABLE t1; +DROP TABLE t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test b/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test index 3123a7da2a5..5b55f11da85 100644 --- a/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test +++ b/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test @@ -363,12 +363,11 @@ let $result= query_get_value(SELECT ($rcvd_heartbeats_after - $rcvd_heartbeats_b --connection master DELETE FROM t1; DROP EVENT e1; +--sync_slave_with_master --echo - # Check received heartbeat events while logs flushed on slave ---sync_slave_with_master --echo *** Flush logs on slave *** STOP SLAVE; RESET SLAVE; diff --git a/mysql-test/suite/rpl/t/rpl_row_create_select.test b/mysql-test/suite/rpl/t/rpl_row_create_select.test new file mode 100644 index 00000000000..ca270e92d0c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_create_select.test @@ -0,0 +1,28 @@ +# Testing table creations for row-based replication. + +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--echo # +--echo # BUG#17994219: CREATE TABLE .. SELECT PRODUCES INVALID STRUCTURE, +--echo # BREAKS RBR +--echo # + +connection master; +--echo #After the patch, the display width is set to a default +--echo #value of 21. +CREATE TABLE t1 AS SELECT REPEAT('A', 1000) DIV 1 AS a; +SHOW CREATE TABLE t1; + +CREATE TABLE t2 AS SELECT CONVERT(REPEAT('A', 255) USING UCS2) DIV 1 AS a; +SHOW CREATE TABLE t2; + +--echo #After the patch, no error is reported. +sync_slave_with_master; + +connection master; +DROP TABLE t1; +DROP TABLE t2; + +--source include/rpl_end.inc + diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4b1d7604b9d..61ae3695a1f 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -465,6 +465,7 @@ drop table t1; # # MDEV-5012 Server crashes in Item_ref::real_item on EXPLAIN with select subqueries or views, constant table, derived_merge+derived_with_keys # +set @save_derived_optimizer_switch_bug=@@optimizer_switch; SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (8); @@ -473,5 +474,23 @@ INSERT INTO t2 VALUES (1),(7); EXPLAIN SELECT * FROM (SELECT * FROM t1) AS table1, (SELECT DISTINCT * FROM t2) AS table2 WHERE b = a AND a <> ANY (SELECT 9); DROP TABLE t1, t2; +set optimizer_switch=@save_derived_optimizer_switch_bug; + +--echo # +--echo # MDEV-6163: Error while executing an update query that has the +--echo # same table in a sub-query +--echo # + +set @save_derived_optimizer_switch_bug=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +create table t1 (balance float, accountId varchar(64), primary key (accountId)); + +insert into t1 (accountId,balance) values +('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0); + +update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; +set optimizer_switch=@save_derived_optimizer_switch_bug; +drop table t1; + set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index 626309330b7..c5d442ebe49 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -640,6 +640,13 @@ SELECT COLUMN_ADD( dyncol, 'a', '22:22:22', 'c', REPEAT('x',270) AS CHAR ) FROM DROP table t1; --echo # +--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column +--echo # +SELECT + column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1, + column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2; + +--echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index b65e634ea61..ed2f0e86bb7 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1522,6 +1522,11 @@ SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP; DROP TABLE t1; --echo # +--echo # MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND) +--echo # +SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2; + +--echo # --echo # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL) --echo # --enable_metadata diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index bf84bdf9194..35243864c04 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -637,3 +637,24 @@ create table t1(a point); drop table t1; --echo End of 5.1 tests + +--echo # +--echo # Bug#11759519 INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA LOCAL AND IMPORT ERRORS +--echo # +SET @old_mode= @@sql_mode; +CREATE TABLE t1 (fld1 INT); +--copy_file $EXE_MYSQL $MYSQLTEST_VARDIR/mysql + +SET sql_mode='strict_all_tables'; + +--echo # Without fix, load data hangs forever. +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD,1000 +eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/mysql' REPLACE INTO TABLE t1 + FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; + +SET @@sql_mode= @old_mode; + +--remove_file $MYSQLTEST_VARDIR/mysql +DROP TABLE t1; + diff --git a/mysql-test/t/myisampack.test b/mysql-test/t/myisampack.test index ac47d521930..1f97a28e6fd 100644 --- a/mysql-test/t/myisampack.test +++ b/mysql-test/t/myisampack.test @@ -268,6 +268,53 @@ FLUSH TABLE t1; CHECK TABLE t1; DROP TABLE t1; +# +# MDEV-6245 Certain compressed tables with myisampack are corrupted by +# "CHECK TABLE" +# +# Issue was that checksum failed for tables with NULL and VARCHAR fields +# + +create table `t1` (`id` varchar(15) DEFAULT NULL) ENGINE=MyISAM ROW_FORMAT=FIXED; +insert into t1 values ('aaa'),('bbb'),('ccc'),('ddd'),('eee'); +insert into t1 (select * from t1); +insert into t1 (select * from t1); +insert into t1 (select * from t1); +insert into t1 (select * from t1); +checksum table t1; +insert into t1 values(NULL); +checksum table t1; +flush table t1; +--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 +--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 +check table t1; +checksum table t1; +alter table t1 checksum=1 row_format=fixed; +checksum table t1; +flush table t1; +--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 +--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 +check table t1; +checksum table t1; + +# Testing with row_format=dynamic + +alter table t1 row_format=dynamic checksum=0; +checksum table t1; +flush table t1; +--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 +--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 +check table t1; +checksum table t1; +alter table t1 checksum=1 row_format=dynamic; +checksum table t1; +flush table t1; +--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 +--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 +check table t1; +checksum table t1; +drop table t1; + --echo # --echo # BUG#11751736: DROP DATABASE STATEMENT SHOULD REMOVE .OLD SUFFIX FROM --echo # DATABASE DIRECTORY diff --git a/mysql-test/t/outfile_loaddata.test b/mysql-test/t/outfile_loaddata.test index 26760f9a1b2..745c75cb4de 100644 --- a/mysql-test/t/outfile_loaddata.test +++ b/mysql-test/t/outfile_loaddata.test @@ -169,6 +169,8 @@ TRUNCATE t2; --eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'ъ' --remove_file $file SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; +SELECT * FROM t1; +SELECT * FROM t2; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --eval SELECT * FROM t1 INTO OUTFILE '$file' LINES STARTING BY 'ъ' @@ -191,6 +193,8 @@ TRUNCATE t2; --eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary LINES TERMINATED BY 'ъ' --remove_file $file SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; +SELECT * FROM t1; +SELECT * FROM t2; --echo # Default (binary) charset: diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index e7e764ce138..4c97bab454d 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -1412,3 +1412,60 @@ explain partitions select * from t1 where a between 10 and 13; explain partitions select * from t1 where a between 10 and 10+33; drop table t0, t1; + +--echo # +--echo # MDEV-6239: Partition pruning is not working as expected in an inner query +--echo # + +create table t1 +( + company_id bigint(5), + dept_id bigint(5), + emp_id bigint(5), + emp_name varchar(100), + primary key (company_id, emp_id) +) partition by list (company_id) ( + partition p_1000 values in (1000), + partition p_2000 values in (2000), + partition p_3000 values in (3000) +); + +create table t2 +( + company_id bigint(5), + dept_id bigint(5), + dept_name varchar(100), + primary key (company_id, dept_id) +) partition by list (company_id) ( + partition p_1000 values in (1000), + partition p_2000 values in (2000), + partition p_3000 values in (3000) +); + +insert into t2 values + (1000, 10, 'Engineering'), + (1000, 20, 'Product Management'), + (1000, 30, 'QA'), + (2000, 40, 'Support'), + (2000, 50, 'Professional Services'); + +insert into t1 values +(1000, 10, 1, 'John'), +(1000, 10, 2, 'Smith'), +(1000, 20, 3, 'Jacob'), +(1000, 20, 4, 'Brian'), +(1000, 30, 5, 'Chris'), +(1000, 30, 6, 'Ryan'), +(2000, 40, 7, 'Karin'), +(2000, 40, 8, 'Jay'), +(2000, 50, 9, 'Ana'), +(2000, 50, 10, 'Jessica'); + +--echo # Table t2 should have only partition p_1000. +explain partitions +select * from t1 +where company_id = 1000 +and dept_id in (select dept_id from t2 where COMPANY_ID = 1000); + +drop table t1,t2; + diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index f0434f50c98..7b68f42c4cb 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1640,6 +1640,45 @@ DROP TABLE t1; DROP VIEW v3; --echo # +--echo # MDEV-6105: Emoji unicode character string search query makes mariadb performance down +--echo # +SET NAMES utf8; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1( +id int AUTO_INCREMENT, +fd varchar(20), +primary key(id), +index ix_fd(fd) +)engine=innodb default charset=UTF8; +INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim'); +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +INSERT INTO t1 (fd) SELECT fd FROM t1; +--echo # The following should show "Impossible WHERE" : +explain +SELECT * FROM t1 WHERE fd='😁'; +SELECT * FROM t1 WHERE fd='😁'; + +--echo # The following must not use range access: +--replace_column 9 # +explain select count(*) from t1 where fd <'😁'; +select count(*) from t1 where fd <'😁'; +select count(*) from t1 ignore index (ix_fd) where fd <'😁'; + +drop table t1; +set names default; + +--echo # --echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE --echo # @@ -1706,4 +1745,3 @@ explain select * from t3, t2 where t2.a < t3.b and t3.a=1; --echo # The second table should use 'range': explain select * from t3, t2 where t3.b > t2.a and t3.a=1; drop table t1,t2,t3; - diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 9d9e7848415..a82baf095c1 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1291,5 +1291,34 @@ SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_p DROP TABLE t1,t2,t3,t4; +--echo # +--echo # MDEV-6263: Wrong result when using IN subquery with order by +--echo # +CREATE TABLE t1 ( + id int(11) NOT NULL, + nombre varchar(255) NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t1 (id, nombre) VALUES +(1, 'row 1'),(2, 'row 2'),(3, 'row 3'), +(4, 'row 4'),(5, 'row 5'),(6, 'row 6'); + +CREATE TABLE t2 ( + id_algo int(11) NOT NULL, + id_agente int(11) NOT NULL, + PRIMARY KEY (id_algo,id_agente), + KEY another_data (id_agente) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t2 (id_algo, id_agente) VALUES +(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1); + +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC; + +SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); + +DROP TABLE t1, t2; + --echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index b841d8b1def..e2ac9122a10 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -519,8 +519,16 @@ END;| DELIMITER ;| call test5041(); drop procedure test5041; - SET @@timestamp=DEFAULT; + +--echo # +--echo # MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME) +--echo # +SELECT + CAST(010203101112 AS DATETIME(1)) AS c1, + CAST(010203101112.2 AS DATETIME(1)) AS c2, + CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3; + --echo End of 5.3 tests --echo # diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 1daeec03a08..3b839905848 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -205,6 +205,23 @@ SELECT * FROM t2; SHOW COLUMNS FROM t2; DROP TABLE t2,t1; +--echo # +--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column +--echo # +SET sql_mode=traditional; +CREATE TABLE t1 (a TIME(6)); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); +SET sql_mode=DEFAULT; +INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); +SELECT * FROM t1; +DROP TABLE t1; +SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED); + +--echo # +--echo # MDEV-6100 No warning on CAST(9000000 AS TIME) +--echo # +SELECT CAST(9000000 AS TIME); --echo # --echo # End of 5.3 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index a53427f7fc6..877509a9fc0 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1274,8 +1274,8 @@ SELECT(SELECT 1 AS a LIMIT 1) AS dev; SELECT(SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev; --echo # ---echo # LP bug#1010729: Unexpected syntax error from UNION ---echo # (bug #54382) with single-table join nest +--echo # lp:1010729: Unexpected syntax error from UNION +--echo # (bug #54382) with single-table join nest --echo # CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); @@ -1292,3 +1292,11 @@ SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); DROP TABLE t1,t2; +# +# Bug #18167356: EXPLAIN W/ EXISTS(SELECT* UNION SELECT*) +# WHERE ONE OF SELECT* IS DISTINCT FAILS. +# +create table t1 (a int); +insert t1 values (1),(2),(3),(1); +explain select 1 from dual where exists (select max(a) from t1 group by a union select a+2 from t1); +drop table t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index e36e22bdfac..d1d4b936aba 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1,7 +1,7 @@ --disable_warnings -drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; -drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6; +drop table if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; +drop view if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; drop database if exists mysqltest; --enable_warnings use test; @@ -4083,6 +4083,118 @@ select * from t2; DROP VIEW v1,v2,v3; DROP TABLE t1,t2; +--echo # +--echo # MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized +--echo # with MERGE view) +--echo # + +CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); + +CREATE OR REPLACE view v1 AS + SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +; + +SELECT 1 +FROM (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t1) +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t8) ON 1=1 +; + +SELECT 1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 +; + +drop view v1; +drop table t1,t2,t3,t4,t5,t6; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.2 tests. --echo # ----------------------------------------------------------------- @@ -4971,6 +5083,119 @@ execute stmt1; deallocate prepare stmt1; drop view v1,v2; drop table t1,t2; + +--echo # +--echo # MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized +--echo # with MERGE view) +--echo # + +CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); + +CREATE OR REPLACE view v1 AS + SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +; + +SELECT 1 +FROM (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t1) +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 + FROM t1 a_alias_1 + LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 + LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 + LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 + LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 + LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 +) t8) ON 1=1 +; + +SELECT 1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 +; + +drop view v1; +drop table t1,t2,t3,t4,t5,t6; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- |