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, 8.12.7.6_3 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) ============================================================================