Merge 10.3 into 10.4

11 files changed, 234 insertions, 6 deletions

diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index 16655123737..c10ef142e5b 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -1693,7 +1693,9 @@ disconnect con1;
 #
 # MDEV-22781: create view with CTE without default database
 #
-drop database test;
+create database db;
+use db;
+drop database db;
 create database db1;
 create table db1.t1 (a int);
 insert into db1.t1 values (3),(7),(1);
@@ -1723,7 +1725,6 @@ a
 drop view db1.v1;
 drop table db1.t1;
 drop database db1;
-create database test;
 use test;
 #
 # MDEV-24597: CTE with union used multiple times in query
@@ -2136,6 +2137,54 @@ drop procedure sp1;
 drop function g;
 drop function f;
 drop table t1;
+#
+# MDEV-27086: union using CTEs in CREATE TABLE
+#
+create or replace temporary table tmp as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from tmp;
+a
+1
+2
+create table t1 as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from t1;
+a
+1
+2
+insert into t1 values (3);
+create table t2 as
+with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2)
+select * from cte1 union select * from cte2;
+select * from t2;
+a
+1
+3
+drop table t1,t2;
+#
+# MDEV-26470: CTE in WITH clause of subquery used in DELETE
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (5);
+create table t2 (b int);
+insert into t2 values (4), (1), (3), (2);
+delete from t1
+where a in (with cte(a) as (select * from t2 where b <=2) select a from cte);
+select * from t1;
+a
+3
+7
+5
+insert into t1 values (1), (3);
+delete t1 from t1, t2
+where t1.a=t2.b or
+t1.a in (with cte(a) as (select b+1 from t2) select * from cte);
+select * from t1;
+a
+7
+drop table t1,t2;
 # End of 10.2 tests
 #
 # MDEV-21673: several references to CTE that uses
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 951d7788591..5af5cf04b66 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1205,7 +1205,9 @@ DROP TABLE test.t;
 --echo # MDEV-22781: create view with CTE without default database
 --echo #
 
-drop database test;
+create database db;
+use db;
+drop database db;
 create database db1;
 create table db1.t1 (a int);
 insert into db1.t1 values (3),(7),(1);
@@ -1227,7 +1229,6 @@ drop view db1.v1;
 drop table db1.t1;
 drop database db1;
 
-create database test;
 use test;
 
 --echo #
@@ -1578,6 +1579,52 @@ drop function g;
 drop function f;
 drop table t1;
 
+--echo #
+--echo # MDEV-27086: union using CTEs in CREATE TABLE
+--echo #
+
+create or replace temporary table tmp as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from tmp;
+
+create table t1 as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from t1;
+
+insert into t1 values (3);
+
+create table t2 as
+with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2)
+select * from cte1 union select * from cte2;
+select * from t2;
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-26470: CTE in WITH clause of subquery used in DELETE
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (5);
+
+create table t2 (b int);
+insert into t2 values (4), (1), (3), (2);
+
+delete from t1
+  where a in (with cte(a) as (select * from t2 where b <=2) select a from cte);
+select * from t1;
+
+insert into t1 values (1), (3);
+
+delete t1 from t1, t2
+  where t1.a=t2.b or
+        t1.a in (with cte(a) as (select b+1 from t2) select * from cte);
+select * from t1;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests
 
 --echo #
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index 9bccf358fb7..5edb71ea6af 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -3315,6 +3315,9 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
 WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
 a	b	c	b	c
 DROP TABLE t1,t2;
+select a.a from (select 1 as a) a, (select 2 as b) b cross join (select 3 as c) c left join (select 4 as d) d on 1;
+a
+1
 #
 # MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
 #
@@ -3403,3 +3406,4 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ref	a	a	5	test.t2.key2	1	
 drop table t1,t2,t3;
 drop table t1000,t10,t03;
+# End of 10.3 tests
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index 08930b622e2..b99f05f7c88 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1720,6 +1720,11 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
 
 DROP TABLE t1,t2;
 
+#
+# MDEV-20330 Combination of "," (comma), cross join and left join fails to parse
+#
+select a.a from (select 1 as a) a, (select 2 as b) b cross join (select 3 as c) c left join (select 4 as d) d on 1;
+
 --echo #
 --echo # MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
 --echo #
@@ -1814,3 +1819,4 @@ WHERE
 drop table t1,t2,t3;
 drop table t1000,t10,t03;
 
+--echo # End of 10.3 tests
diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index 5ca0e099026..2044cbc708f 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -1336,6 +1336,37 @@ view_definition
 select 1 not between 2 like 3 and 4 AS `1 not between (2 like 3) and 4`
 drop view v1;
 #
+# Start of 10.2 tests
+#
+#
+# MDEV-27066 Fixed scientific notation parser
+#
+SELECT 1 1.e*1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e*1' at line 1
+SELECT 1 1.e/1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e/1' at line 1
+SELECT 1 1.e^1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e^1' at line 1
+SELECT 1 1.e%1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e%1' at line 1
+SELECT 1 1.e&1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e&1' at line 1
+SELECT 1 1.e|1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e|1' at line 1
+SELECT 1.e(1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e(1)' at line 1
+SELECT (1 1.e);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e)' at line 1
+SELECT 1 1.e, 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e, 1' at line 1
+CREATE TABLE scientific_notation (test int);
+SELECT tmp 1.e.test FROM scientific_notation AS tmp;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e.test FROM scientific_notation AS tmp' at line 1
+DROP TABLE scientific_notation;
+#
+# End of 10.2 tests
+#
+#
 # MDEV-10343 Providing compatibility for basic SQL data types
 #
 CREATE TABLE clob (clob int);
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index d9a6c2df191..d6521b6758b 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1361,6 +1361,52 @@ Select view_definition from information_schema.views where table_schema='test' a
 drop view v1;
 
 --echo #
