Consider allowing DML in conditionals

[msullivan]

I think we could do it without too much trouble.
The key insight here is that we actually already do support conditional DML: DML can appear inside of FOR statements, and so the number of times a DML statement is executed (including possibly zero) depends on the value of the for iterator.

Then,

IF_BRANCH if COND else ELSE_BRANCH

is (modulo the fact that it adds a fence around COND and potentially increases the inferred cardinality)
equivalent to

for b in COND union (
  {
    (for _ in (select () filter b) union (IF_BRANCH)),
    (for _ in (select () filter not b) union (ELSE_BRANCH)),
  }
)

[elprans]

Also, COND must not be correlated with anything, as is the general rule for DML dependencies.

[msullivan]

And if we don't do this, we should document this sort of pattern somewhere

(But let's just do it)

[avedetvedea]

@msullivan I have used your for-loop workaround that you provided in another thread to do some pretty complicated things and never hit a bug. The only problem is I have crazy for loops in my queries when I want to do conditional-DMLs. Looking forward to having this feature which is so coherent with the EQL's do-all-in-a-single-query ethos

[AnsonHwang86]

Waiting for this crazy pattern.