MySQL Gotchas

In SQL NULL represents the the absence of a value. In MySQL an explicit NULL may also represent the next value of a pseudo-sequence and an implicit NULL may represent an implicit default value (a zero or empty string) determined by MySQL.

CREATE TABLE null_1 (
  id INT NOT NULL,
  text1 VARCHAR(32) NOT NULL,
  text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);

INSERT INTO null_1 (id) VALUES(1);
INSERT INTO null_1 (text1) VALUES('test');

mysql> SELECT * FROM null_1;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
|  1 |       | foo   |
|  0 | test  | foo   |
+----+-------+-------+
2 rows in set (0.00 sec)

Here MySQL has inserted an empty string into column text1 on the first row, and zero into column id on the second row, even though each column is defined as NOT NULL with no default value. As no value was provided in the INSERT statements, these can be considered an attempt to insert implicit NULLs into theses columns, which should normally cause the statements to fail.

Another example of NULL being used to trigger the insertion of an non-NULL is with the TIMESTAMP datatype, where NULL causes the field to be set to the current time:

mysql> CREATE TABLE timestamp (
         id INT,
         ts1 TIMESTAMP(8) NOT NULL,
         ts2 TIMESTAMP(8) 
       );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO timestamp VALUES(1, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp (id, ts1) VALUES(2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp (id, ts2) VALUES(3, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM timestamp;
+------+----------+----------+
| id   | ts1      | ts2      |
+------+----------+----------+
|    1 | 20030625 | 20030625 |
|    2 | 20030625 | 00000000 |
|    3 | 20030625 | 20030625 |
+------+----------+----------+
3 rows in set (0.00 sec)

Note that in every case, even though ts1 has no default value, and even when no value was provided in the INSERT statement, the current timestamp was inserted into the column.

The manual explains this behaviour thus:

Presumably this is a way around the limitation that columns could not be defined with a default of NOW() in MySQL versions prior to 4.1. See also Section 1.5 and Section 1.7.

Note: this behaviour can be changed from version 4.1.1:

CREATE TABLE null_2 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  text1 VARCHAR(32) NOT NULL
);

INSERT INTO null_2 VALUES();
INSERT INTO null_2 (id) VALUES(NULL);

mysql> select * from null_2;
+----+-------+
| id | text1 |
+----+-------+
|  1 |       |
|  2 |       |
+----+-------+
2 rows in set (0.00 sec)

In MySQL this behaviour is necessary, as it is the only way of accessing the AUTO_INCREMENT pseudo-sequence. It is still an odd notion though that inserting a NULL on a NOT NULL column can have this kind of effect.

See below for more fun with AUTO_INCREMENT

Continuing from the previous example:

mysql> INSERT INTO null_2 () VALUES(NULL, 'This should be id 3');
Query OK, 1 row affected (0.01 sec)

mysql>  SELECT * FROM null_2 WHERE id IS NULL;
+----+---------------------+
| id | text1               |
+----+---------------------+
|  3 | This should be id 3 |
+----+---------------------+
1 row in set (0.00 sec)

The first person to spot a NULL value in the result set will receive a stuffed toy dolphin from the author.

There is an explanation though:

All further executions of the same statement provide the expected result:

mysql> SELECT * FROM null_2 WHERE id IS NULL;
Empty set (0.00 sec)

The previous gotcha leads us to this interesting problem, which is possibly the proof that 0 equals 1:

CREATE TABLE exmpl3 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val TEXT
);

INSERT INTO exmpl3 VALUES(0, 'test');

mysql> select * from exmpl3;
+----+------+
| id | val  |
+----+------+
|  1 | test |
+----+------+
1 row in set (0.00 sec)

Isn't this fun? Now let's get ambitious:

CREATE TABLE exmpl4 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val TEXT
);

mysql> INSERT INTO exmpl4 VALUES(-1, 'test');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO exmpl4 VALUES(0, 'test');
Query OK, 1 row affected (0.01 sec)

