Skip to content

Outdated operation precedence. #814

Open

Description

@mykhailynab

Here's an expression: SELECT ~1 + 2. If you plug it into PostgreSQL interpreter, here's what the output looks like:

postgres=# select ~1 + 2;
 ?column? 
----------
       -4
(1 row)

postgres=# select ~(1 + 2);
 ?column? 
----------
       -4
(1 row)

postgres=# select (~1) + 2;
 ?column? 
----------
        0
(1 row)

As you can see here, and according to operation precedence listed in documentation, the + operation is more powerful than the ~ operation, and thus we have PGBitwiseNot(1 + 2). However, if we plug this into the parser, we get the following AST:

...
projection: [
    UnnamedExpr(
        BinaryOp {
            left: UnaryOp {
                op: PGBitwiseNot,
                expr: Value(
                    Number(
                        "1",
                        false,
                    ),
                ),
            },
            op: Plus,
            right: Value(
                Number(
                    "2",
                    false,
                ),
            ),
        },
    ),
],
...

Which is implying (~1) + 2 instead of ~(1 + 2). This should be fixed.

UPDATE:
In code, I found a reference to an old PostgreSQL precedence table, using which the parser is built. However, this table is for a very old (7.0, released May 8, 2000) version of PostgreSQL. There latest version is 15.1. Shouldn't this be updated?

Activity

  1. changed the title [-]Wrong operation precedence.[/-] [+]BUG: Wrong operation precedence.[/+] on Feb 24, 2023
  2. changed the title [-]BUG: Wrong operation precedence.[/-] [+]Wrong operation precedence.[/+] on Feb 24, 2023
  3. changed the title [-]Wrong operation precedence.[/-] [+]Outdated operation precedence.[/+] on Feb 24, 2023
  4. AugustoFKL commented on Feb 25, 2023

    @AugustoFKL
    Contributor

    @michael-2956, you're right, this has been a problem for a while. Not sure if we have an issue with that, but that is known.

    I don't have the time to search about it and implement it, since the precedence may vary from each database, and it's quite a complex part of the code. But I'd love to review it if you feel like doing a PR for it!

  5. mykhailynab commented on Feb 25, 2023

    @mykhailynab
    ContributorAuthor

    @AugustoFKL, Is it ok to change lots of code in parser.rs to match PostgreSQL 15.1 standards? Won't this break any other dialects? I thought so, so I implemented the right precedence in the Postgres dialect in my fork on the precedence-patch branch. This is just a temporary solution for myself, but you can look at the changes I made if you want. I'm still testing it.

  6. AugustoFKL commented on Feb 25, 2023

    @AugustoFKL
    Contributor

    @michael-2956 I prefer to add permissiveness over break backward compatibility.

    If the precedence can't be generic for all dialects, I think it's better to specialize it. Of course, this isn't something simple at all, but I don't like the idea of breaking what we have right now.

  7. mykhailynab commented on Feb 26, 2023

    @mykhailynab
    ContributorAuthor

    @AugustoFKL So, putting it into a dialect was a right decision after all?

    There is a lot of redundancy in the dialect code on my fork, but I've tested it and I think it works fine (at least in my use cases).

    And, in summary, I did have to change some parsing code to fix precedence issues, but mostly I just changed the numbers.

    I think that those non-numbers issues I've come to fix were not exclusive to Postgres, so maybe I should move those fixes to parser.rs, and, possibly through some kind of a get_precedence() function, make it possible for the dialect to have its own precedence. Would that be ok?

    Unfortunately, modifying get_next_precedence turned out to be not enough for me. That's because the 'numbers' are not only used in the function but all over the parsing code. get_precedence(Token) -> u8 is one possible way to solve this.

  8. AugustoFKL commented on Feb 26, 2023

    @AugustoFKL
    Contributor

    @michael-2956, that would be ok... I'll take a look at your code this week, but, if the changes don't cause significant API changes or break backward compatibility, I don't see the problem.

  9. samuelcolvin commented on Jun 28, 2024

    @samuelcolvin
    Contributor

    I'm not sure if this is the same issue, but currently = is binding more strongly than -> and ->>.

    use sqlparser::dialect::PostgreSqlDialect;
    use sqlparser::parser::Parser;
    
    fn main() {
    
        let sql = r#"SELECT 'foo'->'bar'='spam'"#;
    
        let dialect = PostgreSqlDialect {};
    
        let ast = Parser::parse_sql(&dialect, sql).unwrap();
    
        dbg!(ast);
    }

    Contains:

                            UnnamedExpr(
                                BinaryOp {
                                    left: Value(
                                        SingleQuotedString(
                                            "foo",
                                        ),
                                    ),
                                    op: Arrow,
                                    right: BinaryOp {
                                        left: Value(
                                            SingleQuotedString(
                                                "bar",
                                            ),
                                        ),
                                        op: Eq,
                                        right: Value(
                                            SingleQuotedString(
                                                "spam",
                                            ),
                                        ),
                                    },
                                },
                            ),
    
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions