Merge branch '10.5' into 10.6

1 files changed, 242 insertions, 0 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index d738ed33128..39e82210e4c 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3629,6 +3629,248 @@ 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 #
+--echo # MDEV-27510: Splittable derived with grouping over two tables
+--echo #
+
+CREATE TABLE ledgers (
+  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  name VARCHAR(32)
+) ENGINE=MyISAM;
+
+CREATE TABLE charges (
+  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  from_ledger_id BIGINT UNSIGNED NOT NULL,
+  to_ledger_id BIGINT UNSIGNED NOT NULL,
+  amount INT NOT NULL,
+  KEY fk_charge_from_ledger (from_ledger_id),
+  KEY fk_charge_to_ledger (to_ledger_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE transactions (
+  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  ledger_id BIGINT UNSIGNED NOT NULL,
+  KEY fk_transactions_ledger (ledger_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE transaction_items (
+  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  transaction_id BIGINT UNSIGNED NOT NULL,
+  charge_id BIGINT UNSIGNED,
+  amount INT NOT NULL,
+  KEY fk_items_transaction (transaction_id),
+  KEY fk_items_charge (charge_id)
+) ENGINE=MyISAM;
+
+INSERT INTO ledgers (id, name) VALUES
+(1, 'Anna'), (2, 'John'), (3, 'Fred');
+
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(1, 2, 1, 200), (2, 1, 2, 330), (3, 1, 2, 640), (4, 3, 1, 640), (5, 3, 2, 1000),
+(6, 3, 1, 660), (7, 2, 3, 650), (8, 3, 2, 160), (9, 2, 1, 740), (10, 3, 2, 310),
+(11, 2, 1, 640), (12, 3, 2, 240), (13, 3, 2, 340), (14, 2, 1, 720),
+(15, 2, 3, 100),
+(16, 2, 3, 980), (17, 2, 1, 80), (18, 1, 2, 760), (19, 2, 3, 740),
+(20, 2, 1, 990);
+
+INSERT INTO transactions (id, ledger_id) VALUES
+(2, 1), (3, 1), (5, 1), (8, 1), (12, 1), (18, 1), (22, 1), (28, 1),
+(34, 1), (35, 1),
+(40, 1), (1, 2), (4, 2), (6, 2), (10, 2), (13, 2), (16, 2), (17, 2),
+(20, 2), (21, 2),
+(24, 2), (26, 2), (27, 2), (29, 2), (31, 2), (33, 2), (36, 2), (37, 2),
+(39, 2), (7, 3),
+(9, 3), (11, 3), (14, 3), (15, 3), (19, 3), (23, 3), (25, 3), (30, 3),
+(32, 3), (38, 3);
+
+INSERT INTO transaction_items (id, transaction_id, charge_id, amount) VALUES
+(1, 1, 1, -200), (2, 2, 1, 200), (3, 3, 2, -330), (4, 4, 2, 330),
+(5, 5, 3, -640),
+(6, 6, 3, 640), (7, 7, 4, -640), (8, 8, 4, 640), (9, 9, 5, -1000),
+(10, 10, 5, 1000),
+(11, 11, 6, -660), (12, 12, 6, 660), (13, 13, 7, -650), (14, 14, 7, 650),
+(15, 15, 8, -160),
+(16, 16, 8, 160), (17, 17, 9, -740), (18, 18, 9, 740), (19, 19, 10, -310),
+(20, 20, 10, 310),
+(21, 21, 11, -640), (22, 22, 11, 640), (23, 23, 12, -240), (24, 24, 12, 240),
+(25, 25, 13, -340),
+(26, 26, 13, 340), (27, 27, 14, -720), (28, 28, 14, 720), (29, 29, 15, -100),
+(30, 30, 15, 100),
+(31, 31, 16, -980), (32, 32, 16, 980), (33, 33, 17, -80), (34, 34, 17, 80),
+(35, 35, 18, -760),
+(36, 36, 18, 760), (37, 37, 19, -740), (38, 38, 19, 740), (39, 39, 20, -990),
+(40, 40, 20, 990);
+
+ANALYZE TABLE ledgers, charges, transactions, transaction_items;
+
+let $q=
+SELECT
+    charges.id,
+    charges.from_ledger_id,
+    charges.to_ledger_id,
+    from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+    SELECT
+        transactions.ledger_id,
+        transaction_items.charge_id,
+        count(*) as num_rows
+    FROM transaction_items
+    INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+    GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+   from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+
+set optimizer_switch='split_materialized=on';
+eval $q;
+eval EXPLAIN $q;
+eval EXPLAIN FORMAT=JSON $q;
+
+set optimizer_switch='split_materialized=off';
+eval $q;
+eval EXPLAIN $q;
+
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(101, 4, 2, 100), (102, 7, 2, 200);
+
+let $q1=
+SELECT
+    charges.id,
+    charges.from_ledger_id,
+    charges.to_ledger_id,
+    from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+    SELECT
+        transactions.ledger_id,
+        transaction_items.charge_id,
+        count(*) as num_rows
+    FROM transaction_items
+    INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+    GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+   from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+
+set optimizer_switch='split_materialized=on';
+eval $q1;
+eval EXPLAIN $q1;
+eval EXPLAIN FORMAT=JSON $q1;
+
+set optimizer_switch='split_materialized=off';
+eval $q1;
+eval EXPLAIN $q1;
+
+set optimizer_switch='split_materialized=default';
+
+DROP TABLE transaction_items;
+DROP TABLE transactions;
+DROP TABLE charges;
+DROP TABLE ledgers;
+
 --echo # End of 10.3 tests
 
 --echo #