[teiid-issues] [JBoss JIRA] (TEIID-4405) delete using in statement does not remove records from temporary table
Bram Gadeyne (JIRA)
issues at jboss.org
Tue Aug 23 02:38:00 EDT 2016
Bram Gadeyne created TEIID-4405:
-----------------------------------
Summary: 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)
More information about the teiid-issues
mailing list