[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) 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) 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) 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