[JBoss JIRA] (TEIID-4402) Insert statement with a date field fails
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4402?page=com.atlassian.jira.plugin... ]
Work on TEIID-4402 started by Steven Hawkins.
---------------------------------------------
> Insert statement with a date field fails
> ----------------------------------------
>
> Key: TEIID-4402
> URL: https://issues.jboss.org/browse/TEIID-4402
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 9.0.2
> Environment: WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Teiid 9.0.2
> jdk1.8.0_101
> Salesforce sandbox
> Salesforce connectors / translators: salesforce and salesforce-34
> Reporter: J Price
> Assignee: Steven Hawkins
>
> I have a custom object created in Salesforce which has date fields. The model has the fields identified as date correctly. When specifying a date using an insert statement I receive an error back from Salesforce:
> _
> org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 Sandbox: Start Date: value not of required type: 2016-03-17_
> I have tried forcing it to a date using the parsedate function as well as passing it as is.
> I setup a proxy to try and identify the cause and found that the datatype being passed in the underlying SOAP request is xsd:string instead of xsd:date.
> Using fiddler I duplicated the underlying SOAP request, but changed the types to xsd:date, and resubmitted the request successfully.
> ----
> As an example I have created a simple custom object in salesforce. The DDL for the imported object is as follows:
> {{
> CREATE TABLE SimpleDateObjectDemo__c (
> Id VARCHAR(18) DEFAULT Generated upon creation NOT NULL,
> OwnerId VARCHAR(18),
> IsDeleted BOOLEAN,
> Name VARCHAR(80),
> Start_Date__c DATE
> );
> }}
> The following query produces the same error:
> {{
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c)
> VALUES ('2001-01-01')
> }}
> The above query results in the following log messages:
> {{
> 14:43:28,743 INFO [org.teiid.PLANNER] (Worker3_QueryProcessorQueue19) ffLmdSxeJfyw
> ============================================================================
> USER COMMAND:
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ----------------------------------------------------------------------------
> OPTIMIZE:
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ----------------------------------------------------------------------------
> GENERATE CANONICAL:
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> CANONICAL PLAN:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> EXECUTING PlaceAccess
> AFTER:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> EXECUTING AssignOutputElements
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count]})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count]})
> ============================================================================
> EXECUTING CalculateCost
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
> ============================================================================
> EXECUTING PlanSorts
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c])
> Project(groups=[])
> Source(groups=[Sandbox.SimpleDateObjectDemo__c])
> ============================================================================
> EXECUTING CollapseSource
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
> PROCESS PLAN =
> AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ============================================================================
> ----------------------------------------------------------------------------
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ============================================================================
> 14:43:30,088 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue20) ffLmdSxeJfyw Connector worker process failed for atomic-request=ffLmdSxeJfyw.0.0.0: org.teiid.translator.TranslatorException: Start Date: value not of required type: 2001-01-01
> at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:121)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:403)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy57.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: javax.resource.ResourceException: Start Date: value not of required type: 2001-01-01
> at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:403)
> at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:368)
> at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:89)
> ... 19 more
> 14:43:30,093 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue21) ffLmdSxeJfyw TEIID30020 Processing exception for request ffLmdSxeJfyw.0 'TEIID30504 Sandbox: Start Date: value not of required type: 2001-01-01'. Originally TeiidProcessingException SalesforceConnectionImpl.java:403. Enable more detailed logging to see the entire stacktrace.
> }}
> The SOAP request produced for the above query is as follows:
> {{
> <?xml version="1.0" encoding="UTF-8"?>
> <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <env:Header>
> <CallOptions xmlns="urn:partner.soap.sforce.com">
> <client>RedHat/MetaMatrix/</client>
> <defaultNamespace xsi:nil="true" />
> </CallOptions>
> <SessionHeader xmlns="urn:partner.soap.sforce.com">
> <sessionId>REMOVED</sessionId>
> </SessionHeader>
> </env:Header>
> <env:Body>
> <m:create xmlns:m="urn:partner.soap.sforce.com" xmlns:sobj="urn:sobject.partner.soap.sforce.com">
> <m:sObjects>
> <sobj:type xsi:type="xsd:string">SimpleDateObjectDemo__c</sobj:type>
> <sobj:Start_Date__c xsi:type="xsd:string">2001-01-01</sobj:Start_Date__c>
> </m:sObjects>
> </m:create>
> </env:Body>
> </env:Envelope>
> }}
> The XMI for the above table is as follows:
> {{
> <relational:BaseTable xmi:uuid="mmuuid:878a18c9-359c-4756-b192-79ed7c91d7af" name="SimpleDateObjectDemo__c" nameInSource="SimpleDateObjectDemo__c">
> <columns xmi:uuid="mmuuid:22b29abc-6d51-475e-891c-6a19a4869cee" name="Id" nameInSource="Id" nativeType="id" length="18" nullable="NO_NULLS" defaultValue="Generated upon creation" uniqueKeys="mmuuid/f1d6a7d7-cdff-4bc9-b1df-61096c31aacf">
> <type href="http://www.w3.org/2001/XMLSchema#string"/>
> </columns>
> <columns xmi:uuid="mmuuid:4b64550b-057f-49d5-ac44-6f4275508a0c" name="OwnerId" nameInSource="OwnerId" nativeType="id" length="18">
> <type href="http://www.w3.org/2001/XMLSchema#string"/>
> </columns>
> <columns xmi:uuid="mmuuid:cc2969bd-98fd-4918-b6e2-6c0b98407f8b" name="IsDeleted" nameInSource="IsDeleted" nativeType="boolean">
> <type href="http://www.w3.org/2001/XMLSchema#boolean"/>
> </columns>
> <columns xmi:uuid="mmuuid:0c42849d-7b8c-4bc9-8472-bf976c9946db" name="Name" nameInSource="Name" nativeType="string" length="80">
> <type href="http://www.w3.org/2001/XMLSchema#string"/>
> </columns>
> <columns xmi:uuid="mmuuid:c085614b-49b8-4e30-810b-13f0e8d337c5" name="Start_Date__c" nameInSource="Start_Date__c" nativeType="date">
> <type href="http://www.w3.org/2001/XMLSchema#date"/>
> </columns>
> <primaryKey xmi:uuid="mmuuid:f1d6a7d7-cdff-4bc9-b1df-61096c31aacf" name="Id_PK" columns="mmuuid/22b29abc-6d51-475e-891c-6a19a4869cee"/>
> </relational:BaseTable>
> }}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4351) JDG: Does not support 1-to-1 relationship
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4351?page=com.atlassian.jira.plugin... ]
Work on TEIID-4351 started by Van Halbert.
------------------------------------------
> JDG: Does not support 1-to-1 relationship
> -----------------------------------------
>
> Key: TEIID-4351
> URL: https://issues.jboss.org/browse/TEIID-4351
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.6
> Reporter: Van Halbert
> Assignee: Van Halbert
>
> When the primary object has a defined 1-to-1 relationship, the internal relationship isn't generating a second table by the metadata processor, even though its defined in the protobuf definition file and the marshallers. And when the second table is manually built, and then tried to preview, the server is throwing a methodNotFound exception, because its looking back on the primary object for the method, not the internal object.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4405) delete using in statement does not remove records from temporary table
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4405?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-4405:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1369461
Bugzilla Update: Perform
> delete using in statement does not remove records from temporary table
> ----------------------------------------------------------------------
>
> Key: TEIID-4405
> URL: https://issues.jboss.org/browse/TEIID-4405
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 9.1, 8.13.7, 9.0.4
>
>
> Hi,
> I have a temporary table called #tmp_cohort that was constructed using this create statement.
> create local temporary table #tmp_cohort(
> patientid integer not null,
> age float not null,
> sex string not null,
> patgroup string not null,
> admtime timestamp not null,
> distime timestamp not null,
> los long not null,
> icuoutcome string,
> hospoutcome string,
> PRIMARY KEY(patientid)
> );
> After filling it up it contains 12230 records. I only want to keep 10 records and remove the rest.
> When I do the following select it does indeed return the 10 records I want to keep.
> select *
> from #tmp_cohort c
> where c.patientid in (24123,55785,16667,53701,30763,59762,22679,46328,46453,55956)
> The delete command however returns "0 rows deleted":
> delete from #tmp_cohort
> where patientid not in (24123,55785,16667,53701,30763,59762,22679,46328,46453,55956);
> This is the query plan:
> ============================================================================
> USER COMMAND:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ----------------------------------------------------------------------------
> OPTIMIZE:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ----------------------------------------------------------------------------
> GENERATE CANONICAL:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> CANONICAL PLAN:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING PlaceAccess
> AFTER:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING AssignOutputElements
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count]})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), OUTPUT_COLS=[Count]})
> ============================================================================
> EXECUTING CalculateCost
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0})
> ============================================================================
> EXECUTING PlanSorts
> AFTER:
> Access(groups=[#tmp_cohort])
> Project(groups=[])
> Source(groups=[#tmp_cohort])
> ============================================================================
> EXECUTING CollapseSource
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}, ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
> PROCESS PLAN =
> AccessNode(0) output=[Count] DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ============================================================================
> ----------------------------------------------------------------------------
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ============================================================================
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4405) delete using in statement does not remove records from temporary table
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4405?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4405:
----------------------------------
Component/s: Query Engine
Fix Version/s: 9.1
8.13.7
9.0.4
Priority: Blocker (was: Critical)
> delete using in statement does not remove records from temporary table
> ----------------------------------------------------------------------
>
> Key: TEIID-4405
> URL: https://issues.jboss.org/browse/TEIID-4405
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 9.1, 8.13.7, 9.0.4
>
>
> Hi,
> I have a temporary table called #tmp_cohort that was constructed using this create statement.
> create local temporary table #tmp_cohort(
> patientid integer not null,
> age float not null,
> sex string not null,
> patgroup string not null,
> admtime timestamp not null,
> distime timestamp not null,
> los long not null,
> icuoutcome string,
> hospoutcome string,
> PRIMARY KEY(patientid)
> );
> After filling it up it contains 12230 records. I only want to keep 10 records and remove the rest.
> When I do the following select it does indeed return the 10 records I want to keep.
> select *
> from #tmp_cohort c
> where c.patientid in (24123,55785,16667,53701,30763,59762,22679,46328,46453,55956)
> The delete command however returns "0 rows deleted":
> delete from #tmp_cohort
> where patientid not in (24123,55785,16667,53701,30763,59762,22679,46328,46453,55956);
> This is the query plan:
> ============================================================================
> USER COMMAND:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ----------------------------------------------------------------------------
> OPTIMIZE:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ----------------------------------------------------------------------------
> GENERATE CANONICAL:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> CANONICAL PLAN:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING PlaceAccess
> AFTER:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING AssignOutputElements
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count]})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), OUTPUT_COLS=[Count]})
> ============================================================================
> EXECUTING CalculateCost
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0})
> ============================================================================
> EXECUTING PlanSorts
> AFTER:
> Access(groups=[#tmp_cohort])
> Project(groups=[])
> Source(groups=[#tmp_cohort])
> ============================================================================
> EXECUTING CollapseSource
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}, ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
> PROCESS PLAN =
> AccessNode(0) output=[Count] DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ============================================================================
> ----------------------------------------------------------------------------
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ============================================================================
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4151) AssertionError: Delete failed
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4151?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4151:
---------------------------------------
The fix here was incomplete - see TEIID-4405
> AssertionError: Delete failed
> -----------------------------
>
> Key: TEIID-4151
> URL: https://issues.jboss.org/browse/TEIID-4151
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.11.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.4
>
>
> When using a query like
> insert into #CaresequencesDaily
> some large select;
> delete
> from #CaresequencesDaily
> where datum < (select cast(startime as date) from #period);
> delete
> from #CaresequencesDaily
> where datum > (select cast(endtime as date) from #period);
> We get the following exception on the execution of the second delete query. It seems it does not matter in what order the delete queries are executed.
> Unexpected exception for request lMZm1kGe28/C.24: java.lang.AssertionError: Delete failed
> at org.teiid.query.tempdata.TempTable.deleteTuple(TempTable.java:801) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.tempdata.TempTable.access$500(TempTable.java:83) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.tempdata.TempTable$2.tuplePassed(TempTable.java:775) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.tempdata.TempTable$UpdateProcessor.process(TempTable.java:257) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.tempdata.TempTable.delete(TempTable.java:783) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.tempdata.TempTableDataManager$1.createTupleSource(TempTableDataManager.java:242) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.tempdata.TempTableDataManager$ProxyTupleSource.nextTuple(TempTableDataManager.java:109) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:457) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:267) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.11.3.jar:8.11.3]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.11.3.jar:8.11.3]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_60]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_60]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_60]
> I've tried to create a reproducable example but this doesn't throw the exception. It might be helpfull to understand what is goiing on.
> insert into #tmp_params
> select parsetimestamp('2016-04-01','yyyy-MM-dd') as starttime, parsetimestamp('2016-04-15','yyyy-MM-dd') as endtime;
> insert into #tmp_dates
> select cast(parsetimestamp('2016-03-20','yyyy-MM-dd') as date) as datum, 'somevalue' as somevalue
> UNION select cast(parsetimestamp('2016-04-02','yyyy-MM-dd') as date) as datum, 'somevalue' as somevalue
> UNION select cast(parsetimestamp('2016-04-20','yyyy-MM-dd') as date) as datum, 'somevalue' as somevalue;
> delete
> from #tmp_dates
> where datum > (select cast(endtime as date) from #tmp_params);
> --error is thrown when executing this second statement
> delete
> from #tmp_dates
> where datum < (select cast(starttime as date) from #tmp_params);
> select *
> from #tmp_dates
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4404) Install CLI should pre-run before dist build
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4404?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4404:
-------------------------------------
What you mention above is not true, for at least standalone mode. One can execute
{code}
./bin/add-user.sh -a -u teiidUser -p password1! -g user,odata,rest
./bin/standalone.sh -c standalone-teiid.xml
{code}
That is closest we can get to. One possibility is, in full server distro we can remove the need to use "standalone-teiid.xml" and rename this to standalone.xml. We can also look into doing the same with domain.xml.
> Install CLI should pre-run before dist build
> --------------------------------------------
>
> Key: TEIID-4404
> URL: https://issues.jboss.org/browse/TEIID-4404
> Project: Teiid
> Issue Type: Feature Request
> Affects Versions: 9.1
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
>
> In teiid we have provide a server dist(http://teiid.jboss.org/downloads/, With WildFly), but it not install teiid in advanced, after download the dist, we have to execute
> {code}
> ./bin/standalone.sh
> ./bin/jboss-cli.sh --connect --file=bin/scripts/teiid-standalone-mode-install.cli
> ./bin/domain.sh
> ./bin/jboss-cli.sh --connect --file=bin/scripts/teiid-domain-mode-install.cli
> ./bin/add-user.sh -a -u dashboardAdmin -p password1! -g admin
> ./bin/add-user.sh -a -u teiidUser -p password1! -g user
> ./bin/add-user.sh -a -u restUser -p password1! -g rest
> ./bin/add-user.sh -a -u odataUser -p password1! -g odata
> ./bin/add-user.sh admin password1!
> {code}
> However, in other JBoss product, like infinispan(http://infinispan.org/download/), jbpm, the server dist, download can run directly, do not need run any install cli.
> So, I do wonder if we can do some adjustment that make teiid compatible with other jboss product.
> In addition, as [1], configure api depend on a server dist which can run directly, now, it have to start teiid server manually.
> [1] https://github.com/teiid/wildfly-swarm-teiid/tree/master/config-api
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4406) group by cast(... as date) results in NullPointException
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4406?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-4406:
--------------------------------
Workaround Description: group by FORMATTIMESTAMP(oc.StartOfPeriod, 'yyyy-MM-dd')
Workaround: Workaround Exists
> group by cast(... as date) results in NullPointException
> --------------------------------------------------------
>
> Key: TEIID-4406
> URL: https://issues.jboss.org/browse/TEIID-4406
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
>
> example query:
> select oc.patientid, cast(oc.StartOfPeriod as date) as periodstart,
> max(case when variableid = 10001001 then oc.Value end) as sofa_resp,
> max(case when variableid = 10001002 then oc.Value end) as sofa_cardio,
> max(case when variableid = 10001003 then oc.Value end) as sofa_liver,
> max(case when variableid = 10001004 then oc.Value end) as sofa_coag,
> max(case when variableid = 10001005 then oc.Value end) as sofa_cns,
> max(case when variableid = 10001006 then oc.Value end) as sofa_renal,
> max(case when variableid = 30010000 then oc.Value end) as sofa_total
> from #tmp_cohort10 c
> join oldcos_scores oc on oc.PatientID = c.patientid
> group by oc.patientid, cast(oc.StartOfPeriod as date)
> stacktrace:
> 2016-08-23 11:46:25,263 ERROR [org.teiid.PROCESSOR] (Worker50_QueryProcessorQueue784) BCXKsyPCGz6r TEIID30019 Unexpected exception for request BCXKsyPCGz6r.148: java.lang.NullPointerException
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:426)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:418)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:281)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:226)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:386)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:344)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:276)
> at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:184)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.<init>(ConnectorWorkItem.java:175)
> at org.teiid.dqp.internal.datamgr.ConnectorManager.registerRequest(ConnectorManager.java:203)
> at org.teiid.dqp.internal.process.DataTierManagerImpl.registerRequest(DataTierManagerImpl.java:1011)
> at org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:179)
> at org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:500)
> at org.teiid.query.processor.relational.AccessNode.openInternal(AccessNode.java:268)
> at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:171)
> at org.teiid.query.processor.relational.JoinStrategy.openRight(JoinStrategy.java:105)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:223)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:94)
> at org.teiid.query.processor.relational.GroupingNode.groupSortPhase(GroupingNode.java:490)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:366)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4406) group by cast(... as date) results in NullPointException
by Bram Gadeyne (JIRA)
Bram Gadeyne created TEIID-4406:
-----------------------------------
Summary: group by cast(... as date) results in NullPointException
Key: TEIID-4406
URL: https://issues.jboss.org/browse/TEIID-4406
Project: Teiid
Issue Type: Bug
Affects Versions: 9.0.2
Reporter: Bram Gadeyne
Assignee: Steven Hawkins
example query:
select oc.patientid, cast(oc.StartOfPeriod as date) as periodstart,
max(case when variableid = 10001001 then oc.Value end) as sofa_resp,
max(case when variableid = 10001002 then oc.Value end) as sofa_cardio,
max(case when variableid = 10001003 then oc.Value end) as sofa_liver,
max(case when variableid = 10001004 then oc.Value end) as sofa_coag,
max(case when variableid = 10001005 then oc.Value end) as sofa_cns,
max(case when variableid = 10001006 then oc.Value end) as sofa_renal,
max(case when variableid = 30010000 then oc.Value end) as sofa_total
from #tmp_cohort10 c
join oldcos_scores oc on oc.PatientID = c.patientid
group by oc.patientid, cast(oc.StartOfPeriod as date)
stacktrace:
2016-08-23 11:46:25,263 ERROR [org.teiid.PROCESSOR] (Worker50_QueryProcessorQueue784) BCXKsyPCGz6r TEIID30019 Unexpected exception for request BCXKsyPCGz6r.148: java.lang.NullPointerException
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:426)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:418)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:281)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:226)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:386)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:344)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:276)
at org.teiid.dqp.internal.datamgr.LanguageBridgeFactory.translate(LanguageBridgeFactory.java:184)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.<init>(ConnectorWorkItem.java:175)
at org.teiid.dqp.internal.datamgr.ConnectorManager.registerRequest(ConnectorManager.java:203)
at org.teiid.dqp.internal.process.DataTierManagerImpl.registerRequest(DataTierManagerImpl.java:1011)
at org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:179)
at org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:500)
at org.teiid.query.processor.relational.AccessNode.openInternal(AccessNode.java:268)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:171)
at org.teiid.query.processor.relational.JoinStrategy.openRight(JoinStrategy.java:105)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:223)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:94)
at org.teiid.query.processor.relational.GroupingNode.groupSortPhase(GroupingNode.java:490)
at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:366)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4405) delete using in statement does not remove records from temporary table
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4405?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-4405:
-------------------------------------
Maybe one extra note. I have a second connection that also creates the same temporary table. But as far as I know this should not be a problem?
> delete using in statement does not remove records from temporary table
> ----------------------------------------------------------------------
>
> Key: TEIID-4405
> URL: https://issues.jboss.org/browse/TEIID-4405
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
>
> Hi,
> I have a temporary table called #tmp_cohort that was constructed using this create statement.
> create local temporary table #tmp_cohort(
> patientid integer not null,
> age float not null,
> sex string not null,
> patgroup string not null,
> admtime timestamp not null,
> distime timestamp not null,
> los long not null,
> icuoutcome string,
> hospoutcome string,
> PRIMARY KEY(patientid)
> );
> After filling it up it contains 12230 records. I only want to keep 10 records and remove the rest.
> When I do the following select it does indeed return the 10 records I want to keep.
> select *
> from #tmp_cohort c
> where c.patientid in (24123,55785,16667,53701,30763,59762,22679,46328,46453,55956)
> The delete command however returns "0 rows deleted":
> delete from #tmp_cohort
> where patientid not in (24123,55785,16667,53701,30763,59762,22679,46328,46453,55956);
> This is the query plan:
> ============================================================================
> USER COMMAND:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ----------------------------------------------------------------------------
> OPTIMIZE:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ----------------------------------------------------------------------------
> GENERATE CANONICAL:
> DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> CANONICAL PLAN:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING PlaceAccess
> AFTER:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> EXECUTING AssignOutputElements
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count]})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), OUTPUT_COLS=[Count]})
> ============================================================================
> EXECUTING CalculateCost
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
> Source(groups=[#tmp_cohort], props={ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), VIRTUAL_COMMAND=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956), OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0})
> ============================================================================
> EXECUTING PlanSorts
> AFTER:
> Access(groups=[#tmp_cohort])
> Project(groups=[])
> Source(groups=[#tmp_cohort])
> ============================================================================
> EXECUTING CollapseSource
> AFTER:
> Access(groups=[#tmp_cohort], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[Count], EST_CARDINALITY=12230.0, EST_COL_STATS={Count=[-1.0, -1.0]}, ATOMIC_REQUEST=DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)})
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
> PROCESS PLAN =
> AccessNode(0) output=[Count] DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ============================================================================
> ----------------------------------------------------------------------------
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] DELETE FROM #tmp_cohort WHERE #tmp_cohort.patientid NOT IN (24123, 55785, 16667, 53701, 30763, 59762, 22679, 46328, 46453, 55956)
> ============================================================================
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months