-
1. Re: Count(*) returns 1
From the plan this is fully pushed down, so the heart of the issues is at the source.
The source query is:
SELECT COUNT(*) FROM (SELECT 1 AS c_0 FROM izisprod.prod_P_GeneralData AS g_0, izisprod.prod_P_PharmaRec AS g_1, izisprod.prod_P_PharmaOrder AS g_2, izisprod.prod_S_PharmaRef AS g_3, izisprod.prod_S_WardRef AS g_4 WHERE (g_0.PatientID = g_1.PatientID) AND (g_2.OrderNumber = g_1.OrderNumber) AND (g_3.PharmaID = g_1.PharmaID) AND (g_4.WardID = g_0.WardID) AND (g_0.PatGroup IN (120, 125, 126, 127)) AND ((g_0.Status IN (1, 4)) OR (g_0.ResearchPat = 1)) AND (g_0.AdmissionTime >= {ts'2014-01-01 00:00:00.0'}) AND (g_1.GivenDose <> 0.0) AND (g_1.GivenDose IS NOT NULL) GROUP BY g_2.OrderNumber, g_3.PharmaID, g_3.GenericName, g_3.PharmaName, g_4.Abbreviation, g_2.OrderTime) AS v_0
What source is it, and can you check what the query and inline view return from the source directly?
-
2. Re: Count(*) returns 1
Hi Steven,
Thank you for this quick reply! This query is executed against a Sybase Server. The inner query only returns 1 row containing the value 1.
If the "select 1" is replaced by select g_2.OrderNumber, g_3.PharmaID, g_3.GenericName, g_3.PharmaName, g_4.Abbreviation, g_2.OrderTime then indeed all the rows apear.
Intuitivaly I would expect Sybase to return 1 row par group containing 1 field with value 1.
I suppose this is a Sybase issue.
-
3. Re: Count(*) returns 1
> I suppose this is a Sybase issue.
Yes it would appear so. The select logically happens after the grouping and should produce a 1 row for each grouping, then the count should total those. You can log an issue for us to workaround their issue. Ideally Teiid could collapse the view layers here or specifically for sybase we could select a column rather than a constant.
-
4. Re: Count(*) returns 1
Hi Steven,
I've created a discussion on the SAP community website. http://scn.sap.com/message/15663951#15663951
Someone already replied:
The ability to write queries with GROUP BY that do not include all the columns from the GROUP BY in the SELECT is a TSQL extension of the ANSI standard, as is a SELECT clause without any aggregates. As the docs say, the results can sometimes be hard to understand.
Substitute an aggregate for the simple literal and I think you will get a result more like what you were expecting (which truthfully doesn't seem very useful to me).
I will test to see if adding an aggregate helps resolving the issue (next week).
-
5. Re: Count(*) returns 1
> I will test to see if adding an aggregate helps resolving the issue (next week).
It shouldn't matter whether it's an aggregate or a grouping column, so yes it's an easy workaround.
What he is saying though does not seem correct. I don't recall anything in the spec requiring grouping columns to be in the select regardless of whether aggregates are present. The results will be hard to understand yes, but you are only looking for a count - it's just written in a non-intuitive way because of Teiid.
The only relevant clause I see is in the spec:
Without Feature T301, “Functional dependencies”, in conforming SQL language, if T is a grouped table,
then in each <value expression> contained in the <select list>, each <column reference> that references a
column of T shall reference a grouping column or be specified in an aggregated argument of a <set function
specification>
Which does not apply to literals.
And for what it's worth this behavior is sybase specific. No other vendor including sqlserver seems to operate that way. And you can find plenty of counter examples: https://mariadb.com/kb/en/sql-99/33-searching-with-groups/rules-for-grouping-columns/
-
-
-
8. Re: Count(*) returns 1
Went ahead and logged/worked this as the workaround is straight-forward [TEIID-3283] Create workaround for sybase grouping issue - JBoss Issue Tracker
-