TPC-DS check list in 20.3+.

Missing features:

• INTERSECT / EXCEPT operators not implemented fixed via Subquery operators #24757 in 21.9
• [NOT] EXISTS-operator not implemented WHERE EXISTS ( ... subquery ...) not supported and error message is confusing #6852 fixed via Support EXISTS(subquery) #29731 in 21.11
• Interval syntax like '+ 30 days' is not supported fixed via INTERVAL operator support string literal #15978 in 20.11
• CTE syntax is different from ANSI SQL 99 fixed via First attempt of supporting CTE #14771 in 20.10?
• CTE returning multiple rows is not supported Support for non-scalar values in WITH clause #2416 partially fixed via First attempt of supporting CTE #14771 in 20.10?
• Window functions are not supported (OVER ... PARTITION BY ... ) Window functions #1469
• Missing analytical functions RANK, FIRST, LAST, LAG the exact syntax is not supported but can be emulated using existing toolset
• GROUPING SETS Support GROUPING SETS #33631 22.5
• GROUPING specification for ROLLUP, CUBE and GROUPING SETS; Add GROUPING function #37163 22.6
• Correlated subqueries are not supported (Missing columns: "x" while processing query) Correlated subqueries #6697 Cross join failed with the error 'Missing columns: "x" while processing query' #9861

Bugs:

• count(*) is not supported for multiple join queries Cross join does not support asterisks for complex queries #9853 fixed via Fix count(*) with multiple joins #10291 in 20.3.8
• COMMA to CROSS JOIN rewriter is not enabled or cannot rewrite query Cross join failed with the error 'COMMA to CROSS JOIN rewriter is not enabled or cannot rewrite query' #9863 fixed via Fix JOIN rewriters inconsistency #9830 in 20.3.5
• Mix of COMMA and other JOINS is not supported Cross join does not support mix of COMMA and other JOINS #9864 fixed via Fix JOIN rewriters inconsistency #9830 in 20.3.5
• illegal type FixedString(x) of argument of function like 'like' is not supported for FixedString type #9882 fixed via Implement operator LIKE for FixedString at left hand side. #9890
• Logical error: CROSS JOIN has expressions Cross join failed with the error 'Logical error: CROSS JOIN has expressions' #9910 fixed via Fix COMMA and other JOIN mix #10311 in 20.3.7
• Multiple JOIN do not support asterisks for complex queries yet Cross join of subqueries does not support asterisks for complex queries #10481 fixed via Rewrite multiple JOINs v2 #9739 in 20.4
• Aggregate function "x" is found inside another aggregate function in query (clash of aliases) Excessive substitution of aliases #9715
• Cannot refer column "x" to table Cross join failed with the error 'Cannot refer column "x" to table' #9855
• Illegal types Float64 and Decimal(9, 2) of arguments of function multiply Decimals and Floats can not be used in expressions without explicit type cast #9881

Common join optimizations:

• Use right table as a predicate for the left one Pushdown JOIN USING/ON subquery into IN section for indexed columns #8062
• AND (cond OR cond) prevents CROSS JOIN to INNER JOIN rewrite AND (cond OR cond) prevent CROSS JOIN to INNER JOIN rewrite #19856 partially fixed via Cross join to inner rewrite #20392 in 21.4

Merge join performance optimizations:

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