Merge branch '10.4' into bb-10.4-MDEV-22265bb-10.4-MDEV-22265

1 files changed, 77 insertions, 0 deletions

diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result
index ecd115c2dd0..db75287c61c 100644
--- a/mysql-test/main/group_by.result
+++ b/mysql-test/main/group_by.result
@@ -2884,6 +2884,52 @@ GROUP BY t.table_name;
 ERROR HY001: Out of sort memory, consider increasing server sort buffer size
 SET max_sort_length= @save_max_sort_length;
 #
+# MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view
+#
+CREATE TABLE t1
+(
+id INT PRIMARY KEY AUTO_INCREMENT,
+dt datetime,
+INDEX(dt),
+foo int
+);
+INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1);
+INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1);
+INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1);
+INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1);
+INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1);
+INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1);
+INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1);
+INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1);
+INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt;
+SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
+dt	foo
+2020-09-25 12:00:00	1
+2020-09-25 13:00:00	1
+2020-09-26 12:00:00	1
+2020-09-26 13:00:00	2
+2020-09-27 12:00:00	1
+2020-09-27 13:00:00	1
+2020-09-28 12:00:00	1
+2020-09-28 13:00:00	1
+SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
+dt	foo
+2020-09-25 12:00:00	1
+2020-09-25 13:00:00	1
+2020-09-26 12:00:00	1
+2020-09-26 13:00:00	2
+2020-09-27 12:00:00	1
+2020-09-27 13:00:00	1
+2020-09-28 12:00:00	1
+2020-09-28 13:00:00	1
+DROP TABLE t1;
+DROP VIEW v1,v2;
+#
+# End of 10.2 tests
+#
+#
 # MDEV-16170
 # Server crashes in Item_null_result::type_handler on SELECT with ROLLUP
 #
@@ -2894,4 +2940,35 @@ f	COUNT(*)
 1	1
 NULL	1
 DROP TABLE t1;
+#
+# MDEV-24710 Uninitialized value upon CREATE .. SELECT ... VALUE
+#
+CREATE TABLE t1 (a VARCHAR(8) NOT NULL DEFAULT '');
+INSERT INTO t1 (a) VALUES ('foo');
+CREATE TABLE t2 AS SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE VALUE(a) IS NOT NULL;
+SELECT * from t2;
+f1	f2
+NULL	NULL
+SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE VALUE(a) IS NOT NULL;
+f1	f2
+NULL	NULL
+SELECT MAX(a) AS f1, a AS f2 FROM t1 WHERE 1=0;
+f1	f2
+NULL	NULL
+drop table t1,t2;
+# Extra test by to check the fix for MDEV-24710
+create table t20 (pk int primary key, a int);
+insert into t20 values (1,1);
+create table t21 (pk int primary key, b int not null);
+insert into t21 values (1,1);
+create table t22 (a int);
+insert into t22 values (1),(2);
+select a, (select max(t21.b) from t20 left join t21 on t21.pk=t20.a+10
+where t20.pk=1 and rand(123) < 0.5) as SUBQ from t22;
+a	SUBQ
+1	NULL
+2	NULL
+drop table t20, t21, t22;
+#
 # End of 10.3 tests
+#