Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4877

Boolean syntax is broken on pg 9.5 data sources

    Details

    • Steps to Reproduce:
      Hide

      1. In postgresql create the test_d table by the following script:

      create table test.test_d (d integer);
      insert into test.test_d (d) values (3);
      insert into test.test_d (d) values (1);
      insert into test.test_d (d) values (3);
      

      2. Add postgresql database configuration in standalone-teiid.xml

                      <datasource jndi-name="java:/test_pg" pool-name="test_pg" enabled="true" use-java-context="true">
                          <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url>
                          <driver-class>org.postgresql.Driver</driver-class>
                          <driver>org.postgresql</driver>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                              <prefill>false</prefill>
                              <use-strict-min>false</use-strict-min>
                              <flush-strategy>FailingConnectionOnly</flush-strategy>
                          </pool>
                          <security>
                              <user-name>postgres</user-name>
                              <password>xxxxxx</password>
                          </security>
                          <validation>
                              <check-valid-connection-sql>select 0</check-valid-connection-sql>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:

          <model name="test_pg">
              <property name="importer.useFullSchemaName" value="false"/>
      	<property name="importer.tableTypes" value="TABLE,VIEW"/>
      	<property name="importer.importKeys" value="false"/>
              <source name="test_pg" translator-name="myPg" connection-jndi-name="java:/test_pg"/>
          </model>
      

      4. Configure in test-vdb.xml the following virtual view:

          <model visible = "true" type = "VIRTUAL" name = "views">
              <metadata type = "DDL"><![CDATA[
                CREATE virtual view test_d (d integer) as 
      	     select 3 union all
      	     select 1 union all
      	     select 3
              ]]>
              </metadata>
          </model>
      

      5. Running the following query:

      with mytab as
      (
          select d>2 as col1
          FROM "views.test_d"
      )
      select * from mytab where col1 = true ;;
      

      will return correct results:

      col1
      ------
      true
      true
      

      6. Running the following query:

      with mytab as
      (
          select d>2 as col1
          FROM "test_pg.test_d"
      )
      select * from mytab where col1 = true ;;
      

      will fail with the error message mentioned in description of the issue.
      But running the same query on PostgreSQL 9.3 will work fine, without any exceptions showing the same results as in 5 step. The behavior can be related to changes listed in https://www.postgresql.org/docs/9.6/static/release-9-5.html, especially because of it:

      Adjust operator precedence to match the SQL standard (Tom Lane)
      > The precedence of <=, >= and <> has been reduced to match that of <, > and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to be just below these six comparison operators. Also, multi-keyword operators beginning with NOT now have the precedence of their base operator (for example, NOT BETWEEN now has the same precedence as BETWEEN) whereas before they had inconsistent precedence, behaving like NOT with respect to their left operand but like their base operator with respect to their right operand. The new configuration parameter operator_precedence_warning can be enabled to warn about queries in which these precedence changes result in different parsing choices.
      

      Show
      1. In postgresql create the test_d table by the following script: create table test.test_d (d integer ); insert into test.test_d (d) values (3); insert into test.test_d (d) values (1); insert into test.test_d (d) values (3); 2. Add postgresql database configuration in standalone-teiid.xml < datasource jndi-name = "java:/test_pg" pool-name = "test_pg" enabled = "true" use-java-context = "true" > < connection -url>jdbc:postgresql://localhost:5432/test?charSet=utf8</ connection -url> < driver -class>org.postgresql.Driver</ driver -class> < driver >org.postgresql</ driver > < pool > < min -pool-size>2</ min -pool-size> < max -pool-size>70</ max -pool-size> < prefill >false</ prefill > < use -strict-min>false</ use -strict-min> < flush -strategy>FailingConnectionOnly</ flush -strategy> </ pool > < security > < user -name>postgres</ user -name> < password >xxxxxx</ password > </ security > < validation > < check -valid-connection-sql>select 0</ check -valid-connection-sql> </ validation > < timeout > < blocking -timeout-millis>120000</ blocking -timeout-millis> < idle -timeout-minutes>5</ idle -timeout-minutes> </ timeout > </ datasource > 3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource: < model name = "test_pg" > < property name = "importer.useFullSchemaName" value = "false" /> < property name = "importer.tableTypes" value = "TABLE,VIEW" /> < property name = "importer.importKeys" value = "false" /> < source name = "test_pg" translator-name = "myPg" connection-jndi-name = "java:/test_pg" /> </ model > 4. Configure in test-vdb.xml the following virtual view: < model visible = "true" type = "VIRTUAL" name = "views" > < metadata type = "DDL" > <![CDATA[ CREATE virtual view test_d (d integer) as select 3 union all select 1 union all select 3 ]]> </ metadata > </ model > 5. Running the following query: with mytab as ( select d>2 as col1 FROM "views.test_d" ) select * from mytab where col1 = true ;; will return correct results: col1 ------ true true 6. Running the following query: with mytab as ( select d>2 as col1 FROM "test_pg.test_d" ) select * from mytab where col1 = true ;; will fail with the error message mentioned in description of the issue. But running the same query on PostgreSQL 9.3 will work fine, without any exceptions showing the same results as in 5 step. The behavior can be related to changes listed in https://www.postgresql.org/docs/9.6/static/release-9-5.html , especially because of it: Adjust operator precedence to match the SQL standard (Tom Lane) > The precedence of <=, >= and <> has been reduced to match that of <, > and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to be just below these six comparison operators. Also, multi-keyword operators beginning with NOT now have the precedence of their base operator (for example, NOT BETWEEN now has the same precedence as BETWEEN) whereas before they had inconsistent precedence, behaving like NOT with respect to their left operand but like their base operator with respect to their right operand. The new configuration parameter operator_precedence_warning can be enabled to warn about queries in which these precedence changes result in different parsing choices.

      Description

      Running the following query using test_pg as a PostgreSQL data source working on 9.5 version:

      with mytab as
      (
          select d>2 as col1
          FROM "test_pg.test_d"
      )
      select * from mytab where col1 = true ;;
      

      will fail with the following error message:

      2017-04-27 16:04:50,560 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue6) 94Wi2JTG/lCK Connector worker process failed for atomic-request=94Wi2JTG/lCK.0.0.0: org.teiid.trans
      lator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."d" > 2 AS c_0 FROM "public"."test_d" AS g_0 WHERE g_0."
      d" > 2 = TRUE LIMIT 100]
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
              at java.lang.reflect.Method.invoke(Method.java:606)
              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
              at com.sun.proxy.$Proxy56.execute(Unknown Source)
              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
              at java.util.concurrent.FutureTask.run(FutureTask.java:262)
              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
              at java.lang.Thread.run(Thread.java:745)
      Caused by: org.postgresql.util.PSQLException: ОШИБКА: ошибка синтаксиса (примерное положение: "=")
        Позиция: 75
              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
              at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
              ... 18 more
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 dalex dalex
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: