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

Wrong results on left join + aggregation

XMLWordPrintable

    • 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

      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.

            rhn-engineering-shawkins Steven Hawkins
            dalex005 Dmitrii Pogorelov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: