[MCOL-4688] Query with 2 Counts Created: 2021-04-21  Updated: 2023-07-01

Status: Open
Project: MariaDB ColumnStore
Component/s: Columnstore Select Handler
Affects Version/s: 5.4.1
Fix Version/s: Icebox

Type: Task Priority: Major
Reporter: andreas eschbacher Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Columnstore
Environment:

RHEL 8.2

Attachments: Zip Archive BugExposal_EBI-208.zip     Text File non-working_query.txt     Text File working_query.txt    

 Description   

Hello,

Columnstore 5.4.1

We try to run these Pentaho generated queries.
The query with 1 count is working,
the query with 2 counts is not running.
Which Parameter do we have to tweak to make the query running.
Attached please find the running query and non running query.

ty br Andreas

 Comments   
Comment by andreas eschbacher [ 2021-04-21 ]

don't work:
Columnstore 2x distinct not working,

works
Columnstore (count without distinct "normal" count works)
1 count 2x group by (with or without distincts) works

group by: same columns as count distinct

we will do some tests and post our results again

Comment by andreas eschbacher [ 2021-04-21 ]

This code reproduces the bug (for the screenshots, see BugExposal_Ebi-208.zip):

drop table if exists A;
create table A (
AID int,
AName varchar(200)
) ENGINE=Columnstore DEFAULT CHARSET=UTF8;
drop table if exists B;
create table B (
BID int,
FK_AID int,
BName varchar(200)
) ENGINE=Columnstore DEFAULT CHARSET=UTF8;
insert into A values (1, "BLA");
insert into B values (2, 1, "XYZ");
SELECT
B.FK_AID,
B.BID,
COUNT(DISTINCT B.BID),
COUNT(DISTINCT B.FK_AID)
FROM
A,
B
WHERE
B.FK_AID = A.AID
GROUP BY B.BID , B.FK_AID; BugExposal_EBI-208.zip

Comment by andreas eschbacher [ 2021-05-05 ]

Upgrading to Columnstore Version 5.5.1 did not solved the Problem
Problem is still there in CS 5.5.1

Comment by David Hall (Inactive) [ 2022-10-13 ]

Running on the very latest (not released) columnstore, this works:

MariaDB [dhall]> create table A (
 
    -> AID int,
 
    -> AName varchar(200)
 
    -> ) ENGINE=Columnstore DEFAULT CHARSET=UTF8;
 
Query OK, 0 rows affected (2.668 sec)
 
 
 
MariaDB [dhall]> create table B (
 
    -> BID int,
 
    -> FK_AID int,
 
    -> BName varchar(200)
 
    -> ) ENGINE=Columnstore DEFAULT CHARSET=UTF8;
 
Query OK, 0 rows affected (2.590 sec)
 
 
 
MariaDB [dhall]> insert into A values (1, "BLA");
 
Query OK, 1 row affected (1.554 sec)
 
 
 
MariaDB [dhall]> insert into B values (2, 1, "XYZ");
 
Query OK, 1 row affected (1.622 sec)
 
 
 
MariaDB [dhall]> SELECT
 
    -> B.FK_AID,
 
    -> B.BID,
 
    -> COUNT(DISTINCT B.BID),
 
    -> COUNT(DISTINCT B.FK_AID)
 
    -> FROM
 
    -> A,
 
    -> B
 
    -> WHERE
 
    -> B.FK_AID = A.AID
 
    -> GROUP BY B.BID , B.FK_AID; 
+--------+------+-----------------------+--------------------------+
 
| FK_AID | BID  | COUNT(DISTINCT B.BID) | COUNT(DISTINCT B.FK_AID) |
 
+--------+------+-----------------------+--------------------------+
 
|      1 |    2 |                     1 |                        1 |
 
+--------+------+-----------------------+--------------------------+
 
1 row in set (1.203 sec)

Generated at Thu Feb 08 02:52:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.