Follow-up on Clickbench benchmark

[waitingkuo]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]
(This section helps Arrow developers understand the context and why for this feature, in addition to the what)

The goal is to pass all 43 cases to submit to https://benchmark.clickhouse.com/

i did some initial progress in #2902
We now passed 36 cases
This issue is to track the progress to pass all 43 cases.

• 11 it crashes (fixed since clickbench has another parquet that marks those column as string)
• 12 it crashes (same as 12)
• 14 it crashes (same as 12)
• 37
  • EventDate is uint in parquet, i can't add schema via create external table as parquet location 'xxx' to cast it to date; and then compare (solved by modifying queries.sql to cast in sql query)
    • Cannot cast Uint16 to Date (solved by casting utint16 to int and then cast again to date, should improve this in the future
    • Cannot group by binary (solve by casting binary to text, for now, submitted Cannot GROUP BY Binary #3050 for tracking this) (closed by Allow Overriding AsyncFileReader used by ParquetExec #3051)
• 38 - same as 37
• 39
  • same as 37
  • Binary cannot be compared with UTF-8 (fixed by casting binary to UTF-8)
• 40 - same as 37
• 41 - same as 37
• 42 - same as 37
• 43 - i need to fix 37-42 first then come back to this to see the reason

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

[waitingkuo]

43. passed

original query:

SELECT DATE_TRUNC('minute', EventTime) AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_TRUNC('minute', EventTime) ORDER BY DATE_TRUNC('minute', EventTime) LIMIT 10 OFFSET 1000;

Since datafusion import columns as case-sensitive so we need to modify it as

SELECT DATE_TRUNC('minute', "EventTime") AS M, COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-14' AND "EventDate" <= '2013-07-15' AND "IsRefresh" = 0 AND "DontCountHits" = 0 GROUP BY DATE_TRUNC('minute', "EventTime") ORDER BY DATE_TRUNC('minute', "EventTime") LIMIT 10 OFFSET 1000;

EventTime is originally Int in parquet, we need to cast it (as parquet importer doesn't allow us to add schema for now)

SELECT DATE_TRUNC('minute', to_timestamp_seconds("EventTime")) AS M, COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate"::INT::DATE >= '2013-07-14' AND "EventDate"::INT::DATE <= '2013-07-15' AND "IsRefresh" = 0 AND "DontCountHits" = 0 GROUP BY DATE_TRUNC('minute', to_timestamp_seconds("EventTime")) ORDER BY DATE_TRUNC('minute', to_timestamp_seconds("EventTime") LIMIT 10 OFFSET 1000;

The last isue is that we cannot ORCER BY BY DATE_TRUNC('minute', to_timestamp_seconds("EventTime")
i fixed it as ORCER BY BY DATE_TRUNC('minute', M)

[waitingkuo]

Current issue for 11 (12 and 13 have the similar issue)

origin:

SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;

add double quote as column name imported as case-sensitive

SELECT "MobilePhoneModel", COUNT(DISTINCT "UserID") AS u FROM hits WHERE "MobilePhoneModel" <> '' GROUP BY "MobilePhoneModel" ORDER BY u DESC LIMIT 10;

Cast to TEXT as we cannot group by binary for now #3035

SELECT "MobilePhoneModel"::TEXT, COUNT(DISTINCT "UserID") AS u FROM hits WHERE "MobilePhoneModel"::TEXT <> '' GROUP BY "MobilePhoneModel"::TEXT ORDER BY u DESC LIMIT 10;

Unfortunately it crashes. i feel like group by casted doesn't work here
i can reproduce similar issue by select count(distinct a) from (select '1' as a) b group by a::TEXT;. this query crashes in datafusion but works in postgresql

[waitingkuo]

37-42 passed by

1. Cast "EventTime" to Timestamp by to_timestamp_seconds
2. Cast Binary to TEXT so that it could be compared with another UTF-8

[waitingkuo]

#3050 Cannot group by binary only happens in mac, it works in linux i remove all the casting that cast from binary to text, and it works in linux now

finally all test cases passed
i submitted the pr and also listed some issues for future improvements: https://github.com/waitingkuo/ClickBench/blob/datafusion/datafusion/README.md

this is incorrect

[waitingkuo]

i finally found the root cause for most of the issues..... their full-dataset and partial-dataset have different schema, submitted a issue there ClickHouse/ClickBench#18

[waitingkuo]

close again :D