A bug in MySQL, or working as intended? You decide (opens in new tab)

> What? b is 2? It should be 4. Or produce an error, or something. But not 2. There's no universe in which that result could be considered correct, it's just wrong.

It is correct, and what I would expect based on the behavior in MySQL documentation.

https://dev.mysql.com/doc/refman/8.0/en/insert.html

"An expression expr can refer to any column that was set earlier in a value list."

As the columns are evaluated in left-to-right order, "earlier" in this context means "to the left of".

Therefore:

  INSERT INTO t1 (b,a) values (DEFAULT, 3);

At the time `b` is being evaluated, `a` is to the right of it, so the current value of `a` is its own default value, which is `1`. So, `a+1` will evaluate to `1+1` or `2`.

This is not a bug, this is the documented and expected behavior.

To try and illustrate more clearly, try this:

  CREATE TABLE t1 (a int default 11, b int default 22, c int default (a+b+33));

To start simply:

  INSERT INTO t1 (a, b, c) VALUES (DEFAULT, DEFAULT, DEFAULT);

This gives us:

  SELECT * FROM t1;
  +------+------+------+
  | a    | b    | c    |
  +------+------+------+
  |   11 |   22 |   66 |
  +------+------+------+

No surprises.

Next:

  DELETE FROM t1;
  INSERT INTO t1 (a, b, c) VALUES (44, 55, DEFAULT);
  
  SELECT * FROM t1;
  +------+------+------+
  | a    | b    | c    |
  +------+------+------+
  |   44 |   55 |  132 |
  +------+------+------+

Again, no surprises. Now, let's reorder things a bit:

  DELETE FROM t1;
  INSERT INTO t1 (b, c, a) VALUES (87, DEFAULT, 65);

Knowing that we're evaluating this left-to-right, and `a`, `b` and `c` start out set to their default values as defined in the table schema, what do we expect? <a=65, b=87, c=??>

At the time `c` is being computed though, what is the value of `a`? Is it `65`, or `11`? We would expect it to be equal to `11`, as we haven't evaluated the value of `a` in the INSERT statement, yet.

Therefore, we expect `c = 11 + 87 + 33 = 131` and NOT `c = 65 + 87 + 33 = 185`.

  SELECT * FROM t1;
  +------+------+------+
  | a    | b    | c    |
  +------+------+------+
  |   65 |   87 |  131 |
  +------+------+------+

And, sure enough, there it is.

This is well-defined and expected behavior. Sorry, not a bug.