Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 12.0
    • Fix Version/s: 12.0.1, 12.1, 11.2.3, 8.12.18.6_4
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)

    • Steps to Reproduce:
      Hide

      1. Add two MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml (adventureworks and adventureworks2):

                      <datasource jndi-name="java:/adventureworks" pool-name="adventureworks" enabled="true" use-java-context="true">
                          <connection-url>jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull</connection-url>
                          <driver>mysql</driver>
                          <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                          </pool>
                          <security>
                              <user-name>XXXX</user-name>
                              <password>XXXX</password>
                          </security>
                          <validation>
                              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
                      <datasource jndi-name="java:/adventureworks2" pool-name="adventureworks2" enabled="true" use-java-context="true">
                          <connection-url>jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull</connection-url>
                          <driver>mysql</driver>
                          <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                          </pool>
                          <security>
                              <user-name>XXXX</user-name>
                              <password>XXXX</password>
                          </security>
                          <validation>
                              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      2. Add in test-vdb.xml java:/adventureworks and java:/adventureworks2 configured in previous step as a data sources:

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

      3. Run the following queries:

      select
      	 count(a.SalesPersonID)
      	, count(b.SalesPersonID)
      from "adventureworks2.salesorderheader" a
      left join "adventureworks.salesperson" b on a.SalesPersonID = b.SalesPersonID ;;
       
      select
      	 count(b.SalesPersonID)
      	, count(a.SalesPersonID)
      from "adventureworks2.salesorderheader" a
      left join "adventureworks.salesperson" b on a.SalesPersonID = b.SalesPersonID ;;
      

      The first query produces (that is wrong)
      the second one produces

      Show
      1. Add two MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml (adventureworks and adventureworks2): <datasource jndi-name= "java:/adventureworks" pool-name= "adventureworks" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> XXXX </user-name> <password> XXXX </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> <datasource jndi-name= "java:/adventureworks2" pool-name= "adventureworks2" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> XXXX </user-name> <password> XXXX </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 2. Add in test-vdb.xml java:/adventureworks and java:/adventureworks2 configured in previous step as a data sources: <model name= "adventureworks" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "adventureworks" translator-name= "mylobs" connection-jndi-name= "java:/adventureworks" /> </model> <model name= "adventureworks2" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "adventureworks2" translator-name= "mylobs" connection-jndi-name= "java:/adventureworks2" /> </model> 3. Run the following queries: select count ( a .SalesPersonID) , count (b.SalesPersonID) from "adventureworks2.salesorderheader" a left join "adventureworks.salesperson" b on a .SalesPersonID = b.SalesPersonID ;; select count (b.SalesPersonID) , count ( a .SalesPersonID) from "adventureworks2.salesorderheader" a left join "adventureworks.salesperson" b on a .SalesPersonID = b.SalesPersonID ;; The first query produces (that is wrong) the second one produces

      Description

      When using a query with a left join between tables from different schemes and count it produces wrong results. The workflow for reproducing the bug contains two example queries. The only difference between these queries is the order of the counts in select clause. The first query returns wrong results (the output of the "count(b.SalesPersonID)" field).
      Additional info: a) the wrong count results are the distinct values b) occurs also with other aggregations.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  shawkins Steven Hawkins
                  Reporter:
                  dalex005 Dmitrii Pogorelov
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: