@@ -46,11 +46,9 @@ set datafusion.execution.target_partitions = 4;
4646## Test tables
4747##########
4848
49- # `x` is a PRIMARY KEY: unique and not nullable.
5049statement ok
5150CREATE TABLE t_pk (x INT, y INT, PRIMARY KEY (x)) AS VALUES (1, 10), (2, 20);
5251
53- # `x` is UNIQUE and nullable`, so `x` does NOT determine `y`.
5452statement ok
5553CREATE TABLE t_uniq (x INT UNIQUE, y INT) AS VALUES (NULL, 2), (NULL, 1), (1, 3);
5654
@@ -61,15 +59,8 @@ SELECT x, y FROM t_uniq;
6159NULL 1
6260NULL 2
6361
64- ##########
65- ## 1. Removing DISTINCT
66- ## `ReplaceDistinctWithAggregate` (datafusion/optimizer/src/replace_distinct_aggregate.rs)
67- ##
68- ## A DISTINCT is a no-op when its input is already known to have unique rows,
69- ## and can then be dropped from the plan entirely.
70- ##########
7162
72- # 1.1 PRIMARY KEY: rows are unique, so the DISTINCT is removed and no
63+ # 1.1 PRIMARY KEY: rows are unique; the DISTINCT is removed and no
7364# Aggregate appears in the plan.
7465query TT
7566EXPLAIN SELECT DISTINCT x FROM t_pk;
@@ -94,9 +85,8 @@ EXPLAIN SELECT DISTINCT x FROM t_uniq;
9485----
9586logical_plan TableScan: t_uniq projection=[x]
9687
97- # 1.3 A PRIMARY KEY downgraded to a non-unique dependency by a LEFT JOIN:
98- # each `t_pk` row can occur once per matching row on the right, so the
99- # DISTINCT must be KEPT.
88+ # 1.3 A PRIMARY KEY downgraded to a non-unique dependency by a LEFT JOIN
89+ # so the DISTINCT must be KEPT.
10090# Fixed by: https://github.com/apache/datafusion/pull/23548
10191statement ok
10292CREATE TABLE t_orders (x INT, amount INT) AS VALUES (1, 10), (1, 20), (2, 30);
@@ -137,13 +127,6 @@ logical_plan
13712701)Aggregate: groupBy=[[t_uniq.x]], aggr=[[]]
13812802)--TableScan: t_uniq projection=[x]
139129
140- ##########
141- ## 2. Dropping trailing ORDER BY keys
142- ## datafusion/optimizer/src/eliminate_duplicated_expr.rs
143- ##
144- ## A trailing sort key adds no ordering information when the earlier keys
145- ## already determine it, and can be dropped.
146- ##########
147130
148131# 2.1 PRIMARY KEY: `x` determines `y`, so `ORDER BY x, y` is equivalent to
149132# `ORDER BY x` and the `y` key is dropped from the plan.
@@ -174,8 +157,7 @@ logical_plan
17415701)Sort: t_uniq.x ASC NULLS LAST
17515802)--TableScan: t_uniq projection=[x, y]
176159
177- # 2.3 GROUP BY derived key: after `GROUP BY x` the key `x` really does
178- # determine `cnt`, so dropping the `cnt` sort key is CORRECT.
160+ # 2.3 After `GROUP BY x` the `x` does determine `cnt`, so can drop `cnt` from sort
179161query TT
180162EXPLAIN SELECT x, cnt FROM (SELECT x, count(*) AS cnt FROM t_uniq GROUP BY x) ORDER BY x, cnt;
181163----
@@ -185,17 +167,9 @@ logical_plan
18516703)----Aggregate: groupBy=[[t_uniq.x]], aggr=[[count(Int64(1))]]
18616804)------TableScan: t_uniq projection=[x]
187169
188- ##########
189- ## 3. Dropping GROUP BY expressions
190- ## `optimize_projections` (datafusion/optimizer/src/optimize_projections/mod.rs)
191- ## -> `get_required_group_by_exprs_indices`
192- ##
193- ## A grouping column that the parent plan does not read can be dropped when
194- ## the remaining grouping columns already determine it.
195- ##########
196170
197171# 3.1 PRIMARY KEY: `x` determines `y`, and `y` is not selected, so grouping
198- # by `x, y` is the same as grouping by `x`. CORRECT.
172+ # by `x, y` is the same as grouping by `x`.
199173query TT
200174EXPLAIN SELECT x FROM t_pk GROUP BY x, y;
201175----
@@ -204,7 +178,7 @@ logical_plan
20417802)--TableScan: t_pk projection=[x]
205179
206180# 3.2 Nullable UNIQUE: grouping by `x, y` is NOT the same as grouping by
207- # `x` -- the two NULL rows differ in `y` and belong in separate groups.
181+ # `x` -- two NULL rows differ in `y` and belong in separate groups.
208182#
209183# BUG: `y` is dropped from the GROUP BY and the two NULL groups are merged,
210184# so one row goes missing.
@@ -224,25 +198,16 @@ logical_plan
22419802)--TableScan: t_uniq projection=[x]
225199
226200# 3.3 The same grouping, but with `y` selected so the parent needs it: no
227- # column can be dropped and the answer is right. CORRECT.
201+ # column can be dropped and the answer is right.
228202query II rowsort
229203SELECT x, y FROM t_uniq GROUP BY x, y;
230204----
2312051 3
232206NULL 1
233207NULL 2
234208
235- ##########
236- ## 4. Selecting columns that are not in the GROUP BY
237- ## `add_group_by_exprs_from_dependencies` (datafusion/expr/src/logical_plan/builder.rs)
238- ##
239- ## A column determined by the GROUP BY key has one value per group, so it
240- ## may be selected without being listed in the GROUP BY clause. DataFusion
241- ## implements this by silently appending it to the GROUP BY.
242- ##########
243-
244209# 4.1 PRIMARY KEY: `x` determines `y`, so `y` has a single well-defined
245- # value per group and one row is returned per `x`. CORRECT.
210+ # value per group and one row is returned per `x`.
246211query II rowsort
247212SELECT x, y FROM t_pk GROUP BY x;
248213----
@@ -278,15 +243,6 @@ logical_plan
27824301)Aggregate: groupBy=[[t_uniq.x, t_uniq.y]], aggr=[[]]
27924402)--TableScan: t_uniq projection=[x, y]
280245
281- ##########
282- ## 5. GROUP BY derived keys on the NULL-padded side of an outer join
283- ##
284- ## The inner aggregate makes `x` a key of `g`. A LEFT JOIN then NULL-pads
285- ## `g` for non-matching probe rows.
286- ##
287- ## Note this section uses no constraints at all -- the dependency comes
288- ## purely from GROUP BY.
289- ##########
290246
291247statement ok
292248CREATE TABLE t_null (x INT) AS VALUES (NULL), (NULL);
@@ -295,7 +251,7 @@ statement ok
295251CREATE TABLE t_probe (z INT) AS VALUES (0), (2);
296252
297253# 5.1 Grouping by `g.x, g.cnt` must keep both columns: `g.x` alone does not
298- # determine `g.cnt` after NULL padding. CORRECT.
254+ # determine `g.cnt` after NULL padding.
299255query II
300256SELECT g.x, count(*) AS c
301257 FROM t_probe a
@@ -327,7 +283,7 @@ logical_plan
32728310)--------------TableScan: t_null projection=[x]
328284
329285# 5.2 The ORDER BY variant: `g.x` is NULL for both rows, so the `g.cnt`
330- # tie-breaker is what orders them. CORRECT.
286+ # tie-breaker is what orders them.
331287query II
332288SELECT g.x, g.cnt
333289 FROM t_probe a
0 commit comments