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/information_schema.result13
-rw-r--r--mysql-test/main/information_schema.test10
-rw-r--r--mysql-test/main/innodb_ext_key.result19
-rw-r--r--mysql-test/main/innodb_ext_key.test23
-rw-r--r--mysql-test/main/ps.result12
-rw-r--r--mysql-test/main/ps.test10
-rw-r--r--mysql-test/main/selectivity.result58
-rw-r--r--mysql-test/main/selectivity.test45
-rw-r--r--mysql-test/main/selectivity_innodb.result58
-rw-r--r--mysql-test/main/subselect4.result10
-rw-r--r--mysql-test/main/subselect4.test16
-rw-r--r--mysql-test/main/table_value_constr.result101
-rw-r--r--mysql-test/main/table_value_constr.test58
-rw-r--r--mysql-test/main/type_decimal.result89
-rw-r--r--mysql-test/main/type_decimal.test44
-rw-r--r--mysql-test/main/view.result15
-rw-r--r--mysql-test/main/view.test16
19 files changed, 619 insertions, 12 deletions
diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result
index ca65c66ea41..0d2c2e6acbc 100644
--- a/mysql-test/main/default.result
+++ b/mysql-test/main/default.result
@@ -3388,3 +3388,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/information_schema.result b/mysql-test/main/information_schema.result
index 910a657e034..024d481cca9 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -2325,5 +2325,18 @@ group by f;
f
drop table t1;
#
+# MDEV-24929 Server crash in thr_multi_unlock or in
+# get_schema_tables_result upon select from I_S with joins
+#
+CREATE TABLE t1 (a TIMESTAMP, KEY (a));
+INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11');
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL);
+count(*)
+2
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL);
+count(*)
+2
+DROP TABLE t1;
+#
# End of 10.3 tests
#
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index 4e05ac4e5db..4e20ede257b 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -2036,5 +2036,15 @@ group by f;
drop table t1;
--echo #
+--echo # MDEV-24929 Server crash in thr_multi_unlock or in
+--echo # get_schema_tables_result upon select from I_S with joins
+--echo #
+
+CREATE TABLE t1 (a TIMESTAMP, KEY (a));
+INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11');
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL);
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL);
+DROP TABLE t1;
+--echo #
--echo # End of 10.3 tests
--echo #
diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result
index 7cc03bee546..1dab686feb8 100644
--- a/mysql-test/main/innodb_ext_key.result
+++ b/mysql-test/main/innodb_ext_key.result
@@ -795,6 +795,21 @@ EXPLAIN
}
drop table t1;
SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (
+pk VARCHAR(50),
+a VARCHAR(20),
+KEY k1(a),
+PRIMARY KEY(pk)
+)ENGINE=INNODB;
+INSERT INTO t2 SELECT a,a FROM t1;
+EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index NULL k1 23 NULL 10 Using index
+DROP TABLE t1,t2;
set global innodb_stats_persistent= @innodb_stats_persistent_save;
-set global innodb_stats_persistent_sample_pages=
-@innodb_stats_persistent_sample_pages_save;
+set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save;
diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test
index 413d5570be5..4e04f032864 100644
--- a/mysql-test/main/innodb_ext_key.test
+++ b/mysql-test/main/innodb_ext_key.test
@@ -620,6 +620,25 @@ select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and
drop table t1;
SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE t2 (
+ pk VARCHAR(50),
+ a VARCHAR(20),
+ KEY k1(a),
+ PRIMARY KEY(pk)
+)ENGINE=INNODB;
+
+INSERT INTO t2 SELECT a,a FROM t1;
+EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1);
+
+DROP TABLE t1,t2;
+
set global innodb_stats_persistent= @innodb_stats_persistent_save;
-set global innodb_stats_persistent_sample_pages=
- @innodb_stats_persistent_sample_pages_save;
+set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save;
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index f1972b6b40e..fd8f37bca0e 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5489,6 +5489,18 @@ ERROR HY000: Default/ignore value is not supported for such parameter usage
EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0;
Database
#
+# MDEV-24779: main.subselect fails in buildbot with --ps-protocol
+#
+CREATE TABLE t1(a INT);
+PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))";
+EXECUTE stmt;
+EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))
+0
+EXECUTE stmt;
+EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))
+0
+DROP TABLE t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index e702cb76bbb..6a109951d0e 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -4932,6 +4932,16 @@ EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING DEFAULT;
EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0;
--echo #
+--echo # MDEV-24779: main.subselect fails in buildbot with --ps-protocol
+--echo #
+
+CREATE TABLE t1(a INT);
+PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))";
+EXECUTE stmt;
+EXECUTE stmt;
+DROP TABLE t1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index ca6a7ef25c7..7e3202337ec 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1882,4 +1882,62 @@ a b
set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
drop table t1;
# End of 10.1 tests
+#
+# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect
+#
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size=255;
+CREATE TABLE t1 (a BIT(32), b INT);
+INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 66.41 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81
+SELECT HEX(a), b from t1 where t1.a >= 81;
+HEX(a) b
+51 81
+52 82
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+DROP TABLE t1;
+#
+# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3
+#
+CREATE TABLE t1(a int);
+INSERT INTO t1 values (1),(2),(2),(3),(4);
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size= 255;
+set use_stat_tables='preferably';
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+SET optimizer_use_condition_selectivity=3;
+# filtered should show 25 %
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+DROP TABLE t1;
+# End of 10.2 tests
set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index 2a0cc823eb4..1d96dc0bf80 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1281,6 +1281,51 @@ drop table t1;
--echo # End of 10.1 tests
+--echo #
+--echo # MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect
+--echo #
+
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size=255;
+CREATE TABLE t1 (a BIT(32), b INT);
+INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
+SELECT HEX(a), b from t1 where t1.a >= 81;
+
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3
+--echo #
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 values (1),(2),(2),(3),(4);
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size= 255;
+
+set use_stat_tables='preferably';
+
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+SET optimizer_use_condition_selectivity=3;
+
+--echo # filtered should show 25 %
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+FLUSH TABLES;
+
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
+
#
# Clean up
#
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 3bf313ae2b3..d5dc17591b4 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1892,6 +1892,64 @@ a b
set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
drop table t1;
# End of 10.1 tests
+#
+# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect
+#
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size=255;
+CREATE TABLE t1 (a BIT(32), b INT);
+INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 66.41 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81
+SELECT HEX(a), b from t1 where t1.a >= 81;
+HEX(a) b
+51 81
+52 82
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+DROP TABLE t1;
+#
+# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3
+#
+CREATE TABLE t1(a int);
+INSERT INTO t1 values (1),(2),(2),(3),(4);
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size= 255;
+set use_stat_tables='preferably';
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+SET optimizer_use_condition_selectivity=3;
+# filtered should show 25 %
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+DROP TABLE t1;
+# End of 10.2 tests
set @@global.histogram_size=@save_histogram_size;
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 79c42def277..813d9ff5564 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -2687,6 +2687,15 @@ f
bar
DROP TABLE t1, t2;
#
+# MDEV-23449: alias do not exist and a query do not report an error
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
+SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id);
+ERROR 42S22: Unknown column 's.id' in 'group statement'
+DROP TABLE t1;
+# End of 10.2 tests
+#
# MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init
#
CREATE TABLE t1 (i1 int,v1 varchar(1),KEY (v1,i1));
@@ -2717,7 +2726,6 @@ Warnings:
Warning 1931 Query execution was interrupted. The query examined at least 3020 rows, which exceeds LIMIT ROWS EXAMINED (500). The query result may be incomplete
SET join_cache_level= @save_join_cache_level;
DROP TABLE t1,t2,t3,t4;
-# End of 10.2 tests
#
# MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
#
diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
index 3a9ad7f715c..3a6ea922c4d 100644
--- a/mysql-test/main/subselect4.test
+++ b/mysql-test/main/subselect4.test
@@ -2202,6 +2202,19 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
--echo #
+--echo # MDEV-23449: alias do not exist and a query do not report an error
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
+
+--error ER_BAD_FIELD_ERROR
+SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id);
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
+
+--echo #
--echo # MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init
--echo #
@@ -2236,9 +2249,6 @@ from t2 join t1 on
SET join_cache_level= @save_join_cache_level;
DROP TABLE t1,t2,t3,t4;
-
---echo # End of 10.2 tests
-
--echo #
--echo # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
--echo #
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 5f176fcd3eb..f965fab0e7a 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2675,6 +2675,107 @@ 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 4cbca891e57..4014a8b4d62 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1402,6 +1402,64 @@ 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 #
--echo # End of 10.3 tests
--echo #
diff --git a/mysql-test/main/type_decimal.result b/mysql-test/main/type_decimal.result
index 6e7d7c8b6fe..0228652f74c 100644
--- a/mysql-test/main/type_decimal.result
+++ b/mysql-test/main/type_decimal.result
@@ -176,9 +176,8 @@ Note 1265 Data truncated for column 'a' at row 2
insert ignore into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
Warnings:
Warning 1264 Out of range value for column 'a' at row 1
-Warning 1366 Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t1`.`a` at row 2
+Warning 1264 Out of range value for column 'a' at row 2
Note 1265 Data truncated for column 'a' at row 3
-Warning 1366 Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t1`.`a` at row 4
insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
@@ -209,7 +208,7 @@ a
99999999.99
0.00
99999999.99
-0.00
+99999999.99
0.00
0.00
123.40
@@ -1078,6 +1077,90 @@ t1 CREATE TABLE `t1` (
DROP TABLE t1;
DROP TABLE t1dec102;
#
+# MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result
+#
+SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a;
+a
+0
+SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a;
+a
+0
+SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;
+a
+99999999999999999999999999999999999999
+Warnings:
+Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
+Warning 1292 Truncated incorrect DECIMAL value: '.00000000000000000000000000000000000001e111111111111111111111'
+Warning 1264 Out of range value for column 'a' at row 1
+CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128));
+INSERT INTO t1 VALUES
+('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'),
+('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'),
+('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'),
+('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent');
+BEGIN NOT ATOMIC
+DECLARE done INT DEFAULT FALSE;
+DECLARE vstr, vcomment VARCHAR(128);
+DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str;
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+OPEN cur1;
+read_loop:
+LOOP
+FETCH cur1 INTO vstr, vcomment;
+IF done THEN
+LEAVE read_loop;
+END IF;
+SELECT vstr AS `--------`, vcomment AS `--------`;
+SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+SHOW WARNINGS;
+SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+SHOW WARNINGS;
+END LOOP;
+END;
+$$
+-------- --------
+0e-111111111111111111111 Zero mantissa and a huge negative exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level Code Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level Code Message
+Warning 1292 Truncated incorrect DECIMAL value: '0e-111111111111111111111garbage'
+-------- --------
+0e111111111111111111111 Zero mantissa and a huge positive exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level Code Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level Code Message
+Warning 1292 Truncated incorrect DECIMAL value: '0e111111111111111111111garbage'
+-------- --------
+1e-111111111111111111111 Non-zero mantissa and a huge negative exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level Code Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level Code Message
+Warning 1292 Truncated incorrect DECIMAL value: '1e-111111111111111111111garbage'
+-------- --------
+1e111111111111111111111 Non-zero mantissa, huge positive exponent
+CAST(str AS DECIMAL(38,0))
+99999999999999999999999999999999999999
+Level Code Message
+Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
+Warning 1292 Truncated incorrect DECIMAL value: '1e111111111111111111111'
+Warning 1264 Out of range value for column 'CAST(str AS DECIMAL(38,0))' at row 1
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+99999999999999999999999999999999999999
+Level Code Message
+Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
+Warning 1292 Truncated incorrect DECIMAL value: '1e111111111111111111111garbage'
+Warning 1264 Out of range value for column 'CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))' at row 1
+DROP TABLE t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/type_decimal.test b/mysql-test/main/type_decimal.test
index 4ffbcbd3288..1076592fe62 100644
--- a/mysql-test/main/type_decimal.test
+++ b/mysql-test/main/type_decimal.test
@@ -670,6 +670,50 @@ DROP TABLE t1;
DROP TABLE t1dec102;
--echo #
+--echo # MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result
+--echo #
+
+SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a;
+SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a;
+SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;
+
+CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128));
+INSERT INTO t1 VALUES
+('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'),
+('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'),
+('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'),
+('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent');
+
+# The loop below issues SHOW WARNINGS manually, disable automatic warnings
+--disable_warnings
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ DECLARE done INT DEFAULT FALSE;
+ DECLARE vstr, vcomment VARCHAR(128);
+ DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN cur1;
+read_loop:
+ LOOP
+ FETCH cur1 INTO vstr, vcomment;
+ IF done THEN
+ LEAVE read_loop;
+ END IF;
+ SELECT vstr AS `--------`, vcomment AS `--------`;
+ SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+ SHOW WARNINGS;
+ SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+ SHOW WARNINGS;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+--enable_warnings
+
+
+DROP TABLE t1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 49e57aca219..c55c93e118e 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6774,6 +6774,21 @@ DROP PROCEDURE sp1;
DROP VIEW v1;
DROP TABLE t1, t2;
#
+# MDEV-23291: SUM column from a derived table returns invalid values
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+CREATE view v1 AS
+SELECT a as x, (select x) as y, (select y) as z FROM t1;
+SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q;
+sum(z)
+3
+SELECT sum(z) FROM v1;
+sum(z)
+3
+DROP TABLE t1;
+DROP VIEW v1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 47ede54e1c7..1429642b0ab 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6485,6 +6485,22 @@ DROP VIEW v1;
DROP TABLE t1, t2;
--echo #
+--echo # MDEV-23291: SUM column from a derived table returns invalid values
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+
+CREATE view v1 AS
+SELECT a as x, (select x) as y, (select y) as z FROM t1;
+
+SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q;
+SELECT sum(z) FROM v1;
+
+DROP TABLE t1;
+DROP VIEW v1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
View Lorry Controller Status