summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/cte_nonrecursive.result67
-rw-r--r--mysql-test/main/cte_nonrecursive.test50
-rw-r--r--mysql-test/main/cte_recursive.result27
-rw-r--r--mysql-test/main/cte_recursive.test21
-rw-r--r--mysql-test/main/mysql_client_test.result5
-rw-r--r--mysql-test/main/mysql_client_test.test4
-rw-r--r--mysql-test/main/order_by_innodb.result52
-rw-r--r--mysql-test/main/order_by_innodb.test51
-rw-r--r--mysql-test/main/selectivity_innodb.result51
-rw-r--r--mysql-test/main/selectivity_innodb.test55
-rw-r--r--mysql-test/main/selectivity_no_engine.result20
-rw-r--r--mysql-test/main/selectivity_no_engine.test21
-rw-r--r--mysql-test/main/skip_name_resolve.result22
-rw-r--r--mysql-test/main/skip_name_resolve.test26
-rw-r--r--mysql-test/main/win.result287
-rw-r--r--mysql-test/main/win.test147
16 files changed, 904 insertions, 2 deletions
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index 550864b20c6..2c3555755cd 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -2019,6 +2019,73 @@ drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop table t1;
+#
+# MDEV-26095: missing RECURSIVE for the recursive definition of CTE
+# embedded into another CTE definition
+#
+create table t1 (a int);
+insert into t1 values (5), (7);
+with cte_e as (
+with recursive cte_r as (
+select a from t1 union select a+1 as a from cte_r r where a < 10
+) select * from cte_r
+) select * from cte_e;
+a
+5
+7
+6
+8
+9
+10
+with cte_e as (
+with cte_r as (
+select a from t1 union select a+1 as a from cte_r r where a < 10
+) select * from cte_r
+) select * from cte_e;
+ERROR 42S02: Table 'test.cte_r' doesn't exist
+drop table t1;
+#
+# MDEV-26025: query with two usage of a CTE executing via PS /SP
+#
+create table t1 (a int, b int);
+insert into t1 value (1,3), (3,2), (1,3), (4,1);
+prepare stmt from "with
+cte1 as ( select a,b from t1 where a = 1 AND b = 3 ),
+cte2 as ( select a,b from cte1 ),
+cte3 as ( select a,b from cte2 )
+select * from cte3, cte2";
+execute stmt;
+a b a b
+1 3 1 3
+1 3 1 3
+1 3 1 3
+1 3 1 3
+execute stmt;
+a b a b
+1 3 1 3
+1 3 1 3
+1 3 1 3
+1 3 1 3
+deallocate prepare stmt;
+create procedure sp() with
+cte1 as ( select a,b from t1 where a = 1 AND b = 3 ),
+cte2 as ( select a,b from cte1 ),
+cte3 as ( select a,b from cte2 )
+select * from cte3, cte2;
+call sp();
+a b a b
+1 3 1 3
+1 3 1 3
+1 3 1 3
+1 3 1 3
+call sp();
+a b a b
+1 3 1 3
+1 3 1 3
+1 3 1 3
+1 3 1 3
+drop procedure sp;
+drop table t1;
# 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 ac74683ad44..dfb14e65f2c 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1492,6 +1492,56 @@ drop procedure sp3;
drop table t1;
+--echo #
+--echo # MDEV-26095: missing RECURSIVE for the recursive definition of CTE
+--echo # embedded into another CTE definition
+--echo #
+
+create table t1 (a int);
+insert into t1 values (5), (7);
+
+with cte_e as (
+ with recursive cte_r as (
+ select a from t1 union select a+1 as a from cte_r r where a < 10
+ ) select * from cte_r
+) select * from cte_e;
+
+--ERROR ER_NO_SUCH_TABLE
+with cte_e as (
+ with cte_r as (
+ select a from t1 union select a+1 as a from cte_r r where a < 10
+ ) select * from cte_r
+) select * from cte_e;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-26025: query with two usage of a CTE executing via PS /SP
+--echo #
+
+create table t1 (a int, b int);
+insert into t1 value (1,3), (3,2), (1,3), (4,1);
+
+let $q=
+with
+ cte1 as ( select a,b from t1 where a = 1 AND b = 3 ),
+ cte2 as ( select a,b from cte1 ),
+ cte3 as ( select a,b from cte2 )
+select * from cte3, cte2;
+
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+eval create procedure sp() $q;
+
+call sp();
+call sp();
+
+drop procedure sp;
+drop table t1;
+
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 21c6b225a89..50d3e889c4c 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -4500,6 +4500,33 @@ deallocate prepare stmt;
drop table folks;
set big_tables=@save_big_tables;
#
+# MDEV-26135: execution of PS for query with hanging recursive CTE
+#
+create table t1 (a int);
+insert into t1 values (5), (7);
+create table t2 (b int);
+insert into t2 values (3), (7), (1);
+with recursive r as (select a from t1 union select a+1 from r where a < 10)
+select * from t2;
+b
+3
+7
+1
+prepare stmt from "with recursive r as (select a from t1 union select a+1 from r where a < 10)
+select * from t2";
+execute stmt;
+b
+3
+7
+1
+execute stmt;
+b
+3
+7
+1
+deallocate prepare stmt;
+drop table t1,t2;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index c3537e5bd0c..4937784fbe4 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2855,6 +2855,27 @@ drop table folks;
set big_tables=@save_big_tables;
--echo #
+--echo # MDEV-26135: execution of PS for query with hanging recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values (5), (7);
+create table t2 (b int);
+insert into t2 values (3), (7), (1);
+
+let $q=
+with recursive r as (select a from t1 union select a+1 from r where a < 10)
+select * from t2;
+
+eval $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table t1,t2;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/mysql_client_test.result b/mysql-test/main/mysql_client_test.result
index 8a2921f28b5..26afee9e4d7 100644
--- a/mysql-test/main/mysql_client_test.result
+++ b/mysql-test/main/mysql_client_test.result
@@ -127,6 +127,11 @@ Data:
EOF
mysql_stmt_next_result(): 0; field_count: 0
# ------------------------------------
+# cat MYSQL_TMP_DIR/test_mdev26145.out.log
+# ------------------------------------
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def MAX(a) MAX(a) 3 11 0 Y 32768 0 63
+# ------------------------------------
# cat MYSQL_TMP_DIR/test_explain_meta.out.log
diff --git a/mysql-test/main/mysql_client_test.test b/mysql-test/main/mysql_client_test.test
index fc2e3a00ed5..07d4b43154a 100644
--- a/mysql-test/main/mysql_client_test.test
+++ b/mysql-test/main/mysql_client_test.test
@@ -36,6 +36,10 @@ echo ok;
--echo # ------------------------------------
--cat_file $MYSQL_TMP_DIR/test_wl4435.out.log
--echo # ------------------------------------
+--echo # cat MYSQL_TMP_DIR/test_mdev26145.out.log
+--echo # ------------------------------------
+--cat_file $MYSQL_TMP_DIR/test_mdev26145.out.log
+--echo # ------------------------------------
--echo
--echo
diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result
index 9cdf9800cee..14b9b861a14 100644
--- a/mysql-test/main/order_by_innodb.result
+++ b/mysql-test/main/order_by_innodb.result
@@ -147,4 +147,56 @@ i n
656 eight
set optimizer_switch= @save_optimizer_switch;
DROP TABLE t1,t2,t3;
+#
+# MDEV-25858: Query results are incorrect when indexes are added
+#
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb;
+insert into t1 values (1),(2),(3);
+CREATE TABLE t2 (
+id int NOT NULL PRIMARY KEY,
+id2 int NOT NULL,
+d1 datetime,
+d2 timestamp NOT NULL,
+KEY id2 (id2)
+) engine=innodb;
+insert into t2 values
+(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00');
+select
+t1.id,t2.id
+from
+t1 left join
+t2 on t2.id2 = t1.id and
+t2.id = (select dd.id
+from t2 dd
+where
+dd.id2 = t1.id and
+d1 > '2019-02-06 00:00:00'
+ order by
+dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+);
+id id
+1 NULL
+2 1
+3 3
+create index for_latest_sort on t2 (d1 desc, d2 desc, id desc);
+select
+t1.id,t2.id
+from
+t1 left join
+t2 on t2.id2 = t1.id and
+t2.id = (select dd.id
+from t2 dd
+where
+dd.id2 = t1.id and
+d1 > '2019-02-06 00:00:00'
+ order by
+dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+);
+id id
+1 NULL
+2 1
+3 3
+drop table t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test
index f4c738263ae..97c043b8dbc 100644
--- a/mysql-test/main/order_by_innodb.test
+++ b/mysql-test/main/order_by_innodb.test
@@ -135,4 +135,55 @@ set optimizer_switch= @save_optimizer_switch;
DROP TABLE t1,t2,t3;
+--echo #
+--echo # MDEV-25858: Query results are incorrect when indexes are added
+--echo #
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb;
+insert into t1 values (1),(2),(3);
+
+CREATE TABLE t2 (
+ id int NOT NULL PRIMARY KEY,
+ id2 int NOT NULL,
+ d1 datetime,
+ d2 timestamp NOT NULL,
+ KEY id2 (id2)
+) engine=innodb;
+
+insert into t2 values
+ (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+ (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+ (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00');
+
+select
+ t1.id,t2.id
+from
+ t1 left join
+ t2 on t2.id2 = t1.id and
+ t2.id = (select dd.id
+ from t2 dd
+ where
+ dd.id2 = t1.id and
+ d1 > '2019-02-06 00:00:00'
+ order by
+ dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+ );
+
+create index for_latest_sort on t2 (d1 desc, d2 desc, id desc);
+
+select
+ t1.id,t2.id
+from
+ t1 left join
+ t2 on t2.id2 = t1.id and
+ t2.id = (select dd.id
+ from t2 dd
+ where
+ dd.id2 = t1.id and
+ d1 > '2019-02-06 00:00:00'
+ order by
+ dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+ );
+drop table t1,t2;
+
--echo # End of 10.2 tests
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 34951c858c8..53fa6e663fb 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -2103,6 +2103,57 @@ drop view v1;
#
# End of 10.1 tests
#
+#
+# MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity
+#
+set
+@tmp_jcl=@@join_cache_level,
+@tmp_sel=@@optimizer_use_condition_selectivity;
+set
+join_cache_level=3,
+optimizer_use_condition_selectivity=2;
+CREATE TABLE t1 (
+c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int,
+c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int,
+c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int,
+c29 int, c30 int, c31 int, c32 int, c33 int, c34 int
+) ENGINE=InnoDB;
+SELECT * FROM t1
+WHERE
+(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
+c11, c12, c13, c14, c15, c16, c17, c18, c19,
+c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
+c30, c31, c32, c33, c34) IN (SELECT * FROM t1) ;
+c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34
+set
+join_cache_level=@tmp_jcl,
+optimizer_use_condition_selectivity=@tmp_sel;
+drop table t1;
+#
+# MDEV-25013: SIGSEGV in best_extension_by_limited_search | SIGSEGV in restore_prev_nj_state
+#
+SET join_cache_level=3;
+CREATE TABLE t1 (
+TEXT1 TEXT, TEXT2 TEXT, TEXT3 TEXT, TEXT4 TEXT, TEXT5 TEXT,
+TEXT6 TEXT, TEXT7 TEXT, TEXT8 TEXT, TEXT9 TEXT, TEXT10 TEXT,
+TEXT11 TEXT, TEXT12 TEXT,TEXT13 TEXT,TEXT14 TEXT,TEXT15 TEXT,
+TEXT16 TEXT,TEXT17 TEXT,TEXT18 TEXT,TEXT19 TEXT,TEXT20 TEXT,
+TEXT21 TEXT,TEXT22 TEXT,TEXT23 TEXT,TEXT24 TEXT,TEXT25 TEXT,
+TEXT26 TEXT,TEXT27 TEXT,TEXT28 TEXT,TEXT29 TEXT,TEXT30 TEXT,
+TEXT31 TEXT,TEXT32 TEXT,TEXT33 TEXT,TEXT34 TEXT,TEXT35 TEXT,
+TEXT36 TEXT,TEXT37 TEXT,TEXT38 TEXT,TEXT39 TEXT,TEXT40 TEXT,
+TEXT41 TEXT,TEXT42 TEXT,TEXT43 TEXT,TEXT44 TEXT,TEXT45 TEXT,
+TEXT46 TEXT,TEXT47 TEXT,TEXT48 TEXT,TEXT49 TEXT,TEXT50 TEXT
+) ENGINE=InnoDB;
+EXPLAIN SELECT 1 FROM t1 NATURAL JOIN t1 AS t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t2 hash_ALL NULL #hash#$hj 150 test.t1.TEXT1,test.t1.TEXT2,test.t1.TEXT3,test.t1.TEXT4,test.t1.TEXT5,test.t1.TEXT6,test.t1.TEXT7,test.t1.TEXT8,test.t1.TEXT9,test.t1.TEXT10,test.t1.TEXT11,test.t1.TEXT12,test.t1.TEXT13,test.t1.TEXT14,test.t1.TEXT15,test.t1.TEXT16,test.t1.TEXT17,test.t1.TEXT18,test.t1.TEXT19,test.t1.TEXT20,test.t1.TEXT21,test.t1.TEXT22,test.t1.TEXT23,test.t1.TEXT24,test.t1.TEXT25,test.t1.TEXT26,test.t1.TEXT27,test.t1.TEXT28,test.t1.TEXT29,test.t1.TEXT30,test.t1.TEXT31,test.t1.TEXT32,test.t1.TEXT33,test.t1.TEXT34,test.t1.TEXT35,test.t1.TEXT36,test.t1.TEXT37,test.t1.TEXT38,test.t1.TEXT39,test.t1.TEXT40,test.t1.TEXT41,test.t1.TEXT42,test.t1.TEXT43,test.t1.TEXT44,test.t1.TEXT45,test.t1.TEXT46,test.t1.TEXT47,test.t1.TEXT48,test.t1.TEXT49,test.t1.TEXT50 1 Using where; Using join buffer (flat, BNLH join)
+set join_cache_level=@tmp_jcl;
+drop table t1;
+#
+# End of 10.1 tests
+#
set use_stat_tables= @tmp_ust;
set optimizer_use_condition_selectivity= @tmp_oucs;
set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity_innodb.test b/mysql-test/main/selectivity_innodb.test
index 6c457e2848b..057a36fcf62 100644
--- a/mysql-test/main/selectivity_innodb.test
+++ b/mysql-test/main/selectivity_innodb.test
@@ -174,6 +174,61 @@ drop view v1;
--echo # End of 10.1 tests
--echo #
+--echo #
+--echo # MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity
+--echo #
+
+set
+ @tmp_jcl=@@join_cache_level,
+ @tmp_sel=@@optimizer_use_condition_selectivity;
+set
+ join_cache_level=3,
+ optimizer_use_condition_selectivity=2;
+
+CREATE TABLE t1 (
+ c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int,
+ c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int,
+ c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int,
+ c29 int, c30 int, c31 int, c32 int, c33 int, c34 int
+) ENGINE=InnoDB;
+
+SELECT * FROM t1
+WHERE
+ (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
+ c11, c12, c13, c14, c15, c16, c17, c18, c19,
+ c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
+ c30, c31, c32, c33, c34) IN (SELECT * FROM t1) ;
+
+set
+ join_cache_level=@tmp_jcl,
+ optimizer_use_condition_selectivity=@tmp_sel;
+drop table t1;
+
+--echo #
+--echo # MDEV-25013: SIGSEGV in best_extension_by_limited_search | SIGSEGV in restore_prev_nj_state
+--echo #
+
+SET join_cache_level=3;
+CREATE TABLE t1 (
+ TEXT1 TEXT, TEXT2 TEXT, TEXT3 TEXT, TEXT4 TEXT, TEXT5 TEXT,
+ TEXT6 TEXT, TEXT7 TEXT, TEXT8 TEXT, TEXT9 TEXT, TEXT10 TEXT,
+ TEXT11 TEXT, TEXT12 TEXT,TEXT13 TEXT,TEXT14 TEXT,TEXT15 TEXT,
+ TEXT16 TEXT,TEXT17 TEXT,TEXT18 TEXT,TEXT19 TEXT,TEXT20 TEXT,
+ TEXT21 TEXT,TEXT22 TEXT,TEXT23 TEXT,TEXT24 TEXT,TEXT25 TEXT,
+ TEXT26 TEXT,TEXT27 TEXT,TEXT28 TEXT,TEXT29 TEXT,TEXT30 TEXT,
+ TEXT31 TEXT,TEXT32 TEXT,TEXT33 TEXT,TEXT34 TEXT,TEXT35 TEXT,
+ TEXT36 TEXT,TEXT37 TEXT,TEXT38 TEXT,TEXT39 TEXT,TEXT40 TEXT,
+ TEXT41 TEXT,TEXT42 TEXT,TEXT43 TEXT,TEXT44 TEXT,TEXT45 TEXT,
+ TEXT46 TEXT,TEXT47 TEXT,TEXT48 TEXT,TEXT49 TEXT,TEXT50 TEXT
+) ENGINE=InnoDB;
+EXPLAIN SELECT 1 FROM t1 NATURAL JOIN t1 AS t2;
+
+set join_cache_level=@tmp_jcl;
+drop table t1;
+--echo #
+--echo # End of 10.1 tests
+--echo #
+
set use_stat_tables= @tmp_ust;
set optimizer_use_condition_selectivity= @tmp_oucs;
set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity_no_engine.result b/mysql-test/main/selectivity_no_engine.result
index 7fc3c6e9909..c513c19133d 100644
--- a/mysql-test/main/selectivity_no_engine.result
+++ b/mysql-test/main/selectivity_no_engine.result
@@ -293,6 +293,26 @@ SELECT * FROM t1 WHERE t1.d = 0 AND t1.p = '1' AND t1.i != '-1' AND t1.n = 'some
i n d p
set optimizer_use_condition_selectivity= @tmp_mdev8779;
DROP TABLE t1;
+#
+# MDEV-23937: SIGSEGV in looped best_extension_by_limited_search from greedy_search
+# (Testcase only)
+#
+set
+@tmp_jcl= @@join_cache_level,
+@tmp_ucs= @@optimizer_use_condition_selectivity;
+set
+join_cache_level=3,
+optimizer_use_condition_selectivity=2;
+CREATE TABLE t1 AS SELECT * FROM mysql.user;
+CREATE TABLE t3 (b VARCHAR (1));
+CREATE TABLE t2 (c2 INT);
+INSERT INTO t2 VALUES (1);
+EXPLAIN
+SELECT * FROM t1 AS a NATURAL JOIN t1 AS b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE a ALL NULL NULL NULL NULL 4
+1 SIMPLE b hash_ALL NULL #hash#$hj 828 test.a.Host,test.a.User,test.a.Password,test.a.Select_priv,test.a.Insert_priv,test.a.Update_priv,test.a.Delete_priv,test.a.Create_priv,test.a.Drop_priv,test.a.Reload_priv,test.a.Shutdown_priv,test.a.Process_priv,test.a.File_priv,test.a.Grant_priv,test.a.References_priv,test.a.Index_priv,test.a.Alter_priv,test.a.Show_db_priv,test.a.Super_priv,test.a.Create_tmp_table_priv,test.a.Lock_tables_priv,test.a.Execute_priv,test.a.Repl_slave_priv,test.a.Repl_client_priv,test.a.Create_view_priv,test.a.Show_view_priv,test.a.Create_routine_priv,test.a.Alter_routine_priv,test.a.Create_user_priv,test.a.Event_priv,test.a.Trigger_priv,test.a.Create_tablespace_priv,test.a.Delete_history_priv,test.a.ssl_type,test.a.ssl_cipher,test.a.x509_issuer,test.a.x509_subject,test.a.max_questions,test.a.max_updates,test.a.max_connections,test.a.max_user_connections,test.a.plugin,test.a.authentication_string,test.a.password_expired,test.a.is_role,test.a.default_role,test.a.max_statement_time 4 Using where; Using join buffer (flat, BNLH join)
+DROP TABLE t1,t2,t3;
#
# End of the test file
#
diff --git a/mysql-test/main/selectivity_no_engine.test b/mysql-test/main/selectivity_no_engine.test
index 345b7bd1e8a..b5f52dd167d 100644
--- a/mysql-test/main/selectivity_no_engine.test
+++ b/mysql-test/main/selectivity_no_engine.test
@@ -228,6 +228,27 @@ SELECT * FROM t1 WHERE t1.d = 0 AND t1.p = '1' AND t1.i != '-1' AND t1.n = 'some
set optimizer_use_condition_selectivity= @tmp_mdev8779;
DROP TABLE t1;
+--echo #
+--echo # MDEV-23937: SIGSEGV in looped best_extension_by_limited_search from greedy_search
+--echo # (Testcase only)
+--echo #
+set
+ @tmp_jcl= @@join_cache_level,
+ @tmp_ucs= @@optimizer_use_condition_selectivity;
+set
+ join_cache_level=3,
+ optimizer_use_condition_selectivity=2;
+
+CREATE TABLE t1 AS SELECT * FROM mysql.user;
+CREATE TABLE t3 (b VARCHAR (1));
+CREATE TABLE t2 (c2 INT);
+INSERT INTO t2 VALUES (1);
+
+EXPLAIN
+SELECT * FROM t1 AS a NATURAL JOIN t1 AS b;
+
+DROP TABLE t1,t2,t3;
+
--echo #
--echo # End of the test file
--echo #
diff --git a/mysql-test/main/skip_name_resolve.result b/mysql-test/main/skip_name_resolve.result
index 9a903ebf472..fe71b714cbc 100644
--- a/mysql-test/main/skip_name_resolve.result
+++ b/mysql-test/main/skip_name_resolve.result
@@ -39,4 +39,24 @@ SET @@LOCAL.skip_name_resolve=0;
ERROR HY000: Variable 'skip_name_resolve' is a read only variable
SET @@GLOBAL.skip_name_resolve=0;
ERROR HY000: Variable 'skip_name_resolve' is a read only variable
-End of 5.1 tests
+#
+# End of 5.1 tests
+#
+#
+# MDEV-26081 set role crashes when a hostname cannot be resolved
+#
+create user u1@`%`;
+create role r1;
+create role r2;
+grant r2 to r1;
+grant r1 to u1@`%`;
+connect u1,127.0.0.1,u1,,,$MASTER_MYPORT;
+set role r2;
+ERROR OP000: User `u1`@`%` has not been granted role `r2`
+disconnect u1;
+connection default;
+drop user u1@`%`;
+drop role r1, r2;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/main/skip_name_resolve.test b/mysql-test/main/skip_name_resolve.test
index b0c5118f970..0ff19092b82 100644
--- a/mysql-test/main/skip_name_resolve.test
+++ b/mysql-test/main/skip_name_resolve.test
@@ -50,4 +50,28 @@ SET @@LOCAL.skip_name_resolve=0;
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
SET @@GLOBAL.skip_name_resolve=0;
---echo End of 5.1 tests
+--echo #
+--echo # End of 5.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-26081 set role crashes when a hostname cannot be resolved
+--echo #
+
+create user u1@`%`;
+create role r1;
+create role r2;
+grant r2 to r1;
+grant r1 to u1@`%`;
+
+connect u1,127.0.0.1,u1,,,$MASTER_MYPORT;
+error ER_INVALID_ROLE;
+set role r2;
+disconnect u1;
+connection default;
+drop user u1@`%`;
+drop role r1, r2;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index 7f0c2b88a41..3cc88125b12 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3911,6 +3911,293 @@ sum(i) over () IN ( SELECT 1 FROM t1 a)
0
DROP TABLE t1;
#
+# MDEV-25565: 2-nd call of SP with SELECT from view / derived table / CTE
+# returning the result of calculation of 2 window
+# functions that use the same window specification
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5);
+create view v2 as select a from t1 group by a;
+create view v1 as select * from v2;
+create procedure sp1() select v1.a,
+sum(v1.a) over (partition by v1.a order by v1.a) as k,
+avg(v1.a) over (partition by v1.a order by v1.a) as m
+from v1;
+call sp1();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp1();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "select v1.a,
+sum(v1.a) over (partition by v1.a order by v1.a) as k,
+avg(v1.a) over (partition by v1.a order by v1.a) as m
+from v1";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+create procedure sp2() select * from
+( select dt1.a,
+sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
+avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
+from (select * from v2) as dt1
+) as dt;
+call sp2();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp2();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "select * from
+( select dt1.a,
+sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
+avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
+from (select * from v2) as dt1
+) as dt";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+create procedure sp3() select * from
+( select dt1.a,
+sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
+avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
+from ( select * from (select * from t1 group by a) as dt2 ) as dt1
+) as dt;
+call sp3();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp3();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "select * from
+( select dt1.a,
+sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
+avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
+from ( select * from (select * from t1 group by a) as dt2 ) as dt1
+) as dt";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+create procedure sp4() with cte1 as (select * from (select * from t1 group by a) as dt2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte;
+call sp4();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp4();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "with cte1 as (select * from (select * from t1 group by a) as dt2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+create procedure sp5() with cte1 as (select * from v2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte;
+call sp5();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp5();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "with cte1 as (select * from v2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+create procedure sp6() with
+cte1 as (with cte2 as (select * from t1 group by a) select * from cte2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte;
+call sp6();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp6();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "with
+cte1 as (with cte2 as (select * from t1 group by a) select * from cte2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+create procedure sp7() with
+cte2 as (select * from v1),
+cte1 as (select * from cte2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte;
+call sp7();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+call sp7();
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+prepare stmt from "with
+cte2 as (select * from v1),
+cte1 as (select * from cte2),
+cte as
+( select cte1.a,
+sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+from cte1 )
+select * from cte";
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+execute stmt;
+a k m
+1 1 1.0000
+3 3 3.0000
+5 5 5.0000
+7 7 7.0000
+deallocate prepare stmt;
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop procedure sp4;
+drop procedure sp5;
+drop procedure sp6;
+drop procedure sp7;
+drop view v1,v2;
+drop table t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index 2e859007d0f..5a547b363b7 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2557,6 +2557,153 @@ SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1;
DROP TABLE t1;
--echo #
+--echo # MDEV-25565: 2-nd call of SP with SELECT from view / derived table / CTE
+--echo # returning the result of calculation of 2 window
+--echo # functions that use the same window specification
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5);
+
+create view v2 as select a from t1 group by a;
+create view v1 as select * from v2;
+
+let $q1=
+select v1.a,
+ sum(v1.a) over (partition by v1.a order by v1.a) as k,
+ avg(v1.a) over (partition by v1.a order by v1.a) as m
+from v1;
+
+eval create procedure sp1() $q1;
+call sp1();
+call sp1();
+
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q2=
+select * from
+ ( select dt1.a,
+ sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
+ avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
+ from (select * from v2) as dt1
+ ) as dt;
+
+eval create procedure sp2() $q2;
+call sp2();
+call sp2();
+
+eval prepare stmt from "$q2";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q3=
+select * from
+ ( select dt1.a,
+ sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
+ avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
+ from ( select * from (select * from t1 group by a) as dt2 ) as dt1
+ ) as dt;
+
+eval create procedure sp3() $q3;
+call sp3();
+call sp3();
+
+eval prepare stmt from "$q3";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q4=
+with cte1 as (select * from (select * from t1 group by a) as dt2),
+ cte as
+ ( select cte1.a,
+ sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+ avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+ from cte1 )
+select * from cte;
+
+eval create procedure sp4() $q4;
+call sp4();
+call sp4();
+
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q5=
+with cte1 as (select * from v2),
+ cte as
+ ( select cte1.a,
+ sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+ avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+ from cte1 )
+select * from cte;
+
+eval create procedure sp5() $q5;
+call sp5();
+call sp5();
+
+eval prepare stmt from "$q5";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q6=
+with
+cte1 as (with cte2 as (select * from t1 group by a) select * from cte2),
+ cte as
+ ( select cte1.a,
+ sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+ avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+ from cte1 )
+select * from cte;
+
+eval create procedure sp6() $q6;
+call sp6();
+call sp6();
+
+eval prepare stmt from "$q6";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q7=
+with
+ cte2 as (select * from v1),
+ cte1 as (select * from cte2),
+ cte as
+ ( select cte1.a,
+ sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
+ avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
+ from cte1 )
+select * from cte;
+
+eval create procedure sp7() $q7;
+call sp7();
+call sp7();
+
+eval prepare stmt from "$q7";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop procedure sp4;
+drop procedure sp5;
+drop procedure sp6;
+drop procedure sp7;
+drop view v1,v2;
+drop table t1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
View Lorry Controller Status