My understanding so far regarding the first problem.
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
Στις Τρί, 12 Μαρ 2019 στις 4:48 μ.μ., ο/η Stamatis Zampetakis <
zabe...@gmail.com> έγραψε:
> If I find some time, I will try to look this evening.
>
> Στις Τρί, 12 Μαρ 2019 στις 4:26 μ.μ., ο/η Kevin Risden <kris...@apache.org>
> έγραψε:
>
>> Bump - Any ideas on the postgres failure?
>>
>> Kevin Risden
>>
>>
>> On Mon, Mar 11, 2019 at 11:51 AM Kevin Risden <kris...@apache.org> wrote:
>>
>> > It looks like there are 2 failures so far when running the integration
>> > tests (
>> > https://calcite.apache.org/docs/howto.html#running-integration-tests)
>> >
>> > 1. I'm not sure about the JdbcTest and what causes the failure. The
>> error
>> > is an exception from postgres and not from Calcite itself. Can anyone
>> help
>> > with determine the cause of this test failure?
>> >
>> > 2. For the other test, I think the JdbcAdapterTest is missing the
>> ":NULL"
>> > part after null that was changed as part of CALCITE-2454.
>> >
>> > Partial output from the test run is below:
>> >
>> > ./mvnw verify -Pit
>> >
>> > [INFO] -------------------------------------------------------
>> > [INFO] T E S T S
>> > [INFO] -------------------------------------------------------
>> > [INFO] Running org.apache.calcite.test.JdbcTest
>> > 2019-03-11 11:23:41,539 [main] INFO - open start - state modified
>> > 2019-03-11 11:23:41,555 [main] INFO - Checkpoint start
>> > 2019-03-11 11:23:41,555 [main] INFO - Checkpoint end - txts: 25
>> > [ERROR] Tests run: 290, Failures: 0, Errors: 1, Skipped: 21, Time
>> elapsed:
>> > 65.154 s <<< FAILURE! - in org.apache.calcite.test.JdbcTest
>> > [ERROR] testIsNotDistinctInFilter(org.apache.calcite.test.JdbcTest)
>> Time
>> > elapsed: 0.041 s <<< ERROR!
>> > java.sql.SQLException:
>> > Error while executing SQL "select *
>> > from "foodmart"."employee" as e1
>> > where e1."last_name" is distinct from e1."last_name"": While executing
>> > SQL [SELECT *
>> > FROM "foodmart"."employee"
>> > WHERE ("last_name" IS NOT NULL OR "last_name" IS NOT NULL) AND
>> "last_name"
>> > = "last_name" IS NOT TRUE] on JDBC sub-schema
>> > at
>> >
>> org.apache.calcite.test.JdbcTest.testIsNotDistinctInFilter(JdbcTest.java:1585)
>> > Caused by: java.lang.RuntimeException:
>> > While executing SQL [SELECT *
>> > FROM "foodmart"."employee"
>> > WHERE ("last_name" IS NOT NULL OR "last_name" IS NOT NULL) AND
>> "last_name"
>> > = "last_name" IS NOT TRUE] on JDBC sub-schema
>> > at
>> >
>> org.apache.calcite.test.JdbcTest.testIsNotDistinctInFilter(JdbcTest.java:1585)
>> > Caused by: org.postgresql.util.PSQLException:
>> > ERROR: argument of IS NOT TRUE must be type boolean, not type character
>> > varying
>> > Position: 114
>> > at
>> >
>> org.apache.calcite.test.JdbcTest.testIsNotDistinctInFilter(JdbcTest.java:1585)
>> >
>> > [INFO] Running org.apache.calcite.test.JdbcAdapterTest
>> > [ERROR] Tests run: 36, Failures: 1, Errors: 0, Skipped: 0, Time elapsed:
>> > 2.605 s <<< FAILURE! - in org.apache.calcite.test.JdbcAdapterTest
>> > [ERROR] testOverDisallowPartial(org.apache.calcite.test.JdbcAdapterTest)
>> > Time elapsed: 0.015 s <<< FAILURE!
>> > java.lang.AssertionError:
>> >
>> > Expected: a string containing "PLAN=JdbcToEnumerableConverter\n
>> > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3],
>> > category_id=[$4], currency_id=[$5], amount=[$6],
>> > last_version=[CASE(>=(COUNT() OVER (PARTITION BY $1 ORDER BY $3 ROWS
>> > BETWEEN 3 PRECEDING AND CURRENT ROW), 2), LAST_VALUE($3) OVER
>> (PARTITION BY
>> > $1 ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), null)])\n
>> > JdbcTableScan(table=[[foodmart, expense_fact]])\n"
>> > but: was "PLAN=JdbcToEnumerableConverter\n
>> > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3],
>> > category_id=[$4], currency_id=[$5], amount=[$6],
>> > last_version=[CASE(>=(COUNT() OVER (PARTITION BY $1 ORDER BY $3 ROWS
>> > BETWEEN 3 PRECEDING AND CURRENT ROW), 2), LAST_VALUE($3) OVER
>> (PARTITION BY
>> > $1 ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), null:NULL)])\n
>> > JdbcTableScan(table=[[foodmart, expense_fact]])\n\n"
>> > at
>> >
>> org.apache.calcite.test.JdbcAdapterTest.testOverDisallowPartial(JdbcAdapterTest.java:572)
>> >
>> > [INFO]
>> > [INFO] Results:
>> > [INFO]
>> > [ERROR] Failures:
>> > [ERROR] JdbcAdapterTest.testOverDisallowPartial:572
>> > Expected: a string containing "PLAN=JdbcToEnumerableConverter\n
>> > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3],
>> > category_id=[$4], currency_id=[$5], amount=[$6],
>> > last_version=[CASE(>=(COUNT() OVER (PARTITION BY $1 ORDER BY $3 ROWS
>> > BETWEEN 3 PRECEDING AND CURRENT ROW), 2), LAST_VALUE($3) OVER
>> (PARTITION BY
>> > $1 ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), null)])\n
>> > JdbcTableScan(table=[[foodmart, expense_fact]])\n"
>> > but: was "PLAN=JdbcToEnumerableConverter\n
>> > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3],
>> > category_id=[$4], currency_id=[$5], amount=[$6],
>> > last_version=[CASE(>=(COUNT() OVER (PARTITION BY $1 ORDER BY $3 ROWS
>> > BETWEEN 3 PRECEDING AND CURRENT ROW), 2), LAST_VALUE($3) OVER
>> (PARTITION BY
>> > $1 ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), null:NULL)])\n
>> > JdbcTableScan(table=[[foodmart, expense_fact]])\n\n"
>> > [ERROR] Errors:
>> > [ERROR] JdbcTest.testIsNotDistinctInFilter:1585 » SQL Error while
>> > executing SQL "selec...
>> > [INFO]
>> > [ERROR] Tests run: 326, Failures: 1, Errors: 1, Skipped: 21
>> >
>> >
>> > Kevin Risden
>> >
>>
>
