5:30 a.m.
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
6:47 a.m.
New subject: [JBoss JIRA] Updated: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Claudio Venturini updated TEIID-1458:
-------------------------------------
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003 (was: 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)
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
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
7:03 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Claudio Venturini commented on TEIID-1458:
------------------------------------------
Looking at the command log, I see that Teiid pushes the COUNT, AVG and SUM operations to
SQL Server, and the result of this query is correct. Maybe Teiid does something wrong in
the execution of the join. It seems that when joining Teiid discards the result of the
COUNT operation coming from the SQL server.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
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
7:32 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1458:
---------------------------------------
The problem is in the logic that decomposes the aggregation/grouping across the join. It
is not properly considering count(*). The workaround for your query would be to use
count(SL.product_id) instead of count(*). I'll have a look at fixing this.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
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
8:13 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Claudio Venturini commented on TEIID-1458:
------------------------------------------
Thanks Steven, but your hint doesn't solve the problem. If I use COUNT(SL.product_id)
instead of COUNT(\*) the result set is the same.
I get a correct result set only if I use COUNT(SL.price) or COUNT(SL.quantity). I thought
this behavior could be caused by the fact that price and quantity have distinct values in
the rows of the group. To check I added a third record for the invoice number 009843 and
product 126, with quantity 145 and price 2.763902, so that now there is a completely
duplicate record, for that invoice and product. Adding this record the result of
COUNT(SL.quantity) and COUNT(SL.price) is 3, so it is still correct.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
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
9:47 a.m.
New subject: [JBoss JIRA] Issue Comment Edited: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins edited comment on TEIID-1458 at 2/1/11 10:46 AM:
----------------------------------------------------------------
updated the rulepushaggregates logic to properly account for aggregates that need computed
after the join is preformed. this currently means that count(\*) will prevent the
optimization from taking place.
was (Author: shawkins):
updated the rulepushaggregates logic to properly account for aggregates that need
computed after the join is preformed. this currently means that count(*) will prevent the
optimization from taking place.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
9:47 a.m.
New subject: [JBoss JIRA] Resolved: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1458.
-----------------------------------
Fix Version/s: 7.4
Resolution: Done
updated the rulepushaggregates logic to properly account for aggregates that need computed
after the join is preformed. this currently means that count(*) will prevent the
optimization from taking place.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
9:47 a.m.
New subject: [JBoss JIRA] Issue Comment Edited: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins edited comment on TEIID-1458 at 2/1/11 10:47 AM:
----------------------------------------------------------------
The problem is in the logic that decomposes the aggregation/grouping across the join. It
is not properly considering count(\*). The workaround for your query would be to use
count(SL.product_id) instead of count(\*). I'll have a look at fixing this.
was (Author: shawkins):
The problem is in the logic that decomposes the aggregation/grouping across the join.
It is not properly considering count(*). The workaround for your query would be to use
count(SL.product_id) instead of count(*). I'll have a look at fixing this.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
10:03 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Claudio Venturini commented on TEIID-1458:
------------------------------------------
Thank you Steven.
Is there some way to patch a current installation of Teiid 7.3, without being forced to
use Teiid 7.4, which is currently under development?
thanks
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
10:13 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1458:
---------------------------------------
Claudio,
There isn't yet much variance with 7.4 if you do a build from trunk. The first alpha
should be available in about 2 to 3 weeks. Another option would be to apply the patch
http://fisheye.jboss.org/rdiff/Teiid?csid=2894&u&N to the 7.3.x branch
https://svn.jboss.org/repos/teiid/branches/7.3.x and build a patched 7.3.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
11:25 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Claudio Venturini commented on TEIID-1458:
------------------------------------------
Ok, I checked out Teiid 7.3 from SVN, and patched it. I confirm that it works.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
11:31 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1458:
---------------------------------------
Thanks Claudio, that's good news.
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
12:48 p.m.
New subject: [JBoss JIRA] Closed: (TEIID-1458) Wrong count(*) result when joining data from two models
[
https://issues.jboss.org/browse/TEIID-1458?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1458.
---------------------------------
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.2, 7.3
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 machine as JBoss, SQL Server 2000 Standard SP4
running on Windows Server 2003
Reporter: Claudio Venturini
Assignee: Steven Hawkins
Labels: count
Fix For: 7.4
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
4813
days inactive
4904
days old
12 comments
2 participants
participants (2)
-
Claudio Venturini (JIRA) -
Steven Hawkins (JIRA)