[MDEV-25714] Join using derived with aggregation returns incorrect results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.5.10, 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.3.30, 10.4.20, 10.5.11
Component/s: Data Manipulation - Subquery
Labels:
- regression

Description

One of the unittests in Moodle LMS started failing on MariaDB since testing docker image was upgraded to version 10.5.10.

The same unittest and the same query was passing on MariaDB 10.5.9. Also it passes on MySQL, Postgres, MsSQL and Oracle (all databases supported by Moodle).

I have created an SQL file to demonstrate the problem. It creates two database tables, fills them with the data and performs a query:

SELECT h.id, gi.itemtype, gi.itemmodule, h.userid, h.rawgrade

FROM grade_grades_history h

         JOIN (SELECT itemid, MAX(id) AS id

               FROM grade_grades_history

               WHERE userid = 131000

               GROUP BY itemid) maxquery ON h.id = maxquery.id AND h.itemid = maxquery.itemid

         JOIN grade_items gi ON gi.id = h.itemid

WHERE gi.courseid = 128000;

This query is slightly simplified from what we actually use in Moodle in order to demonstrate the problem.

On MariaDB 10.5.9 and all other databases it returns:

id	itemtype	itemmodule	userid	rawgrade

330004	course	NULL	131000	NULL

330003	mod	assign	131000	50.00000

On MariaDB 10.5.10 it returns:

id	itemtype	itemmodule	userid	rawgrade

330004	course	NULL	131000	NULL

To make it even more interesting, the following query (using "LEFT JOIN") returns correct results. This is even more confusing because in the return values you can see that data in the grade_items table is present and it is actually an inner join.

SELECT h.id, gi.itemtype, gi.itemmodule, h.userid, h.rawgrade

FROM grade_grades_history h

         JOIN (SELECT itemid, MAX(id) AS id

               FROM grade_grades_history

               WHERE userid = 131000

               GROUP BY itemid) maxquery ON h.id = maxquery.id AND h.itemid = maxquery.itemid

         LEFT JOIN grade_items gi ON gi.id = h.itemid

Attaching the test file demo_sql_error_simplified.sql

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

demo_sql_error_simplified.sql
2021-05-18 10:51
6 kB
Marina Glancy

Issue Links

is caused by

MDEV-25128 Wrong result from join with materialized semi-join and splittable derived

Closed

is duplicated by

MDEV-25841 group by in subquery returns wrong result

Closed

MDEV-25843 Wrong Query Result with LATERAL DERIVED (split_materialized=on)

Closed

relates to

MDEV-27132 Wrong result from query when using split optimization

Closed

MDEV-27694 regression? Join using derived with aggregation returns incorrect results

Closed

MDEV-25725 Suddenly Queryplan skip LEFT JOINS and fail to retrieve full results on certain dataset

Closed

(1 relates to)

Activity

Ascending order - Click to sort in descending order

View 26 older comments

Daniel Howard added a comment - 2022-01-12 12:48

I have encountered this issue again in v10.5.13.

Just as with the previous commenter, we are doing a left join against a derived table which contains aggregates, and there are 2 ON conditions in the join.

I tried to reduce the problem to the simplest possible test case, but I've found it extremely difficult to pin down. Sometimes we get the right results, and sometimes we don't, even when using the exact same SQL test script in the setup. Furthermore, sometimes we get the right results when the test data is first added, then a few minutes later, we will start getting the wrong results.

I have seen it change from correct to incorrect many times in my testing. But I have never seen it change back from incorrect to correct. Once it breaks, it seems to stay broken.

When we are getting the incorrect results, there are always 2 'LATERAL DERIVED' rows in the EXPLAIN output.

Daniel Howard added a comment - 2022-01-12 12:48 I have encountered this issue again in v10.5.13. Just as with the previous commenter, we are doing a left join against a derived table which contains aggregates, and there are 2 ON conditions in the join. I tried to reduce the problem to the simplest possible test case, but I've found it extremely difficult to pin down. Sometimes we get the right results, and sometimes we don't, even when using the exact same SQL test script in the setup. Furthermore, sometimes we get the right results when the test data is first added, then a few minutes later, we will start getting the wrong results. I have seen it change from correct to incorrect many times in my testing. But I have never seen it change back from incorrect to correct. Once it breaks, it seems to stay broken. When we are getting the incorrect results, there are always 2 'LATERAL DERIVED' rows in the EXPLAIN output.

Igor Babaev (Inactive) added a comment - 2022-01-13 18:16

danhowardmws,
Please provide the query returning a wrong result and the output from EXPLAIN FORMAT=JSON for this query.

Igor Babaev (Inactive) added a comment - 2022-01-13 18:16 danhowardmws , Please provide the query returning a wrong result and the output from EXPLAIN FORMAT=JSON for this query.

Daniel Howard added a comment - 2022-01-14 09:56

@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

Daniel Howard added a comment - 2022-01-14 09:56 @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 } } } } } }

Jens-U. Mozdzen added a comment - 2022-01-30 14:07 - edited

I see the same problem in MariaDB v10.6.5, using the queries provided by Daniel:

+----+----------------+--------------+---------------+

| id | from_ledger_id | to_ledger_id | from_num_rows |

+----+----------------+--------------+---------------+

|  2 |              1 |            2 |             2 |

|  3 |              1 |            2 |             2 |

|  5 |              3 |            2 |             2 |

|  8 |              3 |            2 |             2 |

| 10 |              3 |            2 |             2 |

| 12 |              3 |            2 |             2 |

| 13 |              3 |            2 |             2 |

| 18 |              1 |            2 |             2 |

+----+----------------+--------------+---------------+

8 rows in set (0,001 sec)

EXPLAIN output:

  "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": ["mariadbtest.charges.from_ledger_id", "mariadbtest.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": ["mariadbtest.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": ["mariadbtest.transaction_items.transaction_id"],

            "rows": 1,

            "filtered": 100

Real-life problem that brought me here is that Openstack malfunctions because of this error (the amount of supposedly allocated VCPUs is way above the actual number, because the query used by "placement" API also accounts for other resources, especially memory, too).

Jens-U. Mozdzen added a comment - 2022-01-30 14:07 - edited I see the same problem in MariaDB v10.6.5, using the queries provided by Daniel: +----+----------------+--------------+---------------+ | id | from_ledger_id | to_ledger_id | from_num_rows | +----+----------------+--------------+---------------+ | 2 | 1 | 2 | 2 | | 3 | 1 | 2 | 2 | | 5 | 3 | 2 | 2 | | 8 | 3 | 2 | 2 | | 10 | 3 | 2 | 2 | | 12 | 3 | 2 | 2 | | 13 | 3 | 2 | 2 | | 18 | 1 | 2 | 2 | +----+----------------+--------------+---------------+ 8 rows in set (0,001 sec) EXPLAIN output: { "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": ["mariadbtest.charges.from_ledger_id", "mariadbtest.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": ["mariadbtest.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": ["mariadbtest.transaction_items.transaction_id"], "rows": 1, "filtered": 100 } } } } } } Real-life problem that brought me here is that Openstack malfunctions because of this error (the amount of supposedly allocated VCPUs is way above the actual number, because the query used by "placement" API also accounts for other resources, especially memory, too).

Shi Yan added a comment - 2022-04-08 00:09

We met with the same issue from Openstack/Placement malfunction(as Jens-U.Mozdzen mentioned) on Mariadb version 10.5.13.
And using the SQL queried provided by Daniel, can confirm it breaks in versions 10.5.12, 10.5.13, and 10.6.5.

But in versions 10.5.15 and 10.6.7, the issue looks be fixed, and we cannot replicate the issue anymore after the upgrade. Although I cannot see any relevant info in their release notes.

Shi Yan added a comment - 2022-04-08 00:09 We met with the same issue from Openstack/Placement malfunction(as Jens-U.Mozdzen mentioned) on Mariadb version 10.5.13. And using the SQL queried provided by Daniel, can confirm it breaks in versions 10.5.12, 10.5.13, and 10.6.5. But in versions 10.5.15 and 10.6.7, the issue looks be fixed, and we cannot replicate the issue anymore after the upgrade. Although I cannot see any relevant info in their release notes.

MariaDB Server

Join using derived with aggregation returns incorrect results

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration