-
1. Re: count(*) issue
shawkins Jan 13, 2016 3:46 PM (in response to kchen007)Which 8.12 version and could you provide the the query plan if possible?
-
2. Re: count(*) issue
kchen007 Jan 13, 2016 4:01 PM (in response to shawkins)8.12.3. here is the debug output from teiid:
thanks,
Kevin
2016-01-13 14:56:27,859 INFO [org.teiid.COMMAND_LOG] (New I/O worker #1) START USER COMMAND: startTime=2016-01-13 14:56:27.859 requestID=eW6VX+tqIyfT.0 txID=null sessionID=eW6VX+tqIyfT applicationName=JDBC principal=edm@dsds-security-domain vdbName=EDM_Oracle vdbVersion=1 sql=select count(*) from CD_WELL
2016-01-13 14:56:27,859 INFO [org.teiid.COMMAND_LOG] (New I/O worker #1) START USER COMMAND: startTime=2016-01-13 14:56:27.859 requestID=eW6VX+tqIyfT.0 txID=null sessionID=eW6VX+tqIyfT applicationName=JDBC principal=edm@dsds-security-domain vdbName=EDM_Oracle vdbVersion=1 sql=select count(*) from CD_WELL
2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread eW6VX+tqIyfT.0 with state NEW
2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread eW6VX+tqIyfT.0 with state NEW
2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 Command has no cache hint and result set cache mode is not on.
2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 Command has no cache hint and result set cache mode is not on.
2016-01-13 14:56:27,879 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 executing select count(*) from CD_WELL
2016-01-13 14:56:27,879 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 executing select count(*) from CD_WELL
2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for delegate
2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for delegate
2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2016-01-13 14:56:27,979 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for eW6VX+tqIyfT.0 ProjectNode(3) output=[IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1] [IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1]
LimitNode(4) output=[anon_grp1.agg0] limit 100
GroupingNode(5) output=[anon_grp1.agg0] null{anon_grp1.agg0=SUM(IFNULL(anon_grp2.agg0, 1))}
AccessNode(6) output=[IFNULL(anon_grp2.agg0, 1)] SELECT IFNULL(anon_grp2.agg0, 1) FROM EDM.CD_WELL AS g_02016-01-13 14:56:27,979 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for eW6VX+tqIyfT.0 ProjectNode(3) output=[IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1] [IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1]
LimitNode(4) output=[anon_grp1.agg0] limit 100
GroupingNode(5) output=[anon_grp1.agg0] null{anon_grp1.agg0=SUM(IFNULL(anon_grp2.agg0, 1))}
AccessNode(6) output=[IFNULL(anon_grp2.agg0, 1)] SELECT IFNULL(anon_grp2.agg0, 1) FROM EDM.CD_WELL AS g_0 -
3. Re: count(*) issue
shawkins Jan 13, 2016 4:20 PM (in response to kchen007)Is CD_WELL a source table, or is there a view in here as well?
-
4. Re: count(*) issue
kchen007 Jan 13, 2016 4:36 PM (in response to shawkins)it is a view in oracle database. but exposed as source table.
-
5. Re: count(*) issue
kchen007 Jan 13, 2016 4:38 PM (in response to kchen007)I tried another one which a table in oracle database and I got the same error.
-
6. Re: count(*) issue
shawkins Jan 13, 2016 4:40 PM (in response to shawkins)The issue will occur if you have a multi-source, count\(*) and a stagable grouping, and the source supports ifnull. So something will need to be logged to capture this.
The workaround around would be to count over a column:
select count(ifnull(col, 1)) from cd_well
Or to disable source support for ifnull.
-
7. Re: count(*) issue
kchen007 Jan 13, 2016 5:11 PM (in response to shawkins)disable the ISNULL function works. but this will may cause issues for our client who maybe use this function. Is it possible to fix this in Teiid?
thanks
Kevin
-
8. Re: count(*) issue
shawkins Jan 13, 2016 5:40 PM (in response to kchen007)> Is it possible to fix this in Teiid?
Yes, of course. Can you log an issue?
-
9. Re: count(*) issue
kchen007 Jan 13, 2016 5:53 PM (in response to shawkins)thanks!!!
[TEIID-3903] for multi-src vdb, the select count(*) does not work for oracle/sqlserver connector
Kevin
-
10. Re: count(*) issue
shawkins Jan 13, 2016 7:57 PM (in response to kchen007)Thanks, the fix will be in 8.13 CR3, but 8.12.4 has already been released.
-
11. Re: count(*) issue
kchen007 Jan 15, 2016 10:40 AM (in response to shawkins)Thanks Steven, I merged your change and it fixed count(*).