[teiid-issues] [JBoss JIRA] Resolved: (TEIID-1621) Unexpected grouping for SELECT queries on views that include a GROUP BY

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1621?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-1621.
-----------------------------------

    Fix Version/s: 7.4.1
       Resolution: Done


Aggregate decomposition over a nested join was not appropriately creating staged group by with the needed grouping columns.  This led to unexpected values being projected from the grouping node.

> Unexpected grouping for SELECT queries on views that include a GROUP BY
> -----------------------------------------------------------------------
>
>                 Key: TEIID-1621
>                 URL: https://issues.jboss.org/browse/TEIID-1621
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.4
>         Environment: Teiid 7.4 (with patch) deployed on JBoss AS 5.1, installed on Ubuntu Server 10.04 LTS. Four source models, two of which in MySQL 5.1.51 (on the same machine of JBoss), one on SQLServer 2000 (on another machine), and one on SQL Server 2005 (on another machine). All sources participate in XA transactions.
>            Reporter: Claudio Venturini
>            Assignee: Steven Hawkins
>              Labels: group, grouping, resultset, sum
>             Fix For: 7.4.1
>
>         Attachments: server.log, vdb-project.zip
>
>
> I've a set of models like the one that I've already explained in https://issues.jboss.org/browse/TEIID-1562. In addition I've a physical model, named sole_rugiada_phy, which extracts data from a SQL Server 2005 database. On top of it there's a view model, named sole_rugiada_log, which transform some fields and does some joins grouping.
> In particular the base table named 'vendita_referenza' is defined as follows:
> {code:sql}
> SELECT
> 		SR_op.codop, CAST(SR_v.n_bolla AS VARCHAR) AS codice, SUP_ro.codref, SR_v.data, CAST(SR_v.n_prog AS INTEGER) AS n_prog, CAST(SUM(SR_v.quantita) AS INTEGER) AS quantita, SUM((((SUP_r.quantita / SUP_ro.quantita_reale) * SR_v.quantita) * SR_v.prezzo)) AS fatturato, COUNT(*) AS groupdim
> 	FROM
> 		((sole_rugiada_phy.MISURA124.dbo.VENDITA_REFERENZA AS SR_v INNER JOIN support_log.mediator.referenza_op AS SUP_ro ON RTRIM(LTRIM(CAST(SR_v.codref AS VARCHAR))) = SUP_ro.codice) INNER JOIN support_log.mediator.referenza AS SUP_r ON SUP_ro.codref = SUP_r.codref) INNER JOIN sole_rugiada_log.misura124.solerugiada.op AS SR_op ON SUP_ro.codop = SR_op.codop
> 	WHERE
> 		SR_v.data >= PARSEDATE('20090701', 'yyyyMMdd')
> 	GROUP BY SR_op.codop, SR_v.data, SR_v.n_bolla, SR_v.n_prog, SUP_ro.codref
> {code}
> If I run the following query, that is a simple SELECT statement, everything works fine:
> {code:sql}
> SELECT
>     codop,
>     codice,
>     codref,
>     data,
>     n_prog,
>     quantita,
>     fatturato,
>     groupdim
> FROM
>     sole_rugiada_log.misura124.solerugiada.vendita_referenza
> {code}
> The query above retrieves 18717 records. But if I remove some fields from the SELECT statements something goes wrong. Take for example the following query:
> {code:sql}
> SELECT
>     codop,
>     codref,
>     data,
>     quantita,
>     fatturato
> FROM
>     sole_rugiada_log.misura124.solerugiada.vendita_referenza
> {code}
> The above query gets only one record, which is a grouping over all the record, even if it doesn't include a GROUP BY clause. It's not clear what type of aggregation it does. It seems to be a sum of all records, but the result is not totally exact. The value of 'fatturato' is near the real sum (5043502.561000000 instead of 5046687.249000000), but the value of 'quantita' is much different from the real sum (489566 instead of 5537638).
> I noticed that removing the GROUP BY from the definition of the base table the problem disappears, but, of course, most of times this cannot be used as a workaround.
> Attached there are the project of the VDB and a log of the execution of the query that produces the wrong result.

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