[MDEV-13390] Identity server Db Select Statement order by issue. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.2.2, 10.3(EOL)
Fix Version/s: N/A
Component/s: Data Manipulation - Subquery, Optimizer
Labels:
None

Description

Please have a look at the attached screenshots, it will be easy to reproduce.

when i run this Query, it return wrong results when i run this query on 10.2.7

but return right results when i run on 10.1.x.

SELECT `api`.`Id`, `api`.`Description`, `api`.`DisplayName`, `api`.`Enabled`, `api`.`Name`

FROM `ApiResources` AS `api`

WHERE EXISTS (

    SELECT 1

    FROM `ApiScopes` AS `x`

    WHERE `x`.`Name` IN ('openid', 'profile', 'ccx2api', 'ccx2lmapi') AND (`api`.`Id` = `x`.`ApiResourceId`))

ORDER BY `api`.`Id`

Attachments

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

10.1.png
364 kB
2017-07-28 10:48
10.2.png
289 kB
2017-07-28 10:46
2017-07-27 (1).png
346 kB
2017-07-27 10:55
2017-07-27 (2).png
332 kB
2017-07-27 10:55
ccx-id_apiresources.sql
2 kB
2017-07-28 10:52
ccx-id_apiscopes.sql
3 kB
2017-07-28 10:52

Issue Links

is duplicated by

MDEV-12959 ORDER BY is mixing up rows

Closed

MDEV-13994 Bad join results with orderby_uses_equalities=on

Closed

relates to

MDEV-13694 Wrong result upon GROUP BY with orderby_uses_equalities=on

Closed

MDEV-13704 Nested query does not give same result as subquery and an outer query

Closed

MDEV-13994 Bad join results with orderby_uses_equalities=on

Closed

Activity

Ascending order - Click to sort in descending order

View 7 older comments

Ajay Kumar added a comment - 2017-07-31 06:49 - edited

Tried set optimizer_switch='materialization=off';

working Thanks

Ajay Kumar added a comment - 2017-07-31 06:49 - edited Tried set optimizer_switch='materialization=off'; working Thanks

Sergei Petrunia added a comment - 2017-08-01 11:50

ANALYZE: {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 1.1786,

    "read_sorted_file": {

      "r_rows": 2,

      "filesort": {

        "sort_key": "t1.`Id`",

        "r_loops": 1,

        "r_total_time_ms": 0.1815,

        "r_used_priority_queue": false,

        "r_output_rows": 2,

        "r_buffer_size": "240",

        "table": {

          "table_name": "<subquery2>",

          "access_type": "ALL",

          "possible_keys": ["distinct_key"],

          "r_loops": 1,

          "rows": 2,

          "r_rows": 2,

          "r_total_time_ms": 0.2101,

          "filtered": 100,

          "r_filtered": 1,

          "materialized": {

            "unique": 1,

            "query_block": {

              "select_id": 2,

              "table": {

                "table_name": "t2",

                "access_type": "range",

                "possible_keys": ["Name", "id_t2"],

                "key": "Name",

                "key_length": "202",

                "used_key_parts": ["Name"],

                "r_loops": 1,

                "rows": 2,

                "r_rows": 2,

                "r_total_time_ms": 0.4923,

                "filtered": 100,

                "r_filtered": 100,

                "index_condition": "t2.`Name` in ('aa','bb')"

},

    "table": {

      "table_name": "t1",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["Id"],

      "ref": ["test.t2.id_t2"],

      "r_loops": 2,

      "rows": 1,

      "r_rows": 1,

      "r_total_time_ms": 0.0924,

      "filtered": 100,

      "r_filtered": 100

Sergei Petrunia added a comment - 2017-08-01 11:50 ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 1.1786, "read_sorted_file": { "r_rows": 2, "filesort": { "sort_key": "t1.`Id`", "r_loops": 1, "r_total_time_ms": 0.1815, "r_used_priority_queue": false, "r_output_rows": 2, "r_buffer_size": "240", "table": { "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.2101, "filtered": 100, "r_filtered": 1, "materialized": { "unique": 1, "query_block": { "select_id": 2, "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["Name", "id_t2"], "key": "Name", "key_length": "202", "used_key_parts": ["Name"], "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.4923, "filtered": 100, "r_filtered": 100, "index_condition": "t2.`Name` in ('aa','bb')" } } } } } }, "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["Id"], "ref": ["test.t2.id_t2"], "r_loops": 2, "rows": 1, "r_rows": 1, "r_total_time_ms": 0.0924, "filtered": 100, "r_filtered": 100 } } }

Sergei Petrunia added a comment - 2017-08-01 12:11

Peculiar things about this query plan:

first, we do the subquery materialization (this reads data from table t2)
then, materialized subquery is used as a source data for filesort().
filesort has "sort_key": "t1.`Id`". That is, sort criteria uses table t1.

This is valid since the query has

WHERE (api.Id = x.ApiResourceId))  -- this is in the subquery

...

ORDER BY api.Id

however, this is where the bug most likely is.
SJ-Materialization-scan strategy unpacks in-equality columns to the outer select' columns.
I mean, join execution does that but does this happen when the filesort() is employed?

The guess agrees with the observation that the error started to occur after commit a95e384d54702 (which made such query plans possible).

Sergei Petrunia added a comment - 2017-08-01 12:11 Peculiar things about this query plan: first, we do the subquery materialization (this reads data from table t2) then, materialized subquery is used as a source data for filesort(). filesort has "sort_key": "t1.`Id`" . That is, sort criteria uses table t1. This is valid since the query has WHERE (api.Id = x.ApiResourceId)) -- this is in the subquery ... ORDER BY api.Id however, this is where the bug most likely is. SJ-Materialization-scan strategy unpacks in-equality columns to the outer select' columns. I mean, join execution does that but does this happen when the filesort() is employed? The guess agrees with the observation that the error started to occur after commit a95e384d54702 (which made such query plans possible).

Alice Sherepa added a comment - 2017-09-14 11:41

Sergei Petrunia, after fixing please check if initially reported problem and one from mdev-12959 are fixed

Alice Sherepa added a comment - 2017-09-14 11:41 Sergei Petrunia, after fixing please check if initially reported problem and one from mdev-12959 are fixed

Varun Gupta (Inactive) added a comment - 2020-07-09 05:57

Fixed by ~~MDEV-13994~~

Varun Gupta (Inactive) added a comment - 2020-07-09 05:57 Fixed by MDEV-13994

MariaDB Server

Identity server Db Select Statement order by issue.

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration