sql: support correlated subquery in DELETE WHERE clause

[knz]

Is your feature request related to a problem? Please describe.

Found via the Hibernate test org.hibernate.test.hql.BulkManipulationTest.testDeleteWithSubquery

        delete
        from
            MANY_TO_MANY
        where
            (
                IN_ID
            ) in (
                select
                    ID
                from
                    SIMPLE_1
                where
                    (
                        select
                            count(associated1_.SIMPLE_1_ID)
                        from
                            SIMPLE_2 associated1_
                        where
                            SIMPLE_1.ID=associated1_.SIMPLE_1_ID
                    )=0
                    and (
                        NAME like '%'
                    )
                )

[knz]

Another via org.hibernate.test.hql.DeleteWithSubqueryTest.testDeleteMemberOf

        delete
        from
            Attrvalue
        where
            id in (
                select
                    attrvalue4_.id
                from
                    Employee employee1_ cross
                join
                    Employeegroup employeegr2_ cross
                join
                    Attrset attrset3_ cross
                join
                    Attrvalue attrvalue4_
                where
                    employeegr2_.id=employee1_.employeegroup_id
                    and attrset3_.id=employee1_.attrset_id
                    and (
                        attrvalue4_.id in (
                            select
                                attrvalues5_.attrvalues_id
                            from
                                ATTRSET_X_ATTRVALUE attrvalues5_
                            where
                                attrset3_.id=attrvalues5_.Attrset_id
                        )
                    )
                )

[awoods187]

@andy-kimball would this work once mutations are supported in 2.2? Would this need the apply operator @jordanlewis ?

[knz]

Yes optimizer knowledge of mutation will be 70% of the work needed here. For the requirement on apply, it will depend on the query I think. Not sure about those quoted above.

[andy-kimball]

Yes, I'd expect these to work once we added CRUD support to the optimizer. They shouldn't need Apply.

[BramGruneir]

Closed thanks to #33524.

[BramGruneir]

One thing to note is that this probably won't work with cascading yet. Probably worth opening a new issue.

…

On Mon, Feb 4, 2019, 15:02 Bram Gruneir ***@***.*** wrote: Closed #31670 <#31670>. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#31670 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABihuT1vamX1AgdT_XehA_mv5NMcdd2kks5vKJHOgaJpZM4Xx-_j> .