pepijnve commented on issue #17982: URL: https://github.com/apache/datafusion/issues/17982#issuecomment-3384455707
I've never been able to convince myself to pony up the money for the SQL specification, but perhaps I should. This feels like one of those cases where it would be useful to have. As a poor man's proxy I asked Claude. Take this with a grain of salt as with anything AI produced. Does the SQL specification mandate eager evaluation of function arguments? > The SQL specification does not mandate eager evaluation of function arguments. In fact, SQL's evaluation model is generally left implementation-defined, giving database systems considerable flexibility. > > Key points: > **No explicit evaluation order mandate**: The SQL standard doesn't prescribe whether arguments must be evaluated before, during, or conditionally within function execution. This is intentionally left to implementers. > **Short-circuit evaluation is common**: Many database systems use lazy/short-circuit evaluation for certain operations: > * `CASE` expressions typically don't evaluate branches that won't be selected > * Boolean operators like `AND` and `OR` often short-circuit (e.g., `false AND (1/0 = 1)` won't cause a division by zero error in most systems) > * `COALESCE` and `NULLIF` typically evaluate arguments only as needed > > **Implementation variance**: Different database systems handle this differently: > * Some may optimize away function calls with constant arguments > * Some may defer evaluation until necessary > * Evaluation order can vary between different functions in the same system > **Why this matters**: This flexibility allows for optimizations but can lead to portability issues. For example, relying on evaluation order for side effects or avoiding errors (like division by zero) may work in one database but not another. > > If you need guaranteed evaluation order or conditional execution, using explicit `CASE` expressions or implementation-specific features is more reliable than depending on function argument evaluation semantics. Is this flexibility described in the ISO standard? > Yes, the ISO SQL standard does address this, though it's somewhat scattered across different sections. The key concept is that SQL uses "**effectively evaluated**" semantics rather than mandating specific evaluation timing or order. > > Key standard provisions: > 1. **General Rules flexibility**: The SQL standard's "General Rules" sections describe what must be true logically, not how or when to compute it. Implementations can evaluate expressions in any order that produces correct results. > 2. **Conditional evaluation**: The standard explicitly allows (and expects) conditional evaluation for constructs like CASE. From ISO/IEC 9075-2 (Foundation), the CASE expression rules indicate that only the selected branch needs to be evaluated. > 3. **No guaranteed ordering**: Unlike some programming languages, SQL doesn't guarantee left-to-right argument evaluation. The standard states that the evaluation order of operands and function arguments is implementation-dependent unless specifically required by the operation. > 4. "**Without actually evaluating**": The standard uses language like "without necessarily evaluating" in various places, indicating that implementations can defer or skip evaluation when the result is determinable without it. > 5. **Error handling ambiguity**: The standard doesn't mandate when errors must be raised. An expression that could raise an error (like 1/0) might not if the optimizer can prove the value isn't needed. > > **Practical implication**: This means you cannot reliably depend on side effects or evaluation order in SQL expressions. The standard intentionally provides this flexibility to allow for query optimization. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
