diff options
Diffstat (limited to 'mysql-test/r')
43 files changed, 1701 insertions, 112 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 13fbe00f2e3..b8f51ee25ad 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1359,6 +1359,58 @@ rename table t2 to t1; execute stmt1; deallocate prepare stmt1; drop table t2; +# +# MDEV-8960 Can't refer the same column twice in one ALTER TABLE +# +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` SET DEFAULT 2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL DEFAULT 2 +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT, +MODIFY COLUMN `consultant_id` BIGINT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` bigint(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; CREATE TABLE t1 ( id INT(11) NOT NULL, x_param INT(11) DEFAULT NULL, @@ -2125,59 +2177,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DROP TABLE t1; # -# MDEV-8960 Can't refer the same column twice in one ALTER TABLE -# -CREATE TABLE t1 ( -`a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, -ALTER COLUMN `consultant_id` DROP DEFAULT; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL, - `consultant_id` int(11) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8 -DROP TABLE t1; -CREATE TABLE t1 ( -`a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, -ALTER COLUMN `consultant_id` SET DEFAULT 2; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL, - `consultant_id` int(11) NOT NULL DEFAULT 2 -) ENGINE=MyISAM DEFAULT CHARSET=utf8 -DROP TABLE t1; -CREATE TABLE t1 ( -`a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, -ALTER COLUMN `consultant_id` DROP DEFAULT; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL, - `consultant_id` int(11) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8 -DROP TABLE t1; -CREATE TABLE t1 ( -`a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, -ALTER COLUMN `consultant_id` DROP DEFAULT, -MODIFY COLUMN `consultant_id` BIGINT; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL, - `consultant_id` bigint(20) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8 -DROP TABLE t1; -# -# Start of 10.1 tests +# End of 10.0 tests # # # MDEV-7374 : Losing connection to MySQL while running ALTER TABLE @@ -2201,6 +2201,9 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # +# End of 10.1 tests +# +# # MDEV-10421 duplicate CHECK CONSTRAINTs # CREATE TABLE t1 (a INT, b INT) engine=myisam; @@ -2272,3 +2275,6 @@ t1 CREATE TABLE `t1` ( `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/r/cache_temporal_4265.result b/mysql-test/r/cache_temporal_4265.result index 980bb957e19..7f215de43fb 100644 --- a/mysql-test/r/cache_temporal_4265.result +++ b/mysql-test/r/cache_temporal_4265.result @@ -7,7 +7,6 @@ a 2002-03-04 Warnings: Note 1003 2000-01-01 -Note 1003 2000-01-06 set debug_dbug=''; drop table t1; create table t1 (id int not null, ut timestamp(6) not null); diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index ee2db76e517..08fa5a79743 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -228,6 +228,22 @@ a d 3 11120436154190595086 drop table t1, t2; End of 5.0 tests +# +# Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE +# (SIG 6 -STRINGS/CTYPE-UTF8.C:5151) +# +set @@sql_mode=''; +CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2); +Warnings: +Note 1291 Column 'c1' has duplicated value '' in SET +INSERT INTO t1 VALUES(990101.102); +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +SELECT COALESCE(c1)FROM t1 ORDER BY 1; +COALESCE(c1) + +DROP TABLE t1; +set @@sql_mode=default; CREATE TABLE t1(a YEAR); SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END; 1 diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 2fceebd1971..3ad6fb8fabe 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -86,7 +86,7 @@ select * from t2,t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where 1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort explain select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t where t2.c=t.a; @@ -176,7 +176,7 @@ select * from t2 where c in (select c from t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where 1 PRIMARY <derived2> ref key0 key0 8 test.t2.c 2 Using where; FirstMatch(t2) -2 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort explain select * from t2 where c in (select c from (select count(*) as c from t1 @@ -245,8 +245,8 @@ select * from t as r1, t as r2 where r1.a=r2.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where 1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary -2 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary +3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary explain select * from (select distinct a from t1 where b >= 'c') as r1, (select distinct a from t1 where b >= 'c') as r2 @@ -370,7 +370,7 @@ select * from t2,t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where 1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL explain @@ -598,7 +598,7 @@ select * from v2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where 1 PRIMARY <derived3> ref key0 key0 5 test.t2.c 2 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort # with clause in the specification of a view that whose definition # table alias for a with table create view v3 as @@ -1055,3 +1055,27 @@ deallocate prepare stmt1; deallocate prepare stmt2; drop view v1,v2; drop table t1,t2; +# +# MDEV-13796: UNION of two materialized CTEs +# +CREATE TABLE t1 (id int, k int); +CREATE TABLE t2 (id int); +INSERT INTO t1 VALUES (3,5), (1,7), (4,3); +INSERT INTO t2 VALUES (4), (3), (2); +WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id), +d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id) +SELECT * FROM d1 UNION SELECT * FROM d2; +SUM(k) +8 +explain WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id), +d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id) +SELECT * FROM d1 UNION SELECT * FROM d2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +4 UNION <derived3> ALL NULL NULL NULL NULL 9 +3 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1,t2; diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 946ba16ac5c..a4f32927cf1 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -86,7 +86,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a select * from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 30 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where 3 UNION t1 ALL NULL NULL NULL NULL 5 3 UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -114,7 +114,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a select * from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where 3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5 3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -3 SUBQUERY folks ALL NULL NULL NULL NULL 12 100.00 Using where +3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL -2 UNCACHEABLE SUBQUERY <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (/* select#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -877,7 +877,7 @@ where p.id = a.father or p.id = a.mother select * from ancestors; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 -2 SUBQUERY folks ALL NULL NULL NULL NULL 12 100.00 Using where +2 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 3 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL @@ -1236,7 +1236,7 @@ where p.id = ma.mother select * from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 -2 SUBQUERY folks ALL NULL NULL NULL NULL 12 Using where +2 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 3 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 4 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 @@ -1300,14 +1300,14 @@ from prev_gen select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 -4 SUBQUERY folks ALL NULL NULL NULL NULL 12 Using where +4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL -3 SUBQUERY folks ALL NULL NULL NULL NULL 12 Using where +3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -1353,7 +1353,6 @@ EXPLAIN { "query_block": { "select_id": 4, - "operation": "UNION", "table": { "table_name": "folks", "access_type": "ALL", @@ -1382,7 +1381,6 @@ EXPLAIN { "query_block": { "select_id": 3, - "operation": "UNION", "table": { "table_name": "folks", "access_type": "ALL", @@ -1489,7 +1487,6 @@ EXPLAIN { "query_block": { "select_id": 3, - "operation": "UNION", "table": { "table_name": "v", "access_type": "ALL", @@ -1757,7 +1754,6 @@ EXPLAIN { "query_block": { "select_id": 2, - "operation": "UNION", "table": { "table_name": "t1", "access_type": "ALL", @@ -1840,7 +1836,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a select * from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5 4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL @@ -2387,7 +2383,6 @@ ANALYZE { "query_block": { "select_id": 2, - "operation": "UNION", "table": { "message": "No tables used" } @@ -2794,7 +2789,7 @@ SELECT c1 FROM t, cte ) SELECT COUNT(*) FROM cte; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 -2 SUBQUERY t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +2 DERIVED t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 3 RECURSIVE UNION t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using join buffer (flat, BNL join) NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL @@ -2812,7 +2807,7 @@ SELECT c2 FROM t, cte ) SELECT COUNT(*) FROM cte; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 -2 SUBQUERY t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +2 DERIVED t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 3 RECURSIVE UNION t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using join buffer (flat, BNL join) NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result index a6cea80fef5..5244d6eb333 100644 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@ -5078,6 +5078,24 @@ E05B DROP TABLE t1; # Start of ctype_E05C.inc # +# MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +# +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET gbk); +INSERT INTO t1 VALUES (0xEE5D); +SELECT a<>0xEE5D AS a FROM t1; +a +0 +CREATE VIEW v1 AS SELECT a<>0xEE5D AS a FROM t1; +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 `t1`.`a` <> 0xee5d AS `a` from `t1` latin1 latin1_swedish_ci +SELECT * FROM v1; +a +0 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.0 tests # # diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index 94b25849a2a..f9e0ae5a455 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -8005,6 +8005,24 @@ SELECT _latin1 0x7E, _latin1 X'7E', _latin1 B'01111110'; _latin1 0x7E _latin1 X'7E' _latin1 B'01111110' ~ ~ ~ # +# MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +# +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET latin1); +INSERT INTO t1 VALUES (0xC0); +SELECT a<>0xEE5D AS a FROM t1; +a +1 +CREATE VIEW v1 AS SELECT a<>0xC0 AS a FROM t1; +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 `t1`.`a` <> 0xc0 AS `a` from `t1` latin1 latin1_swedish_ci +SELECT * FROM v1; +a +0 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.0 tests # # diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 5b914258515..fcc8f27010c 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -4564,6 +4564,38 @@ NO_ENGINE_SUBSTITUTION SET sql_mode=DEFAULT; SET NAMES utf8; # +# MDEV-13972 crash in Item_func_sec_to_time::get_date +# +SELECT SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)); +SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '77760000' +# +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +set sql_mode=""; +CREATE TABLE t1 (c1 VARCHAR(32766) CHARACTER SET ucs2); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(32766) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(32767) CHARACTER SET ucs2); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(32768) CHARACTER SET ucs2); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +set sql_mode=default; +# # End of 5.5 tests # # diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index 0a4a49dd39e..59ce4c2f445 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -1680,6 +1680,23 @@ NO_ENGINE_SUBSTITUTION SET sql_mode=DEFAULT; SET NAMES utf8; # +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +set sql_mode=''; +CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(16383) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +set sql_mode=default; +# # End of 5.5 tests # # diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index ae5466ea776..7381c1c4004 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -6278,6 +6278,30 @@ Warnings: SET sql_mode=DEFAULT; DROP TABLE t1; # +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +SET sql_mode=''; +CREATE TABLE t1 (c1 VARCHAR(21844) CHARACTER SET utf8); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(21844) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(21845) CHARACTER SET utf8); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(21846) CHARACTER SET utf8); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +SET sql_mode=default; +# # End of 5.5 tests # # diff --git a/mysql-test/r/delete_returning.result b/mysql-test/r/delete_returning.result index 0618b495a53..3a95de0cdca 100644 --- a/mysql-test/r/delete_returning.result +++ b/mysql-test/r/delete_returning.result @@ -199,3 +199,15 @@ i 2 DROP PROCEDURE p1; DROP TABLE t1; +# +# MDEV-13776: DELETE ... RETURNING with sql_mode='ONLY_FULL_GROUP_BY' +# +set @sql_mode_save= @@sql_mode; +set sql_mode='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUE(1),(2),(3); +DELETE FROM t1 WHERE id > 2 RETURNING *; +id +3 +set sql_mode=@sql_mode_save; +DROP TABLE t1; diff --git a/mysql-test/r/empty_string_literal.result b/mysql-test/r/empty_string_literal.result new file mode 100644 index 00000000000..2ca491a7dd8 --- /dev/null +++ b/mysql-test/r/empty_string_literal.result @@ -0,0 +1,181 @@ +USE test; +# +# MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL +# +set @mode='EMPTY_STRING_IS_NULL'; +SET SESSION character_set_connection=latin2; +SET SESSION character_set_client=cp1250; +# +# Test litteral +# +SET sql_mode=@mode; +select @@sql_mode; +@@sql_mode +EMPTY_STRING_IS_NULL +SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x'); +NULL CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x') +NULL latin2 NULL binary NULL latin2 x latin2 +SELECT CHARSET(NULLIF('','')),NULLIF('',''); +CHARSET(NULLIF('','')) NULLIF('','') +latin2 NULL +SET sql_mode=default; +SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x'); + CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x') + latin2 NULL binary NULL latin2 x latin2 +SELECT CHARSET(NULLIF('','')),NULLIF('',''); +CHARSET(NULLIF('','')) NULLIF('','') +latin2 NULL +# +# Test NCHAR litteral +# +SET sql_mode=@mode; +SELECT N'',CHARSET(N''), N'x', CHARSET(N'x'); +NULL CHARSET(N'') x CHARSET(N'x') +NULL utf8 x utf8 +SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N''); +CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'') +utf8 NULL +SET sql_mode=default; +SELECT N'',CHARSET(N''), N'x', CHARSET(N'x'); + CHARSET(N'') x CHARSET(N'x') + utf8 x utf8 +SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N''); +CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'') +utf8 NULL +# +# Test CHARSET prefix litteral +# +SET sql_mode=@mode; +SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x'); +NULL CHARSET(_cp1250 '') x CHARSET(_cp1250 'x') +NULL cp1250 x cp1250 +SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 ''); +CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '') +cp1250 NULL +SET sql_mode=default; +SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x'); + CHARSET(_cp1250 '') x CHARSET(_cp1250 'x') + cp1250 x cp1250 +SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 ''); +CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '') +cp1250 NULL +SET sql_mode=@mode; +# +# Test litteral concat +# +SELECT 'a' 'b'; +a +ab +SELECT 'a' ''; +a +a +SELECT '' 'b'; +b +b +SELECT '' ''; +NULL +NULL +SELECT '' 'b' 'c'; +b +bc +SELECT '' '' 'c'; +c +c +SELECT 'a' '' 'c'; +a +ac +SELECT 'a' '' ''; +a +a +SELECT '' '' ''; +NULL +NULL +SELECT '' '' '',CHARSET('' '' ''); +NULL CHARSET('' '' '') +NULL latin2 +SELECT _latin1'' '' '',CHARSET(_latin1'' '' ''); +NULL CHARSET(_latin1'' '' '') +NULL latin1 +SELECT N'' '' '',CHARSET(N'' '' ''); +NULL CHARSET(N'' '' '') +NULL utf8 +# +# UNION - implicit group by +# +SELECT 1, null +UNION +SELECT 1 , '' +ORDER BY 1; +1 NULL +1 NULL +SELECT 1, null +UNION +SELECT 1 , N'' +ORDER BY 1; +1 NULL +1 NULL +SELECT 1, null +UNION +SELECT 1 , _cp1250 '' +ORDER BY 1; +1 NULL +1 NULL +SELECT NULLIF(_cp1250 '',_cp1250 '') +UNION +SELECT NULLIF(N'',N''); +NULLIF(_cp1250 '',_cp1250 '') +NULL +SELECT 1 , _latin2 '' +UNION +SELECT 1 , _cp1250 ''; +ERROR HY000: Illegal mix of collations (latin2_general_ci,IGNORABLE) and (cp1250_general_ci,IGNORABLE) for operation 'UNION' +SELECT 1, null +UNION +SELECT 1 , '' +UNION +SELECT 1 , N''; +1 NULL +1 NULL +CREATE TABLE t1 (c1 INT,c2 VARCHAR(10)); +INSERT INTO t1 VALUES (1,'one'); +INSERT INTO t1 VALUES (1,''); +INSERT INTO t1 VALUES (1,null); +# +# Test in a view +# +CREATE VIEW v1 +AS SELECT c1, c2 +FROM t1 +UNION +SELECT c1 , '' + FROM t1 +ORDER BY 1,2; +SELECT * FROM v1; +c1 c2 +1 NULL +1 one +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 `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` union select `t1`.`c1` AS `c1`,NULL AS `NULL` from `t1` order by 1,2 cp1250 latin2_general_ci +DROP VIEW v1; +DROP TABLE t1; +EXPLAIN EXTENDED SELECT ''; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select NULL AS `NULL` +EXPLAIN EXTENDED SELECT _latin1''; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select NULL AS `NULL` +EXPLAIN EXTENDED SELECT N''; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select NULL AS `NULL` +EXPLAIN EXTENDED SELECT '' ''; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select NULL AS `NULL` diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result index 3c849d5a649..bcc171e404d 100644 --- a/mysql-test/r/errors.result +++ b/mysql-test/r/errors.result @@ -27,7 +27,7 @@ create table t1 (a int(256)); ERROR 42000: Display width out of range for 'a' (max = 255) set sql_mode='traditional'; create table t1 (a varchar(66000)); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead set sql_mode=default; CREATE TABLE t1 (a INT); SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index f12fc40e9aa..692806c7cac 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -813,9 +813,17 @@ PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM EXECUTE s; 1 DROP TABLE t1; +# # End of 5.3 tests # -# Start of 10.0 tests +create table t1 (a int); +insert t1 values (1),(2),(3); +select * from t1 where 1 in (a, name_const('a', null)); +a +1 +drop table t1; +# +# End of 5.5 tests # # # MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL @@ -831,7 +839,7 @@ SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A'); a b DROP TABLE t1; # -# Start of 10.1 tests +# End of 10.0 tests # # # MDEV-8755 Equal field propagation is not performed any longer for the IN list when multiple comparison types diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index b1d3d96aa73..15e4fbec605 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -152,6 +152,9 @@ json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]') select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]'); json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]') 1 +select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}'); +json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}') +1 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]"); json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]") 1 @@ -402,6 +405,13 @@ abc select json_unquote('abc'); json_unquote('abc') abc +create table t1 (c VARCHAR(8)) DEFAULT CHARSET=latin1; +insert into t1 values ('abc'),('def'); +select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1; +fld +{"foo": "{\"bar\": \"abc\"}", "qux": "abc"} +{"foo": "{\"bar\": \"def\"}", "qux": "def"} +drop table t1; select json_object("a", json_object("b", "abcd")); json_object("a", json_object("b", "abcd")) {"a": {"b": "abcd"}} @@ -443,6 +453,11 @@ json_length('{"a": 1, "b": {"c": 30}}', '$.b') select json_length('{"a": 1, "b": {"c": 30}}'); json_length('{"a": 1, "b": {"c": 30}}') 2 +select json_length('{}{'); +json_length('{}{') +NULL +Warnings: +Warning 4038 Syntax error in JSON text in argument 1 to function 'json_length' at position 3 create table json (j INT); show create table json; Table Create Table @@ -705,6 +720,11 @@ json_data SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data; json_data {"user": "Jožko Mrkvičká"} +select json_contains_path('{"foo":"bar"}', 'one', '$[]'); +json_contains_path('{"foo":"bar"}', 'one', '$[]') +NULL +Warnings: +Warning 4042 Syntax error in JSON path in argument 3 to function 'json_contains_path' at position 3 # # Start of 10.3 tests # diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result index ece2019ef83..e030df99756 100644 --- a/mysql-test/r/func_regexp_pcre.result +++ b/mysql-test/r/func_regexp_pcre.result @@ -885,32 +885,32 @@ 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,',133),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; -CONCAT(REPEAT('100,',133),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +SELECT CONCAT(REPEAT('100,',60),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',60),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' 1 SELECT CONCAT(REPEAT('100,',200),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; CONCAT(REPEAT('100,',200),'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,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); -REGEXP_INSTR(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') 1 SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); REGEXP_INSTR(CONCAT(REPEAT('100,',200),'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,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); -LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) -535 +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +243 SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',200),'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,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); -LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) 0 SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 9da6caef8e6..e710f3ac438 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2772,7 +2772,33 @@ SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2 3 # -# Start of 10.0 tests +# MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +# +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002'); +SELECT * FROM t1; +a +46:58:57.999999 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-13972 crash in Item_func_sec_to_time::get_date +# +DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); +DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect time value: '20000101' +SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +SEC_TO_TIME(MAKEDATE(0,RAND(~0))) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '20000101' +# +# End of 5.5 tests # # # MDEV-8205 timediff returns null when comparing decimal time to time string value diff --git a/mysql-test/r/gis-precise.result b/mysql-test/r/gis-precise.result index f816278a0ba..292dfe0462c 100644 --- a/mysql-test/r/gis-precise.result +++ b/mysql-test/r/gis-precise.result @@ -485,6 +485,25 @@ ST_Touches(ST_PolygonFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))'),ST_PointFromText select ST_Touches(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)')); ST_Touches(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)')) 0 +SELECT ST_RELATE( +ST_DIFFERENCE( +GEOMETRYFROMTEXT(' + MULTILINESTRING( + ( 12841 36140, 8005 31007, 26555 31075, 52765 41191, + 28978 6548, 45720 32057, 53345 3221 ), + ( 8304 59107, 25233 31592, 40502 25303, 8205 42940 ), + ( 7829 7305, 58841 56759, 64115 8512, 37562 54145, 2210 14701 ), + ( 20379 2805, 40807 27770, 28147 14883, 26439 29383, 55663 5086 ), + ( 35944 64702, 14433 23728, 49317 26241, 790 16941 ) + ) + '), +GEOMETRYFROMTEXT('POINT(46061 13545)') +), +GEOMETRYFROMTEXT('POINT(4599 60359)'), +'F*FFFF**F' + ) as relate_res; +relate_res +0 DROP TABLE IF EXISTS p1; CREATE PROCEDURE p1(dist DOUBLE, geom TEXT) BEGIN diff --git a/mysql-test/r/gis2.result b/mysql-test/r/gis2.result index 214431e1d2d..c0b476e080b 100644 --- a/mysql-test/r/gis2.result +++ b/mysql-test/r/gis2.result @@ -12,3 +12,27 @@ WHERE ST_Contains(point_data, GeomFromText('Point(38.0248492 23.8512726)')); id 2 DROP TABLE t1; +create table t1 (p point default "qwer"); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +create table t1 (p point default 0); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +create table t1 (p point not null default st_geometryfromtext('point 0)')); +ERROR 42000: Invalid default value for 'p' +create table t1 (p point not null default st_geometryfromtext('point(0 0)')); +insert into t1 values(default); +select st_astext(p) from t1; +st_astext(p) +POINT(0 0) +drop table t1; +create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))); +set timestamp=10; +insert into t1 values(default); +ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION' for column 'p' at row 1 +drop table t1; +SET timestamp=default; +create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))); +set timestamp=10; +alter table t1 add column i int; +ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION' for column 'p' at row 1 +drop table t1; +SET timestamp=default; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 00ec9d9f39c..1c073881a9d 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -659,7 +659,7 @@ proc body longblob proc definer char(141) proc created timestamp proc modified timestamp -proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') +proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') proc comment text proc character_set_client char(32) proc collation_connection char(32) @@ -2140,3 +2140,35 @@ drop database db1; connection default; disconnect con1; set global sql_mode=default; +USE test; +# +# End of 10.0 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-13242 Wrong results for queries with row constructors and information_schema +# +CREATE TABLE tt1(c1 INT); +CREATE TABLE tt2(c2 INT); +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1', 'c1')); +count(*) +1 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt2', 'c2')); +count(*) +1 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')); +count(*) +2 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (SELECT 'tt1','c1' FROM dual UNION SELECT 'tt2', 'c2' FROM dual); +count(*) +2 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name='tt1' AND column_name='c1') OR (table_name='tt2' AND column_name='c2'); +count(*) +2 +SELECT column_name FROM information_schema.columns WHERE (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')) ORDER BY column_name; +column_name +c1 +c2 +DROP TABLE tt1, tt2; diff --git a/mysql-test/r/mdev13607.result b/mysql-test/r/mdev13607.result new file mode 100644 index 00000000000..08848bc645b --- /dev/null +++ b/mysql-test/r/mdev13607.result @@ -0,0 +1,469 @@ +# +# Bug mdev-13607: overflow of current_record_count +# +CREATE TABLE t1 (id INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1),(2),(3),(4),(5),(6),(7),(8),(9),(10), +(11),(12),(13),(14),(15),(16),(17),(18),(19),(20), +(21),(22),(23),(24),(25),(26),(27),(28),(29),(30), +(31),(32),(33),(34),(35),(36),(37),(38),(39),(40), +(41),(42),(43),(44),(45),(46),(47),(48),(49),(50); +CREATE TABLE t2 (id INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (id INT) ENGINE=InnoDB; +INSERT INTO t3 VALUES (1),(2); +ANALYZE TABLE t1, t2, t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +explain SELECT * FROM +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_1 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_2 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_3 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_4 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_5 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_6 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_7 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_8 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_9 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_10 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_11 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_12 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_13 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_14 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_15 +INNER JOIN +(SELECT p1.* FROM t1 p1 NATURAL JOIN t2 r1 NATURAL JOIN t3 d1 NATURAL JOIN t1 p2 NATURAL JOIN t2 r2 NATURAL JOIN t3 d2 NATURAL JOIN t1 p3 NATURAL JOIN t2 r3 NATURAL JOIN t3 d3 NATURAL JOIN t1 p4 NATURAL JOIN t2 r4 NATURAL JOIN t3 d4 NATURAL JOIN t1 p5 NATURAL JOIN t2 r5 NATURAL JOIN t3 d5 NATURAL JOIN t1 p6 NATURAL JOIN t2 r6 NATURAL JOIN t3 d6 NATURAL JOIN t1 p7 NATURAL JOIN t2 r7 NATURAL JOIN t3 d7 NATURAL JOIN t1 p8 NATURAL JOIN t2 r8 NATURAL JOIN t3 d8 NATURAL JOIN t1 p9 ) gp_16 +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY r1 ALL NULL NULL NULL NULL 2 +1 PRIMARY d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 PRIMARY r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived4> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived5> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived6> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived14> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived17> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +17 DERIVED r1 ALL NULL NULL NULL NULL 2 +17 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +17 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +17 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r1 ALL NULL NULL NULL NULL 2 +16 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +16 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +16 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r1 ALL NULL NULL NULL NULL 2 +15 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +15 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +15 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r1 ALL NULL NULL NULL NULL 2 +14 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +14 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +14 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r1 ALL NULL NULL NULL NULL 2 +13 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +13 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +13 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r1 ALL NULL NULL NULL NULL 2 +12 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +12 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +12 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r1 ALL NULL NULL NULL NULL 2 +11 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +11 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +11 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r1 ALL NULL NULL NULL NULL 2 +10 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +10 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +10 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r1 ALL NULL NULL NULL NULL 2 +9 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +9 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +9 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r1 ALL NULL NULL NULL NULL 2 +8 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +8 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +8 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r1 ALL NULL NULL NULL NULL 2 +7 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +7 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +7 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r1 ALL NULL NULL NULL NULL 2 +6 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +6 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +6 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r1 ALL NULL NULL NULL NULL 2 +5 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +5 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +5 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r1 ALL NULL NULL NULL NULL 2 +4 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +4 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +4 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r1 ALL NULL NULL NULL NULL 2 +3 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED r8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED d8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p1 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p3 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p4 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p5 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p6 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +3 DERIVED p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 78eb374b142..f20a08d690a 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1717,8 +1717,16 @@ t1 CREATE TABLE `t1` ( `v` mediumtext CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +set statement sql_mode='' for create table t1 (v varchar(65535)); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; set storage_engine=MyISAM; set @save_concurrent_insert=@@concurrent_insert; set global concurrent_insert=1; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 13f7507d4cb..0037511b741 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1145,7 +1145,8 @@ The following options may be given as the first argument: NO_ZERO_DATE, ALLOW_INVALID_DATES, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE, - NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH + NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH, + EMPTY_STRING_IS_NULL --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) --standard-compliant-cte diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result index f0921a2097c..2e518c194f0 100644 --- a/mysql-test/r/partition_datatype.result +++ b/mysql-test/r/partition_datatype.result @@ -312,14 +312,18 @@ select * from t1 where a = 'bbbb'; a bbbb drop table t1; +set sql_mode=''; create table t1 (a varchar(3070)) partition by key (a); ERROR HY000: The total length of the partitioning fields is too large +create table t1 (a varchar(65532) not null) partition by key (a); +ERROR HY000: The total length of the partitioning fields is too large create table t1 (a varchar(65533)) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +ERROR HY000: A BLOB field is not allowed in partition function create table t1 (a varchar(65534) not null) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +ERROR HY000: A BLOB field is not allowed in partition function create table t1 (a varchar(65535)) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +ERROR HY000: A BLOB field is not allowed in partition function +set sql_mode=default; create table t1 (a bit(27), primary key (a)) engine=myisam partition by hash (a) (partition p0, partition p1, partition p2); diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 567be0d0d3f..4cb5bb79982 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4202,6 +4202,149 @@ Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 deallocate prepare stmt2; drop table t1; +# +# MDEV-9208: Function->Function->View = Mysqld segfault +# (Server crashes in Dependency_marker::visit_field on 2nd +# execution with merged subquery) +# +CREATE TABLE t1 (i1 INT); +insert into t1 values(1),(2); +CREATE TABLE t2 (i2 INT); +insert into t2 values(1),(2); +prepare stmt from " + select 1 from ( + select + if (i1<0, 0, 0) as f1, + (select f1) as f2 + from t1, t2 + ) sq +"; +execute stmt; +1 +1 +1 +1 +1 +execute stmt; +1 +1 +1 +1 +1 +drop table t1,t2; +# +# MDEV-9619: Assertion `null_ref_table' failed in virtual +# table_map Item_direct_view_ref::used_tables() const on 2nd +# execution of PS +# +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); +PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; +EXECUTE stmt; +f1 +EXECUTE stmt; +f1 +insert into t1 values ('c'); +EXECUTE stmt; +f1 +c +EXECUTE stmt; +f1 +c +deallocate prepare stmt; +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); +PREPARE stmt FROM "SELECT * FROM v1 WHERE (f1,f1) = SOME ( SELECT f2,f2 FROM t2 )"; +EXECUTE stmt; +f1 +EXECUTE stmt; +f1 +insert into t1 values ('c'); +EXECUTE stmt; +f1 +c +EXECUTE stmt; +f1 +c +deallocate prepare stmt; +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE (111,table3.column3) IN ( SELECT 111,table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; +create table t1 (a int, b int, c int); +create table t2 (x int, y int, z int); +create table t3 as select * from t1; +insert into t1 values (1,2,3),(4,5,6),(100,200,300),(400,500,600); +insert into t2 values (1,2,3),(7,8,9),(100,200,300),(400,500,600); +insert into t3 values (1,2,3),(11,12,13),(100,0,0),(400,500,600); +set @optimizer_switch_save=@@optimizer_switch; +set @join_cache_level_save=@@join_cache_level; +set optimizer_switch='materialization=off'; +set join_cache_level=0; +select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z); +a b c +1 2 3 +400 500 600 +prepare stmt from "select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z)"; +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +create view v1 as select * from t1; +create view v2 as select * from t2; +create view v3 as select * from t3; +select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z); +a b c +1 2 3 +400 500 600 +prepare stmt from "select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z)"; +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +set optimizer_switch=@optimizer_switch_save; +set join_cache_level=@join_cache_level_save; +deallocate prepare stmt; +drop view v1,v2,v3; +drop table t1,t2,t3; # End of 5.5 tests # # Start of 10.2 tests diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 6813c40a5cf..bc46a4fdd0b 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1807,4 +1807,85 @@ id state capital 7 Pennsylvania Harrisburg 8 Virginia Richmond DROP TABLE t1; +# +# mdev-11574: do not build index merge of two indexes when +# one index is an infix of the other index +# +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +DROP INDEX Country ON City; +CREATE INDEX CountryName ON City(Country,Name); +CREATE INDEX Name ON City(Name); +select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +ID Name Country Population +175 Antwerpen BEL 446525 +176 Gent BEL 224180 +3068 Berlin DEU 3386667 +3087 Bonn DEU 301048 +3242 Lahti FIN 96921 +2974 Paris FRA 2125246 +1466 Napoli ITA 1002619 +1474 Venezia ITA 277305 +2808 Bergen NOR 230948 +2807 Oslo NOR 508726 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 +2918 Braga PRT 90535 +2915 Porto PRT 273060 +3580 Moscow RUS 8389200 +3581 St Petersburg RUS 4694000 +3048 Stockholm SWE 750348 +3051 Uppsala SWE 189569 +explain select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City range CountryName,Name CountryName 38 NULL 22 Using index condition; Using where +DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 0c3b682b197..ce90f522d6e 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1808,5 +1808,86 @@ id state capital 7 Pennsylvania Harrisburg 8 Virginia Richmond DROP TABLE t1; +# +# mdev-11574: do not build index merge of two indexes when +# one index is an infix of the other index +# +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +DROP INDEX Country ON City; +CREATE INDEX CountryName ON City(Country,Name); +CREATE INDEX Name ON City(Name); +select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +ID Name Country Population +175 Antwerpen BEL 446525 +176 Gent BEL 224180 +3068 Berlin DEU 3386667 +3087 Bonn DEU 301048 +3242 Lahti FIN 96921 +2974 Paris FRA 2125246 +1466 Napoli ITA 1002619 +1474 Venezia ITA 277305 +2808 Bergen NOR 230948 +2807 Oslo NOR 508726 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 +2918 Braga PRT 90535 +2915 Porto PRT 273060 +3580 Moscow RUS 8389200 +3581 St Petersburg RUS 4694000 +3048 Stockholm SWE 750348 +3051 Uppsala SWE 189569 +explain select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where +DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 2037dcac182..2029413c0f0 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -51,6 +51,9 @@ delete t1 from t1,t3 where t1.a=t3.a; drop table t1; insert into t1 values(1); ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +drop temporary table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' connection default; set global read_only=0; lock table t1 write; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 38a7bd8e1c2..d17469abae5 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -972,7 +972,7 @@ def information_schema COLUMNS COLUMNS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N def information_schema COLUMNS COLUMNS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 def information_schema COLUMNS COLUMNS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 def information_schema COLUMNS COLUMNS COLUMN_NAME COLUMN_NAME 253 192 1 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589815 0 Y 16 0 33 +def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589788 0 Y 16 0 33 def information_schema COLUMNS COLUMNS IS_NULLABLE IS_NULLABLE 253 9 2 N 1 0 33 def information_schema COLUMNS COLUMNS DATA_TYPE DATA_TYPE 253 192 3 N 1 0 33 def information_schema COLUMNS COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 96 0 Y 0 0 33 @@ -997,7 +997,7 @@ def information_schema COLUMNS COLUMNS COLUMN_NAME Field 253 192 1 N 1 0 33 def information_schema COLUMNS COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 def information_schema COLUMNS COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 def information_schema COLUMNS COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 +def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589788 0 Y 16 0 33 def information_schema COLUMNS COLUMNS EXTRA Extra 253 90 0 N 1 0 33 Field Type Null Key Default Extra c int(11) NO PRI NULL diff --git a/mysql-test/r/show_function_with_pad_char_to_full_length.result b/mysql-test/r/show_function_with_pad_char_to_full_length.result new file mode 100644 index 00000000000..785cab7b3e6 --- /dev/null +++ b/mysql-test/r/show_function_with_pad_char_to_full_length.result @@ -0,0 +1,24 @@ +create function f() returns int return 1; +show function status; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +T f T T T T T T T T T +set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; +show function status; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +T f T T T T T T T T T +drop function f; +select @@sql_mode; +@@sql_mode +PAD_CHAR_TO_FULL_LENGTH +create function f() returns int return 1; +select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME='f'; +ROUTINE_NAME +f +set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; +select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME='f'; +ROUTINE_NAME +f +drop function f; +select @@sql_mode; +@@sql_mode +PAD_CHAR_TO_FULL_LENGTH diff --git a/mysql-test/r/sp-anchor-row-type-cursor.result b/mysql-test/r/sp-anchor-row-type-cursor.result index a2435e17f9f..add771c534c 100644 --- a/mysql-test/r/sp-anchor-row-type-cursor.result +++ b/mysql-test/r/sp-anchor-row-type-cursor.result @@ -1001,3 +1001,33 @@ t1 CREATE TABLE `t1` ( `rec1.a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP PROCEDURE p1; +# +# MDEV-14139 Anchored data types for variables +# +BEGIN NOT ATOMIC +DECLARE c1 CURSOR FOR SELECT 10 AS a, 'bbb' AS b, TIME'10:20:30' AS c; +BEGIN +DECLARE row1 ROW TYPE OF c1; +DECLARE a_row1 TYPE OF row1; +DECLARE aa_row1 TYPE OF a_row1; +CREATE TABLE t2 AS SELECT a_row1.a AS a, a_row1.b AS b, a_row1.c AS c; +SHOW CREATE TABLE t2; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT aa_row1.a AS a, aa_row1.b AS b, aa_row1.c AS c; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +END; +$$ +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` varchar(3) DEFAULT NULL, + `c` time DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` varchar(3) DEFAULT NULL, + `c` time DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/r/sp-anchor-row-type-table.result b/mysql-test/r/sp-anchor-row-type-table.result index e3ad1aa831e..28a5180e6c9 100644 --- a/mysql-test/r/sp-anchor-row-type-table.result +++ b/mysql-test/r/sp-anchor-row-type-table.result @@ -781,3 +781,30 @@ DROP PROCEDURE p1; DROP FUNCTION f2; DROP FUNCTION f1; DROP DATABASE db1; +# +# MDEV-14139 Anchored data types for variables +# +CREATE TABLE t1 (int11 INT, text0 TEXT); +BEGIN NOT ATOMIC +DECLARE row1 ROW TYPE OF t1; +DECLARE a_row1 TYPE OF row1; +DECLARE aa_row1 TYPE OF a_row1; +CREATE TABLE t2 AS SELECT a_row1.int11 AS int11, a_row1.text0 AS text0; +SHOW CREATE TABLE t2; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT aa_row1.int11 AS int11, aa_row1.text0 AS text0; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +$$ +Table Create Table +t2 CREATE TABLE `t2` ( + `int11` int(11) DEFAULT NULL, + `text0` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Table Create Table +t2 CREATE TABLE `t2` ( + `int11` int(11) DEFAULT NULL, + `text0` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/r/sp-anchor-type.result b/mysql-test/r/sp-anchor-type.result index 44f67531b2f..b2de14bca1c 100644 --- a/mysql-test/r/sp-anchor-type.result +++ b/mysql-test/r/sp-anchor-type.result @@ -965,3 +965,109 @@ DROP TABLE t1; # # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types # +# +# MDEV-14139 Anchored data types for variables +# +BEGIN NOT ATOMIC +DECLARE a TYPE OF a; +END; +$$ +ERROR 42000: Undeclared variable: a +BEGIN NOT ATOMIC +DECLARE int11 INT; +DECLARE dec103 DECIMAL(10,3); +DECLARE flt0 FLOAT; +DECLARE dbl0 DOUBLE; +DECLARE enum0 ENUM('a','b'); +DECLARE bit3 BIT(3); +DECLARE varchar10 VARCHAR(10); +DECLARE text1 TEXT; +DECLARE tinytext1 TINYTEXT; +DECLARE mediumtext1 MEDIUMTEXT; +DECLARE longtext1 LONGTEXT; +DECLARE time3 TIME(3); +DECLARE datetime4 DATETIME(4); +DECLARE timestamp5 TIMESTAMP(5); +DECLARE date0 DATE; +DECLARE a_int11 TYPE OF int11; +DECLARE a_dec103 TYPE OF dec103; +DECLARE a_flt0 TYPE OF flt0; +DECLARE a_dbl0 TYPE OF dbl0; +DECLARE a_bit3 TYPE OF bit3; +DECLARE a_enum0 TYPE OF enum0; +DECLARE a_varchar10 TYPE OF varchar10; +DECLARE a_text1 TYPE OF text1; +DECLARE a_tinytext1 TYPE OF tinytext1; +DECLARE a_mediumtext1 TYPE OF mediumtext1; +DECLARE a_longtext1 TYPE OF longtext1; +DECLARE a_time3 TYPE OF time3; +DECLARE a_datetime4 TYPE OF datetime4; +DECLARE a_timestamp5 TYPE OF timestamp5; +DECLARE a_date0 TYPE OF date0; +DECLARE aa_int11 TYPE OF a_int11; +DECLARE aa_dec103 TYPE OF a_dec103; +DECLARE aa_flt0 TYPE OF a_flt0; +DECLARE aa_dbl0 TYPE OF a_dbl0; +DECLARE aa_bit3 TYPE OF a_bit3; +DECLARE aa_enum0 TYPE OF a_enum0; +DECLARE aa_varchar10 TYPE OF a_varchar10; +DECLARE aa_text1 TYPE OF a_text1; +DECLARE aa_tinytext1 TYPE OF a_tinytext1; +DECLARE aa_mediumtext1 TYPE OF a_mediumtext1; +DECLARE aa_longtext1 TYPE OF a_longtext1; +DECLARE aa_time3 TYPE OF a_time3; +DECLARE aa_datetime4 TYPE OF a_datetime4; +DECLARE aa_timestamp5 TYPE OF a_timestamp5; +DECLARE aa_date0 TYPE OF a_date0; +CREATE TABLE t1 AS +SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3, +a_enum0,a_varchar10, +a_text1,a_tinytext1,a_mediumtext1,a_longtext1, +a_time3,a_datetime4,a_timestamp5,a_date0; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 AS +SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3, +aa_enum0,aa_varchar10, +aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1, +aa_time3,aa_datetime4,aa_timestamp5,aa_date0; +SHOW CREATE TABLE t1; +DROP TABLE t1; +END; +$$ +Table Create Table +t1 CREATE TABLE `t1` ( + `a_int11` int(11) DEFAULT NULL, + `a_dec103` decimal(10,3) DEFAULT NULL, + `a_flt0` float DEFAULT NULL, + `a_dbl0` double DEFAULT NULL, + `a_bit3` bit(3) DEFAULT NULL, + `a_enum0` varchar(1) DEFAULT NULL, + `a_varchar10` varchar(10) DEFAULT NULL, + `a_text1` text DEFAULT NULL, + `a_tinytext1` tinytext DEFAULT NULL, + `a_mediumtext1` mediumtext DEFAULT NULL, + `a_longtext1` longtext DEFAULT NULL, + `a_time3` time(3) DEFAULT NULL, + `a_datetime4` datetime(4) DEFAULT NULL, + `a_timestamp5` timestamp(5) NULL DEFAULT NULL, + `a_date0` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Table Create Table +t1 CREATE TABLE `t1` ( + `aa_int11` int(11) DEFAULT NULL, + `aa_dec103` decimal(10,3) DEFAULT NULL, + `aa_flt0` float DEFAULT NULL, + `aa_dbl0` double DEFAULT NULL, + `aa_bit3` bit(3) DEFAULT NULL, + `aa_enum0` varchar(1) DEFAULT NULL, + `aa_varchar10` varchar(10) DEFAULT NULL, + `aa_text1` text DEFAULT NULL, + `aa_tinytext1` tinytext DEFAULT NULL, + `aa_mediumtext1` mediumtext DEFAULT NULL, + `aa_longtext1` longtext DEFAULT NULL, + `aa_time3` time(3) DEFAULT NULL, + `aa_datetime4` datetime(4) DEFAULT NULL, + `aa_timestamp5` timestamp(5) NULL DEFAULT NULL, + `aa_date0` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/r/sp-row.result b/mysql-test/r/sp-row.result index 61c0660cf71..adb67030feb 100644 --- a/mysql-test/r/sp-row.result +++ b/mysql-test/r/sp-row.result @@ -2256,3 +2256,28 @@ END; $$ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +# +# MDEV-14139 Anchored data types for variables +# +BEGIN NOT ATOMIC +DECLARE row1 ROW(int11 INT,text1 TEXT); +DECLARE a_row1 TYPE OF row1; +DECLARE aa_row1 TYPE OF a_row1; +CREATE TABLE t1 AS SELECT a_row1.int11 AS int11, a_row1.text1 AS text1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 AS SELECT aa_row1.int11 AS int11, aa_row1.text1 AS text1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +END; +$$ +Table Create Table +t1 CREATE TABLE `t1` ( + `int11` int(11) DEFAULT NULL, + `text1` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Table Create Table +t1 CREATE TABLE `t1` ( + `int11` int(11) DEFAULT NULL, + `text1` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 7c881fd5ad5..c6c241413d6 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -475,8 +475,8 @@ set sql_mode=16384+(65536*4); select @@sql_mode; @@sql_mode REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI -set sql_mode=2147483648*2; -ERROR 42000: Variable 'sql_mode' can't be set to the value of '4294967296' +set sql_mode=2147483648*2*2; +ERROR 42000: Variable 'sql_mode' can't be set to the value of '8589934592' select @@sql_mode; @@sql_mode REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 025782a365d..315bb9dc1b5 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1240,9 +1240,9 @@ Warning 1364 Field 'i' doesn't have a default value DROP TABLE t1; set @@sql_mode='traditional'; create table t1(a varchar(65537)); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead create table t1(a varbinary(65537)); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead set @@sql_mode='traditional'; create table t1(a int, b date not null); alter table t1 modify a bigint unsigned not null; diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 68e664c2a22..b4feb8de200 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -502,3 +502,20 @@ 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; +# +# MDEV-13135: subquery with ON expression subject to +# semi-join optimizations +# +CREATE TABLE t1 (a INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a AS v_a FROM t1; +INSERT INTO t1 VALUES (1),(3); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1 WHERE a NOT IN ( +SELECT b FROM t2 INNER JOIN v1 ON (b IN ( SELECT a FROM t1 )) +WHERE v_a = b +); +a +1 +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index b88497dbd9b..7ba77e60e42 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -214,7 +214,7 @@ proc CREATE TABLE `proc` ( `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, @@ -239,7 +239,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index b88497dbd9b..7ba77e60e42 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -214,7 +214,7 @@ proc CREATE TABLE `proc` ( `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, @@ -239,7 +239,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index 14905ab81a0..ad74cf6a09d 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -214,7 +214,7 @@ proc CREATE TABLE `proc` ( `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, @@ -239,7 +239,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index b88497dbd9b..7ba77e60e42 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -214,7 +214,7 @@ proc CREATE TABLE `proc` ( `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, @@ -239,7 +239,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 9a92ff21e9f..57cdd1561df 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -772,5 +772,31 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table if exists t1; # +# MDEV-11586 UNION of FLOAT type results in erroneous precision +# +CREATE TABLE t1 (f FLOAT); +INSERT INTO t1 VALUES (1.1); +SELECT f FROM t1 UNION SELECT 1; +f +1.100000023841858 +1 +SELECT 1 UNION SELECT f FROM t1; +1 +1 +1.100000023841858 +SELECT f FROM t1 UNION SELECT 2147483647; +f +1.100000023841858 +2147483647 +SELECT 2147483647 UNION SELECT f FROM t1; +2147483647 +2147483647 +1.100000023841858 +SELECT CASE WHEN 0 THEN (SELECT f FROM t1) ELSE 2147483647 END AS c1, +CASE WHEN 1 THEN 2147483647 ELSE (SELECT f FROM t1) END AS c2; +c1 c2 +2147483647 2147483647 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/r/type_varchar.result b/mysql-test/r/type_varchar.result index 110c07c37a9..0b2a5b54d08 100644 --- a/mysql-test/r/type_varchar.result +++ b/mysql-test/r/type_varchar.result @@ -513,7 +513,80 @@ Warning 1292 Truncated incorrect DOUBLE value: 's ' Warning 1292 Truncated incorrect DOUBLE value: ' ' DROP TABLE t1; # -# Start of 10.0 tests +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +set sql_mode=''; +CREATE TABLE t1 (c1 VARBINARY(65532)); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varbinary(65532) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65533)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65534)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65535)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65536)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumblob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65532)); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(65532) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65533)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65534)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65535)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65536)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +set sql_mode=default; +CREATE TABLE t1 (c1 VARCHAR(65536)); +ERROR 42000: Column length too big for column 'c1' (max = 65532); use BLOB or TEXT instead +# +# End of 5.5 tests # # # MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns |