revision-id: db2eeafc20de584d430ce9c0903478aeb8782d66 (mariadb-10.3.35-375-gdb2eeaf) parent(s): 074bef4dcaad15dba40013e9d2ddf0011b7744a1 author: Igor Babaev committer: Igor Babaev timestamp: 2023-01-23 15:54:49 -0800 message: MDEV-30081 Crash with splitting from constant mergeable derived table This bug manifested itself in very rare situations when splitting optimization was applied to a materialized derived table with group clause by key over a constant meargeable derived table that was in inner part of an outer join. In this case the used tables for the key to access the split table incorrectly was evaluated to a not empty table map. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 58 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 49 +++++++++++++++++++++++ sql/item.cc | 2 +- 3 files changed, 108 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index ebe0c229..88f20bc 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -18131,4 +18131,62 @@ DROP TABLE transaction_items; DROP TABLE transactions; DROP TABLE charges; DROP TABLE ledgers; +# +# MDEV-30081: Splitting from a constant mergeable derived table +# used in inner part of an outer join. +# +CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(4),(7); +CREATE TABLE t2 ( +id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,4,6),(7,7,7); +CREATE TABLE t3 ( +wid int, wtid int, otid int, oid int, +PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7); +CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); +CREATE TABLE t5 ( +id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) +) ENGINE=MyISAM ; +INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); +ANALYZE TABLE t1,t2,t3,t4,t5; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +test.t4 analyze status OK +test.t5 analyze status OK +CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1); +SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM +t1, t2, t3 +LEFT JOIN +(SELECT t4.* FROM t4 WHERE t4.a=3) dt +ON t3.oid = dt.id AND t3.otid = 14 +LEFT JOIN v1 +ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; +wid wtid otid oid t1_id t2_id id a id1 +7 17 7 7 7 7 NULL NULL NULL +EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM +t1, t2, t3 +LEFT JOIN +(SELECT t4.* FROM t4 WHERE t4.a=3) dt +ON t3.oid = dt.id AND t3.otid = 14 +LEFT JOIN v1 +ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 const PRIMARY,oid PRIMARY 4 const 1 +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t2 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t4 const PRIMARY,a NULL NULL NULL 1 Impossible ON condition +1 PRIMARY <derived3> ref key0 key0 5 const 0 Using where +3 LATERAL DERIVED t5 ref id1 id1 5 const 0 Using index +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4,t5; # End of 10.3 tests diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 619d104..e039d1f 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3853,4 +3853,53 @@ DROP TABLE transactions; DROP TABLE charges; DROP TABLE ledgers; + +--echo # +--echo # MDEV-30081: Splitting from a constant mergeable derived table +--echo # used in inner part of an outer join. +--echo # + + CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(4),(7); + +CREATE TABLE t2 ( + id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,4,6),(7,7,7); + +CREATE TABLE t3 ( + wid int, wtid int, otid int, oid int, + PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7); + +CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); + +CREATE TABLE t5 ( + id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) +) ENGINE=MyISAM ; +INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); + +ANALYZE TABLE t1,t2,t3,t4,t5; + +CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1); + +let $q= +SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM + t1, t2, t3 + LEFT JOIN + (SELECT t4.* FROM t4 WHERE t4.a=3) dt + ON t3.oid = dt.id AND t3.otid = 14 + LEFT JOIN v1 + ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; + +eval $q; +eval EXPLAIN $q; + +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4,t5; + --echo # End of 10.3 tests diff --git a/sql/item.cc b/sql/item.cc index 02220fd..9ecbefa 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10838,7 +10838,7 @@ table_map Item_direct_view_ref::used_tables() const table_map used= (*ref)->used_tables(); return (used ? used : - ((null_ref_table != NO_NULL_TABLE) ? + (null_ref_table != NO_NULL_TABLE && !null_ref_table->const_table ? null_ref_table->map : (table_map)0 )); }