Repository navigation
Parse scientific notation and mixed integer/decimal IN lists in row filters - #4371
rahulsmahadev wants to merge 2 commits into
Conversation
The row filter parser only accepted decimals with a fractional part and literal sets of a single numeric kind, so `x > 1e-3` and `x in (1, 2.5)` raised a ParseException. Co-authored-by: Isaac <no-reply@databricks.com>
|
@jackylee-ch could you take a look when you have a chance? This small change to the expression parser lets |
|
Thanks for picking this up! I think the scientific notation part should follow ANSI SQL. In ANSI, Parsing it as a Could we emit a The mixed integer/decimal |
…er types A number with an exponent is an approximate numeric literal in ANSI SQL, and Spark, Trino and DuckDB parse 1e-3 as a double. As a DecimalLiteral, 1e3 has scale -3 and could not bind to decimal columns. Parse the exponent forms as DoubleLiteral instead, and convert a whole DoubleLiteral to int and long columns (a fractional value raises, as for decimals) so that long_col = 1e3 binds. Co-authored-by: Isaac <no-reply@databricks.com>
|
Thanks, done in bccbed0: numbers with an exponent now parse as |
jackylee-ch
left a comment
There was a problem hiding this comment.
Thanks for the update; the double-literal approach looks right to me. One question inline.
| ) | ||
| for expression, expected in [ | ||
| ("dec = 1e3", DecimalLiteral(Decimal("1000.00"))), | ||
| ("dec > 2.5e-1", DecimalLiteral(Decimal("0.25"))), |
There was a problem hiding this comment.
dec <= 5e-3 binds to dec <= 0.01 (and dec = 1e-3 to dec = 0.00) because DoubleLiteral.to(DecimalType) rounds half-up to the column scale, so a 0.01 row matches a 0.005 bound. Should exponent forms on a decimal column fail on a scale mismatch like DecimalLiteral does, and can this case get a test?
There was a problem hiding this comment.
Thanks @jackylee-ch, good catch. DoubleLiteral.to(DecimalType) rounds half-up to the column scale, like Java's DoubleLiteral.to(DECIMAL), so the expression API already binds LessThanOrEqual("dec", 0.005) as dec <= 0.01; this PR makes that reachable from string filters. I'd suggest raising Could not convert 0.005 into a decimal(10, 2) when the value does not fit the column scale exactly, so dec <= 5e-3 fails like dec <= 0.005 while dec <= 5e-1 still binds as 0.50. That also changes the expression API for Python floats on decimal columns and differs from Java's rounding, so I'd like to confirm that's acceptable before changing it. I'll add a test either way.
Rationale for this change
The row filter parser rejects two kinds of valid numeric literals:
Decimal literals used pyparsing's
common.real(), which has no exponent, so1e-3parses as1and the rest is left over. A literal set had to be all decimals or all integers, so a list that mixes1and2.5did not parse either.This change parses a number with an exponent (
1e-3,-1.5E3,2.5e1) as aDoubleLiteral: in ANSI SQL it is an approximate numeric literal, and Spark, Trino and DuckDB parse it as a double. Numbers without an exponent parse as before (DecimalLiteralorLongLiteral). A literal set can now mix integers, decimals and doubles. Each token is parsed to its own literal type before the existing set deduplication and binding rules apply. To keeplong_col = 1e3working, aDoubleLiteralwith a whole value now converts tointandlongcolumns; a fractional value raises, as it does for aDecimalLiteral. Binding to decimal and double columns uses the existing conversions (a double is rounded to the scale of a decimal column). A list that mixes numbers with strings still raises aParseException(test_in_different_types).Are these changes tested?
Yes. In
tests/expressions/test_parser.py,test_scientific_notationcovers exponents on either side of a comparison and inBETWEEN,test_in_mixed_numberscovers mixedINlists (in both orders) andNOT INlists, andtest_bind_numeric_literalsbinds exponent forms against decimal, long and double columns, and a mixed integer and doubleINlist against a long column. Intests/expressions/test_literals.py,test_double_to_integer_and_long_conversioncovers the new conversion, andtest_invalid_float_conversionsno longer expects aDoubleLiteralto rejectintandlong. The new tests fail on main and pass with this change.Are there any user-facing changes?
Bug fix: row filter strings accept scientific notation (as doubles) and
INlists that mix numeric literals. ADoubleLiteralwith a whole value now converts tointandlong. Expressions that parsed before parse to the same result.This pull request and its description were written by Isaac.