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.

        1. wrong_count_results_1.jpg
          wrong_count_results_1.jpg
          35 kB
        2. wrong_count_results_2.jpg
          wrong_count_results_2.jpg
          29 kB

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

                Created:
                Updated:
                Resolved: