What does k=v do in the context of INSERT INTO tbl VALUES (expr)

Coming off this answer, I'm wondering why the lexer allowed it to get all the way down to the geometry grammar in this INSERT statement

CREATE TABLE foo ( x varchar(255) );
> INSERT INTO foo VALUES ( x='foo' );
Query OK, 1 row affected (0.01 sec)

I know that's not right, and as the answer above indicates that it should be INSERT INTO tbl SET, but what's happening? How is MySQL interpreting that? In PostgreSQL, such a syntax is rejected.

test=# CREATE TABLE foo ( x varchar(255) );
INSERT INTO foo VALUES ( x='foo' );

PostgreSQL interprets it as a column reference and errors out, further providing a hint

ERROR:  column "x" does not exist
LINE 1: INSERT INTO foo VALUES ( x='foo' );
HINT:  There is a column named "x" in table "foo", but it cannot be referenced
from this part of the query.

MySQL Seems to check to make sure the field name exists at run time but for what end I have no idea.

> INSERT INTO foo VALUES ( DOESNOTEXIST=5 );
ERROR 1054 (42S22): Unknown column 'DOESNOTEXIST' in 'field list'
> INSERT INTO foo VALUES ( x=5 );
Query OK, 1 row affected (0.01 sec)

If it does not exist you get an error. I'm not sure what happens if it does exist though. NULL is inserted, but why? Why that would be any less of an error. Can someone explain?

I know MySQL has a User-Defined Variable syntax, however I don't think that has anything to do with it here.

When you created the table with this command

CREATE TABLE foo ( x varchar(255) );

columnx was defined with an implicit default of NULL.

When you compare a non-existent variable with a real value as you did before you get NULL.

When it comes to the expression you gave, x=5, you are asking if NULL equals 5. In terms of symantics, NULL cannot by compared with anything:

mysql> select NULL = 5;
+----------+
| NULL = 5 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

not even with NULL itself

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

It needs a content context (if that makes any sense).

Anyway, when you tried to evaluate x=5, that gets processed internally as evaluating x, which of course, is NULL with the row coming into existence, followed by making a comparison with a non-NULL value of 5. Since the expression must evaluate as NULL, you essentially did this:

mysql> insert into foo values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql>

which has the same effect as as doing this

mysql> insert into foo values ();
Query OK, 1 row affected (0.01 sec)

mysql>

StackExchange Database Administrators Q#194120, answer score: 6