MySQL Oddities

Tuesday Jul 12th 2005 by Ian Gilfillan
Share:

Don't let MySQL's oddities catch you by surprise. This article introduces some MySQL oddities, and examines some points on general SQL.

Introduction

For a number of years Ian Barwick has maintained an excellent page called MySQL Gotchas. These are MySQL features, which do not work as expected according to either the SQL standard, or the way other common relational databases would do things. I have been sent the link to this page countless times, usually by somebody trying to persuade me I have made a horrible mistake by using MySQL, or that I shouldn't be suggesting MySQL in certain instances.

The list itself is not anti-MySQL, although it has often been used in that context, not only by people sending me the link, but more frequently in the sort of ill-informed forum comment that plagues cyberspace along the lines of:
MySQL sucks - rather use a real database like X. See MySQL Gotchas.
This article introduces some of the oddities to new readers, and examines some of the points raised in the section on general SQL.

NULLS

NULL values in a relational database have always been contentious. Much has been written about it (you can start with Fabian Pascal's critique at DBAzine), but in brief, many argue that permitting NULL's in a database was a mistake, and that the SQL standard is flawed in this respect. Nonetheless NULL's are here to stay, and are supposed to represent the absence of a value. Let's look at what MySQL does differently in this regard. Try this example:



mysql> CREATE TABLE t1 (
 i1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 i2 INT NOT NULL, 
 f1 VARCHAR(10) NOT NULL,
 f2 VARCHAR(10) NOT NULL DEFAULT 'nada', 
 ts TIMESTAMP NOT NULL
);

mysql> INSERT INTO t1 (i1) VALUES(1);

mysql> INSERT INTO t1 (i1,f1) VALUES(NULL,'something');

What would you expect to happen with these examples? In the first example, only the field i1 is supplied. Since the others are defined as NOT NULL, and only f2 has a defined default, you may expect the query to fail, since there are no supplied values for i2, f1 and ts.

Similarly, in the second example, we are attempting to pass NULL into i1, defined as NOT NULL and with no default, as well as not supplying values for i2, f2 and ts. However, both queries run successfully. Here is what is in the table.

mysql> SELECT * FROM t1;
+----+----+-----------+------+---------------------+
| i1 | i2 | f1        | f2   | ts                  |
+----+----+-----------+------+---------------------+
|  1 |  0 |           | nada | 2005-07-06 21:58:15 |
|  2 |  0 | something | nada | 2005-07-06 21:58:40 |
+----+----+-----------+------+---------------------+

The results will be surprising if you are more familiar with another DBMS. MySQL creates default values in certain instances if none are supplied. The default values here are '' (empty string) for the VARCHAR field, 0 for INT field i2 and the current date and time for the TIMESTAMP field ts. The INT field i1 is even more surprising. Although it was specifically assigned the value NULL in the second query, it still contains a value, 2. This is how the MySQL AUTO_INCREMENT sequence works. If the fields were defined as accepting NULLs, MySQL would have inserted a NULL. Instead, since they were defined as NOT NULL, MySQL uses alternative defaults.

There is an even more alarming case, to my mind, where the actual results of query are not consistent, even though ostensibly no data has changed. If you have not run any other queries since the two INSERT statements above, run these two queries below, one after another. Even though they are identical, the result changes.

mysql> SELECT * FROM t1 WHERE i1 IS NULL;
+----+----+-----------+------+---------------------+
| i1 | i2 | f1        | f2   | ts                  |
+----+----+-----------+------+---------------------+
|  2 |  0 | something | nada | 2005-07-06 21:58:40 |
+----+----+-----------+------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE i1 IS NULL;
Empty set (0.00 sec) 

There is certainly no NULL value in i1, but MySQL justifies this exception as being of benefit to Access and Delphi (for determining the last AUTO_INCREMENT value) and possibly other ODBC applications. The full list of defaults MySQL uses vary according to the field type, as follows:

  • For numeric fields except for those defined with AUTO_INCREMENT, the default is '0'.
  • For numeric fields defined with AUTO_INCREMENT column, the default is the next value in the sequence.
  • For string types except for ENUM, the default is an empty string.
  • For the ENUM type, the default is the first enumeration value.
  • For DATETIMEs, the default is '0000-00-00 00:00:00'
  • For DATEs, the default is '0000-00-00'
  • For TIMESTAMPs, the default is the current date and time.
  • For TIMEs, the default is '00:00:00'
  • For YEARs, the default is '0000'

Note that as of MySQL 5.0.2, this default behavior can be changed, and MySQL made to behave in the same way as standard SQL by setting one of the strict modes. I plan an article on this topic in future, but for now you can read more in the MySQL manual section about the Server SQL mode. You can also stop MySQL from accepting zero dates with the NO_ZERO_DATE SQL mode. Zero dates are not standard, and are automatically converted to NULL when using newer versions of MyODBC.

More AUTO_INCREMENT oddities

As we have seen above, AUTO_INCREMENT fields take the next value in the sequence as default, even when specifically supplied a NULL value. Here are a few more cases where things are not quite what they seem.

mysql> INSERT INTO t1 (i1) VALUES (0);
Query OK, 1 row affected (0.06 sec)

Has it done what you would expect? Nope - 0 has become 3.

mysql> SELECT * FROM t1;
+----+----+-----------+------+---------------------+
| i1 | i2 | f1        | f2   | ts                  |
+----+----+-----------+------+---------------------+
|  1 |  0 |           | nada | 2005-07-06 21:58:15 |
|  2 |  0 | something | nada | 2005-07-06 21:58:40 |
|  3 |  0 |           | nada | 2005-07-06 22:33:16 |
+----+----+-----------+------+---------------------+

Let's try some more:

mysql> INSERT INTO t1 (i1) VALUES (-1);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO t1 (i1) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (i1) VALUES (9999999999999999999999999999);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> INSERT INTO t1 (i1) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 1

mysql> SELECT * FROM t1;
+------------+----+-----------+------+---------------------+
| i1         | i2 | f1        | f2   | ts                  |
+------------+----+-----------+------+---------------------+
|          1 |  0 |           | nada | 2005-07-06 21:58:15 |
|          2 |  0 | something | nada | 2005-07-06 21:58:40 |
|          3 |  0 |           | nada | 2005-07-06 22:33:16 |
|         -1 |  0 |           | nada | 2005-07-06 22:34:58 |
|          4 |  0 |           | nada | 2005-07-06 22:35:01 |
| 2147483647 |  0 |           | nada | 2005-07-06 22:42:40 |
+------------+----+-----------+------+---------------------+

5 rows in set (0.00 sec)

MySQL is behaving quite consistently here, but in a surprising way. AUTO_INCREMENT fields, as we saw above, take the next value in the sequence as default if you attempt to insert a NULL value. If you attempt to insert a negative value, or a larger value than the numeric can handle, MySQL's behavior is undefined. Running MySQL 5.0.2, MySQL successfully inserts a negative number (other versions simply insert the next number in the sequence) and the attempt to insert zero is treated the same as if you had inserted a null (so 4 is inserted, the next in the sequence). Entering a number greater than the integer can handle inserts a number equal to the maximum value of the integer (other versions have also simply inserted the next in the sequence). However, as soon as you attempt to insert a valid number (1 in this case), MySQL will attempt to insert this actual number, and, being a primary key, this fails since we already have a record with this value.

Not quite boolean

Since MySQL has no boolean data types, many use the ENUM type, as follows:

mysql> 
CREATE table boolean (
  i1 INT, 
  b1 ENUM('0','1')
)

And that works fine in most cases. However, it is easy to make a few mistakes. Here is a common one:

mysql>
CREATE table boolean2 (
  i1 INT, 
  b1 ENUM('0','1') NOT NULL DEFAULT 1
);

You are probably assuming here that the field b1 defaults to true (1). Don't be too sure.

mysql> INSERT INTO boolean2 (i1) VALUES (1);

mysql> SELECT * FROM boolean2;
+------+------+
| i1   | b1   |
+------+------+
|    1 | 0    |
+------+------+
1 row in set (0.00 sec)

In spite of setting the default to 1, MySQL has entered 0 as a value. What happened? Simple - ENUM is a character type, so the default value should be a string. MySQL however does not warn you of your error. Let's correct that mistake, and try again.

CREATE table boolean3 (
  i1 INT, 
  b1 ENUM('0','1') NOT NULL DEFAULT '1'
);

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

mysql> SELECT * FROM boolean3;
+------+----+
| i1   | b1 |
+------+----+
|    1 | 1  |
+------+----+

Let's create another version of the table, where the ENUM field still cannot be null, but it has no predefined default.

CREATE table boolean5(
  i1 INT, 
  b1 ENUM('0','1') NOT NULL
);

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

mysql> SELECT * FROM boolean5;
+------+----+
| i1   | b1 |
+------+----+
|    1 | 0  |
+------+----+

As with our earlier examples, MySQL inserts a default without warning you. In this case, the default is the first ENUM option, '0'. Another common mistake:

mysql> INSERT INTO boolean5 (b1) VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM boolean5;
+------+----+
| i1   | b1 |
+------+----+
|    1 | 0  |
| NULL | 0  |
+------+----+
2 rows in set (0.00 sec)

We have made the same mistake as before, except this time in inserting the data. We have forgotten that b1 is a string, and our value should have been enclosed in quotes. The field i1 is NULL, as we would expect if we did not define it, as it has been defined to accept NULLs

Conclusion

Most people do not work only in one DBMS, and if you have moved from another DBMS, or plan to work on another in the future, do not let MySQL's oddities catch you by surprise. There are still many more lurking and we will look at some of these in future articles.

» See All Articles by Columnist Ian Gilfillan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved