[JBoss JIRA] (TEIID-2605) Optimization substitutes wrong column in where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2605?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2605.
---------------------------------
> Optimization substitutes wrong column in where clause
> -----------------------------------------------------
>
> Key: TEIID-2605
> URL: https://issues.jboss.org/browse/TEIID-2605
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Environment: z/OS
> Reporter: Jeff Hayes
> Assignee: Steven Hawkins
> Attachments: query_plan.txt, views.xml
>
>
> Optimization results in a query with a different column in the WHERE clause producing an empty result set.
> Full query plan is attached but the beginning and ending queries are shown below. Note that the subject column of the IN clause is SCOPEID but optimization changes it to AUTHID for some reason.
> USER COMMAND:
> SELECT * FROM SECURITY.SCPXREF AS CHORUS_B WHERE (CHORUS_B.SYSID = 'DE29') AND ((CHORUS_B.SCOPEID IN (SELECT SN5.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN5 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN4.SCOPEID FROM SECURITY.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))))) OR (CHORUS_B.SCOPEID IN (SELECT SN4.SCOPEID FROM SECURIT
> Y.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))) OR (CHORUS_B.SC
> OPEID IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.S
> CPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))) OR (CHORUS_B.SCOPEID IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEX
> T AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))) OR (CHORUS_B.SCOPEID = 'CHRDEPT1'))
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(10) output=[x.sysid AS sysid, x.scopeid AS authid, x.authid AS scopeid, x.authtype AS authtype] SELECT g_0.SYSID, g_0.SCO
> PEID, g_0.AUTHID, g_0.AUTHTYPE FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPXREF AS g_0 WHERE (g_0.SYSID = 'DE29') AND ((g_0.AUTHID IN
> (SELECT g_1.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_1 WHERE (g_1.SYSID = 'DE29') AND (g_1.NEXTREC IN (SELECT g_2
> .SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_2 WHERE (g_2.SYSID = 'DE29') AND (g_2.NEXTREC IN (SELECT g_3.SCOPEID FR
> OM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_3 WHERE (g_3.SYSID = 'DE29') AND (g_3.NEXTREC IN (SELECT g_4.SCOPEID FROM SECURITY
> _CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_4 WHERE (g_4.SYSID = 'DE29') AND (g_4.NEXTREC = 'CHRDEPT1'))))))))) OR (g_0.AUTHID IN (SELECT
> g_5.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_5 WHERE (g_5.SYSID = 'DE29') AND (g_5.NEXTREC IN (SELECT g_6.SCOPEI
> D FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_6 WHERE (g_6.SYSID = 'DE29') AND (g_6.NEXTREC IN (SELECT g_7.SCOPEID FROM SECU
> RITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_7 WHERE (g_7.SYSID = 'DE29') AND (g_7.NEXTREC = 'CHRDEPT1'))))))) OR (g_0.AUTHID IN (SELE
> CT g_8.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_8 WHERE (g_8.SYSID = 'DE29') AND (g_8.NEXTREC IN (SELECT g_9.SCOP
> EID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_9 WHERE (g_9.SYSID = 'DE29') AND (g_9.NEXTREC = 'CHRDEPT1'))))) OR (g_0.AUTH
> ID IN (SELECT g_10.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_10 WHERE (g_10.SYSID = 'DE29') AND (g_10.NEXTREC = 'C
> HRDEPT1'))) OR (g_0.AUTHID = 'CHRDEPT1'))
> The view definitions are shown below:
> <view name="SCPNEXT">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="nextrec" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT t.sysid, t.scopeid, t.nextrec
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT n.sysid, n.scopeid, n.nextrec
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPNEXT n
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> <view name="SCPXREF">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="authid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="authtype" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT sysid, scopeid, authid, authtype
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT x.sysid, x.scopeid, x.authid, x.authtype
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPXREF x
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> I guess the best way to test this is to define these views and run the input query with SHOWPLAN=DEBUG and see if the AUTHID substitution is occurring.
> Thanks!
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
1 week, 4 days
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5434.
-----------------------------------
Fix Version/s: 11.0.2
11.1
10.3.4
Resolution: Done
The placement of the select node is due fairly specific circumstances - in this case a union where a branch has a window function. In most instances the select node would be pushed under the source node. Addressed by updating the output columns of any intervening nodes.
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0.2, 11.1, 10.3.4
>
> Attachments: count_plan.txt, count_plan_debug_log.txt, count_plan_text.txt, select_plan.txt, select_plan_debug_log.txt, select_plan_text.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5434:
---------------------------------------
The issue is with the sort optimization that is pulling up the distinct operation on the union to the join node. The intervening select node is having it's output columns left as the reduced set, so that when the distinct is processed it's over just the join predicate column. That is why you are seeing a smaller row count with the count query. I'll have a fix for this today.
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, count_plan_debug_log.txt, count_plan_text.txt, select_plan.txt, select_plan_debug_log.txt, select_plan_text.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5434:
---------------------------------------
The join nodes are planned differently:
JoinNode(3) [Dependent] [ENHANCED SORT JOIN [SEMI] (SORT_DISTINCT/SORT)] [INNER JOIN] criteria=[convert(PatientID, long)=epa.PatientID] output=[]
JoinNode(1) [Dependent] [MERGE JOIN (SORT_DISTINCT/ALREADY_SORTED)] [INNER JOIN] criteria=[convert(PatientID, long)=epa.PatientID] output=[pa.ICUSessionID, DateTime, "Value"]
I'll see if that points to what could be the issue.
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, count_plan_debug_log.txt, count_plan_text.txt, select_plan.txt, select_plan_debug_log.txt, select_plan_text.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-5434:
-------------------------------------
I think you mean the result of the "PLAN_TEXT" column? I've added these aswell as the new debug log files. I can't seem to remove the old debug log files from this thread.
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, count_plan_debug_log.txt, count_plan_text.txt, select_plan.txt, select_plan_debug_log.txt, select_plan_text.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-5434:
--------------------------------
Attachment: count_plan_debug_log.txt
count_plan_text.txt
select_plan_debug_log.txt
select_plan_text.txt
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, count_plan_debug_log.txt, count_plan_text.txt, select_plan.txt, select_plan_debug_log.txt, select_plan_text.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-5434:
-------------------------------------
How can I obtain this query plan after execution? I now used "SET SHOWPLAN DEBUG" before executing the query and "SHOW PLAN" after executing the query.
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, select_plan.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5434:
---------------------------------------
A quick glance at the plans would suggest that they only differ in projected symbols and the additional grouping operation for the count plan. Can you provide the query plan after execution showing the processing node row counts?
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, select_plan.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Work on TEIID-5434 started by Steven Hawkins.
---------------------------------------------
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, select_plan.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-5434:
-------------------------------------
I'm fairly confident that in this case the output of count(*) is inaccurate.
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: count_plan.txt, select_plan.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months