PostgreSQL compatibility mismatches in simple SQL expressions #22247

Describe the bug

While comparing simple SQL statements between PostgreSQL and DataFusion, I found several PostgreSQL compatibility mismatches where DataFusion returns a different answer or returns a value where PostgreSQL raises a domain error.

I excluded cases that are only unsupported PostgreSQL functions in DataFusion and minor type/display-format differences.

Environment

PostgreSQL was built locally from source:

PostgreSQL 19devel
commit aa1f93a3387ad619c14cea2b8ed01e6f49cb6600

DataFusion:

datafusion-cli 53.1.0
commit 1ab146ad6cc119c7656ae1def75fd40697e5f94a

Mismatches

1. ^ evaluates as bitwise XOR instead of PostgreSQL exponentiation

SELECT 2 ^ 3;

PostgreSQL:

8

DataFusion:

1

2. SIMILAR TO should treat % as a wildcard

SELECT 'abc' SIMILAR TO 'a%';

PostgreSQL:

true

DataFusion:

false

3. replace with an empty search string should be a no-op

SELECT replace('abc', '', 'x');

PostgreSQL:

abc

DataFusion:

xaxbxcx

4. array_length of an empty array dimension should be NULL

SELECT array_length(array[]::int[], 1);

PostgreSQL:

NULL

DataFusion:

0

5. Negative array subscripts should not index from the end

SELECT (array[10,20,30])[-1];

PostgreSQL:

NULL

DataFusion:

30

6. time + interval should wrap within the 24-hour time domain

SELECT time '23:30' + interval '2 hours';

PostgreSQL:

01:30:00

DataFusion:

25 hours 30 mins

7. time - interval should wrap within the 24-hour time domain

SELECT time '01:30' - interval '2 hours';

PostgreSQL:

23:30:00

DataFusion:

-30 mins

8. extract(second ...) should preserve fractional seconds

SELECT extract(second from timestamp '2020-01-01 00:00:12.345678');

PostgreSQL:

12.345678

DataFusion:

12

9. extract(milliseconds ...) should preserve fractional milliseconds

SELECT extract(milliseconds from timestamp '2020-01-01 00:00:12.345678');

PostgreSQL:

12345.678

DataFusion:

12345

10. regexp_count should count empty-pattern matches

SELECT regexp_count('abc', '');

PostgreSQL:

4

DataFusion:

0

11. regexp_instr with an empty pattern should return 1

SELECT regexp_instr('abc', '');

PostgreSQL:

1

DataFusion:

0

12. regexp_like should honor PostgreSQL multiline flag m

SELECT regexp_like(E'a\nb', '^b', 'm');

PostgreSQL:

true

DataFusion:

false

13. regexp_replace should honor PostgreSQL multiline flag m

SELECT regexp_replace(E'a\nb', '^b', 'x', 'm');

PostgreSQL:

a
x

DataFusion:

a\nb

14. round(float8) should match PostgreSQL half-tie behavior

SELECT round(2.5::float8);

PostgreSQL:

2

DataFusion:

3.0

15. factorial(21) should not overflow when PostgreSQL returns a numeric answer

SELECT factorial(21);

PostgreSQL:

51090942171709440000

DataFusion:

Overflow happened on FACTORIAL(21)

16. factorial of a negative value should error

SELECT factorial(-1);

PostgreSQL:

ERROR: factorial of a negative number is undefined

DataFusion:

1

17. sqrt(-1.0::float8) should error, not return NaN

SELECT sqrt((-1.0)::float8);

PostgreSQL:

ERROR: cannot take square root of a negative number

DataFusion:

NaN

18. ln(-1.0::float8) should error, not return NaN

SELECT ln((-1.0)::float8);

PostgreSQL:

ERROR: cannot take logarithm of a negative number

DataFusion:

NaN

19. log(0.0::float8) should error, not return -inf

SELECT log(0.0::float8);

PostgreSQL:

ERROR: cannot take logarithm of zero

DataFusion:

-inf

20. power(0.0::float8, -1.0::float8) should error, not return infinity

SELECT power(0.0::float8, -1.0::float8);

PostgreSQL:

ERROR: zero raised to a negative power is undefined

DataFusion:

inf

Expected behavior

For PostgreSQL-compatible SQL semantics, DataFusion should either match PostgreSQL's result or raise the same class of domain/semantic error for these simple expressions.