Clickhouse: TPC-DS compatibility issues

[alex-zaitsev]

TPC-DS check list in 20.3+.

Missing features:

• [ ] INTERSECT / EXCEPT operators not implemented  
• [ ] [NOT] EXISTS-operator not implemented #6852
• [x] Interval syntax like '+ 30 days' is not supported fixed via #15978 in 20.11
• [x] CTE syntax is different from ANSI SQL 99 fixed via #14771 in 20.10?
• [x] CTE returning multiple rows is not supported #2416 partially fixed via #14771 in 20.10?
• [ ] Window functions are not supported (OVER ... PARTITION BY ... ) #1469
• [ ] Missing analytical functions RANK, FIRST, LAST, LAG
• [ ] [GROUPING](https://docs.microsoft.com/en-us/sql/t-sql/functions/grouping-transact-sql?view=sql-server-ver15) aggregate function not implemented  
• [ ] Correlated subqueries are not supported (Missing columns: "x" while processing query) #6697 #9861

Bugs:

• [x] count(*) is not supported for multiple join queries #9853 fixed via #10291 in 20.3.8
• [x] COMMA to CROSS JOIN rewriter is not enabled or cannot rewrite query #9863 fixed via #9830 in 20.3.5
• [x] Mix of COMMA and other JOINS is not supported #9864 fixed via #9830 in 20.3.5
• [x] illegal type FixedString(x) of argument of function like #9882 fixed via #9890
• [x] Logical error: CROSS JOIN has expressions #9910 fixed via #10311 in 20.3.7
• [x] Multiple JOIN do not support asterisks for complex queries yet #10481 fixed via #9739 in 20.4
• [ ] Aggregate function "x" is found inside another aggregate function in query (clash of aliases) #9715
• [ ] Cannot refer column "x" to table #9855
• [ ] Illegal types Float64 and Decimal(9, 2) of arguments of function multiply #9881

Common join optimizations:

• [ ] Use right table as a predicate for the left one #8062

Merge join performance optimizations:

• [ ] Adaptive bucketing of the left table
• [ ] Using PK sorting of the left table
• [ ] Using PK sorting of the right table

[leoones]

When can I support this ？？

CTE syntax is different
CTE returning a table is not supported
Window functions are not supported
RANK OVER is not supported
GROUPING aggregate function not implemented

[4ertus2]

Missing columns: "x" while processing query #9861

It's an error for not implemented #6697. So it's a feature too.

[alex-zaitsev]

@4ertus2 , agree. Moved to features section

[leoones]

@4ertus2 , agree. Moved to features section
really want these features ！

[leoones]

@4ertus2 , agree. Moved to features section

Hope to add in later versions

[leoones]

When can the with statement return multiple lines ？？
Window functions are not supported (OVER ... PARTITION BY ... ) & analytical functions RANK, FIRST, LAST, LAG ， Do you have a plan ？？

[alex-zaitsev]

@leoones , 'WITH' returning multiple lines is hopefully for Q3 and window functions for Q4 earliest. Those are complex projects, and we are looking for contributors and sponsors to get it done.

[vkingnew]

this functions are very import to analyse complex query,desire it!

CTE syntax is different
CTE returning a table is not supported
Window functions are not supported
RANK OVER is not supported
MySQL 8.0 has implements windows functions:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

[ucasFL]

Interval syntax like '+ 30 days' is not supported

This task has been done in 15978 @alex-zaitsev