summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/default.result15
-rw-r--r--mysql-test/main/default.test19
-rw-r--r--mysql-test/main/table_value_constr.result101
-rw-r--r--mysql-test/main/table_value_constr.test58
4 files changed, 192 insertions, 1 deletions
diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result
index cf0788b2fb2..f5ee1474b94 100644
--- a/mysql-test/main/default.result
+++ b/mysql-test/main/default.result
@@ -3390,3 +3390,18 @@ ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
ERROR 42S22: Unknown column 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' in 'DEFAULT'
DROP TABLE t1;
# end of 10.2 test
+#
+# MDEV-22703 DEFAULT() on a BLOB column can overwrite the default
+# record, which can cause crashes when accessing already released
+# memory.
+#
+CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB;
+INSERT INTO t1 () VALUES (),();
+ALTER TABLE t1 FORCE;
+SELECT DEFAULT(h) FROM t1;
+SELECT length(DEFAULT(h)) FROM t1;
+length(DEFAULT(h))
+25
+25
+INSERT INTO t1 () VALUES ();
+drop table t1;
diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test
index 27e38eeeb49..c0561deac67 100644
--- a/mysql-test/main/default.test
+++ b/mysql-test/main/default.test
@@ -1,3 +1,5 @@
+--source include/have_innodb.inc
+
#
# test of already fixed bugs
#
@@ -2107,5 +2109,20 @@ CREATE OR REPLACE TABLE t1(i int);
ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`;
DROP TABLE t1;
-
--echo # end of 10.2 test
+
+--echo #
+--echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default
+--echo # record, which can cause crashes when accessing already released
+--echo # memory.
+--echo #
+
+CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB;
+INSERT INTO t1 () VALUES (),();
+ALTER TABLE t1 FORCE;
+--disable_result_log
+SELECT DEFAULT(h) FROM t1;
+--enable_result_log
+SELECT length(DEFAULT(h)) FROM t1;
+INSERT INTO t1 () VALUES ();
+drop table t1;
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 69c75ddab75..e112aca78af 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2674,4 +2674,105 @@ values (1,2);
values ((select min(a), max(b) from t1));
ERROR 21000: Operand should contain 1 column(s)
drop table t1;
+#
+# MDEV-24840: union of TVCs in IN subquery
+#
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+select a from t1 where a in (values (7) union values (8));
+a
+7
+explain extended select a from t1 where a in (values (7) union values (8));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+4 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+5 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00
+3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`8`)))
+prepare stmt from "select a from t1 where a in (values (7) union values (8))";
+execute stmt;
+a
+7
+execute stmt;
+a
+7
+deallocate prepare stmt;
+select a from t1 where a not in (values (7) union values (8));
+a
+3
+1
+explain extended select a from t1 where a not in (values (7) union values (8));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+5 DEPENDENT UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where
+3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`8`))))
+select a from t1 where a < all(values (7) union values (8));
+a
+3
+1
+explain extended select a from t1 where a < all(values (7) union values (8));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00
+3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`)))
+select a from t1 where a >= any(values (7) union values (8));
+a
+7
+explain extended select a from t1 where a >= any(values (7) union values (8));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00
+3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`)))
+drop table t1;
+#
+# MDEV-24934:EXPLAIN for queries based on TVC using subqueries
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+values (8), ((select * from t1 where a between 2 and 4));
+8
+8
+3
+explain values (8), ((select * from t1 where a between 2 and 4));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
+values ((select * from t1 where a between 2 and 4)),
+((select * from t1 where a > 10));
+(select * from t1 where a between 2 and 4)
+3
+NULL
+explain values ((select * from t1 where a between 2 and 4)),
+((select * from t1 where a > 10));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
+values (10,11), ((select * from t1 where a = 7) + 1, 21);
+10 11
+10 11
+8 21
+explain values (10,11), ((select * from t1 where a = 7) + 1, 21);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
+drop table t1;
End of 10.3 tests
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index e8697bef589..0a78fd9b386 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1401,4 +1401,62 @@ values ((select min(a), max(b) from t1));
drop table t1;
+--echo #
+--echo # MDEV-24840: union of TVCs in IN subquery
+--echo #
+
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+
+let $q=
+select a from t1 where a in (values (7) union values (8));
+eval $q;
+eval explain extended $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q=
+select a from t1 where a not in (values (7) union values (8));
+eval $q;
+eval explain extended $q;
+
+let $q=
+select a from t1 where a < all(values (7) union values (8));
+eval $q;
+eval explain extended $q;
+
+let $q=
+select a from t1 where a >= any(values (7) union values (8));
+eval $q;
+eval explain extended $q;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+let $q1=
+values (8), ((select * from t1 where a between 2 and 4));
+eval $q1;
+eval explain $q1;
+
+let $q2=
+values ((select * from t1 where a between 2 and 4)),
+ ((select * from t1 where a > 10));
+eval $q2;
+eval explain $q2;
+
+let $q3=
+values (10,11), ((select * from t1 where a = 7) + 1, 21);
+eval $q3;
+eval explain $q3;
+
+drop table t1;
+
--echo End of 10.3 tests
View Lorry Controller Status