[MCOL-4719] ColumnStore ignores where clause: inline view with a window function - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 5.5.1
Fix Version/s: 6.2.1, 6.2.2
Component/s: None
Labels:
None
Environment:
10.5.9-6-MariaDB-enterprise-log
Columnstore 5.5

Sprint:
2021-9, 2021-10

Description

When columnstore_select_handler=on this query should return only 2 rows. Instead it returns all rows.

create database test1;

use test1;

CREATE TABLE test_table (

category CHAR(1),

count INTEGER(1)

) ENGINE=COLUMNSTORE;

INSERT INTO test_table (category, count) VALUES ('A', 1);

INSERT INTO test_table (category, count) VALUES ('A', 2);

INSERT INTO test_table (category, count) VALUES ('B', 3);

INSERT INTO test_table (category, count) VALUES ('B', 4);

set columnstore_select_handler=off;

select * FROM (

SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio

FROM test_table

) a

where ratio > .5;

ratio

0.6667

0.5714

set columnstore_select_handler=on;

select * FROM (

SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio

FROM test_table

) a

where ratio > .5;

ratio

0.6667

0.3333

0.5714

0.4286

Attachments

Activity

Ascending order - Click to sort in descending order

Edward Stoever created issue - 2021-05-12 20:52

Edward Stoever made changes - 2021-05-12 20:53

Field	Original Value	New Value
Description	{{monospaced text}} create database test1; use test1; CREATE TABLE test_table ( category CHAR(1), count INTEGER(1) ) ENGINE=COLUMNSTORE; INSERT INTO test_table (category, count) VALUES ('A', 1); INSERT INTO test_table (category, count) VALUES ('A', 2); INSERT INTO test_table (category, count) VALUES ('B', 3); INSERT INTO test_table (category, count) VALUES ('B', 4); set columnstore_select_handler=off; select * FROM ( SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio FROM test_table ) a where ratio > .5; ratio 0.6667 0.5714 set columnstore_select_handler=on; select * FROM ( SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio FROM test_table ) a where ratio > .5; ratio 0.6667 0.3333 0.5714 0.4286	{{create database test1; use test1; CREATE TABLE test_table ( category CHAR(1), count INTEGER(1) ) ENGINE=COLUMNSTORE; INSERT INTO test_table (category, count) VALUES ('A', 1); INSERT INTO test_table (category, count) VALUES ('A', 2); INSERT INTO test_table (category, count) VALUES ('B', 3); INSERT INTO test_table (category, count) VALUES ('B', 4); set columnstore_select_handler=off; select * FROM ( SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio FROM test_table ) a where ratio > .5; ratio 0.6667 0.5714 set columnstore_select_handler=on; select * FROM ( SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio FROM test_table ) a where ratio > .5; ratio 0.6667 0.3333 0.5714 0.4286}}

Edward Stoever made changes - 2021-05-12 20:54

Description

{{create database test1;
use test1;

CREATE TABLE test_table (
category CHAR(1),
count INTEGER(1)
) ENGINE=COLUMNSTORE;

INSERT INTO test_table (category, count) VALUES ('A', 1);
INSERT INTO test_table (category, count) VALUES ('A', 2);
INSERT INTO test_table (category, count) VALUES ('B', 3);
INSERT INTO test_table (category, count) VALUES ('B', 4);

set columnstore_select_handler=off;
select * FROM (
SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio
FROM test_table
) a
where ratio > .5;
ratio
0.6667
0.5714

set columnstore_select_handler=on;
select * FROM (
SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio
FROM test_table
) a
where ratio > .5;
ratio
0.6667
0.3333
0.5714
0.4286}}

create database test1;
use test1;

CREATE TABLE test_table (
category CHAR(1),
count INTEGER(1)
) ENGINE=COLUMNSTORE;

INSERT INTO test_table (category, count) VALUES ('A', 1);
INSERT INTO test_table (category, count) VALUES ('A', 2);
INSERT INTO test_table (category, count) VALUES ('B', 3);
INSERT INTO test_table (category, count) VALUES ('B', 4);

set columnstore_select_handler=off;
select * FROM (
SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio
FROM test_table
) a
where ratio > .5;
ratio
0.6667
0.5714

