[teiid-issues] [JBoss JIRA] Created: (TEIID-1458) Wrong count(*) result when joining data from two models

Tue Feb 1 06:30:39 EST 2011

Wrong count(*) result when joining data from two models
-------------------------------------------------------

                 Key: TEIID-1458
                 URL: https://issues.jboss.org/browse/TEIID-1458
             Project: Teiid
          Issue Type: Bug
          Components: Query Engine
    Affects Versions: 7.3, 7.2
         Environment: Teiid 7.3 deployed on Jboss AS 5.1.0 GA running on Ubuntu Server 10.04 LTS, MySQL 5.1.51 running on the same maching as JBoss, SQL Server 2000 Standard SP4 running on Windows Server 2003
            Reporter: Claudio Venturini
            Assignee: Steven Hawkins

count(\*) gets a wrong result when is performed in a query which joins two tables coming from two different models.

The first (named "sqlsrv") is a source model for a table stored in SQL Server 2000. The second (named "mysql") is a source model for a table stored in MySQl 5.1.

The query performs an inner join on the column "product_id", which is shared by the two tables. All records match correctly, so there should be no difference in using an inner join instead of other join types.

The query is the following:

{noformat}
SELECT
    invoice,
    invoice_date,
    code,
    sum(quantity) AS s,
    avg(price) AS av,
    count(*) AS dim
FROM
    sqlsrv.test2.dbo.sales SL
    INNER JOIN mysql.test.product_code PC ON SL.product_id = PC.product_id
GROUP BY invoice_date, invoice, code
ORDER BY invoice_date ASC, invoice ASC, code ASC
{noformat}

The result of count(\*) is 1 for all records, while for some of them it should be 2, as there are duplicate records in the sales table, which are grouped by the GROUP BY clause.

Note that the problem exists only with the inner join. Left, right and outer joins work well, even if the set of records that they produce is the same as that produced by the inner join.

I noticed the problem in Teiid 7.2. Tonight I upgraded to 7.3, but the problem is still there.
If the data are all in the same DB, the query works as expected. It is not significant in which of the two DB each table resides. I think it is neither a problem of the SQL Server connector, nor of the MySQL connector.

The data are the following:

*product_code*:
||code||category||product_id|| 
|1|1|125|
|2|1|127|
|3|1|123|
|4|1|121|
|5|1|126|
|6|1|124|
|7|1|122|

*sales*:
||invoice||invoice_date||product_id||quantity||price|| 
|009831|2009-08-15 00:00:00|125|350|1.070261|
|009831|2009-08-15 00:00:00|124|960|1.070261|
|009843|2009-08-15 00:00:00|121|648|1.515264|
|009843|2009-08-15 00:00:00|126|145|2.763902|
|009843|2009-08-15 00:00:00|126|25|2.407148|
|009855|2009-08-15 00:00:00|122|768|1.122835|
|009855|2009-08-15 00:00:00|123|540|1.158511|
|009855|2009-08-15 00:00:00|125|480|1.070261|
|009857|2009-08-15 00:00:00|122|440|1.498365|
|009857|2009-08-15 00:00:00|126|115|2.585525|
|009866|2009-08-15 00:00:00|122|736|1.498365|
|009866|2009-08-15 00:00:00|123|558|1.391339|
|009866|2009-08-15 00:00:00|125|378|1.336887|
|009866|2009-08-15 00:00:00|127|510|1.605391|
|009866|2009-08-15 00:00:00|126|435|2.585525|
|009847|2009-08-15 00:00:00|126|55|2.763902|
|009847|2009-08-15 00:00:00|126|5|2.407148|
|009847|2009-08-15 00:00:00|121|240|1.872018|

The result of the above query is:

||invoice||invoice_date||code||s||av||dim||
|009831|2009-08-15 00:00:00|1|350.0|1.070261|1|
|009831|2009-08-15 00:00:00|6|960.0|1.070261|1|
|009843|2009-08-15 00:00:00|4|648.0|1.515264|1|
|009843|2009-08-15 00:00:00|5|170.0|2.585525|1|
|009847|2009-08-15 00:00:00|4|240.0|1.872018|1|
|009847|2009-08-15 00:00:00|5|60.0|2.585525|1|
|009855|2009-08-15 00:00:00|1|480.0|1.070261|1|
|009855|2009-08-15 00:00:00|3|540.0|1.158511|1|
|009855|2009-08-15 00:00:00|7|768.0|1.122835|1|
|009857|2009-08-15 00:00:00|5|115.0|2.585525|1|
|009857|2009-08-15 00:00:00|7|440.0|1.498365|1|
|009866|2009-08-15 00:00:00|1|378.0|1.336887|1|
|009866|2009-08-15 00:00:00|2|510.0|1.605391|1|
|009866|2009-08-15 00:00:00|3|558.0|1.391339|1|
|009866|2009-08-15 00:00:00|5|435.0|2.585525|1|
|009866|2009-08-15 00:00:00|7|736.0|1.498365|1|

As you can see, count(\*) (the "dim" column) always returns 1, even if it should return 2 for the product with id 126 in invoices 009843 and 009847.

If needed, I can provide you the two source models, and a dump of the two DBs.

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira