You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Here's an interesting bug that I found. For a SQL query like:
SELECTt1.name, CAST(SUM(b.dec) ASDECIMAL(20,6)) AS dec FROM t2 as b LEFT JOIN t1 ONb.exid=t1.t1_idGROUP BYb.exid`)
The CAST expression results in the wrong return value: the result of the SUM aggregation is casted to DECIMAL(0, 0), so it has no decimal precision after the period.
Why? Well, the planner aggregator in extractAggr is being too eager with the 20 and 6 literals inside the CAST.
The planner finds a *CastExpr and iterates it, but when recursing into it, it considers 20 and 6, which are both *sqlparser.Literal, as part of the aggregation, and generates an AggregateAnyValue for each of the literals.
This behavior makes sense for a compound expression like SELECT 3 * SUM(b.dec), but generating a new Any Aggregation for these literals is not correct. It results in very unexpected results: in this specific bug, since we now have a literal 20 as an aggregated expression for the query, when the planner performs the offset rewrites, it'll try to replace CAST(... AS DECIMAL(20,6)) with CAST(... AS DECIMAL(:offset_5,:offset_6)), but since the *CastExpr only holds *Literal and not sqlparser.Expr, it'll result in CAST(... AS DECIMAL(nil, nil)), and remove the decimal precision.
Ouchie!
@systay: I don't know how to fix this! We could special-case *CastExpr to ensure we don't consider its *Literal fields and extract them into aggregated expressions, but surely this is not scalable. There are other SQL functions that can be applied on top of aggregations and that contain literals that should not be extracted.
The text was updated successfully, but these errors were encountered:
Here's an interesting bug that I found. For a SQL query like:
The
CAST
expression results in the wrong return value: the result of theSUM
aggregation is casted toDECIMAL(0, 0)
, so it has no decimal precision after the period.Why? Well, the planner aggregator in
extractAggr
is being too eager with the20
and6
literals inside theCAST
.vitess/go/vt/vtgate/planbuilder/operators/queryprojection.go
Lines 512 to 536 in bcfc5fb
The planner finds a
*CastExpr
and iterates it, but when recursing into it, it considers20
and6
, which are both*sqlparser.Literal
, as part of the aggregation, and generates anAggregateAnyValue
for each of the literals.This behavior makes sense for a compound expression like
SELECT 3 * SUM(b.dec)
, but generating a new Any Aggregation for these literals is not correct. It results in very unexpected results: in this specific bug, since we now have a literal20
as an aggregated expression for the query, when the planner performs the offset rewrites, it'll try to replaceCAST(... AS DECIMAL(20,6))
withCAST(... AS DECIMAL(:offset_5,:offset_6))
, but since the*CastExpr
only holds*Literal
and notsqlparser.Expr
, it'll result inCAST(... AS DECIMAL(nil, nil))
, and remove the decimal precision.Ouchie!
@systay: I don't know how to fix this! We could special-case
*CastExpr
to ensure we don't consider its*Literal
fields and extract them into aggregated expressions, but surely this is not scalable. There are other SQL functions that can be applied on top of aggregations and that contain literals that should not be extracted.The text was updated successfully, but these errors were encountered: