0

In the list here:

https://mariadb.com/kb/en/operator-precedence/

case-expressions are put at the same precedence level as BETWEEN, between the NOT operator and the comparison operators.

However, case-expressions always begin with CASE and end with END, and all subexpressions are also delimited by the CASE keywords. They're like parenthetical expressions, so I don't understand why case-expressions are on this list.

Is there an SQL expression that would be parsed differently if the case-expression precedence was set higher or lower?

To give an example, with 2 + 3 * 4, we get different results when parentheses are used in these 2 ways: 2 + (3 * 4) and (2 + 3) * 4. This question is about how it's impossible to do the same with CASE. One can't substitute the use of + for CASE and show 2 different uses of parentheses such that the result differs between them.

To compare with other RDBMSes, neither SQLite nor PostgreSQL include CASE in their operator precedence lists.

5
  • 1
    I think you're right. there's only one way to parse it because not other interpretations are valid. Alll other operators are two argument, before and after, just after like ~ or !, or odd like binary, and case is self enclosed with not real arguments. precedence on a self contained expression is a bit meaningless which is why (/) are in the table either..
    – danblack
    Jul 30, 2020 at 22:40
  • @danblack Even more confusing is that the table seems to be an accurate reflection of the source code. Which makes me wonder how an expression like 1 + case when 1 then 1 end works. CASE is probably the only exception where an expression can have a subexpression that uses an operator of lower precedence without requiring parentheses. I mean, despite the code being so, the behaviour of the parser is as if CASE were of the highest precedence, as it can only be.
    – JoL
    Jul 30, 2020 at 23:20
  • @danblack I want to read the commit history around the addition of that line, but it seems that history is not available. It's added to mysql's repo as part of a huge commit, child of the root commit, with the message "Import changeset".
    – JoL
    Jul 30, 2020 at 23:27
  • @danblack *"the only exception where a binary operator expression can have a subexpression that uses an operator of lower precedence without requiring parentheses." Corrected because subexpressions of case can use operators of lower precedence. However, it's true, 1 + NOT 0 is a syntax error. I would have expected 1 + CASE ... to also raise a syntax error given its supposed precedence level.
    – JoL
    Jul 30, 2020 at 23:30
  • when you think about it, precedence is effectively how to parse the expression. If you want to see how things are parsed I suggest adding a debugger to the source code and seeing what happens. Or understanding bison files. Case with subexpression expr_lex also appears in IF expressions and a number of statements.
    – danblack
    Jul 30, 2020 at 23:36

1 Answer 1

0

The documentation is like that because it's an accurate reflection of this part of the source code:

%left   OR_SYM OR2_SYM
%left   XOR
%left   AND_SYM AND_AND_SYM

%left   PREC_BELOW_NOT
%left   NOT_SYM

%left   BETWEEN_SYM CASE_SYM WHEN_SYM THEN_SYM ELSE
%left   '=' EQUAL_SYM GE '>' LE '<' NE IS LIKE SOUNDS_SYM REGEXP IN_SYM
%left   '|'
%left   '&'
%left   SHIFT_LEFT SHIFT_RIGHT
%left   '-' '+' ORACLE_CONCAT_SYM
%left   '*' '/' '%' DIV_SYM MOD_SYM
%left   '^'
%left   MYSQL_CONCAT_SYM
%left   NEG '~' NOT2_SYM BINARY

That's despite how the parser behaves. Despite how, for example, 1 + NOT 0 raises a syntax error for following an operator of high precedence with one of lower precedence, 1 + CASE WHEN 1 THEN 1 END doesn't raise a similar syntax error for the same reason. Any expression involving CASE treats it as an operator of the highest precedence.

I'm left wondering now why the source code is expressed like that despite it not behaving like that. Unfortunately, I lack knowledge of bison to make sense of this discrepancy without investing more time in this curiosity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.