- commits - lists.mariadb.org

[Commits] 4e4f742: Adjusted test results after the fix for MDEV-20411 (2)
by IgorBabaev 27 Jun '21

27 Jun '21

revision-id: 4e4f742ed7987ee52a34618d2ea3731e5e198ed8 (mariadb-10.2.31-1027-g4e4f742) parent(s): 8b3f816cab30eadd574b975d11c9e525a0b1691e author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-26 23:11:10 -0700 message: Adjusted test results after the fix for MDEV-20411 (2) --- mysql-test/suite/funcs_1/r/myisam_views-big.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index a1d59e1..d3227d8 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -4054,11 +4054,11 @@ CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table; 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 'or REPLACE AS Select * from tb2 my_table' at line 1 CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50; -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 'CASCADED CHECK OPTION AS Select * +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 'WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50; -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 'LOCAL CHECK OPTION AS Select * +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 'WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 SELECT * FROM tb2 my_table CREATE VIEW As v1; 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 'CREATE VIEW As v1' at line 1 @@ -4088,7 +4088,7 @@ FROM test.tb2 my_table CHECK OPTION WITH CASCADED; 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 'CHECK OPTION WITH CASCADED' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -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 'CASCADED CHECK OPTION +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 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION; @@ -4117,7 +4117,7 @@ FROM test.tb2 my_table CHECK OPTION WITH LOCAL; 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 'CHECK OPTION WITH LOCAL' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -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 'CASCADED CHECK OPTION +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 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION;

1 0

[Commits] 8b3f816: Adjusted test results after the fix for MDEV-20411
by IgorBabaev 26 Jun '21

26 Jun '21

revision-id: 8b3f816cab30eadd574b975d11c9e525a0b1691e (mariadb-10.2.31-1026-g8b3f816) parent(s): 12c80df4825955253a1a442098dbceb2a3e59971 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-26 08:51:17 -0700 message: Adjusted test results after the fix for MDEV-20411 --- mysql-test/suite/funcs_1/r/innodb_views.result | 8 ++++---- mysql-test/suite/funcs_1/r/memory_views.result | 8 ++++---- mysql-test/suite/funcs_1/r/storedproc.result | 11 +++++------ mysql-test/suite/innodb_fts/r/fulltext.result | 2 +- 4 files changed, 14 insertions(+), 15 deletions(-) diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 1316988..5e4b008 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -3551,11 +3551,11 @@ CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table; 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 'or REPLACE AS Select * from tb2 my_table' at line 1 CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50; -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 'CASCADED CHECK OPTION AS Select * +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 'WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50; -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 'LOCAL CHECK OPTION AS Select * +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 'WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 SELECT * FROM tb2 my_table CREATE VIEW As v1; 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 'CREATE VIEW As v1' at line 1 @@ -3585,7 +3585,7 @@ FROM test.tb2 my_table CHECK OPTION WITH CASCADED; 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 'CHECK OPTION WITH CASCADED' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -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 'CASCADED CHECK OPTION +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 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION; @@ -3614,7 +3614,7 @@ FROM test.tb2 my_table CHECK OPTION WITH LOCAL; 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 'CHECK OPTION WITH LOCAL' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -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 'CASCADED CHECK OPTION +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 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION; diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index 9f3fbd7..acd0425 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -3552,11 +3552,11 @@ CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table; 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 'or REPLACE AS Select * from tb2 my_table' at line 1 CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50; -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 'CASCADED CHECK OPTION AS Select * +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 'WITH CASCADED CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50; -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 'LOCAL CHECK OPTION AS Select * +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 'WITH LOCAL CHECK OPTION AS Select * from tb2 my_table limit 50' at line 1 SELECT * FROM tb2 my_table CREATE VIEW As v1; 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 'CREATE VIEW As v1' at line 1 @@ -3586,7 +3586,7 @@ FROM test.tb2 my_table CHECK OPTION WITH CASCADED; 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 'CHECK OPTION WITH CASCADED' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -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 'CASCADED CHECK OPTION +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 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION; @@ -3615,7 +3615,7 @@ FROM test.tb2 my_table CHECK OPTION WITH LOCAL; 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 'CHECK OPTION WITH LOCAL' at line 2 CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table; -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 'CASCADED CHECK OPTION +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 'WITH CASCADED CHECK OPTION AS SELECT F59, F60 FROM test.tb2 my_table' at line 1 CREATE OR REPLACE AS SELECT F59, F60 FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION; diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index 516ea98..64573ed 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -2807,7 +2807,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * from t1 where f2=f1' at line 1 CREATE PROCEDURE with() SELECT * from t1 where f2=f1; -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 '() +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 'with() SELECT * from t1 where f2=f1' at line 1 CREATE PROCEDURE write() SELECT * from t1 where f2=f1; @@ -9219,7 +9219,7 @@ CREATE PROCEDURE sp1() BEGIN declare with char; END// -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 'char; +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 'with char; END' at line 3 DROP PROCEDURE IF EXISTS sp1; Warnings: @@ -11547,9 +11547,8 @@ BEGIN declare with condition for sqlstate '02000'; declare exit handler for with set @var2 = 1; END// -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 'condition for sqlstate '02000'; -declare exit handler for with set @var2 = 1; -END' at line 3 +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 'with condition for sqlstate '02000'; +declare exit handler for with set @var2 ...' at line 3 DROP PROCEDURE IF EXISTS sp1; Warnings: Note 1305 PROCEDURE db_storedproc.sp1 does not exist @@ -13649,7 +13648,7 @@ CREATE PROCEDURE sp1( ) BEGIN declare with handler for sqlstate '02000' set @var2 = 1; END// -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 'handler for sqlstate '02000' set @var2 = 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 'with handler for sqlstate '02000' set @var2 = 1; END' at line 3 DROP PROCEDURE IF EXISTS sp1; Warnings: diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index 90deb48..1e5a83f 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -56,7 +56,7 @@ Only MyISAM tables support collections MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); -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 'QUERY EXPANSION)' at line 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 'WITH QUERY EXPANSION)' at line 1 explain select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where

1 0

[Commits] 2a2b17c: MDEV-20411 Procedure containing CTE incorrectly stored in mysql.proc
by IgorBabaev 26 Jun '21

26 Jun '21

revision-id: 2a2b17ce6dd1a61cd6d26af8211dc93c339956f1 (mariadb-10.2.31-1024-g2a2b17c) parent(s): 9258cfa4b469ab0d841e32ced7e501e04043637f author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-25 18:03:29 -0700 message: MDEV-20411 Procedure containing CTE incorrectly stored in mysql.proc If the first token of the body of a stored procedure was 'WITH' then the beginning of the body was determined incorrectly and that token was missing in the string representing the body of the SP in mysql.proc. As a resultnany call of such procedure failed as the string representing the body could not be parsed. The patch corrects the code of the functions get_tok_start() and get_cpp_tok_start() of the class Lex_input_stream to make them take into account look ahead tokens. The patch is needed only for 10.2 as this problem has neen resolved in 10.3+. --- mysql-test/r/cte_nonrecursive.result | 55 ++++++++++++++++++++++++++++++++++++ mysql-test/r/fulltext.result | 2 +- mysql-test/t/cte_nonrecursive.test | 29 +++++++++++++++++++ sql/sql_lex.h | 4 +-- 4 files changed, 87 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 7c6c6e8..c1d7fd0 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1964,4 +1964,59 @@ call p1(); ERROR 42S22: Unknown column 'a' in 'field list' drop procedure p1; drop table t1,t2; +# +# MDEV-20411: SP containing only one SELECT with WITH clause +# +create procedure sp1 () +with cte as (select 1 as a) select * from cte; +call sp1(); +a +1 +call sp1(); +a +1 +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); +create procedure sp2 () +with cte as (select * from t1) select * from cte; +call sp2(); +a +3 +7 +1 +7 +1 +1 +3 +1 +5 +call sp2(); +a +3 +7 +1 +7 +1 +1 +3 +1 +5 +create procedure sp3 () +with cte as (select * from t1 group by a) select * from cte; +call sp3(); +a +1 +3 +5 +7 +call sp3(); +a +1 +3 +5 +7 +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 5118cc3..709e91c 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -49,7 +49,7 @@ a b Full-text indexes are called collections Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); -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 'QUERY EXPANSION)' at line 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 'WITH QUERY EXPANSION)' at line 1 explain select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index f994781..cbe4f8b 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1463,4 +1463,33 @@ drop procedure p1; drop table t1,t2; + +--echo # +--echo # MDEV-20411: SP containing only one SELECT with WITH clause +--echo # + +create procedure sp1 () +with cte as (select 1 as a) select * from cte; +call sp1(); +call sp1(); + +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); + +create procedure sp2 () +with cte as (select * from t1) select * from cte; +call sp2(); +call sp2(); + +create procedure sp3 () +with cte as (select * from t1 group by a) select * from cte; +call sp3(); +call sp3(); + +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; + +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 03c06b9..bdf52e8 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -2176,7 +2176,7 @@ class Lex_input_stream /** Get the token start position, in the raw buffer. */ const char *get_tok_start() { - return m_tok_start; + return lookahead_token >= 0 ? m_tok_start_prev : m_tok_start; } void set_cpp_tok_start(const char *pos) @@ -2222,7 +2222,7 @@ class Lex_input_stream /** Get the token start position, in the pre-processed buffer. */ const char *get_cpp_tok_start() { - return m_cpp_tok_start; + return lookahead_token >= 0 ? m_cpp_tok_start_prev : m_cpp_tok_start; } /** Get the token end position, in the pre-processed buffer. */

1 0

[Commits] 4f6ac2d3bf0: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by Sergei Petrunia 25 Jun '21

25 Jun '21

revision-id: 4f6ac2d3bf0dd68e640a6d728fd2b98788bc7959 (mariadb-10.5.10-226-g4f6ac2d3bf0) parent(s): 7abf8b5c4d856a766dfdf4b2e1212694d66cbffb author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-25 19:11:19 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Variant 2, the "handle failures at conversion" approach. The patch is against the 10.5 series. Consider a query in form: select ... from (select item2 as COL1) as T where COL1=123 Condition pushdown into derived table will try to push "COL1=123" condition down into table T. The process of pushdown involves "substituting" the item, that is, replacing Item_field("T.COL1") with its "producing item" item2. In order to use item2, one needs to clone it (call Item::build_clone). If the item is not cloneable (e.g. Item_func_sp is not), the pushdown process will fail and nothing at all will be pushed. Fixed by introducing get_clonable_extracted_cond_for_where() which will try to apply the transformation for as many parts of condition as possible. The parts of condition that couldn't be transformed are dropped. --- mysql-test/main/derived_cond_pushdown.result | 98 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 48 ++++++++++++++ sql/sql_lex.cc | 54 ++++++++++++++- 3 files changed, 198 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 016ca22af1b..6a053b0f551 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17343,3 +17343,101 @@ id select_type table type possible_keys key key_len ref rows Extra drop view v1; drop table t1; # End of 10.4 tests +# +# MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +# +create function f1(a int) returns int DETERMINISTIC return (a+1); +create table t1 ( +pk int primary key, +a int, +b int, +key(a) +); +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; +insert into t1 select a,a,a from t3; +create view v1 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v2 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; +explain select * from v3 where col1=123; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +# This must use ref accesses for reading table t1, not full scans: +explain format=json +select * from v3 where col1=123 and col2=321; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "v3.col1 = 123 and v3.col2 = 321", + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["const"], + "rows": 1, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["const"], + "rows": 1, + "filtered": 100 + } + } + } + ] + } + } + } + } + } +} +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index a880712c8bd..8661bcdf388 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3540,3 +3540,51 @@ drop view v1; drop table t1; --echo # End of 10.4 tests + +--echo # +--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +--echo # +create function f1(a int) returns int DETERMINISTIC return (a+1); + +create table t1 ( + pk int primary key, + a int, + b int, + key(a) +); + +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; + +insert into t1 select a,a,a from t3; + +create view v1 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; + +create view v2 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; + +explain select * from v3 where col1=123; + +--echo # This must use ref accesses for reading table t1, not full scans: +explain format=json +select * from v3 where col1=123 and col2=321; + +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f16102d918b..ac615483aee 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -10360,6 +10360,55 @@ bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead) } +/* + @brief + Given condition cond and transformer+argument, try transforming as many + disjuncts as possible. + + @detail + The motivation of this function is to convert the condition that's being + pushed into a WHERE clause with derived_field_transformer_for_where. + The transformer may fail, in this case we want to convert as much of the + condition as possible. + This function only does it for top-level AND: disjuncts that could not be + converted are dropped. + + @return + Converted condition, or NULL if nothing could be converted +*/ + +static +Item *get_clonable_extracted_cond_for_where(THD *thd, + Item *cond, + Item_transformer transformer, + uchar *arg) +{ + if (cond->type() != Item::COND_ITEM || + ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC) + return cond->transform(thd, transformer, arg); + + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) + { + Item *new_item= item->transform(thd, transformer, arg); + if (!new_item) + li.remove(); + else + li.replace(new_item); + } + switch (((Item_cond*) cond)->argument_list()->elements) + { + case 0: + return 0; + case 1: + return ((Item_cond*) cond)->argument_list()->head(); + default: + return cond; + } +} + + /** @brief Extract the condition that can be pushed into WHERE clause @@ -10368,6 +10417,8 @@ bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead) @param cond the condition from which to extract a pushed condition @param remaining_cond IN/OUT the condition that will remain of cond after the extraction + Note: returning NULL means "re-check the entire + condition at the upper level". @param transformer the transformer callback function to be applied to the fields of the condition so it can be pushed` @@ -10432,8 +10483,7 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, if (!join->group_list && !with_sum_func) { - cond= - cond->transform(thd, transformer, arg); + cond= get_clonable_extracted_cond_for_where(thd, cond, transformer, arg); if (cond) { cond->walk(

1 0

[Commits] 521c546a71c: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by Sergei Petrunia 24 Jun '21

24 Jun '21

revision-id: 521c546a71c9449a87ebabbf13725b91fec45671 (mariadb-10.5.10-226-g521c546a71c) parent(s): 7abf8b5c4d856a766dfdf4b2e1212694d66cbffb author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-24 22:02:09 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Consider a query in form: select ... from (select item2 as COL1) as T where COL1=123 Condition pushdown into derived table will try to push "COL1=123" condition down into table T. The process of pushdown involves "substituting" the item, that is, replacing Item_field("T.COL1") with its "producing item" item2. In order to use item2, one needs to clone it (call Item::build_clone). If the item is not cloneable (e.g. Item_func_sp is not), the pushdown process will fail and nothing at all will be pushed. This patch makes pushdown_cond_for_derived() to first extract the portion of a condition which can be pushed, and then push only that. --- mysql-test/main/derived_cond_pushdown.result | 45 +++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 50 ++++++++++++++++++++++++++++ sql/item.cc | 24 +++++++++++++ sql/item.h | 11 ++++++ sql/item_cmpfunc.h | 4 +++ sql/item_func.h | 1 + sql/sql_derived.cc | 23 +++++++++++++ 7 files changed, 158 insertions(+) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 016ca22af1b..82e34a71964 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17343,3 +17343,48 @@ id select_type table type possible_keys key key_len ref rows Extra drop view v1; drop table t1; # End of 10.4 tests +# +# MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +# +create function f1(a int) returns int DETERMINISTIC return (a+1); +create table t1 ( +pk int primary key, +a int, +b int, +key(a) +); +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; +insert into t1 select a,a,a from t3; +create view v1 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v2 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; +explain select * from v3 where col1=123; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +# This must use ref accesses for reading table t1, not full scans: +explain select * from v3 where col1=123 and col2=321; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index a880712c8bd..1e924747b8f 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3540,3 +3540,53 @@ drop view v1; drop table t1; --echo # End of 10.4 tests + +--echo # +--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +--echo # +create function f1(a int) returns int DETERMINISTIC return (a+1); + +create table t1 ( + pk int primary key, + a int, + b int, + key(a) +); + +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; + +insert into t1 select a,a,a from t3; + +create view v1 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; + +create view v2 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; + +explain select * from v3 where col1=123; + +--echo # This must use ref accesses for reading table t1, not full scans: +explain select * from v3 where col1=123 and col2=321; +explain format=json +select * from v3 where col1=123 and col2=321; + +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; + diff --git a/sql/item.cc b/sql/item.cc index 8cad7111e07..13505690c9b 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7605,6 +7605,30 @@ Item *find_producing_item(Item *item, st_select_lex *sel) return NULL; } + +/* + @brief Check if this item cannot be pushed down into derived table + + @detail + This function checks if derived_field_transformer_for_where() will + fail. It will fail if the "producing item" (column in the derived table) + cannot be cloned. + + @return + false - Ok, can be pushed + true - Cannot be pushed +*/ + +bool Item_field::check_non_pushable_processor(void *arg) +{ + st_select_lex *sel= (st_select_lex *)arg; + Item *producing_item= find_producing_item(this, sel); + if (producing_item) + return producing_item->walk(&Item::check_non_cloneable_processor, 0, 0); + return false; // Ok +} + + Item *Item_field::derived_field_transformer_for_where(THD *thd, uchar *arg) { st_select_lex *sel= (st_select_lex *)arg; diff --git a/sql/item.h b/sql/item.h index e340483466b..a8526bd1016 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2118,6 +2118,12 @@ class Item: public Value_source, If there is some, sets a bit for this key in the proper key map. */ virtual bool check_index_dependence(void *arg) { return 0; } + + /* Return true if the item can NOT be pushed down into a derived table */ + virtual bool check_non_pushable_processor(void *arg) { return 0; } + + /* Return true if the item cannot be cloned (get_copy() will return NULL) */ + virtual bool check_non_cloneable_processor(void *arg) { return 0; } /*============== End of Item processor list ======================*/ /* @@ -2471,10 +2477,14 @@ class Item: public Value_source, marker &= ~EXTRACTION_MASK; } void check_pushable_cond(Pushdown_checker excl_dep_func, uchar *arg); + /* + @seealso pushable_cond_checker_for_derived_inner + */ bool pushable_cond_checker_for_derived(uchar *arg) { return excl_dep_on_table(*((table_map *)arg)); } + bool pushable_cond_checker_for_derived_inner(uchar *arg); bool pushable_cond_checker_for_subquery(uchar *arg) { DBUG_ASSERT(((Item*) arg)->get_IN_subquery()); @@ -3610,6 +3620,7 @@ class Item_field :public Item_ident, return field->table->pos_in_table_list->outer_join; } bool check_index_dependence(void *arg) override; + bool check_non_pushable_processor(void *arg) override; friend class Item_default_value; friend class Item_insert_value; friend class st_select_lex_unit; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index aa7269ab95a..d02a92bbfa9 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2908,6 +2908,8 @@ class Item_func_regex :public Item_bool_func const char *func_name() const { return "regexp"; } enum precedence precedence() const { return IN_PRECEDENCE; } Item *get_copy(THD *) { return 0; } + bool check_non_cloneable_processor(void *arg) { return true; } + void print(String *str, enum_query_type query_type) { print_op(str, query_type); @@ -2947,6 +2949,7 @@ class Item_func_regexp_instr :public Item_long_func bool fix_length_and_dec(); const char *func_name() const { return "regexp_instr"; } Item *get_copy(THD *thd) { return 0; } + bool check_non_cloneable_processor(void *arg) { return true; } }; @@ -3206,6 +3209,7 @@ class Item_equal: public Item_bool_func void set_context_field(Item_field *ctx_field) { context_field= ctx_field; } void set_link_equal_fields(bool flag) { link_equal_fields= flag; } Item* get_copy(THD *thd) { return 0; } + bool check_non_cloneable_processor(void *arg) override { return true; } /* This does not comply with the specification of the virtual method, but Item_equal items are processed distinguishly anyway diff --git a/sql/item_func.h b/sql/item_func.h index e774d9c53bd..506b2da335c 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -3563,6 +3563,7 @@ class Item_func_sp :public Item_func, return TRUE; } Item *get_copy(THD *) { return 0; } + bool check_non_cloneable_processor(void *arg) override { return true; } bool eval_not_null_tables(void *opt_arg) { not_null_tables_cache= 0; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index ed3743b029b..1017dc1a0d8 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1358,6 +1358,25 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) } +/* + @brief + Check if this item can be pushed into given SELECT. + + @param arg Pointer to SELECT_LEX object of the child select + + @seealso Item::pushable_cond_checker_for_derived + + @detail + This function checks if derived_field_transformer_for_where() will + fail. It will fail if the "producing_item" (column in the derived table) + cannot be cloned. +*/ +bool Item::pushable_cond_checker_for_derived_inner(uchar *arg) +{ + return !walk(&Item::check_non_pushable_processor, false, arg); +} + + /** @brief Extract condition that can be pushed into a derived table/view @@ -1486,6 +1505,10 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!extracted_cond_copy) continue; + extracted_cond_copy->check_pushable_cond( + &Item::pushable_cond_checker_for_derived_inner, (uchar*)sl); + extracted_cond_copy= extracted_cond_copy->build_pushable_cond(thd, NULL, 0); + /* Rename the columns of all non-first selects of a union to be compatible by names with the columns of the first select. It will allow to use copies

1 0

[Commits] 7f24e37: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 7f24e37fbecd5af08ec473a7a1449f305977c6d7 (mariadb-10.4.11-659-g7f24e37) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 12:23:13 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY Cherry-picking only test case. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ 2 files changed, 38 insertions(+) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..f761a9f 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +3 +7 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests

1 0

[Commits] 883d9a6: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 883d9a66ccdef5b32876d24377d327b61e1a12c0 (mariadb-10.4.11-659-g883d9a6) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 10:58:06 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..040f291 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index fc01dcd..8e6c8db 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1262,7 +1262,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] 6e94ef4: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 6e94ef41859cc628f92224e33dd661d9a4be215b (mariadb-10.3.26-183-g6e94ef4) parent(s): 9dc50ea2293640f8153bc806612fd72634498010 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-21 22:25:37 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index dd0cac7..65a1adc 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3478,3 +3478,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index e2e30f3..8f10013 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1163,7 +1163,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] d4a7781: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 21 Jun '21

21 Jun '21

revision-id: d4a7781e43b251a59a5bd2aa13132f8b8abdfd5d (mariadb-10.2.31-997-gd4a7781) parent(s): dfa2d0bc13362b949b1b1699955583f74e7db90a author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-21 16:15:07 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/r/derived_view.result | 21 +++++++++++++++++++++ mysql-test/t/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index fe25feb..d8ee508 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -3436,3 +3436,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 46d6a78..89ada40 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -2249,3 +2249,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3ab9384..632baf4 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1091,7 +1091,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_union()) + else if (unit->is_union() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] e8888cf9f8d: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by Sergei Petrunia 20 Jun '21

20 Jun '21

revision-id: e8888cf9f8de304e1d7e6253cff8a3def2f4d379 (mariadb-10.2.31-962-ge8888cf9f8d) parent(s): f3dd96ad25efe23081981f52a54a57b17a5a890e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-21 01:06:00 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Consider a query in form: select ... from (select item2 as COL1) as T where COL1=123 Condition pushdown into derived table will try to push "COL1=123" condition down into table T. The process of pushdown involves "substituting" the item, that is, replacing Item_field("T.COL1") with its "producing item" item2. In order to use item2, one needs to clone it (call Item::build_clone). If the item is not cloneable (e.g. SP function call is not), the pushdown process will fail and nothing at all will be pushed. This patch makes pushdown_cond_for_derived() to first extract the portion of a condition which can be pushed, and then push only that. Included changes: - TABLE_LIST::check_pushable_cond_for_table() can now accept the criteria that item must meet in order to be pushable. It is used in two places: first, extract the condition that only depends on a given table, and then, extract the condition that's pushable into a given SELECT. - TABLE_LIST::build_pushable_cond_for_table() works the same as before but it is no longer a member of TABLE_LIST for the sake of symmetry with check_pushable_cond_for_table. --- mysql-test/r/derived_cond_pushdown.result | 45 +++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 48 +++++++++++++++++++++++++++++++ sql/item.cc | 24 ++++++++++++++++ sql/item.h | 8 ++++++ sql/item_cmpfunc.h | 3 ++ sql/item_func.h | 1 + sql/sql_derived.cc | 15 ++++++++-- sql/table.cc | 37 +++++++++++++----------- sql/table.h | 7 +++-- 9 files changed, 167 insertions(+), 21 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 25237aa11a9..bae971ced74 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10634,4 +10634,49 @@ m 7 drop view v1; drop table t1; +# +# MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +# +create function f1(a int) returns int DETERMINISTIC return (a+1); +create table t1 ( +pk int primary key, +a int, +b int, +key(a) +); +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; +insert into t1 select a,a,a from t3; +create view v1 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v2 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; +explain select * from v3 where col1=123; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +# This must use ref accesses for reading table t1, not full scans: +explain select * from v3 where col1=123 and col2=321; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 31b49047bf1..374b61f846a 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2212,4 +2212,52 @@ select * from v1 where m > 0; drop view v1; drop table t1; +--echo # +--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +--echo # +create function f1(a int) returns int DETERMINISTIC return (a+1); + +create table t1 ( + pk int primary key, + a int, + b int, + key(a) +); + +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; + +insert into t1 select a,a,a from t3; + +create view v1 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; + +create view v2 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; + +explain select * from v3 where col1=123; + +--echo # This must use ref accesses for reading table t1, not full scans: +explain select * from v3 where col1=123 and col2=321; + +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; + + --echo # End of 10.2 tests diff --git a/sql/item.cc b/sql/item.cc index 42272fe0148..d729da23daa 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7249,6 +7249,30 @@ Item *find_producing_item(Item *item, st_select_lex *sel) return NULL; } + +/* + @brief Check if this item cannot be pushed down into derived table + + @detail + This function checks if derived_field_transformer_for_where() will + fail. It will fail if the "producing item" (column in the derived table) + cannot be cloned. + + @return + false - Ok, can be pushed + true - Cannot be pushed +*/ + +bool Item_field::check_non_pushable_processor(void *arg) +{ + st_select_lex *sel= (st_select_lex *)arg; + Item *producing_item= find_producing_item(this, sel); + if (producing_item) + return producing_item->walk(&Item::check_non_cloneable_processor, 0, 0); + return false; // Ok +} + + Item *Item_field::derived_field_transformer_for_where(THD *thd, uchar *arg) { st_select_lex *sel= (st_select_lex *)arg; diff --git a/sql/item.h b/sql/item.h index c94709c733e..f0753f7ded1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1829,6 +1829,12 @@ class Item: public Value_source, */ virtual bool check_valid_arguments_processor(void *arg) { return 0; } virtual bool update_vcol_processor(void *arg) { return 0; } + + /* Return true if the item can NOT be pushed down into a derived table */ + virtual bool check_non_pushable_processor(void *arg) { return 0; } + + /* Return true if the item cannot be cloned (get_copy() will return NULL) */ + virtual bool check_non_cloneable_processor(void *arg) { return 0; } /*============== End of Item processor list ======================*/ virtual Item *get_copy(THD *thd, MEM_ROOT *mem_root)=0; @@ -2894,6 +2900,8 @@ class Item_field :public Item_ident, friend class Item_default_value; friend class Item_insert_value; friend class st_select_lex_unit; + + bool check_non_pushable_processor(void *arg) override; }; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 26469d88929..ce27989bb16 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2135,6 +2135,7 @@ class Item_func_regex :public Item_bool_func const char *func_name() const { return "regexp"; } enum precedence precedence() const { return IN_PRECEDENCE; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) { return true; } void print(String *str, enum_query_type query_type) { print_op(str, query_type); @@ -2163,6 +2164,7 @@ class Item_func_regexp_instr :public Item_int_func bool fix_length_and_dec(); const char *func_name() const { return "regexp_instr"; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) { return true; } }; @@ -2421,6 +2423,7 @@ class Item_equal: public Item_bool_func void set_context_field(Item_field *ctx_field) { context_field= ctx_field; } void set_link_equal_fields(bool flag) { link_equal_fields= flag; } Item* get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) override { return true; } /* This does not comply with the specification of the virtual method, but Item_equal items are processed distinguishly anyway diff --git a/sql/item_func.h b/sql/item_func.h index 496109b0e24..5dfcdb956cf 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -2480,6 +2480,7 @@ class Item_func_sp :public Item_func return TRUE; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) override { return true; } bool eval_not_null_tables(void *opt_arg) { not_null_tables_cache= 0; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3ab93840d80..468e7c326aa 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1261,8 +1261,12 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) This condition has to be fixed yet. */ Item *extracted_cond; - derived->check_pushable_cond_for_table(cond); - extracted_cond= derived->build_pushable_cond_for_table(thd, cond); + table_map derived_tbl_bit= derived->table->map; + check_pushable_cond_for_table(cond, + [=](Item *cond) { + return cond->excl_dep_on_table(derived_tbl_bit); + }); + extracted_cond= build_pushable_cond_for_table(thd, derived_tbl_bit, cond); if (!extracted_cond) { /* Nothing can be pushed into the derived table */ @@ -1287,6 +1291,13 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!sl->join->group_list && !sl->with_sum_func) { /* extracted_cond_copy is pushed into where of sl */ + check_pushable_cond_for_table(extracted_cond_copy, + [=](Item *cond) { + return !cond->walk(&Item::check_non_pushable_processor, + false, (void*)sl); + }); + extracted_cond_copy= build_pushable_cond_for_table(thd, derived_tbl_bit, + extracted_cond_copy); extracted_cond_copy= extracted_cond_copy->transform(thd, &Item::derived_field_transformer_for_where, (uchar*) sl); diff --git a/sql/table.cc b/sql/table.cc index 1004f583448..c2206b55184 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8438,19 +8438,19 @@ double KEY::actual_rec_per_key(uint i) @param cond The condition whose subformulas are to be marked @details - This method recursively traverses the AND-OR condition cond and for each subformula - of the codition it checks whether it can be usable for the extraction of a condition + Recursively traverse the AND-OR condition cond and for each subformula + of the codition check whether it can be usable for the extraction of a condition that can be pushed into this table. The subformulas that are not usable are marked with the flag NO_EXTRACTION_FL. @note - This method is called before any call of TABLE_LIST::build_pushable_cond_for_table. + This function is called before any call of build_pushable_cond_for_table(). The flag NO_EXTRACTION_FL set in a subformula allows to avoid building clone for the subformula when extracting the pushable condition. */ -void TABLE_LIST::check_pushable_cond_for_table(Item *cond) +void check_pushable_cond_for_table(Item *cond, + std::function<bool(Item*)> pushable_filter) { - table_map tab_map= table->map; cond->clear_extraction_flag(); if (cond->type() == Item::COND_ITEM) { @@ -8460,7 +8460,7 @@ void TABLE_LIST::check_pushable_cond_for_table(Item *cond) Item *item; while ((item=li++)) { - check_pushable_cond_for_table(item); + check_pushable_cond_for_table(item, pushable_filter); if (item->get_extraction_flag() != NO_EXTRACTION_FL) count++; else if (!and_cond) @@ -8475,25 +8475,29 @@ void TABLE_LIST::check_pushable_cond_for_table(Item *cond) item->clear_extraction_flag(); } } - else if (!cond->excl_dep_on_table(tab_map)) - cond->set_extraction_flag(NO_EXTRACTION_FL); + else + { + if (!pushable_filter(cond)) + cond->set_extraction_flag(NO_EXTRACTION_FL); + } } /** @brief - Build condition extractable from the given one depended only on this table + Build condition extractable from the given one depended only on the table - @param thd The thread handle - @param cond The condition from which the pushable one is to be extracted - + @param thd The thread handle + @param tab_map Table the condition must depend on. + @param cond The condition from which the pushable one is to be extracted + @details For the given condition cond this method finds out what condition depended only on this table can be extracted from cond. If such condition C exists the method builds the item for it. The method uses the flag NO_EXTRACTION_FL set by the preliminary call of - the method TABLE_LIST::check_pushable_cond_for_table to figure out whether - a subformula depends only on this table or not. + check_pushable_cond_for_table() to figure out whether a subformula depends + only on this table or not. @note The built condition C is always implied by the condition cond (cond => C). The method tries to build the most restictive such @@ -8508,9 +8512,8 @@ void TABLE_LIST::check_pushable_cond_for_table(Item *cond) NULL if there is no such a condition */ -Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond) +Item* build_pushable_cond_for_table(THD *thd, table_map tab_map, Item *cond) { - table_map tab_map= table->map; bool is_multiple_equality= cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC; if (cond->get_extraction_flag() == NO_EXTRACTION_FL) @@ -8539,7 +8542,7 @@ Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond) return 0; continue; } - Item *fix= build_pushable_cond_for_table(thd, item); + Item *fix= build_pushable_cond_for_table(thd, tab_map, item); if (!fix && !cond_and) return 0; if (!fix) diff --git a/sql/table.h b/sql/table.h index 83c72f76831..ff80bda5b6e 100644 --- a/sql/table.h +++ b/sql/table.h @@ -16,6 +16,7 @@ along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ +#include <functional> #include "my_global.h" /* NO_EMBEDDED_ACCESS_CHECKS */ #include "sql_plist.h" #include "sql_list.h" /* Sql_alloc */ @@ -2534,8 +2535,6 @@ struct TABLE_LIST return false; } void set_lock_type(THD* thd, enum thr_lock_type lock); - void check_pushable_cond_for_table(Item *cond); - Item *build_pushable_cond_for_table(THD *thd, Item *cond); void remove_join_columns() { @@ -2562,6 +2561,10 @@ struct TABLE_LIST ulong m_table_ref_version; }; +Item *build_pushable_cond_for_table(THD *thd, table_map tab_map, Item *cond); +void check_pushable_cond_for_table(Item *cond, + std::function<bool(Item*)> pushable_filter); + class Item; /*

1 0