Uploaded image for project: 'JBoss Enterprise SOA Platform'
  1. JBoss Enterprise SOA Platform
  2. SOA-2864

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 5.1.0.ER8
    • Fix Version/s: 5.1.0.CR1
    • Component/s: EDS
    • Labels:

      Description

      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:

      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.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  van.halbert Van Halbert
                  Reporter:
                  ventuc Claudio Venturini
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: