Skip to content

Commit b645de0

Browse files
authored
Keep ClickBench SQL in sync across runners (#25773)
## Which issue does this PR close? - Part of #25031 ## Rationale for this change The extended Q6 benchmark uses different SQL from the other ClickBench runners, while Q15 and Q16 are absent from the SQL-benchmark suite and SQL logic test. These copies can drift again when a query is edited in only one place. ## What changes are included in this PR? - Remove the URL cast from the extended Q6 benchmark query. - Add extended Q15 and Q16 SQL benchmarks and corresponding SQL logic test entries. - Add a parity test for standard and extended ClickBench. It checks that each benchmark query appears in its SQL logic test and that benchmark and legacy query files have matching IDs and normalized SQL. - Update both SQL logic test headers to identify the mirrored query locations. ## Are these changes tested? The patch adds `clickbench_parity.rs` and six unit tests covering SQL logic test parsing and SQL normalization. It also adds Q15/Q16 SQL logic test entries with expected value `0`. The diff does not show test execution, generation of those expected values, or the requested Q6 comparisons in single and partitioned configurations. ## Are there any user-facing changes? The extended Q6 SQL benchmark now runs without the URL cast. Q15 and Q16 are added to the extended SQL-benchmark suite. The diff does not establish whether removing the cast changes Q6 results. No public API change is shown. ## LLM-generated code disclosure This PR includes LLM-generated code and comments. All LLM-generated content has been manually reviewed.
1 parent db425eb commit b645de0

6 files changed

Lines changed: 517 additions & 3 deletions

File tree

‎benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q06.benchmark‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,6 @@ SELECT COUNT(*) > 0 from hits;
1212
true
1313

1414
run
15-
SELECT COUNT(*) AS ShareCount FROM hits WHERE "IsMobile" = 1 AND "MobilePhoneModel" LIKE 'iPhone%' AND "SocialAction" = 'share' AND "SocialSourceNetworkID" IN (5, 12) AND "ClientTimeZone" BETWEEN -5 AND 5 AND regexp_match("Referer", '\/campaign\/(spring|summer)_promo') IS NOT NULL AND CASE WHEN split_part(split_part(CAST("URL" AS STRING), 'resolution=', 2), '&', 1) ~ '^\d+$' THEN split_part(split_part(CAST("URL" AS STRING), 'resolution=', 2), '&', 1)::INT ELSE 0 END > 1920 AND levenshtein(CAST("UTMSource" AS STRING), CAST("UTMCampaign" AS STRING)) < 3;
15+
SELECT COUNT(*) AS ShareCount FROM hits WHERE "IsMobile" = 1 AND "MobilePhoneModel" LIKE 'iPhone%' AND "SocialAction" = 'share' AND "SocialSourceNetworkID" IN (5, 12) AND "ClientTimeZone" BETWEEN -5 AND 5 AND regexp_match("Referer", '\/campaign\/(spring|summer)_promo') IS NOT NULL AND CASE WHEN split_part(split_part("URL", 'resolution=', 2), '&', 1) ~ '^\d+$' THEN split_part(split_part("URL", 'resolution=', 2), '&', 1)::INT ELSE 0 END > 1920 AND levenshtein(CAST("UTMSource" AS STRING), CAST("UTMCampaign" AS STRING)) < 3;
1616

1717
result sql_benchmarks/clickbench_extended/results/q06.csv
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q15
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT MAX(c) FROM (
16+
SELECT covar_samp("ResolutionWidth", "ResolutionHeight") as c
17+
FROM hits
18+
GROUP BY "UserID"
19+
);
20+
21+
result sql_benchmarks/clickbench_extended/results/q15.csv
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q16
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT MAX(c) FROM (
16+
SELECT covar_samp("ResolutionWidth", "ResolutionHeight") as c
17+
FROM hits
18+
GROUP BY "RegionID"
19+
);
20+
21+
result sql_benchmarks/clickbench_extended/results/q16.csv

0 commit comments

Comments
 (0)