and then:

mysql> INSERT INTO exmpl4 VALUES(1, 'test');
ERROR 1062: Duplicate entry '1' for key 1
mysql> SELECT * FROM exmpl4;
+----+------+
| id | val  |
+----+------+
| -1 | test |
|  1 | test |
+----+------+
2 rows in set (0.00 sec)

The probable explanation for this is:

The problem here is not the restriction itself, but the combintation of undefined behaviour and the absence of any warnings after inserting a negative value leading to unexpected errors.

One way of changing the value of the AUTO_INCREMENT sequence is with ALTER TABLE ... AUTO_INCREMENT=value. With InnoDB tables however the statement appears to succeed, does not have any effect. Use the syntax SET INSERT_ID=value instead:

mysql> CREATE TABLE exmpl5 (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         val TEXT
       ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test2');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE exmpl5 AUTO_INCREMENT=4;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test4');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM exmpl5;
+----+-------+
| id | val   |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test4 |
+----+-------+
3 rows in set (0.00 sec)

mysql> SET INSERT_ID=5;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test5');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM exmpl5;
+----+-------+
| id | val   |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test4 |
|  5 | test5 |
+----+-------+
4 rows in set (0.00 sec)

An interesting and amusing mistake the author has often made is to forget that ENUM can only contain character values:

mysql> CREATE TABLE enum_exmpl (
         id INT,
         whatever ENUM(0,1)
       );
ERROR 1064: You have an error in your SQL syntax near '0,1) 
       )' at line 3

Oops, my mistake, try again:

mysql> CREATE TABLE enum_exmpl (
         id INT,
         whatever ENUM('0','1')
       );
Query OK, 0 rows affected (0.00 sec)

By the time data is ready to be inserted amnesia has set in:

INSERT INTO enum_exmpl VALUES(1,0);
INSERT INTO enum_exmpl VALUES(2,1);
INSERT INTO enum_exmpl VALUES(3,2);
INSERT INTO enum_exmpl VALUES(4,3);

mysql> SELECT * FROM enum_exmpl;
+------+----------+
| id   | whatever |
+------+----------+
|    1 |          |
|    2 | 0        |
|    3 | 1        |
|    4 |          |
+------+----------+
2 rows in set (0.00 sec)

Two things are happening here:

See: http://dev.mysql.com/doc/mysql/en/ENUM.html for further details.

As MySQL does not have a true boolean datatype it is tempting to use ENUM like this:

mysql> CREATE TABLE enum_exmpl2 (
         id INT,
         whatever ENUM('0','1') NOT NULL DEFAULT 0
       );
ERROR 1067: Invalid default value for 'whatever'

Once again, my mistake. But what if I want the default value to be true?

mysql> CREATE TABLE enum_exmpl2 (
         id INT,
         whatever ENUM('0','1') NOT NULL DEFAULT 1
       );
Query OK, 0 rows affected (0.00 sec)

Syntax OK, looks good, but:

mysql> INSERT INTO enum_exmpl2 (id) VALUES(99);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM enum_exmpl2;
+------+----------+
| id   | whatever |
+------+----------+
|   99 | 0        |
+------+----------+
1 row in set (0.00 sec)

Again, MySQL is performing to specification; to avoid this kind of mistake - DEFAULT 1 instead of DEFAULT '1', be sure to perform all ENUM operations including table creation with character values and not integers.

Note that ENUM also accepts insertion of NULL in columns defined as NOT NULL and as with other datatypes will insert the default value, if defined, or the first value in the ENUM index.

Try this:

mysql> CREATE TABLE casetest (
         id INT,
         string VARCHAR(32) 
       );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO casetest VALUES(1, 'foo');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM casetest WHERE string = 'FOO';
+------+--------+
| id   | string |
+------+--------+
|    1 | foo    |
+------+--------+
1 row in set (0.02 sec)

