[ 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)