Repository navigation
[pull] main from duckdb:main - #5
Merged
Merged
Conversation
Adds a MARK join type keyword to the SQL grammar, analogous to SEMI/ANTI. A MARK join projects the left-hand side plus a boolean marker column indicating whether each left row had a join partner. The binder, planner, and execution engine already supported JoinType::MARK from a JoinRef; this wires up the parser to produce it.
Cover the true/false/NULL marker cases (left NULL, unmatched right NULL, empty right side) and assert equivalence of an explicit single-condition MARK join with the MARK join generated by IN / NOT IN subqueries.
Render the raw true/false/NULL marker column next to the equivalent IN expression for both the NULL-free and NULL-containing right side, instead of only asserting set equivalence via EXCEPT.
The new MARK JOIN keyword reserves MARK (a type_name keyword, like SEMI and ANTI), so it can no longer be used as an unquoted identifier. Rename the exam.mark column to exam.grade in the QUALIFY test accordingly.
Instead of reserving MARK as a keyword (which broke 'mark' as an unquoted identifier), expose all internal join types via 'FROM x JOIN BY <type> y'. The join type name is a plain ColLabel (no reserved word), resolved case-insensitively against the JoinType enum via EnumUtil::FromString, with an optional _join suffix (mark == mark_join). Unknown names raise an error. Also name the mark join marker column __mark_join_marker. Reverts the MARK keyword reservation and the qualify-test rename it required.
Switch the JOIN BY clause from 'JOIN BY <type>' to 'JOIN BY (TYPE <type>)' so future parameters (e.g. MARKER_NAME) can be slotted into the parameter list. JoinRef::ToString now emits this form for internal join types that have no standard JOIN syntax (MARK, SINGLE, ...), fixing the reparse_statement round-trip.
This PR adds support for state export to ordered aggregates. Now that they follow the list infrastructure (#23231) this is relatively straightforward as we can already export/import their intermediate state using the generic state export/import code. The aggregate state is a `list` of structs, where the struct holds all columns required by the aggregate and the order by. For example: ```sql -- required columns are "l_comment", "l_orderkey" select string_agg(l_comment order by l_orderkey) export_state from lineitem group by l_shipdate limit 5; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ string_agg(l_comment ORDER BY l_orderkey) EXPORT_STATE │ │ aggregate_state │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ [{'v0': usual instructions. slyly fi, 'v1': 5601}] │ │ [{'v0': ' carefully. i', 'v1': 5409}] │ │ [{'v0': ' carefully aroun', 'v1': 4800}] │ │ [{'v0': 'play. special, regular pint', 'v1': 3712}] │ │ [{'v0': 'kages. blithe, c', 'v1': 1248}, {'v0': carefully final instr, 'v1': 3271}, {'v0': furiously furiously eve, 'v1': 5382}] │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` The main challenge then lies in the (re)-binding of the ordered aggregates. Effectively this expands the function signature that is held in the aggregate state with an `ORDER BY` clause: ```cpp ext_info->properties.emplace("order_bys", Value::LIST(OrderByEntryType(), std::move(order_values))); ``` For `to_aggregate_state`, we get an extra parameter to define these order terms, with the columns referenced by offset: ```sql select finalize(to_aggregate_state([{'v0': 'world', 'v1': 2}, {'v0': 'hello', 'v1': 1}], 'string_agg', ['VARCHAR'], [NULL], [{'column': 1, 'order': 'ASC NULLS LAST'}])) as result; ┌─────────────┐ │ result │ │ varchar │ ├─────────────┤ │ hello,world │ └─────────────┘ ``` When a column is used both as an argument to the aggregate function, and as a condition in the `ORDER BY` clause, it is contained only once in the list. For example: ```sql select string_agg(l_comment order by l_comment) export_state from lineitem group by l_shipdate limit 3; ┌───────────────────────────────────────────────────────┐ │ string_agg(l_comment ORDER BY l_comment) EXPORT_STATE │ │ aggregate_state │ ├───────────────────────────────────────────────────────┤ │ [{'v0': usual instructions. slyly fi}] │ │ [{'v0': ' carefully. i'}] │ │ [{'v0': ' carefully aroun'}] │ └───────────────────────────────────────────────────────┘ ``` Similarly we can then define the `ORDER BY` term to just refer to the first column. The column will then be both part of the aggregate and part of the `ORDER BY` clause: ```sql select finalize(to_aggregate_state([{'v0': 'world'}, {'v0': 'hello'}], 'string_agg', ['VARCHAR'], [NULL], [{'column': 0, 'order': 'ASC NULLS LAST'}])) as result; ┌─────────────┐ │ result │ │ varchar │ ├─────────────┤ │ hello,world │ └─────────────┘ ```
- removes python example - aligns C and C++ example so that they do the same thing fixes duckdblabs/duckdb-internal#7306
This PR adds syntax to trigger DuckDB's internal join operators like `MARK_JOIN`, `RIGHT_SEMI` etc. The syntax looks like this to avoid adding reserved keywords for important words like 'Mark': ```SQL FROM x JOIN BY (TYPE MARK_JOIN) y ```
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
See Commits and Changes for more details.
Created by
pull[bot] (v2.0.0-alpha.4)
Can you help keep this open source service alive? 💖 Please sponsor : )