To prevent this happening, the table should be defined like this:

CREATE TABLE casetest (
  id INT,
  string VARCHAR(32) BINARY
)

The manual says:

Note that MySQL's behaviour in this regard is the diametrical opposite of the default behaviour of most other databases. (Tested: DB2 8.1, Firebird 1.5.1, Oracle 8.1.7 and PostgreSQL 7.4.3, details of other databases welcome).

MySQL limits VARCHAR (aka CHARACTER VARYING) columns to a column length of 255.

A future MySQL version will remove this restriction:

In the MySQL world VARCHAR columns are the opposite of CHAR: instead of being padded, any trailing blanks are stripped.

mysql> CREATE TABLE vtest (id INT, val VARCHAR(32));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO vtest VALUES(1, 'Watch this space -> ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT concat(val, '|') FROM vtest;
+----------------------+
| concat(val, '|')     |
+----------------------+
| Watch this space ->| |
+----------------------+
1 row in set (0.00 sec)

This is documented, and is not a bug:

Use BLOB or TEXT types to store values with trailing blanks.

Update: Apparently this behaviour will be corrected in the planned 5.0 release (see: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html).

Affects: MySQL <4.1

It is not possible to create a column with a default value which is a function or expression, such as NOW():

mysql> CREATE TABLE timestamp_now (
         id INT,
         timestamp TIMESTAMP NOT NULL DEFAULT NOW()
       );
ERROR 1064: You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'NOW())' at line 3

Occasionally you may see the advice to put NOW() in single quotes:

CREATE TABLE timestamp_now (
  id INT,
  timestamp TIMESTAMP NOT NULL DEFAULT 'NOW()'
)

This syntax is accepted and even appears to work:

mysql> INSERT INTO timestamp_now(id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM timestamp_now;
+------+----------------+
| id   | timestamp      |
+------+----------------+
|    1 | 20030625154717 |
+------+----------------+
1 row in set (0.00 sec)

but this is merely MySQL's automagical "first TIMESTAMP column rule" coming into play, where the first TIMESTAMP column is filled with the current timestamp on each INSERT or UPDATE:

mysql> update timestamp_now SET id=2 WHERE id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from timestamp_now;
+------+----------------+
| id   | timestamp      |
+------+----------------+
|    2 | 20030625154826 |
+------+----------------+
1 row in set (0.00 sec)

Note how the timestamp value has changed; the defined default column value has no effect. If this default is defined for any other column value, 'NOW()' is interpreted as a literal string which cannot be cast to a TIMESTAMP or other date type, and which MySQL in its infinite wisdom silently converts to a zero value.

See: http://dev.mysql.com/doc/mysql/en/DATETIME.html for MySQL's take on the matter.

Affects: MySQL <= 3.23.58; MySQL <= 4.0.13

INSERT INTO ... SELECT ... does not work in MySQL if source and target tables are the same.

CREATE TABLE insert_test (
  id INT,
  txt VARCHAR(32)
);

INSERT INTO insert_test VALUES(1, 'foo');
INSERT INTO insert_test VALUES(2, 'bar');

mysql> INSERT INTO insert_test SELECT 3, txt FROM insert_test WHERE id=2;
ERROR 1066: Not unique table/alias: 'insert_test'

The manual says about this:

Other databases tested have no problem with the same construction. The only exception found is Firebird 1.02, which went into an infinite loop; this problem seems to be fixed in Firebird 1.5. (Note: earlier versions of the MySQL manual implied this behaviour was SQL-conform, though not which version of the standard; MySQL tells me the version referred to was SQL-89).

A further idiosyncracy seems to be this behaviour:

CREATE TABLE insert_test2 (
  id INT NOT NULL PRIMARY KEY,
  txt VARCHAR(32)
);
INSERT INTO insert_test2 VALUES(1, 'foo');
INSERT INTO insert_test2 VALUES(2, 'bar');

Now watch this:

mysql> INSERT INTO insert_test2 VALUES(1, 'bar');
ERROR 1062: Duplicate entry '1' for key 1

Correct behaviour... now watch this:

mysql> INSERT INTO insert_test2 (id, txt) SELECT i.id, i.txt FROM insert_test i WHERE i.id=1;
Query OK, 0 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM insert_test2;
+----+------+
| id | txt  |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
2 rows in set (0.00 sec)

Expected behaviour would be for this statement to fail with the same error as above; instead it appears that the statement was successful, with only the Duplicates: 1 notice indicating the statement was effectively ignored.

Update: in version 4.0.20, and possibly previous versions, this statement returns the expected error message.

Note also that the syntax:

INSERT INTO insert_test SELECT 3, 'fubar';

is not supported, although I am not sure whether this is standard SQL.

Note: This is documented by MySQL thus:

MySQL is slightly allergic to comments beginning with a double dash: -- and requires that a space is inserted between the -- and the following comment, e.g.:

mysql> select 1, --comment
    -> 2;
ERROR 1054: Unknown column 'comment' in 'field list'
mysql> select 1, -- comment
    -> 2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.01 sec)

Note: In recent MySQL versions the error message is returned with the code 1064.

See: http://dev.mysql.com/doc/mysql/en/ANSI_diff_comments.html for further information.

Affects: MySQL <= 3.23.58; MySQL <= 4.0.21

If the first SELECT in a UNION join contains literal values, i.e. values not derived from a column, values from subsequent SELECT statements will be truncated to the length of the literal columns:

mysql> CREATE TABLE union_test (
         id INT,
         val1 VARCHAR(10),
         val2 VARCHAR(10)
       );
mysql> INSERT INTO union_test VALUES(1, 'abcdef','123456');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT 'abc' AS c1, '123' AS c2 
          UNION ALL
       SELECT val1 AS c1, val2 AS c2 FROM union_test;
+------+------+
| c1   | c2   |
+------+------+
| abc  | 123  |
| abc  | 123  |
+------+------+
2 rows in set (0.00 sec)

No reason for this behaviour could be found.

In September 2003 this was confirmed to me by someone from MySQL as a bug. It has been fixed in the 4.1.x series, but remains in earlier versions.

Other databases tested (DB2 8.1, Firebird 1.5rc4, Oracle 8.1.7 and PostgreSQL 7.3) displayed the query results as expected with no truncation.

A more serious consequence of this behaviour is this:

mysql> SELECT 'abc' AS c1, '123' AS c2 
          UNION
       SELECT val1 AS c1, val2 AS c2 FROM union_test;
+------+------+
| c1   | c2   |
+------+------+
| abc  | 123  |
+------+------+
1 row in set (0.20 sec)

where ALL is ommitted from the UNION clause. Even though each row contains different values MySQL treats them as being identical - which is clearly not the case.

Note that the UNION clause is supported in MySQL beginning with version 4.0 (see http://dev.mysql.com/doc/mysql/en/UNION.html).

mysql> SELECT 1/0;
+------+

| 1/0  |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

Other databases (tested: DB2 8.1, Firebird 1.5rc4, Oracle 8.1.7, PostgreSQL 7.3.4) all raise a "division by zero" error when performing the same calculation.

See: http://dev.mysql.com/doc/mysql/en/Arithmetic_functions.html

In MySQL || is translated as a logical OR and not as a concatenation operator:

mysql> select 'a' || 'b';
+------------+
| 'a' || 'b' |
+------------+
|          0 |
+------------+
1 row in set (0.01 sec)

The expected result would be: ab. See: http://dev.mysql.com/doc/mysql/en/Logical_Operators.html.

To perform concatenation in MySQL use CONCAT() (see: http://dev.mysql.com/doc/mysql/en/String_functions.html).

To force || to be used as a string concatenation operator rather than a synonym for OR, MySQL must be started in ANSI mode. See: http://dev.mysql.com/doc/mysql/en/ANSI_mode.html.

So - your app messed up. It tried to insert an invalid value into a column. There are two options here: 1) the database throws an error and your app either deals with it gracefully or fails. 2) The database truncates the value, or takes a guess at what might be an alternative value and silently inserts it without giving you the teensiest hint that what you put in is different to what you'll get out.

mysql> CREATE TABLE bounds_test (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         price NUMERIC(4,2),
         code VARCHAR(8),
         numbers_only INT
       );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO bounds_test VALUES (

         99999999999999,
         21474.83,
         'ABCDEFGHIJK',
         'A quick brown dolphin...'
       );
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM bounds_test;
+------------+--------+----------+--------------+
| id         | price  | code     | numbers_only |
+------------+--------+----------+--------------+
| 2147483647 | 999.99 | ABCDEFGH |            0 |
+------------+--------+----------+--------------+
1 row in set (0.01 sec)

(Note: in MySQL 4.1.x, the presence of warnings is notified on the query status line; executing SHOW WARNINGS after the INSERT displays Data truncated messages for each column).

As a nice extra touch note that although the price was defined with a precision of 4 digits, MySQL inserted a number containing 5. Possibly this is because MySQL internally adds an extra "digit" to store a minus sign, and because the storage space is there uses it with positive numbers to pack an extra digit in, if the explanation here: http://dev.mysql.com/doc/mysql/en/Numeric_types.html is anything to go by.

Other databases (tested: Firebird 1.5rc4, Oracle 8.1.7 and PostgreSQL 7.4) raised errors with the same data. On a column defined as NUMERIC(4,2) the highest value accepted in all databases was the expected 99.99.

For another reason why this is seriously bad database mojo see section 3.5

Throughout history many different calendar systems have been developed around the world. Although the way of counting years still varies, most countries and regions have adopted the Roman-Nordic system of months and weekdays - except, ironically enough, a small corner of Scandinavia with a high dolphin population ;-).

