|
The following test case demonstrates the problem in 10.3
create table t1 (id int not null, itemid int not null, index idx (itemid) ) engine=innodb;
|
insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3);
|
create table t2 (id int not null) engine=innodb;
|
insert into t2 values (2);
|
create table t3 (
|
id int not null, itemid int not null, userid int not null, primary key (id),
|
index idx1 (userid, itemid), index idx2 (itemid)
|
) engine innodb;
|
insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1);
|
analyze table t1,t2,t3;
|
set optimizer_switch='split_materialized=on';
|
explain
|
select t1.id, t1.itemid, dt.id, t2.id
|
from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
select t1.id, t1.itemid, dt.id, t2.id
|
from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
set optimizer_switch='split_materialized=off';
|
explain
|
select t1.id, t1.itemid, dt.id, t2.id
|
from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
select t1.id, t1.itemid, dt.id, t2.id
|
from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
For this test case we have either an empty result set with optimizer_switch='split_materialized=on' and non-empty result set with optimizer_switch='split_materialized=off'.
MariaDB [test]> set optimizer_switch='split_materialized=on';
|
Query OK, 0 rows affected
|
|
MariaDB [test]> explain
|
-> select t1.id, t1.itemid, dt.id, t2.id
|
-> from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
-> where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
+------+-----------------+------------+------+---------------+------+---------+-----------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+-----------------------+------+-------------+
|
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
|
| 1 | PRIMARY | t1 | ref | idx | idx | 4 | test.t2.id | 1 | |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 9 | test.t2.id,test.t1.id | 2 | |
|
| 2 | LATERAL DERIVED | t3 | ref | idx1,idx2 | idx1 | 4 | const | 5 | Using index |
|
+------+-----------------+------------+------+---------------+------+---------+-----------------------+------+-------------+
|
4 rows in set
|
|
MariaDB [test]> select t1.id, t1.itemid, dt.id, t2.id
|
-> from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
-> where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
Empty set (5.988 sec)
|
|
MariaDB [test]> set optimizer_switch='split_materialized=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> explain
|
-> select t1.id, t1.itemid, dt.id, t2.id
|
-> from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
-> where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
+------+-------------+------------+------+---------------+------+---------+-----------------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+-----------------------+------+--------------------------+
|
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
|
| 1 | PRIMARY | t1 | ref | idx | idx | 4 | test.t2.id | 1 | |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 9 | test.t2.id,test.t1.id | 2 | |
|
| 2 | DERIVED | t3 | ref | idx1 | idx1 | 4 | const | 5 | Using where; Using index |
|
+------+-------------+------------+------+---------------+------+---------+-----------------------+------+--------------------------+
|
4 rows in set
|
|
MariaDB [test]> select t1.id, t1.itemid, dt.id, t2.id
|
-> from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, t2
|
-> where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
|
+----+--------+------+----+
|
| id | itemid | id | id |
|
+----+--------+------+----+
|
| 4 | 2 | 4 | 2 |
|
| 4 | 2 | 4 | 2 |
|
+----+--------+------+----+
|
2 rows in set
|
|
|
@Igor Babaev
On version 10.5.13, the test script below reliably exhibits the bug. I run the setup script to create the tables and populate them with some dummy data. The query below counts the number of transaction_item rows for each (ledger_id, charge_id) pair. I've been careful in my test data to ensure that there is only ever 1 transaction_item row for each (ledger_id, charge_id) pair. Usually when I first run the query, I get the correct results (this is obvious because we see from_num_rows=1 on every row in the results set). After a short time (less than 1 minute for me), I start getting the incorrect results, and we see from_num_rows=2 on every row in the results set. I've captured below the output from EXPLAIN FORMAT=JSON for the same query, before and after it starts failing.
Note that that the number of rows of dummy data I have seems to be significant. The more rows I have, the faster the query starts giving incorrect results.
Setup:
DROP TABLE IF EXISTS transaction_items;
|
DROP TABLE IF EXISTS transactions;
|
DROP TABLE IF EXISTS charges;
|
DROP TABLE IF EXISTS ledgers;
|
CREATE TABLE ledgers (
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
name VARCHAR(32)
|
);
|
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,
|
CONSTRAINT fk_charge_from_ledger FOREIGN KEY (from_ledger_id) REFERENCES ledgers (id) ON DELETE CASCADE ON UPDATE RESTRICT,
|
CONSTRAINT fk_charge_to_ledger FOREIGN KEY (to_ledger_id) REFERENCES ledgers (id) ON DELETE CASCADE ON UPDATE RESTRICT
|
);
|
CREATE TABLE transactions (
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
ledger_id BIGINT UNSIGNED NOT NULL,
|
CONSTRAINT fk_transactions_ledger FOREIGN KEY (ledger_id) REFERENCES ledgers (id) ON DELETE CASCADE ON UPDATE RESTRICT
|
);
|
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,
|
CONSTRAINT fk_items_transaction FOREIGN KEY (transaction_id) REFERENCES transactions (id) ON DELETE CASCADE ON UPDATE RESTRICT,
|
CONSTRAINT fk_items_charge FOREIGN KEY (charge_id) REFERENCES charges (id) ON DELETE CASCADE ON UPDATE RESTRICT
|
);
|
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);
|
INSERT INTO `charges` (`id`, `from_ledger_id`, `to_ledger_id`, `amount`) VALUES (6, 3, 1, 660), (7, 2, 3, 650), (8, 3, 2, 160), (9, 2, 1, 740), (10, 3, 2, 310);
|
INSERT INTO `charges` (`id`, `from_ledger_id`, `to_ledger_id`, `amount`) VALUES (11, 2, 1, 640), (12, 3, 2, 240), (13, 3, 2, 340), (14, 2, 1, 720), (15, 2, 3, 100);
|
INSERT INTO `charges` (`id`, `from_ledger_id`, `to_ledger_id`, `amount`) VALUES (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);
|
INSERT INTO `transactions` (`id`, `ledger_id`) VALUES (40, 1), (1, 2), (4, 2), (6, 2), (10, 2), (13, 2), (16, 2), (17, 2), (20, 2), (21, 2);
|
INSERT INTO `transactions` (`id`, `ledger_id`) VALUES (24, 2), (26, 2), (27, 2), (29, 2), (31, 2), (33, 2), (36, 2), (37, 2), (39, 2), (7, 3);
|
INSERT INTO `transactions` (`id`, `ledger_id`) VALUES (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);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (6, 6, 3, 640), (7, 7, 4, -640), (8, 8, 4, 640), (9, 9, 5, -1000), (10, 10, 5, 1000);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (11, 11, 6, -660), (12, 12, 6, 660), (13, 13, 7, -650), (14, 14, 7, 650), (15, 15, 8, -160);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (16, 16, 8, 160), (17, 17, 9, -740), (18, 18, 9, 740), (19, 19, 10, -310), (20, 20, 10, 310);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (21, 21, 11, -640), (22, 22, 11, 640), (23, 23, 12, -240), (24, 24, 12, 240), (25, 25, 13, -340);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (26, 26, 13, 340), (27, 27, 14, -720), (28, 28, 14, 720), (29, 29, 15, -100), (30, 30, 15, 100);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (31, 31, 16, -980), (32, 32, 16, 980), (33, 33, 17, -80), (34, 34, 17, 80), (35, 35, 18, -760);
|
INSERT INTO `transaction_items` (`id`, `transaction_id`, `charge_id`, `amount`) VALUES (36, 36, 18, 760), (37, 37, 19, -740), (38, 38, 19, 740), (39, 39, 20, -990), (40, 40, 20, 990);
|
The query:
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 result when the query is returning correct results:
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "charges",
|
"access_type": "ALL",
|
"possible_keys": ["fk_charge_to_ledger"],
|
"rows": 20,
|
"filtered": 40,
|
"attached_condition": "charges.to_ledger_id = 2"
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "18",
|
"used_key_parts": ["ledger_id", "charge_id"],
|
"ref": ["bugtest.charges.from_ledger_id", "bugtest.charges.id"],
|
"rows": 4,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"filesort": {
|
"sort_key": "transactions.ledger_id, transaction_items.charge_id",
|
"temporary_table": {
|
"table": {
|
"table_name": "transaction_items",
|
"access_type": "ALL",
|
"possible_keys": ["fk_items_transaction", "fk_items_charge"],
|
"rows": 40,
|
"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": ["bugtest.transaction_items.transaction_id"],
|
"rows": 1,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
After a short time (less than 1 minute usually), the query will start returning the wrong results.
EXPLAIN result when the query is returning incorrect results:
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "charges",
|
"access_type": "ALL",
|
"possible_keys": ["fk_charge_to_ledger"],
|
"rows": 20,
|
"filtered": 35,
|
"attached_condition": "charges.to_ledger_id = 2"
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "18",
|
"used_key_parts": ["ledger_id", "charge_id"],
|
"ref": ["bugtest.charges.from_ledger_id", "bugtest.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": ["bugtest.charges.id"],
|
"rows": 1,
|
"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": ["bugtest.transaction_items.transaction_id"],
|
"rows": 1,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
|
|
demo_sql_error_simplified.sql
