feat(duckdb)!: support transpilation of function LEFT from Snowflake …#7419
feat(duckdb)!: support transpilation of function LEFT from Snowflake …#7419fivetran-ashashankar wants to merge 7 commits intomainfrom
Conversation
|
@fivetran-ashashankar Let's also add a check on the query, if the second argument ( This ^ isn't covered for both So, in this approach #7419 (comment) we can also add a check for the length argument. The check should be added on top of the generated query. As we have already discussed we have to cover cases like the following: We don't know the value of |
fivetran-ashashankar
force-pushed
the
RD-1147725-transpile-LEFT
branch
from
e25281d to
a4e768d
Compare
SQLGlot Integration Test ResultsComparing:
By Dialect
Overallmain: 109548 total, 107861 passed (pass rate: 98.5%), sqlglot version: sqlglot:RD-1147725-transpile-LEFT: 108302 total, 107139 passed (pass rate: 98.9%), sqlglot version: Transitions: ✅ 1 test(s) passed |
identified l as a column, adds case and handles the blob convertion |
…to DuckDB - Add left_sql() method to handle BINARY/BLOB arguments - Convert BLOB to HEX string, multiply length by 2, apply LEFT, convert back with UNHEX - For VARCHAR arguments, use native DuckDB LEFT function
…view comments incorporated.
…ew comments. existing test fixes.
…ew comments. exp.case used
fivetran-ashashankar
force-pushed
the
RD-1147725-transpile-LEFT
branch
from
9a7b596 to
b700525
Compare
sqlglot/generators/duckdb.py
Outdated
| def _left_right_sql(self, expression: exp.Left | exp.Right, func_name: str) -> str: | ||
| arg = expression.this | ||
| length = expression.expression | ||
|
|
||
| # For BINARY/BLOB: DuckDB doesn't support RIGHT on BLOB | ||
| # Convert to HEX string, use RIGHT, then convert back to BLOB | ||
| # Only add negative length handling for Snowflake-originated queries | ||
| needs_case = expression.args.get("negative_length_returns_empty") | ||
|
|
||
| # For BINARY/BLOB: DuckDB doesn't support LEFT/RIGHT on BLOB | ||
| # Convert to HEX string, use LEFT/RIGHT, then convert back to BLOB | ||
| if _is_binary(arg): | ||
| # RIGHT(blob, n) becomes UNHEX(RIGHT(HEX(blob), n * 2)) | ||
| # LEFT/RIGHT(blob, n) becomes UNHEX(LEFT/RIGHT(HEX(blob), n * 2)) | ||
| # Each byte becomes 2 hex chars, so multiply length by 2 | ||
| hex_arg = exp.Hex(this=arg) | ||
| hex_length = exp.Mul(this=length, expression=exp.Literal.number(2)) | ||
| # since this exp.Right is not annotated, it won't enter this _is_binary branch during the recursive call | ||
| hex_right = self.func("RIGHT", hex_arg, hex_length) | ||
| result = exp.Unhex(this=hex_right) | ||
| result = exp.Unhex(this=self.func(func_name, hex_arg, hex_length)) | ||
|
|
||
| # Handle negative length: return empty blob | ||
| if needs_case: | ||
| empty_blob = exp.Unhex(this=exp.Literal.string("")) | ||
| case_expr = ( | ||
| exp.case().when(length < exp.Literal.number(0), empty_blob).else_(result) | ||
| ) | ||
| return self.sql(case_expr) | ||
| return self.sql(result) | ||
|
|
||
| # For VARCHAR: Use native RIGHT function | ||
| return self.func("RIGHT", arg, length) | ||
| # For VARCHAR: Use native LEFT/RIGHT function | ||
| # Handle negative length: return empty string | ||
| if needs_case: | ||
| func_expr = exp.func(func_name, arg, length) | ||
| empty_string = exp.Literal.string("") | ||
| case_expr = ( | ||
| exp.case().when(length < exp.Literal.number(0), empty_string).else_(func_expr) | ||
| ) | ||
| return self.sql(case_expr) | ||
| return self.func(func_name, arg, length) |
There was a problem hiding this comment.
Let's minimize this to something like:
def _left_right_sql(self, expression: exp.Left | exp.Right, func_name: str) -> str:
arg = expression.this
length = expression.expression
is_binary = _is_binary(arg)
if is_binary:
hex_arg = exp.Hex(this=arg)
# LEFT/RIGHT(blob, n) becomes UNHEX(LEFT/RIGHT(HEX(blob), n * 2))
# Each byte becomes 2 hex chars, so multiply length by 2
hex_length = exp.Mul(this=length, expression=exp.Literal.number(2))
result = exp.Unhex(this=self.func(func_name, hex_arg, hex_length))
else:
result = exp.func(func_name, arg, length)
if expression.args.get("negative_length_returns_empty"):
empty = exp.Literal.string("")
if is_binary:
empty = exp.Unhex(this=empty)
result = exp.case().when(length < exp.Literal.number(0), empty).else_(result)
return self.sql(result)
…ew comments. minimized code.
- Add DuckDB roundtrip tests for LEFT/RIGHT with literals and columns - Consolidate and expand Snowflake LEFT/RIGHT transpilation tests - Test both VARCHAR and BINARY data types
2 participants
…to DuckDB