3

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.

6

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>
| improve this answer | |
  • Interesting! Really good answer Rolando. I didn't even think about that. So the column name in the context of INSERT ... VALUES is the default value in MySQL, just like DEFAULT. I wonder if people actually use that and if it's supported/documented. – Evan Carroll Dec 28 '17 at 20:39
  • 1
    In the documentation (dev.mysql.com/doc/refman/5.7/en/insert.html), inserting with INSERT INTO tbl_name () VALUES(); is mentioned. It is allowed except for what the doc says : If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value. – RolandoMySQLDBA Dec 28 '17 at 20:45
  • So I enabled strict mode, It still didn't error. Is that referring to this functionality? It's not clear to me. – Evan Carroll Dec 28 '17 at 20:53
  • When I created your table on my laptop I ran SHOW CREATE TABLE foo\G. I got Create Table: CREATE TABLE foo` ( x varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec). So, x has the default of NULL` hardcoded in. I don't know of any instance of creating a column that does not have a default in place. If you can create a table that literally does not have a default value of any kind, then an error should be manifested. – RolandoMySQLDBA Dec 28 '17 at 20:56
  • 3
    Even with that explanation, PostgreSQL's response seems like a much saner way to handle this case. – Wildcard Dec 29 '17 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.