[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:39:00 EDT 2016
[ https://issues.jboss.org/browse/TEIID-4405?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13282401#comment-13282401 ]
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)
More information about the teiid-issues
mailing list