[jira] [Commented] (CALCITE-2918) Integration tests against postgres are broken

["Kevin Risden (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16790979#comment-16790979 ] 

Kevin Risden commented on CALCITE-2918:
---------------------------------------

Analysis by [~zabetak]

The query plan is the following:

JdbcToEnumerableConverter
  JdbcFilter(condition=[AND(OR(IS NOT NULL($3), IS NOT NULL($3)), IS NOT
TRUE(=($3, $3)))])
    JdbcTableScan(table=[[foodmart, employee]])

and the SQL query which is send to Postgres is the one below:

SELECT *
FROM "foodmart"."employee"
WHERE ("last_name" IS NOT NULL OR "last_name" IS NOT NULL) AND "last_name"
= "last_name" IS NOT TRUE

The last part of the where clause does not have parentheses so operator
precedence will take effect.

In Postgres 9.3, which is the one that we use in integration testing, IS
operator has higher precedence than = [1], which leads to incorrectly
interpreting the expression as

("last_name" IS NOT NULL OR "last_name" IS NOT NULL) AND ("last_name" =
("last_name" IS NOT TRUE)).

In later versions of Postgres, e.g., Postgres 9.6, the precedence is
differenent [2] with = having higher precedence than IS so I suppose that
if we try the same query in another version of Postgres it will run without
problem as it happens to be the case for MySQL.

I guess between 1.18 and 1.19 we have changed something in terms of
operator precedence or consideration of parentheses when writting the SQL
query based on a dialect. Apart from that if we don't parenthesize
systematically it means that for different versions of postgres we have to
handle such differences somehow.

I cannot look more now, so if somebody else wants to take it from here feel
free.

Best,
Stamatis

[1] https://www.postgresql.org/docs/9.3/sql-syntax-lexical.html
[2] https://www.postgresql.org/docs/9.6/sql-syntax-lexical.html

> Integration tests against postgres are broken
> ---------------------------------------------
>
>                 Key: CALCITE-2918
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2918
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Kevin Risden
>            Priority: Blocker
>             Fix For: 1.19.0
>
>
> As part of the release process, integration testing against postgres found a failure.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)