Feature Request: Support Conditional Variables in Vitess

[kaushikd49]

Feature Description

Vitess (v21.0.0) does not seem to support conditional variables that Mysql (tested with 5.7) does. So we are requesting this feature. For more info, please see use-cases section.

-- Create a sample table on 
mysql> create table sample(a int, b text, c int, PRIMARY KEY (a));
Query OK, 0 rows affected (0.01 sec)

mysql> desc sample;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   | PRI | NULL    |       |
| b     | text | YES  |     | NULL    |       |
| c     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.01 sec)

-- [Succeeds] Insert on duplicate key update works
mysql> insert into sample(a,b,c) values (1, "hi",10), (3, "yo",20) ON DUPLICATE KEY UPDATE b = CONCAT((b), "_");
Query OK, 2 rows affected (0.01 sec)

mysql> select * from sample;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | hi   |   10 |
| 3 | yo   |   20 |
+---+------+------+
2 rows in set (0.00 sec)


-- [Fails] Try on duplicate key update with conditional variable 
mysql> insert into sample(a,b,c) values (1, "hi",10), (3, "yo",20) ON DUPLICATE KEY UPDATE c = IF((@cond := a %2 = 1), c+1, c+2), b = IF(@cond, VALUES(b), "b_replaced");
ERROR 1105 (HY000): target: commerce.0.primary: vttablet: rpc error: code = InvalidArgument desc = syntax error at position 203 (CallerID: userData1)
mysql>

On v20.0.0+ of Vitess, we get the error below
VT12001 - This statement is unsupported by Vitess. Please rewrite your query to use supported syntax.

Use Case(s)

• We use conditional inserts (INSERT ON DUPLICATE UPDATE .. as shown above) to update each field of a table.
• The condition happens to depend on the value of other fields.
• Since the condition is repeated as part of the update condition on each field, we use a conditional variable.
• Without the condition variable, we could repeat the condition but it could bloat the query length and require re-writing of queries that are now used on our MySQL instances.

[dbussink]

On main this was silently allowed, but would execute an incorrect query. On v21, it should still be returning the correct error message and not the parse error. With #17751 and associated fixes the behavior is now consistent again.

The feature request here is still pending, but this is a complicated thing to solve so it might take a while.