+--echo # Start of 10.2 tests
+--echo #
+--echo #
+
+--echo # MDEV-27066 Fixed scientific notation parser
+--echo #
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e*1;
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e/1;
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e^1;
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e%1;
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e&1;
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e|1;
+
+--error ER_PARSE_ERROR
+SELECT 1.e(1);
+
+--error ER_PARSE_ERROR
+SELECT (1 1.e);
+
+--error ER_PARSE_ERROR
+SELECT 1 1.e, 1;
+
+CREATE TABLE scientific_notation (test int);
+
+--error ER_PARSE_ERROR
+SELECT tmp 1.e.test FROM scientific_notation AS tmp;
+
+DROP TABLE scientific_notation;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
+--echo #
 --echo # MDEV-10343 Providing compatibility for basic SQL data types
 --echo #
 CREATE TABLE clob (clob int);
diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result
index 1724b7ae605..1dee43a13d6 100644
--- a/mysql-test/main/type_date.result
+++ b/mysql-test/main/type_date.result
@@ -939,6 +939,18 @@ Warning	1292	Truncated incorrect datetime value: '1995.0000000'
 Note	1003	select `test`.`t1`.`f` AS `f` from `test`.`t1` where '0000-00-00' between `test`.`t1`.`f` and <cache>('2012-12-12')
 DROP TABLE t1;
 #
+# MDEV-27072 Subquery using the ALL keyword on date columns produces a wrong result
+#
+CREATE TABLE t1 (d DATE);
+INSERT INTO t1 VALUES ('2021-11-17'), ('2021-10-17'),('2022-11-17'), ('2020-10-17');
+SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
+d
+2022-11-17
+SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
+d
+2020-10-17
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test
index a29f78a679b..1136062bb88 100644
--- a/mysql-test/main/type_date.test
+++ b/mysql-test/main/type_date.test
@@ -647,6 +647,15 @@ INSERT INTO t1 VALUES ('2020-01-01'),('2020-01-02');
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1995.0000000 BETWEEN f AND '2012-12-12';
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-27072 Subquery using the ALL keyword on date columns produces a wrong result
+--echo #
+
+CREATE TABLE t1 (d DATE);
+INSERT INTO t1 VALUES ('2021-11-17'), ('2021-10-17'),('2022-11-17'), ('2020-10-17');
+SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
+SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
+DROP TABLE t1;
 
 --echo #
 --echo # End of 10.2 tests
diff --git a/mysql-test/main/type_time.result b/mysql-test/main/type_time.result
index 69aa9561ebf..e383704fdb8 100644
--- a/mysql-test/main/type_time.result
+++ b/mysql-test/main/type_time.result
@@ -1380,6 +1380,18 @@ Warning	1292	Truncated incorrect time value: '1995.0000000'
 Note	1003	select `test`.`t1`.`f` AS `f` from `test`.`t1` where '00:00:00.000000' between `test`.`t1`.`f` and <cache>('23:59:59')
 DROP TABLE t1;
 #
+# MDEV-27098 Subquery using the ALL keyword on TIME columns produces a wrong result
+#
+CREATE TABLE t1 (d TIME);
+INSERT INTO t1 VALUES ('120:00:00'), ('20:00:00'), ('-120:00:00'), ('-220:00:00');
+SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
+d
+120:00:00
+SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
+d
+-220:00:00
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/type_time.test b/mysql-test/main/type_time.test
index 1d4d157b976..0739348fcf0 100644
--- a/mysql-test/main/type_time.test
+++ b/mysql-test/main/type_time.test
@@ -829,6 +829,16 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1995.0000000 BETWEEN f AND '23:59:59';
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-27098 Subquery using the ALL keyword on TIME columns produces a wrong result
+--echo #
+
+CREATE TABLE t1 (d TIME);
+INSERT INTO t1 VALUES ('120:00:00'), ('20:00:00'), ('-120:00:00'), ('-220:00:00');
+SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
+SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 9bcc49d42e7..043092e3945 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -4970,6 +4970,7 @@ sub mysqld_start ($$) {
   # Differs from "generic" MYSQLD_CMD by including all command line
   # options from *.opt and *.combination files.
   $ENV{'MYSQLD_LAST_CMD'}= "$exe  @$args";
+  my $oldexe= $exe;
 
   My::Debugger::setup_args(\$args, \$exe, $mysqld->name());
   $ENV{'VALGRIND_TEST'}= $opt_valgrind = int(($exe || '') eq 'valgrind');
@@ -5025,8 +5026,9 @@ sub mysqld_start ($$) {
   $mysqld->{'started_opts'}= $extra_opts;
 
   my $expect_file= "$opt_vardir/tmp/".$mysqld->name().".expect";
-  return sleep_until_file_created($mysqld->value('pid-file'), $expect_file,
-           $opt_start_timeout, $mysqld->{'proc'}, $warn_seconds);
+  return $oldexe eq $exe ||
+         sleep_until_file_created($mysqld->value('pid-file'), $expect_file,
+                     $opt_start_timeout, $mysqld->{'proc'}, $warn_seconds);
 }