set columnstore_select_handler=on;
select * FROM (
SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio
FROM test_table
) a
where ratio > .5;
ratio
0.6667
0.3333
0.5714
0.4286

Edward Stoever made changes - 2021-05-12 21:23

Description

When columnstore_select_handler=on this query should return only 2 rows. Instead it returns all rows.
{noformat}
create database test1;
use test1;

CREATE TABLE test_table (
category CHAR(1),
count INTEGER(1)
) ENGINE=COLUMNSTORE;

INSERT INTO test_table (category, count) VALUES ('A', 1);
INSERT INTO test_table (category, count) VALUES ('A', 2);
INSERT INTO test_table (category, count) VALUES ('B', 3);
INSERT INTO test_table (category, count) VALUES ('B', 4);

set columnstore_select_handler=off;
select * FROM (
SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio
FROM test_table
) a
where ratio > .5;
ratio
0.6667
0.5714

set columnstore_select_handler=on;
select * FROM (
SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio
FROM test_table
) a
where ratio > .5;
ratio
0.6667
0.3333
0.5714
0.4286
{noformat}

Todd Stoffel (Inactive) made changes - 2021-05-13 17:59

Rank

Ranked higher

Gregory Dorman (Inactive) made changes - 2021-06-10 13:02

Priority

Major [ 3 ]

Blocker [ 1 ]

Gregory Dorman (Inactive) made changes - 2021-06-10 13:02

Fix Version/s

6.1.1 [ 25600 ]

Gregory Dorman (Inactive) made changes - 2021-06-10 13:03

Assignee

David Hall [ david.hall ]

Todd Stoffel (Inactive) made changes - 2021-06-10 18:36

Rank

Ranked higher

Gregory Dorman (Inactive) made changes - 2021-06-15 13:18

Fix Version/s		6.5.1 [ 25801 ]
Fix Version/s	6.1.1 [ 25600 ]

David Hall (Inactive) added a comment - 2021-07-19 21:06 - edited

Another, perhaps more palatable, workaround:
select * FROM ( SELECT (count / SUM(count) OVER (PARTITION BY category)) AS ratio FROM test_table ) a having ratio > .5;
--------

ratio

--------

0.6667

0.5714

--------

David Hall (Inactive) added a comment - 2021-07-19 21:06 - edited Another, perhaps more palatable, workaround: select * FROM ( SELECT (count / SUM(count) OVER (PARTITION BY category)) AS ratio FROM test_table ) a having ratio > .5; -------- ratio -------- 0.6667 0.5714 --------

David Hall (Inactive) added a comment - 2021-07-19 21:10

I have run this test on 1.1, 1.2, and 1.4. All showed the same deviant behavior.

David Hall (Inactive) added a comment - 2021-07-19 21:10 I have run this test on 1.1, 1.2, and 1.4. All showed the same deviant behavior.

David Hall (Inactive) added a comment - 2021-07-21 17:32

Analysis:
Window Functions and aggregates aren't complete until well after filtering is done. This is why you can't use a window function in a WHERE clause. If you try, it will generate an error:

SELECT SUM(count) OVER (PARTITION BY category) AS x FROM test_table where SUM(count) OVER (PARTITION BY category) < 6;

ERROR 4015 (HY000): Window function is allowed only in SELECT list and ORDER BY clause

Optimization attempts to push filters from the outer select to the inner select. This isn't done if the filter is on a result of an aggregation or window function. When the inner select doesn't have the operator, it notices that the filter is on the window function and doesn't optimize.

But when the inner function has an operator that includes the window function, the code only sees the operator and attempts to optimize, causing an illegal filter on the result of a window function which appears to turn into a noop in the lower code.

The fix is to have the code delve into the operator (or function or whatever) to see what windows or aggregates might lurk inside and suppress the optimization accordingly.

