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

Wrong results on left join + aggregation

    XMLWordPrintable

Details

    • 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.

      Attachments

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

        Issue Links

          is related to

          Bug - A problem that impairs or prevents the functions of the product. TEIID-1676 Decomposed aggregates results can be wrong

          • Critical - To be worked on immediately following BLOCKER issues.
          • Closed

          Bug - A problem that impairs or prevents the functions of the product. TEIID-2219 NullPointer Exception when doing count aggregations on two individual columns from separate databases

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Enhancement - An enhancement to or refactoring of existing functionality that is not configurable by an end user (typically a change made by an internal team that affects users) TEIID-3131 Broaden aggregate decomposition scenarios

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: