It's not a bug - it's a gotcha. A "gotcha" is a feature or function which works as advertised - but not as expected.

When working with the MySQL ™ database server I have repeatedly encountered situations where the results of various actions have been unexpected and/or contrary to the behaviour generally expected of an SQL relational database. The cause can usually be traced to implementation details which are documented in the manual. I have created this list in order to further a better understanding of the MySQL database server and hopefully save others unnecessary headscratching.

(For known bugs see this page in the MySQL documentation: http://dev.mysql.com/doc/mysql/en/Open_bugs.html).

The MySQL database server is being continually improved. Some gotchas described here are no longer relevant for the latest versions; in these cases the version numbers affected are noted at the top of each section. As a rule gotchas have been tested against the most recent stable versions from the 3.23.x, 4.0.x and 4.1.x series.

Corrections, suggestions and comments are welcome: <db-gotchas0711@sql-info.de> .

Note: this document contains many examples as they would appear in the MySQL command line client mysql ("mysql monitor"). For convenience, where statements are more than one line, the continuation prompt -> has been omitted, so the statement can be copied directly from this page.

1. General SQL

1.1. NULL, or when NULL IS NOT NULL 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. Example 1. When NULL is not NULL 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 NULL s into theses columns, which should normally cause the statements to fail. ... If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: ... -- http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html 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: Example 2. NULL and TIMESTAMP 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: ... The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. ... -- http://dev.mysql.com/doc/mysql/en/DATETIME.html 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: ... When MySQL is running in MAXDB mode, TIMESTAMP behaves like DATETIME. No automatic updating of TIMESTAMP columns occurs, as described in the following paragraphs. MySQL can be run in MAXDB mode as of version 4.1.1. ... -- http://dev.mysql.com/doc/mysql/en/DATETIME.html Example 3. Accessing pseudo-sequences with NULL 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: Example 4. Is NULL an integer? 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: ... For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row: ... SELECT * FROM tbl_name WHERE auto IS NULL; -- http://dev.mysql.com/doc/mysql/en/ODBC_and_last_insert_id.html 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)



1.2. AUTO_INCREMENT 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 behaviour of auto-increment is not defined if a user gives a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type. ... -- http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html 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. Note Several readers have asked why I would ever want negative values in an AUTO_INCREMENT column. As it happens, the table that caused the discovery of the above oddities didn't actually need an AUTO_INCREMENT column, as the primary key values were to be limited to between -2 and 2 (enforced by a foreign key relation, of course). One was added anyway, probably through force of habit. The problem occurred while loading data with predefined values from a script, which caused a primary key exception, even though the ID values were defined explicitly and the data itself was correct. It's MySQLs habit of discretely altering input, and only (coincidentally) barfing on the side-effects - instead of immediately throwing an error on data it doesn't know what to do with - which is causing me to compose lists like this. Personally I've not yet ever needed sequences which start with negative values. If you do, you'll probably need to look at another database. (And if you're thinking "implement them in the application", you don't need a relational database). 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)



1.3. ENUM 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: when an integer is inserted, the enumeration member corresponding to the index value of that integer is inserted. This is the case with id s 2 and 3 in the above example, where the integer 1 is the first value of the ENUM index ( '0' ) and 2 is the second value ( '1' ).

s and in the above example, where the integer is the first value of the index ( ) and is the second value ( ). when ENUM receives an invalid value, an empty string is inserted; this is the case with id s 0 and 4 , where the inserted integers had no corresponding index value. 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.

1.4. Case sensitivity in CHAR / VARCHAR fields 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: ... Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved. ... -- http://dev.mysql.com/doc/mysql/en/CHAR.html 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).

1.5. VARCHAR limited to 255 characters MySQL limits VARCHAR (aka CHARACTER VARYING ) columns to a column length of 255. A future MySQL version will remove this restriction: Add true VARCHAR support (column lengths longer than 255, ... ). There is already support for this in the MyISAM storage engine, but it is not yet available at the user level. -- http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html

1.6. VARCHAR's trailing blank allergy 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: ... However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the SQL-99 specification.) ... -- http://dev.mysql.com/doc/mysql/en/CHAR.html 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).

1.7. DEFAULT NOW() 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.

1.8. INSERT INTO ... SELECT ... 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: The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing.) -- http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html 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: Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14. -- http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html

1.9. Comments beginning with -- 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.

1.10. UNION and literal values 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).

1.11. Division by zero 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

1.12. 'concatenation' || 'or' 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.

1.13. What goes in - isn't (always) what comes out 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

1.14. February 31st 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: If you use really malformed dates, the result is NULL. -- http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html 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... The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999. Any date not within this range will revert to 0000-00-00. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application. -- http://dev.mysql.com/doc/mysql/en/DATETIME.html 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.