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:

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

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:

sales:

The result of the above query is:

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.