MDEV-27510 Query returns wrong result when using split optimization

This bug may affect the queries that uses a grouping derived table with
grouping list containing references to columns from different tables if
the optimizer decides to employ the split optimization for the derived
table. In some very specific cases it may affect queries with a grouping
derived table that refers only one base table.
This bug was caused by an improper fix for the bug MDEV-25128. The fix
tried to get rid of the equality conditions pushed into the where clause
of the grouping derived table T to which the split optimization had been
applied. The fix erroneously assumed that only those pushed equalities
that were used for ref access of the tables referenced by T were needed.
In fact the function remove_const() that figures out what columns from the
group list can be removed if the split optimization is applied can uses
other pushed equalities as well.
This patch actually provides a proper fix for MDEV-25128. Rather than
trying to remove invalid pushed equalities referencing the fields of SJM
tables with a look-up access the patch attempts not to push such equalities.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

1 files changed, 437 insertions, 2 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 76313486839..e5cf14f64b5 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -17439,7 +17439,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t3.id	2	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
-2	LATERAL DERIVED	cp2	ref	a	a	5	test.t1.a	1	Using index
+2	LATERAL DERIVED	cp2	ref	a	a	5	test.t1.a	1	Using where; Using index
 explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3
 where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2);
 EXPLAIN
@@ -17512,6 +17512,7 @@ EXPLAIN
             "ref": ["test.t1.a"],
             "rows": 1,
             "filtered": 100,
+            "attached_condition": "cp2.a = t3.`id`",
             "using_index": true
           }
         }
@@ -17683,7 +17684,7 @@ EXPLAIN
             "ref": ["test.t1.id"],
             "rows": 3,
             "filtered": 100,
-            "index_condition": "t2.t1_id between 200 and 100000",
+            "index_condition": "t2.t1_id between 200 and 100000 and t2.t1_id = t3.t1_id",
             "attached_condition": "t2.reporting_person = 1"
           }
         }
@@ -17702,4 +17703,438 @@ WHERE t1.id BETWEEN 200 AND 100000;
 id
 set  optimizer_switch='split_materialized=default';
 DROP TABLE t1,t2,t3;
