PostgreSQL dialect: unary minus binds more loosely than `^`, so `-1 ^ 0` returns -1

Open Beginner friendly
#27,094 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
76/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
rust, sql
Domain
databases, testing

Research direction

The follow-up is in DataFusion after the fix in apache/datafusion-sqlparser-rs#2819. First check which sqlparser release includes that fix and how DataFusion pins or upgrades sqlparser; then find the existing sqllogictest cases for PostgreSQL dialect parsing. Done when the dependency includes the fix and tests cover -1 ^ 0 and -2 ^ 2 with results matching PostgreSQL.

Written by the indexing model from the issue text.

Description

bug

This issue tracks the parser fix in apache/datafusion-sqlparser-rs#2819. The cause is in sqlparser, and DataFusion needs follow-up work after the fix lands.

Describe the bug

With the PostgreSQL dialect, ^ is the power operator. Unary minus binds more loosely than ^, so -1 ^ 0 is planned as -(1 ^ 0) and returns -1.

In PostgreSQL, unary minus binds more tightly than ^ (operator precedence table). PostgreSQL computes (-1) ^ 0 and returns 1.

To Reproduce
SET datafusion.sql_parser.dialect = 'PostgreSQL';
SELECT -1 ^ 0;
SELECT -2 ^ 2;
EXPLAIN SELECT -2 ^ 2;

On main (c120619de), datafusion-cli returns:

+------------------------------+
| (- power(Int64(1),Int64(0))) |
+------------------------------+
| -1.0                         |
+------------------------------+
+------------------------------+
| (- power(Int64(2),Int64(2))) |
+------------------------------+
| -4.0                         |
+------------------------------+

The plan shows (- power(Int64(2),Int64(2))), so the minus is applied after the power.

Expected behavior

The same results as PostgreSQL 17:

postgres=# SELECT -1 ^ 0, -2 ^ 2;
 ?column? | ?column?
----------+----------
        1 |        4

DuckDB gives the same results (1 and 4).

Additional context

The cause is in sqlparser: the operand of a unary - or + is parsed at Precedence::MulDivModOp, but PostgreSqlDialect puts ^ above that level. The details are in apache/datafusion-sqlparser-rs#2819.

Work in DataFusion after the parser fix:

  • Upgrade to a sqlparser release that has the fix.
  • Add a sqllogictest case for -1 ^ 0 and -2 ^ 2 with the PostgreSQL dialect.

Related: #22461 (binary operator precedence against PostgreSQL).

Dominant language
Rust
Stars
9.6k
Forks
2.6k
Avg merge
3d 5h
Merged PRs (30d)
352

Getting set up

Open in Codespaces

Starts the project's dev container in your browser, under your own GitHub account.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from apache/datafusion

All issues in apache/datafusion

Similar issues

More Rust issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.