Stamatis - Thanks for looking into this!
I'm not sure the integration test against postgres has been run recently. I
went back to branch-1.18 and get the AbstractMethodError errors identified
here: CALCITE-2732 - Upgrade postgresql driver version. CALCITE-2732 didn't
upgrade the Postgres driver version but changed from JDBC 3 to JDBC 4.1 of
the driver.
I was going to try to bisect but that may not be possible since can't run
the integration tests with branch-1.18 due to CALCITE-2732.
So I guess the question is best way to move forward from here. Here are the
options I think:
- Figure out if something changed in 1.19.0 in relation to parenthesis
- Would require testing branch-1.18 with CALCITE-2732 applied
- Test against Postgres 9.6 instead of 9.3
- Would require upgrading calcite-test-dataset vm to Postgres 9.6
I am leaning towards fixing the parenthesis (regardless if the change was
introduced in 1.19.0 or not.) It seems broken to rely on underlying DB to
have correct operator precedence.
I opened CALCITE-2918 to track this further.
Kevin Risden
On Tue, Mar 12, 2019 at 3:52 PM Stamatis Zampetakis <[email protected]>
wrote:
> 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 <
> [email protected]> έγραψε:
>
> > If I find some time, I will try to look this evening.
> >
> > Στις Τρί, 12 Μαρ 2019 στις 4:26 μ.μ., ο/η Kevin Risden <
> [email protected]>
> > έγραψε:
> >
> >> Bump - Any ideas on the postgres failure?
> >>
> >> Kevin Risden
> >>
> >>
> >> On Mon, Mar 11, 2019 at 11:51 AM Kevin Risden <[email protected]>
> 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
> >> >
> >>
> >
>