David Hall (Inactive) added a comment - 2021-07-21 17:32 Analysis: Window Functions and aggregates aren't complete until well after filtering is done. This is why you can't use a window function in a WHERE clause. If you try, it will generate an error: SELECT SUM(count) OVER (PARTITION BY category) AS x FROM test_table where SUM(count) OVER (PARTITION BY category) < 6 ; ERROR 4015 (HY000): Window function is allowed only in SELECT list and ORDER BY clause Optimization attempts to push filters from the outer select to the inner select. This isn't done if the filter is on a result of an aggregation or window function. When the inner select doesn't have the operator, it notices that the filter is on the window function and doesn't optimize. But when the inner function has an operator that includes the window function, the code only sees the operator and attempts to optimize, causing an illegal filter on the result of a window function which appears to turn into a noop in the lower code. The fix is to have the code delve into the operator (or function or whatever) to see what windows or aggregates might lurk inside and suppress the optimization accordingly.

David Hall (Inactive) made changes - 2021-07-21 19:58

Fix Version/s		6.2.1 [ 26035 ]
Fix Version/s	6.3.1 [ 25801 ]

David Hall (Inactive) made changes - 2021-07-21 19:58

Sprint

2021-9 [ 541 ]

David Hall (Inactive) added a comment - 2021-07-22 18:28

QA
I added the following two queries to mtr using the above defined table:

# a subselect with an arithmetic operator on a window function

select * FROM (SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio FROM test_table) a where ratio > .5;

+--------+

| ratio  |

+--------+

| 0.6667 |

| 0.5714 |

+--------+

# a subselect with a function containing an arithmetic operator on a window function

select * FROM (SELECT round(0+SUM(count) OVER (PARTITION BY category),3) AS x FROM test_table ) a where x>3;

+-------+

| x     |

+-------+

| 7.000 |

| 7.000 |

+-------+

You should be able to create a subquery with any number of nested functions and operators with internal aggregates and window functions.

David Hall (Inactive) added a comment - 2021-07-22 18:28 QA I added the following two queries to mtr using the above defined table: # a subselect with an arithmetic operator on a window function select * FROM (SELECT count / SUM(count) OVER (PARTITION BY category) AS ratio FROM test_table) a where ratio > . 5 ; +--------+ | ratio | +--------+ | 0.6667 | | 0.5714 | +--------+ # a subselect with a function containing an arithmetic operator on a window function select * FROM (SELECT round( 0 +SUM(count) OVER (PARTITION BY category), 3 ) AS x FROM test_table ) a where x> 3 ; +-------+ | x | +-------+ | 7.000 | | 7.000 | +-------+ You should be able to create a subquery with any number of nested functions and operators with internal aggregates and window functions.

David Hall (Inactive) made changes - 2021-07-22 18:58

Assignee	David Hall [ david.hall ]	Gagan Goel [ tntnatbry ]
Status	Open [ 1 ]	In Review [ 10002 ]

David Hall (Inactive) made changes - 2021-07-23 20:35

Sprint

2021-9 [ 541 ]

2021-9, 2021-10 [ 541, 549 ]

Gagan Goel (Inactive) made changes - 2021-07-28 21:21

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Gagan Goel (Inactive) made changes - 2021-07-28 21:21

Assignee

Gagan Goel [ tntnatbry ]

Daniel Lee [ dleeyh ]

Daniel Lee (Inactive) added a comment - 2021-08-04 14:33

Build verified: 6.2.1-1 ( #2921)

Daniel Lee (Inactive) added a comment - 2021-08-04 14:33 Build verified: 6.2.1-1 ( #2921)

Daniel Lee (Inactive) made changes - 2021-08-04 14:33

Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

Todd Stoffel (Inactive) made changes - 2021-08-23 23:26

Rank

Ranked higher

David Hall (Inactive) made changes - 2021-12-10 22:50

Fix Version/s

6.2.2 [ 27000 ]

Richard Stracke made changes - 2023-06-12 12:55

Assignee

Daniel Lee [ dleeyh ]

Richard Stracke [ richard ]

Richard Stracke made changes - 2023-06-12 12:56

Assignee

Richard Stracke [ richard ]

Daniel Lee [ dleeyh ]

Jira Automation (IT) made changes - 2024-07-04 13:05

Zendesk Related Tickets

112592

People

Assignee:: Daniel Lee (Inactive)

Reporter:: Edward Stoever

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2021-05-12 20:52

Updated:: 2024-07-08 01:37

Resolved:: 2021-08-04 14:33

Git Integration

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

MariaDB ColumnStore

Details

Description

Attachments

Activity

People

Dates

Git Integration