diff options
author | Igor Babaev <igor@askmonty.org> | 2022-01-13 22:46:59 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2022-01-17 23:04:39 -0800 |
commit | 97425f740faf83ac2d16585084476470f3f898ce (patch) | |
tree | 84f1dc280a58c283988bd2bffb6714813331a9b0 /mysql-test/main/derived_cond_pushdown.test | |
parent | 7105c810bd61dec2e5190e60104db8fee8899280 (diff) | |
download | mariadb-git-97425f740faf83ac2d16585084476470f3f898ce.tar.gz |
MDEV-27132 Wrong result from query when using split optimization
This bug could affect queries with a grouping derived table containing
equalities in the where clause of its specification if the optimizer
chose to apply split optimization to access the derived table. In such
cases wrong results could be returned from the queries.
When the optimizer considers a possibility of using split optimization
to a derived table it injects equalities joining the derived table with
other tables into the where condition of the derived table. After the join
order for the execution using split optimization has been chosen as the
cheapest the injected equalities that are not used to access the derived
table are removed from the where condition of the derived table.
For this removal the optimizer looks through the conjuncts of the where
condition of the derived table, fetches the equalities and checks whether
they belong to the list of injected equalities.
As the injection of the list was performed just by the insertion of it
into the list of top level AND condition of the where condition some extra
conjuncts from the where condition could be automatically attached to the
end of the list of injected equalities. If such attached conjunct happened
to be an equality predicate it was removed from the where condition of the
derived table and thus lost for checking at the execution phase.
The bug has been fixed by injecting of a shallow copy of the list of the
pushed equalities rather than the list itself leaving the latter intact.
Approved by Oleksandr Byelkin <sanja@mariadb.com>
Diffstat (limited to 'mysql-test/main/derived_cond_pushdown.test')
-rw-r--r-- | mysql-test/main/derived_cond_pushdown.test | 100 |
1 files changed, 100 insertions, 0 deletions
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 9bb9d9b9bcc..4f4ffc9e6d4 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3611,4 +3611,104 @@ deallocate prepare stmt; drop table t1,t2,t3; +--echo # +--echo # MDEV-MDEV-27132: Splittable derived with equality in WHERE +--echo # + +CREATE TABLE t1 ( + id int PRIMARY KEY +) ENGINE=MyISAM; + +INSERT INTO t1 VALUES +(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834), +(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844), +(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854), +(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864), +(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874), +(4875),(4876); + +CREATE TABLE t2 ( + id int PRIMARY KEY AUTO_INCREMENT, + deleted int(1), + t1_id int, + email varchar(255), + reporting_person int(1), + KEY t1_id (t1_id) +) ENGINE=MyISAM; + +INSERT INTO t2 VALUES +(1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1), +(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1), +(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0), +(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0), +(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1), +(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0), +(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0), +(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1); + +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+10000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+20000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+40000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+80000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+160000, email, reporting_person FROM t2; + +CREATE TABLE t3 ( + id int PRIMARY KEY, + deleted int, + t1_id int, + YEAR int(4), + quarter int(1), + KEY t1_id (t1_id,year,quarter) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1), +(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1), +(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2), +(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2), +(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3), +(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3), +(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3), +(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3), +(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3), +(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3), +(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3), +(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3), +(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3), +(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3), +(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3), +(350,0,2304,2020,3),(351,0,3896,2020,3); + +ANALYZE TABLE t1,t2,t3; + +let $q= +SELECT t1.id +FROM t1 + JOIN t3 + ON t3.t1_id = t1.id + JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx + ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; + +set optimizer_switch='split_materialized=on'; + +eval $q; +eval EXPLAIN $q; +eval EXPLAIN FORMAT=JSON $q; + +set optimizer_switch='split_materialized=off'; + +eval $q; + +set optimizer_switch='split_materialized=default'; + +DROP TABLE t1,t2,t3; + --echo # End of 10.3 tests |