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.
Best Answer
When you created the table with this command
column
x
was defined with an implicit default ofNULL
.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 ifNULL
equals 5. In terms of symantics,NULL
cannot by compared with anything:not even with
NULL
itselfIt needs a content context (if that makes any sense).
Anyway, when you tried to evaluate
x=5
, that gets processed internally as evaluatingx
, which of course, isNULL
with the row coming into existence, followed by making a comparison with a non-NULL value of5
. Since the expression must evaluate asNULL
, you essentially did this:which has the same effect as as doing this