[MDEV-10884] Wrong query result with condition pushdown into derived table with ORDER BY ... LIMIT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.2
Component/s: Optimizer
Labels:
None

Description

Test dataset:

create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

select a from ten order by a limit 5;

+------+

| a    |

+------+

|    0 |

|    1 |

|    2 |

|    3 |

|    4 |

+------+

Use the above query in a derived table, without condition_pushdown_for_derived:

set optimizer_switch='condition_pushdown_for_derived=off';

Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);

+------+

| a    |

+------+

|    0 |

|    4 |

+------+

Enabling condition_pushdown_for_derived:

set optimizer_switch='condition_pushdown_for_derived=on';

Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);

+------+

| a    |

+------+

|    0 |

|    4 |

|    5 |

|    6 |

|    7 |

+------+

and see rows with id=5 and so forth. It looks like TBL.a not in (1,2,3) was pushed through ORDER BY ... LIMIT, which changes the query result.

Attachments

Issue Links

relates to

MDEV-9197 Pushdown conditions into non-mergeable views/derived tables

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2016-09-24 20:23

Sergei Petrunia made changes - 2016-09-24 20:25

Field	Original Value	New Value
Description	{noformat} create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); {noformat} {noformat} set optimizer_switch='condition_pushdown_for_derived=off'; Query OK, 0 rows affected (0.00 sec) select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3); +------+ \| a \| +------+ \| 0 \| \| 4 \| +------+ {noformat} {noformat} set optimizer_switch='condition_pushdown_for_derived=on'; Query OK, 0 rows affected (0.00 sec) {noformat} select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3); +------+ \| a \| +------+ \| 0 \| \| 4 \| \| 5 \| \| 6 \| \| 7 \| +------+ {noformat}	{noformat} create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); {noformat} {noformat} set optimizer_switch='condition_pushdown_for_derived=off'; Query OK, 0 rows affected (0.00 sec) select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3); +------+ \| a \| +------+ \| 0 \| \| 4 \| +------+ {noformat} {noformat} set optimizer_switch='condition_pushdown_for_derived=on'; Query OK, 0 rows affected (0.00 sec) select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3); +------+ \| a \| +------+ \| 0 \| \| 4 \| \| 5 \| \| 6 \| \| 7 \| +------+ {noformat}

Sergei Petrunia made changes - 2016-09-24 20:29

Description

{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
{noformat}

{noformat}
set optimizer_switch='condition_pushdown_for_derived=off';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
+------+
{noformat}

{noformat}
set optimizer_switch='condition_pushdown_for_derived=on';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
{noformat}

Test dataset:
{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
select a from ten order by a limit 5;
+------+
| a |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
{noformat}

Use the above query in a derived table, without condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=off';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
+------+
{noformat}

Enabling condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=on';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
{noformat}

and se see rows with id=5 and so forth. It looks like {TBL.a not in (1,2,3)} was pushed through {{ORDER BY ... LIMIT}}.

Sergei Petrunia made changes - 2016-09-24 20:29

Description

Test dataset:
{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
select a from ten order by a limit 5;
+------+
| a |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
{noformat}

Use the above query in a derived table, without condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=off';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
+------+
{noformat}

Enabling condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=on';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
{noformat}

and see rows with id=5 and so forth. It looks like {{TBL.a not in (1,2,3) }} was pushed through {{ORDER BY ... LIMIT}}, which changes the query result.

Sergei Petrunia made changes - 2016-09-24 20:30

Description

Test dataset:
{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
select a from ten order by a limit 5;
+------+
| a |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
{noformat}

Use the above query in a derived table, without condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=off';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
+------+
{noformat}

Enabling condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=on';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
{noformat}

and see rows with id=5 and so forth. It looks like {{TBL.a not in (1,2,3) }} was pushed through {{ORDER BY ... LIMIT}}, which changes the query result.

Test dataset:
{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
select a from ten order by a limit 5;
+------+
| a |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
{noformat}

Use the above query in a derived table, without condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=off';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
+------+
{noformat}

Enabling condition_pushdown_for_derived:
{noformat}
set optimizer_switch='condition_pushdown_for_derived=on';
Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
+------+
| a |
+------+
| 0 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
{noformat}

and see rows with id=5 and so forth. It looks like TBL.a not in (1,2,3) was pushed through ORDER BY ... LIMIT, which changes the query result.

Sergei Petrunia made changes - 2016-09-24 20:33

Assignee

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2016-09-24 20:33

Assignee

Sergei Petrunia [ psergey ]

Igor Babaev [ igor ]

Sergei Petrunia made changes - 2016-09-25 08:45

Link

This issue relates to ~~MDEV-9197~~ [ ~~MDEV-9197~~ ]

Sergei Petrunia made changes - 2016-09-25 08:46

Fix Version/s

10.2 [ 14601 ]

Igor Babaev (Inactive) made changes - 2016-09-26 00:03

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) added a comment - 2016-09-26 00:12

The condition should not have been pushed into SELECTs with ORDER BY ... LIMIT.

Similarly they should not be pushed into SELECTs of a unit with global ORDER BY ... LIMIT.
With condition pushdown into such units allowed we also can observe wrong result sets:

ariaDB [test]> set statement optimizer_switch='condition_pushdown_for_derived=on' for

select * from  (select a from t1 where a < 4 union select a from t1 where a > 5  order by a limit 5) t where t.a not in (2,9);

+------+

| a    |

+------+

|    0 |

|    1 |

|    3 |

|    6 |

|    7 |

+------+

MariaDB [test]> set statement optimizer_switch='condition_pushdown_for_derived=off' for  select * from  (select a from t1 where a < 4 union select a from t1 where a > 5  order by a limit 5) t where t.a not in (2,9);

+------+

| a    |

+------+

|    0 |

|    1 |

|    3 |

|    6 |

+------+

Igor Babaev (Inactive) added a comment - 2016-09-26 00:12 The condition should not have been pushed into SELECTs with ORDER BY ... LIMIT. Similarly they should not be pushed into SELECTs of a unit with global ORDER BY ... LIMIT. With condition pushdown into such units allowed we also can observe wrong result sets: ariaDB [test]> set statement optimizer_switch='condition_pushdown_for_derived=on' for select * from (select a from t1 where a < 4 union select a from t1 where a > 5 order by a limit 5) t where t.a not in (2,9); +------+ | a | +------+ | 0 | | 1 | | 3 | | 6 | | 7 | +------+ MariaDB [test]> set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from (select a from t1 where a < 4 union select a from t1 where a > 5 order by a limit 5) t where t.a not in (2,9); +------+ | a | +------+ | 0 | | 1 | | 3 | | 6 | +------+

Igor Babaev (Inactive) added a comment - 2016-09-26 16:48

The fix for this bug was pushed into the 10.2 tree.

Igor Babaev (Inactive) added a comment - 2016-09-26 16:48 The fix for this bug was pushed into the 10.2 tree.

Igor Babaev (Inactive) made changes - 2016-09-26 16:48

Fix Version/s		10.2.2 [ 22013 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:43

Workflow

MariaDB v3 [ 77213 ]

MariaDB v4 [ 150958 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2016-09-24 20:23

Updated:: 2016-09-26 16:48

Resolved:: 2016-09-26 16:48

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration