DISTINCT

Why this stage exists

select dept from emp prints a department for every employee, so eng comes out three times. Most of the time the question was which departments are there?, and the answer is each one once. That is select distinct dept from emp.

You have already built it, almost. DISTINCT is GROUP BY over the whole select list with no aggregates: select distinct dept from emp gives the same rows as select dept from emp group by dept. Rows are compared item by item, a NULL is the same as a NULL, and the first of each set of equal rows is the one that stays, so the order is first-seen order again.

Streaming

One thing makes Distinct different from a HashAggregate. An aggregate cannot hand up a group until it has seen every row, since a later row might add to it. A Distinct has nothing to add: the moment it sees a row it has not seen before, that row is finished. So it hands it up at once, and remembers it.

That keeps the early stop of the limit-offset stage working. select distinct dept from emp limit 2 reads row 1 (eng), row 2 (ops), has its two rows, and stops: rows_examined=2. A Distinct that read everything first would examine all eight.

Sorting distinct rows

ORDER BY now has to sort what DISTINCT leaves, so the Sort moves above the Distinct:

>explain select distinct dept from emp where salary > 50 order by dept limit 2;
[PLAN] select distinct dept from emp where salary > 50 order by dept limit 2
[PLAN]   └── Limit (count=2, offset=0)
[PLAN]       └── Sort (key=dept ASC)
[PLAN]           └── Distinct
[PLAN]               └── Project (cols=[dept])
[PLAN]                   └── Filter (salary > 50)
[PLAN]                       └── SeqScan (table=emp)

Above the Distinct the rows only hold the items, so a key must be one of them: select distinct dept from emp order by salary would have to put "eng" somewhere by a salary it no longer has, one of three. That is 01509. Order by an item, its alias, or the item written the same way.

When there is nothing to remove

A row that includes the table's first column cannot repeat: that column is unique. So when the select list is *, or holds the first column on its own as an item, the planner leaves the Distinct out, and the plan is the one without DISTINCT. This is the planner using a fact about the data, the primary key's uniqueness, to skip work; the Indexes and the Optimizer part does much more of it.

COUNT(DISTINCT e)

Inside COUNT, DISTINCT counts the different values instead of the rows: count(distinct dept) is 3 where count(dept) is 6. NULLs are still skipped. With GROUP BY each group counts its own: the different salaries per department. Only COUNT takes DISTINCT here; sum(distinct salary) is 01506.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 49.

The Distinct node prints as Distinct, with no fields. Under it the Project; above it the Sort, when there is one, and the Limit.

What you should see when done

$droid --db staff.db
Welcome to droid!
>select distinct dept from emp;
dept
eng
ops
NULL
hr
(4 rows)
[EXEC] rows_examined=8
>select distinct dept from emp order by dept desc limit 2;
dept
ops
hr
(2 rows)
[EXEC] rows_examined=8 sort_runs=1
>select count(distinct dept), count(dept), count(*) from emp;
COUNT(DISTINCT dept) | COUNT(dept) | COUNT(*)
3 | 6 | 8
(1 rows)
[EXEC] rows_examined=8
>select distinct dept from emp order by name;
[ERROR:01509] ORDER BY with DISTINCT must use an item of the select list
>.exit
exiting.. good bye!

The second query had to read every row, because the Sort above the Distinct needs all the distinct rows before it can hand up the first. The messages are yours to word; the tests check only the codes.

Common bugs in this stage

• Blocking. A Distinct that collects every row before handing up any gives the right rows and loses LIMIT's early stop.
• Rows glued into one string. ('ab', 'c') and ('a', 'bc') collapse into one. Compare items one by one.
• NULL never equal. Every NULL row survives. For DISTINCT, as for GROUP BY, NULL equals NULL.
• Sorting below the Distinct. It sorts rows that are then thinned out; the order survives, but a key that is not an item is accepted when it must be 01509.
• A Distinct over the primary key. Correct, but the plan must leave it out.
• COUNT(DISTINCT) counting NULL. NULLs are skipped, as in every aggregate but COUNT(*).

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C and C++: reuse the key-tuple hash table from the previous stage, with the whole output row as the key and no aggregate state. In C++, an unordered_set of rows with the same hash.
• Rust: a HashSet<Vec<Value>>; insert returns whether the row was new, which is exactly the test this operator needs.
• Zig: a std.HashMap with a void value, keyed by the row with the same context as the GROUP BY keys.