mysql> CREATE TABLE datetest (id INT, a_date DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO datetest VALUES(1, '2003-02-31');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM datetest;
+------+------------+
| id   | a_date     |
+------+------------+
|    1 | 2003-02-31 |
+------+------------+
1 row in set (0.00 sec)

So, what's the day before February 31st?

mysql> SELECT DATE_SUB('2003-02-31', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_SUB('2003-02-31', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-03-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

Which is of course two days before the day after February 31st:

mysql> SELECT DATE_ADD('2003-02-31', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2003-02-31', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-03-04                             |
+----------------------------------------+
1 row in set (0.00 sec)

So what kind of checking does MySQL do on date values? A hint:

Obviously, the 31st of February is not malformed enough. Let's try again:

mysql> SELECT DATE_ADD('2003-02-!!!!!!31!!!!!', INTERVAL 1 DAY);
+---------------------------------------------------+
| DATE_ADD('2003-02-!!!!!!31!!!!!', INTERVAL 1 DAY) |
+---------------------------------------------------+
| 2003-03-04                                        |
+---------------------------------------------------+
1 row in set (0.00 sec)

Nope.

mysql> SELECT DATE_ADD('2003-02-99', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2003-02-99', INTERVAL 1 DAY) |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+
1 row in set (0.00 sec

Hmmm. A pattern emerges...

Makes you wonder why they bother...

Addendum: future MySQL versions are scheduled to contain server options which enable sane data checks, although the need to retain backwards-compatibility means these will probably not be enabled by default.

MySQL raises an error if there is a space between a function identifier and its parameter list:

mysql> SELECT MAX(id) FROM datetest;
+---------+
| MAX(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT MAX (id) FROM datetest;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 
'(id) FROM datetest' at line 1

The documentation says:

Most other databases systems (including DB2, Firebird, MSSQL, Oracle and PostgreSQL) do not have a problem with whitespace in this context.