The frustration builds.
I have come to despise MySQL's sql_mode. It is a hack of the most gargantuan proportions.
Basically, the optimizer just ignores the sql_mode whenever it is convenient for it to do so. More importantly, the optimizer silently ignores bad datetime input in various places. The reason for this is because of my statement above: sql_mode is a big ole' hack. Instead of fixing the runtime executor in MySQL to use real ValueObject types — that are immutable and know how to convert (and not convert) between each other, the runtime is a mess of checks for various runtime codes, warning modes, "count_cuted_field" crap and other miscellany that obfuscates the executor pipeline almost beyond recognition.
Slowly, I am attacking the mess, but the executor is so fragile that even tiny changes can wreak havoc on the system, so the going is slow and painful. It's no wonder that the release cycle for the MySQL server is so bloody long...
To give you an example of the symptoms which manifest themselves because of this hack, I present the following, executed in MySQL 5.0.67:
mysql> create table t1(f1 int, f2 date);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30');
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> select * from t1 where f2 <= '2005-09-31' order by f2;
+------+------------+
| f1 | f2 |
+------+------------+
| 1 | 2005-01-01 |
| 2 | 2005-09-01 |
| 3 | 2005-09-30 |
+------+------------+
3 rows in set (0.00 sec)
OK, so far, everything is as "expected" when running in a non-strict mode. Even though "2005-09-31" is clearly an invalid date, MySQL in non-strict mode is expected to ignore such triviality and, we can assume, "corrects" the bad date to something else (maybe an integer representation of "2005-09-30"?)
The MySQL manual is fairly clear about sql_mode=STRICT_ALL_TABLES and bad datetime values:
Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range.
So, we should expect the above statement to throw an error in STRICT_ALL_TABLES, right? Wrong:
mysql> set @@sql_mode="STRICT_ALL_TABLES";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where f2 >= '0000-00-00' order by f2;
+------+------------+
| f1 | f2 |
+------+------------+
| 1 | 2005-01-01 |
| 2 | 2005-09-01 |
| 3 | 2005-09-30 |
| 4 | 2005-10-01 |
| 5 | 2005-12-30 |
+------+------------+
5 rows in set (0.00 sec)
mysql> select * from t1 where f2 > '2005-09-99' order by f2;
+------+------------+
| f1 | f2 |
+------+------------+
| 4 | 2005-10-01 |
| 5 | 2005-12-30 |
+------+------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2005-09-99' for column 'f2' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL accepts the bad date, even in STRICT_ALL_TABLES mode, and does the same implicit conversion, giving us a warning when not the zero date but not telling us what conversion was done. Clearly, a conversion happened because MySQL winnowed the result set. But we have no way to know what the winnowing condition was on.
Let's take this even further. The docs suggest that STRICT_ALL_TABLES must be coupled with NO_ZERO_IN_DATE if real validation on dates is to occur:
Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero month or day parts such as '2004-04-00' or “zero” dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.
So, maybe with "STRICT_ALL_TABLES, NO_ZERO_IN_DATE" sql_mode, we'll actually get an error...
mysql> set @@sql_mode="STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where f2 > '2005-09-99' order by f2;
+------+------------+
| f1 | f2 |
+------+------------+
| 4 | 2005-10-01 |
| 5 | 2005-12-30 |
+------+------------+
2 rows in set, 1 warning (0.00 sec)
mysql> select * from t1 where f2 >= '0000-00-00' order by f2;
+------+------------+
| f1 | f2 |
+------+------------+
| 1 | 2005-01-01 |
| 2 | 2005-09-01 |
| 3 | 2005-09-30 |
| 4 | 2005-10-01 |
| 5 | 2005-12-30 |
+------+------------+
5 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '0000-00-00' for column 'f2' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)
Well, the above ridiculousness is the symptom of a hacked-up runtime engine switching on modes, return codes, and warning levels all over the place. The danger in the above is that we don't actually know how the silent truncation/conversion has affected our query results. What if the query results are not what we intended? A warning on bad input is simply NOT enough.
Want to see another symptom of this kind of implicit truncation/conversion that is rampant in the runtime? Take a look at the following in MySQL:
mysql> create table t1(f1 int, f2 date not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t1 where f2 is null;
Empty set (0.00 sec)
mysql> explain extended select * from t1 where f2 is null;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.02 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (`test`.`t1`.`f2` = 0) |
+-------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
If you take a look at the above query, you will notice that the WHERE condition is asking for rows in which f2 IS NULL. However, f2 is defined as DATE NOT NULL.
POP QUIZ: Why doesn't the optimizer display a Impossible WHERE in the Extra column and optimize away the query?
ANSWER: Because of all those silly silent conversions. MySQL converts "IS NULL" to "= 0" because everything is designed around comparisons to "the zero date". So, even if you have STRICT_ALL_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE mode on, MySQL is still using the zero date behind the scenes.
Here is the relevant code in sql_select.cc:
/* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */
else if (
((field->type() == DRIZZLE_TYPE_DATE) || (field->type() == DRIZZLE_TYPE_DATETIME))
&& (field->flags & NOT_NULL_FLAG)
&& ! field->table->maybe_null)
{
COND *new_cond;
if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
{
cond= new_cond;
/*
Item_func_eq can't be fixed after creation so we do not check
cond->fixed, also it do not need tables so we use 0 as second
argument.
*/
cond->fix_fields(session, &cond);
}
}
Suffice it to say, this "fix" is gone now from Drizzle, and we're back to producing the appropriate plan:
drizzle> use test
Database changed
drizzle> create table t1(f1 int, f2 date not null);
Query OK, 0 rows affected (0.02 sec)
drizzle> insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
drizzle> select * from t1 where f2 is null;
Empty set (0.00 sec)
drizzle> explain extended select * from t1 where f2 is null;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
drizzle> show warnings;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 0 |
+-------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The deeper I dig, the more scared I become.
