PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Operator precedence of `EXISTS`

In Postgres, does the EXISTS operator have the highest precedence of all? For example:

SELECT 1 + EXISTS (SELECT 1)::int;

It seems to be missing from the manual page. Though the highest one is ::, meaning EXISTS would be higher?

In other words, it evaluates it like this:

select (exists (select 1))::int;

And not like this, which, if you try, will error:

select exists ((select 1)::int);

Meaning that the EXISTS operator has higher precedence than the CAST operator. Is this correct?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is not a matter of operator precedence but of how a grammar is parsed, in this case SQL. Let's step back a bit and take a basic arithmetic example for this:

    3+3*2

    If we ignore operator precedence, there are two valid ways to parse this statement:

    (3+3)  * 2     = 12

    And:

    3 +    (3*2)   = 9

    Both of these are valid parses. Operator precedence is used when there are multiple valid parses to resolve ambiguity. Notice, however, that the following is not a valid parse:

    (3+3*)      2  = /error/

    So before we bring in operator precedence to resolve ambiguities, we must first have a validly parsed expression.

    Now we'll return back to the question at hand. We will modify it slightly as well so that the literal used is not castable to an INT:

    SELECT EXISTS (SELECT DATE '2014-01-01')::INT

    In the question, it was assumed that there may be two possible ways to validly parse this statement:

    SELECT (EXISTS <subselect>)     ::INT

    And:

    SELECT EXISTS        (<subselect>::INT)

    But in fact, the latter parse is invalid, the <subselect>::INT will return an INT (or runtime error), not a sub-select, and so (substituting a placeholder value of 1 to make it more clear) SELECT EXISTS 1 is not a valid statement.

    In other words, you only have one valid parse, and so operator precedence is totally irrelevant.

    Here is an example Antlr program that demonstrates this (you can copy-paste it into this site to view, though it doesn't allow you to save-and-share the grammar like most pastebin sites do):

    // SELECT NOT (SELECT 55)::VARCHAR
// SELECT EXISTS (SELECT DATE '2014-01-01')::INT

grammar ExprParser;

program: 
    select EOF
    ;

select: 
    SELECT expr
    ;

subselect: 
    OPEN_PAREN select CLOSE_PAREN
    ;

expr:
    LITERAL            # LiteralExpr
  | subselect          # SubselectExpr
  | EXISTS subselect   # ExistsExpr
  | NOT expr           # LogicalExpr 
  | expr CAST TYPE     # CastExpr
    ;

SELECT : 'SELECT' ;
EXISTS : 'EXISTS' ;
LITERAL: 'DATE '2014-01-01'' | '55';
CAST: '::';
OPEN_PAREN: '(';
CLOSE_PAREN: ')';
TYPE: 'INT'|'BOOL'|'DATE';
NOT: 'NOT';

WS: [ tnrf]+ -> skip ;

    Let's now take a different query that does show operator precedence in action:

    SELECT NOT (SELECT 55)::VARCHAR;

    Here it can be parsed in two ways:

    SELECT NOT   ((SELECT 55)::VARCHAR)           --> NOT "55" --> Error

    And:

    SELECT (NOT (SELECT 55))       ::VARCHAR;     --> false::varchar --> "false"

    Here the first approach is used since the :: operator has higher precedence than NOT. In this way, ironically, the EXISTS is applied before the cast operation, though the NOT is applied after the cast operation. The EXISTS is applied first because there is only one possible grammatical parse, whereas with the NOT there are two possible parses, and so we use operator precedence to resolve ambiguity.

    You can view both example queries for the grammar here.

    And both parse trees in Antlr if useful:

    enter image description here

    enter image description here


  2. 0

    The elephant in the room: EXISTS is not an operator in Postgres, technically speaking.

    It’s a syntax element. The manual lists it in the chapter "Subquery Expressions". Operator precedence does not apply. EXISTS "binds" before any operator. The Postgres source code uses the term "SubLink" for this kind of plan node.

    Accordingly, you’ll also find no corresponding entry in pg_operator.

    Also accordingly, in your example, :: ranking number 2 in operator precedence doesn’t make the cut. Your test isn’t ideal, though. Either precedence would result in integer 1:

    SELECT EXISTS (SELECT 1)::int;

    This is unambiguous:

    SELECT EXISTS (SELECT 'foo')::int;

    Would error out, if ::int took precedence – like this does:

    --  invalid input syntax for type integer: "foo"
SELECT        (SELECT 'foo')::int;

    In case you still want more proof: even the number 1 in the operator precedence ranking, the column separator (.), can’t overcome the gravitational pull of EXISTS:

    CREATE  TABLE t (id int);

SELECT        (SELECT t FROM t LIMIT 1).id;  -- valid
SELECT EXISTS (SELECT t FROM t LIMIT 1).id;  -- invalid!

    fiddle

    The second SELECT would try to append .id to the boolean value true, which is a syntax error.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search