+#
+# MDEV-27510: Splittable derived with grouping over two tables
+#
+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;
+Table	Op	Msg_type	Msg_text
+test.ledgers	analyze	status	OK
+test.charges	analyze	status	OK
+test.transactions	analyze	status	OK
+test.transaction_items	analyze	status	OK
+set optimizer_switch='split_materialized=on';
+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;
+id	from_ledger_id	to_ledger_id	from_num_rows
+2	1	2	1
+3	1	2	1
+5	3	2	1
+8	3	2	1
+10	3	2	1
+12	3	2	1
+13	3	2	1
+18	1	2	1
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	charges	ref	PRIMARY,fk_charge_from_ledger,fk_charge_to_ledger	fk_charge_to_ledger	8	const	7	
+1	PRIMARY	<derived2>	ref	key0	key0	17	test.charges.from_ledger_id,test.charges.id	2	
+2	LATERAL DERIVED	transaction_items	ref	fk_items_transaction,fk_items_charge	fk_items_charge	9	test.charges.id	2	
+2	LATERAL DERIVED	transactions	eq_ref	PRIMARY,fk_transactions_ledger	PRIMARY	8	test.transaction_items.transaction_id	1	Using where
+EXPLAIN FORMAT=JSON 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;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "charges",
+      "access_type": "ref",
+      "possible_keys": ["PRIMARY", "fk_charge_from_ledger", "fk_charge_to_ledger"],
+      "key": "fk_charge_to_ledger",
+      "key_length": "8",
+      "used_key_parts": ["to_ledger_id"],
+      "ref": ["const"],
+      "rows": 7,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "17",
+      "used_key_parts": ["ledger_id", "charge_id"],
+      "ref": ["test.charges.from_ledger_id", "test.charges.id"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "lateral": 1,
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "transaction_items",
+            "access_type": "ref",
+            "possible_keys": ["fk_items_transaction", "fk_items_charge"],
+            "key": "fk_items_charge",
+            "key_length": "9",
+            "used_key_parts": ["charge_id"],
+            "ref": ["test.charges.id"],
+            "rows": 2,
+            "filtered": 100
+          },
+          "table": {
+            "table_name": "transactions",
+            "access_type": "eq_ref",
+            "possible_keys": ["PRIMARY", "fk_transactions_ledger"],
+            "key": "PRIMARY",
+            "key_length": "8",
+            "used_key_parts": ["id"],
+            "ref": ["test.transaction_items.transaction_id"],
+            "rows": 1,
+            "filtered": 100,
+            "attached_condition": "transactions.ledger_id = charges.from_ledger_id"
+          }
+        }
+      }
+    }
+  }
+}
+set optimizer_switch='split_materialized=off';
+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;
+id	from_ledger_id	to_ledger_id	from_num_rows
+2	1	2	1
+3	1	2	1
+5	3	2	1
+8	3	2	1
+10	3	2	1
+12	3	2	1
+13	3	2	1
+18	1	2	1
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	charges	ref	PRIMARY,fk_charge_from_ledger,fk_charge_to_ledger	fk_charge_to_ledger	8	const	7	
+1	PRIMARY	<derived2>	ref	key0	key0	17	test.charges.from_ledger_id,test.charges.id	4	
+2	DERIVED	transaction_items	ALL	fk_items_transaction	NULL	NULL	NULL	40	Using temporary; Using filesort
+2	DERIVED	transactions	eq_ref	PRIMARY	PRIMARY	8	test.transaction_items.transaction_id	1	
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(101, 4, 2, 100), (102, 7, 2, 200);
+set optimizer_switch='split_materialized=on';
+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;
+id	from_ledger_id	to_ledger_id	from_num_rows
+2	1	2	1
+3	1	2	1
+5	3	2	1
+8	3	2	1
+10	3	2	1
+12	3	2	1
+13	3	2	1
+18	1	2	1
+101	4	2	NULL
+102	7	2	NULL
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	charges	ref	fk_charge_to_ledger	fk_charge_to_ledger	8	const	9	
+1	PRIMARY	<derived2>	ref	key0	key0	18	test.charges.from_ledger_id,test.charges.id	2	
+2	LATERAL DERIVED	transaction_items	ref	fk_items_transaction,fk_items_charge	fk_items_charge	9	test.charges.id	2	
+2	LATERAL DERIVED	transactions	eq_ref	PRIMARY,fk_transactions_ledger	PRIMARY	8	test.transaction_items.transaction_id	1	Using where
+EXPLAIN FORMAT=JSON 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;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "charges",
+      "access_type": "ref",
+      "possible_keys": ["fk_charge_to_ledger"],
+      "key": "fk_charge_to_ledger",
+      "key_length": "8",
+      "used_key_parts": ["to_ledger_id"],
+      "ref": ["const"],
+      "rows": 9,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "18",
+      "used_key_parts": ["ledger_id", "charge_id"],
+      "ref": ["test.charges.from_ledger_id", "test.charges.id"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "lateral": 1,
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "transaction_items",
+            "access_type": "ref",
+            "possible_keys": ["fk_items_transaction", "fk_items_charge"],
+            "key": "fk_items_charge",
+            "key_length": "9",
+            "used_key_parts": ["charge_id"],
+            "ref": ["test.charges.id"],
+            "rows": 2,
+            "filtered": 100
+          },
+          "table": {
+            "table_name": "transactions",
+            "access_type": "eq_ref",
+            "possible_keys": ["PRIMARY", "fk_transactions_ledger"],
+            "key": "PRIMARY",
+            "key_length": "8",
+            "used_key_parts": ["id"],
+            "ref": ["test.transaction_items.transaction_id"],
+            "rows": 1,
+            "filtered": 100,
+            "attached_condition": "transactions.ledger_id = charges.from_ledger_id"
+          }
+        }
+      }
+    }
+  }
+}
+set optimizer_switch='split_materialized=off';
+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;
+id	from_ledger_id	to_ledger_id	from_num_rows
+2	1	2	1
+3	1	2	1
+5	3	2	1
+8	3	2	1
+10	3	2	1
+12	3	2	1
+13	3	2	1
+18	1	2	1
+101	4	2	NULL
+102	7	2	NULL
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	charges	ref	fk_charge_to_ledger	fk_charge_to_ledger	8	const	9	
+1	PRIMARY	<derived2>	ref	key0	key0	18	test.charges.from_ledger_id,test.charges.id	4	
+2	DERIVED	transaction_items	ALL	fk_items_transaction	NULL	NULL	NULL	40	Using temporary; Using filesort
+2	DERIVED	transactions	eq_ref	PRIMARY	PRIMARY	8	test.transaction_items.transaction_id	1	
+set optimizer_switch='split_materialized=default';
+DROP TABLE transaction_items;
+DROP TABLE transactions;
+DROP TABLE charges;
+DROP TABLE ledgers;
 # End of 10.3 tests