sql: operator precedence does not match PostgreSQL

[mikecaat]

Unfortunately, CockroachDB does not match PostgreSQL's order of operations. An example of this is here: #64699 (comment) and is detailed here: #66866 (comment)

Changing the order of operations is dangerous and will be very broken for backwards compatibility. However, if we want PostgreSQL compatibility we need to do it. One for the record books...

---

Describe the problem

When I made a test for #66861, I found the order of operation of OPERATOR(pg_config.X) is not same.

root@localhost:26257/postgres> SELECT 'a' || 'b' = 'ab';
  ?column?
------------
    true
(1 row)

root@localhost:26257/postgres> SELECT 'a' || 'b' OPERATOR(pg_catalog.=) 'ab';
  ?column?
------------
  afalse
(1 row)

By the way, the result of executing the same queries with postgresql ver 13.3 is the following.

psql=# SELECT 'a' || 'b' = 'ab';
 ?column?
----------
 t
(1 row)

psql=# SELECT 'a' || 'b' OPERATOR(pg_catalog.=) 'ab';
 ?column?
----------
 t
(1 row)

To Reproduce

Execute the above example.

Expected behavior

I think this should be the same order of operations. Thought?

Environment:

• CockroachDB version: master branch(c4f134c)
• Server OS: MacOS BigSur
• Client app: cockroach sql

Jira issue: CRDB-8268

Epic CRDB-60814

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @rafiss (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for the report! cc @otan do you happen to know what Postgres does for the precedence of OPERATOR?

[otan]

Sorry, my previous comment was wrong.

The order of precedence is defined here:

cockroach/pkg/sql/parser/sql.y

Line 1339 in b971e36

%left OPERATOR // if changing the last token before OPERATOR, change all instances of %prec <last token>

Compared to PostgreSQL: https://github.com/postgres/postgres/blob/9b8ed0f52bffceaccf6fa650ffe482e7da20fbf2/src/backend/parser/gram.y#L796

With || tokenised as CONCAT. In PostgreSQL, %left Op should include CONCAT, so i'm not quite sure what precedence is broken here.

maybe the answer is to do #65107 for BinaryExprs and ComparisonExprs, since it may pretty print the AST differently.

[mikecaat]

Hi, thanks for your comments!

I investigated a little more. On the second thought, I think this is not a bug, but CRDB's limitation of PostgreSQL compatibility.

PostgreSQL's operator precedence said that operators including OPERATOR, CONCAT, &, #, | and so on is the same. But, CRDB doesn't follow PostgreSQL's way intentionally.

A example case is the follow,

In PostgreSQL, the operators | (bitwise OR), # (bitwise XOR), and & (bitwise AND) all have the same precedence.
In CockroachDB, the precedence from highest to lowest is: &, #, |.

So, the issue I addressed happens with other operator combinations.
That's why I think this is not a bug if this behavior is expected. Out of curiosity, I want to know why the behavior is implemented purposely.

Though?

[otan]

I've had a go at changing the order of precedence, but the breaking of compatibility is too hard.

I'm going to rename/reword your issue

[mikecaat]

OK, thanks.