summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/cte_nonrecursive.result110
-rw-r--r--mysql-test/main/cte_nonrecursive.test103
-rw-r--r--sql/sql_lex.cc10
3 files changed, 219 insertions, 4 deletions
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index ff5a6f15597..1bd731c956b 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -1691,3 +1691,113 @@ DROP TABLE test.t;
connection default;
disconnect con1;
# End of 10.2 tests
+#
+# MDEV-21673: several references to CTE that uses
+# local variables / parameters of SP
+#
+CREATE TABLE t1 (col1 int);
+CREATE TABLE t2 (col1 int, col2 date, col3 varchar(16), col4 int);
+CREATE TABLE t3 (col1 int, col2 date);
+CREATE TABLE t4 (col1 int, col2 date);
+INSERT INTO t1 VALUES (3), (7), (9), (1);
+INSERT INTO t2 VALUES
+(3,'2019-09-01','AAA',2), (7,'2019-10-01','AAA',4), (3,'2019-10-01','AAA',8),
+(1,'2019-10-01','BBB',9), (1,'2019-10-01','AAA',4), (1,'2019-10-01','AAA',6);
+INSERT INTO t3 VALUES
+(4,'2018-10-01'), (6,'2018-10-01'), (4,'2017-10-01'), (7,'2017-10-01');
+INSERT INTO t4 VALUES
+(5,'2018-10-01'), (8,'2017-10-01'), (4,'2017-10-01');
+CREATE OR REPLACE PROCEDURE SP1()
+BEGIN
+DECLARE p_date date;
+DECLARE p_var2 varchar(16);
+SET p_date='2019-10-01';
+SET p_var2='AAA';
+WITH cte_first(col) AS
+(
+SELECT DISTINCT col4
+FROM t1, t2
+WHERE t2.col1 = t1.col1 AND t2.col2 = p_date AND t2.col3 = p_var2
+),
+cte2 AS
+(
+SELECT DISTINCT col2
+FROM t3
+WHERE col1 IN ( SELECT col FROM cte_first )
+),
+cte3 AS (
+SELECT distinct t4.col1
+FROM cte2, t4
+WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first )
+)
+SELECT * FROM cte3;
+END|
+CREATE PROCEDURE SP2(IN d date)
+BEGIN
+DECLARE p_var2 varchar(16);
+SET p_var2='AAA';
+WITH cte_first(col) AS
+(
+SELECT DISTINCT col4
+FROM t1, t2
+WHERE t2.col1 = t1.col1 AND t2.col2 = d AND t2.col3 = p_var2
+),
+cte2 AS
+(
+SELECT DISTINCT col2
+FROM t3
+WHERE col1 IN ( SELECT col FROM cte_first )
+),
+cte3 AS (
+SELECT distinct t4.col1
+FROM cte2, t4
+WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first )
+)
+SELECT * FROM cte3;
+END|
+CREATE TABLE t AS
+SELECT col4 AS col
+FROM t1, t2
+WHERE t2.col1 = t1.col1 AND t2.col2 ='2019-10-01' AND t2.col3 = 'AAA';
+SELECT * FROM t;
+col
+4
+8
+4
+6
+CREATE TABLE tt AS
+SELECT col2
+FROM t3
+WHERE col1 IN ( SELECT col FROM t );
+SELECT * FROM tt;
+col2
+2018-10-01
+2018-10-01
+2017-10-01
+SELECT t4.col1
+FROM tt, t4
+WHERE t4.col2 = tt.col2 AND t4.col1 IN ( SELECT col FROM t );
+col1
+8
+4
+DROP TABLE t,tt;
+CALL SP1();
+col1
+8
+4
+CALL SP1();
+col1
+8
+4
+CALL SP2('2019-10-01');
+col1
+8
+4
+CALL SP2('2019-10-01');
+col1
+8
+4
+DROP PROCEDURE SP1;
+DROP PROCEDURE SP2;
+DROP TABLE t1,t2,t3,t4;
+# End of 10.3 tests
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 41a5b815bc7..00242e3d6ae 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1202,3 +1202,106 @@ DROP TABLE test.t;
--disconnect con1
--echo # End of 10.2 tests
+
+--echo #
+--echo # MDEV-21673: several references to CTE that uses
+--echo # local variables / parameters of SP
+--echo #
+
+CREATE TABLE t1 (col1 int);
+CREATE TABLE t2 (col1 int, col2 date, col3 varchar(16), col4 int);
+CREATE TABLE t3 (col1 int, col2 date);
+CREATE TABLE t4 (col1 int, col2 date);
+INSERT INTO t1 VALUES (3), (7), (9), (1);
+INSERT INTO t2 VALUES
+ (3,'2019-09-01','AAA',2), (7,'2019-10-01','AAA',4), (3,'2019-10-01','AAA',8),
+ (1,'2019-10-01','BBB',9), (1,'2019-10-01','AAA',4), (1,'2019-10-01','AAA',6);
+INSERT INTO t3 VALUES
+ (4,'2018-10-01'), (6,'2018-10-01'), (4,'2017-10-01'), (7,'2017-10-01');
+INSERT INTO t4 VALUES
+ (5,'2018-10-01'), (8,'2017-10-01'), (4,'2017-10-01');
+
+DELIMITER |;
+
+CREATE OR REPLACE PROCEDURE SP1()
+BEGIN
+DECLARE p_date date;
+DECLARE p_var2 varchar(16);
+SET p_date='2019-10-01';
+SET p_var2='AAA';
+WITH cte_first(col) AS
+(
+ SELECT DISTINCT col4
+ FROM t1, t2
+ WHERE t2.col1 = t1.col1 AND t2.col2 = p_date AND t2.col3 = p_var2
+),
+cte2 AS
+(
+ SELECT DISTINCT col2
+ FROM t3
+ WHERE col1 IN ( SELECT col FROM cte_first )
+),
+cte3 AS (
+ SELECT distinct t4.col1
+ FROM cte2, t4
+ WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first )
+)
+SELECT * FROM cte3;
+END|
+
+CREATE PROCEDURE SP2(IN d date)
+BEGIN
+DECLARE p_var2 varchar(16);
+SET p_var2='AAA';
+WITH cte_first(col) AS
+(
+ SELECT DISTINCT col4
+ FROM t1, t2
+ WHERE t2.col1 = t1.col1 AND t2.col2 = d AND t2.col3 = p_var2
+),
+cte2 AS
+(
+ SELECT DISTINCT col2
+ FROM t3
+ WHERE col1 IN ( SELECT col FROM cte_first )
+),
+cte3 AS (
+ SELECT distinct t4.col1
+ FROM cte2, t4
+ WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first )
+)
+SELECT * FROM cte3;
+END|
+
+DELIMITER ;|
+
+
+CREATE TABLE t AS
+SELECT col4 AS col
+FROM t1, t2
+WHERE t2.col1 = t1.col1 AND t2.col2 ='2019-10-01' AND t2.col3 = 'AAA';
+SELECT * FROM t;
+
+CREATE TABLE tt AS
+SELECT col2
+FROM t3
+WHERE col1 IN ( SELECT col FROM t );
+SELECT * FROM tt;
+
+SELECT t4.col1
+FROM tt, t4
+WHERE t4.col2 = tt.col2 AND t4.col1 IN ( SELECT col FROM t );
+
+DROP TABLE t,tt;
+
+CALL SP1();
+CALL SP1();
+
+CALL SP2('2019-10-01');
+CALL SP2('2019-10-01');
+
+DROP PROCEDURE SP1;
+DROP PROCEDURE SP2;
+DROP TABLE t1,t2,t3,t4;
+
+--echo # End of 10.3 tests
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 58c91ab21e7..00fca6c6bf9 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -7066,7 +7066,8 @@ Item *LEX::create_item_limit(THD *thd, const Lex_ident_cli_st *ca)
if (unlikely(!(item= new (thd->mem_root)
Item_splocal(thd, rh, &sa,
spv->offset, spv->type_handler(),
- pos.pos(), pos.length()))))
+ clone_spec_offset ? 0 : pos.pos(),
+ clone_spec_offset ? 0 : pos.length()))))
return NULL;
#ifdef DBUG_ASSERT_EXISTS
item->m_sp= sphead;
@@ -7165,14 +7166,15 @@ Item *LEX::create_item_ident_sp(THD *thd, Lex_ident_sys_st *name,
}
Query_fragment pos(thd, sphead, start, end);
+ uint f_pos= clone_spec_offset ? 0 : pos.pos();
+ uint f_length= clone_spec_offset ? 0 : pos.length();
Item_splocal *splocal= spv->field_def.is_column_type_ref() ?
new (thd->mem_root) Item_splocal_with_delayed_data_type(thd, rh, name,
spv->offset,
- pos.pos(),
- pos.length()) :
+ f_pos, f_length) :
new (thd->mem_root) Item_splocal(thd, rh, name,
spv->offset, spv->type_handler(),
- pos.pos(), pos.length());
+ f_pos, f_length);
if (unlikely(splocal == NULL))
return NULL;
#ifdef DBUG_ASSERT_EXISTS
View Lorry Controller Status