test(tpcds): add queries 28-63

[cpcloud]

Adds TPC-DS queries 28-63. The commits adding the Ibis expressions are in batches of 10. It seemed no less annoying to do it that way than to create a separate PR for each batch of 10.

[gforsyth]

general question re: the baseline SQL queries: are there canonical (or non-canonical...?) sources that these are being drawn from? I imagine DuckDB has these somewhere in a benchmarking repo, but is that also the case with Clickhouse? Or did you perform the translation?

[cpcloud]

Bleh, why the heck is druid trying to run tpc ds queries It's not.

[cpcloud]

general question re: the baseline SQL queries: are there canonical (or non-canonical...?) sources that these are being drawn from? I imagine DuckDB has these somewhere in a benchmarking repo, but is that also the case with Clickhouse? Or did you perform the translation?

The DuckDB ones are copied from their repo and then modified in ways that don't change the results (adding column names and casting to dates), to accommodate the more pedantic engines.

Regarding ClickHouse, it looks like there's this https://github.com/Altinity/tpc-ds/tree/master/queries. It looks like they didn't rewrite anything heavily other than some truly atrocious formatting, and they are using some settings like

SET partial_merge_join = 1, partial_merge_join_optimizations = 1

that might allow our code to work as well without having to special case clickhouse.

I don't think there's a source of truth that works across all engines without alteration.

[gforsyth]

I don't think there's a source of truth that works across all engines without alteration.

oh, I'm sure that there isn't -- I was just wondering if we wanted to include links to upstream sources.

Maybe also we can put some notes in a README in the various sql file collections with a brief overview of changes (like the one-sentence description you just gave about the DuckDB queries)

[cpcloud]

Added a readme about the folly of TPC :)

[cpcloud]

Re clickhouse settings, partial_merge_join = 1 just ... doesn't work at all and complains that's either not a setting or it's a read only setting, so I'm abandoning that for now.

I should report the performance issue upstream, 200 MB of TPC-DS data shouldn't really be causing such a nutty performance problem.

[gforsyth]

I should report the performance issue upstream, 200 MB of TPC-DS data shouldn't really be causing such a nutty performance problem.

agreed.

Also, on the subject of 200mb of data... if we bump up to sf=.45 we'll have non-empty results for queries 37, 41, 44, and 58

Only 51 requires a higher threshold (searching now)

[gforsyth]

Only 51 requires a higher threshold (searching now)

hmm, I'm wrong, sf=.45 also works for query 51.

[cpcloud]

Nice, I will update the testing-data repo then!

[cpcloud]

Do we want to merge this first and then I can handle the data updates in a follow-up?

[gforsyth]

Sounds good -- I was waiting on the data updates, but those can definitely go in a follow-up

[cpcloud]

oh lemme run the clouds before merging