MySQL Bugs: #51521: MERGE Algorithm in LEFT JOIN retuns 0000-00-00 instead of NULL

Bug #51521	MERGE Algorithm in LEFT JOIN retuns 0000-00-00 instead of NULL
Submitted:	25 Feb 2010 18:03	Modified:	26 Feb 2010 8:16
Reporter:	Drew Vogel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.42, 5.1.45-bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	date, datetime, merge, null, Views, zero

Description:
I created a view to calculate a DATE field from separate numeric Year/Month/Day fields, eliminating invalid dates. When I join this view back to the original table, using a left join, I expect the DOB field on the right side of the join to be NULL where the join condition is false. However it returns 0000-00-00. This behavior is observed while using the MERGE algorithm but not while using the TEMPTABLE algorithm.

How to repeat:
DROP TABLE IF EXISTS `p9629`.`gtowey_test`;
CREATE TABLE  `p9629`.`gtowey_test` (
  `id` varchar(12) NOT NULL,
  `Birthdate_Year` int(4) default NULL,
  `Birthdate_Day` int(2) default NULL,
  `Birthdate_Month` int(2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SELECT * FROM gtowey_test;
-- This returns:
-- '12345678', 1960, 1, 1
-- '87654321', 1985, 31, 12
-- '00000000', 0, 0, 0
-- as expected

SELECT
  id,
  str_to_date(CONCAT_WS('-', Birthdate_Year, Birthdate_Month, Birthdate_Day), '%Y-%m-%d') as DOB
FROM   `gtowey_test`
WHERE
    id IS NOT NULL
AND Birthdate_Year IS NOT NULL
AND Birthdate_Month IS NOT NULL
AND Birthdate_Day IS NOT NULL
AND Birthdate_Year <> 0
AND Birthdate_Month <> 0
AND Birthdate_Day <> 0;
-- This returns:
-- '12345678', '1960-01-01'
-- '87654321', '1985-12-31'
-- as expected

-- Now I want to join it back to the original table to bring in other fields
-- So let's make a view out of it
CREATE OR REPLACE ALGORITHM=MERGE VIEW gtowey_test_dob AS
SELECT
  id,
  str_to_date(CONCAT_WS('-', Birthdate_Year, Birthdate_Month, Birthdate_Day), '%Y-%m-%d') as DOB
FROM   `gtowey_test`
WHERE
    id IS NOT NULL
AND Birthdate_Year IS NOT NULL
AND Birthdate_Month IS NOT NULL
AND Birthdate_Day IS NOT NULL
AND Birthdate_Year <> 0
AND Birthdate_Month <> 0
AND Birthdate_Day <> 0;

SELECT * FROM gtowey_test_dob;
-- This view returns the same results:
-- '12345678', '1960-01-01'
-- '87654321', '1985-12-31'

SELECT
  id,
  Birthdate_Year,
  DOB
FROM gtowey_test
LEFT JOIN gtowey_test_dob USING (id);
-- This returns:
-- '00000000', '0000-00-00'
-- '12345678', '1960-01-01'
-- '87654321', '1985-12-31'
-- Notice the 0000-00-00

-- Let's try the left join to a sub-select instead of the view:
SELECT
  id,
  DOB
FROM gtowey_test
LEFT JOIN (
  SELECT
    id,
    str_to_date(CONCAT_WS('-', Birthdate_Year, Birthdate_Month, Birthdate_Day), '%Y-%m-%d') as DOB
  FROM   `gtowey_test`
  WHERE
      id IS NOT NULL
  AND Birthdate_Year IS NOT NULL
  AND Birthdate_Month IS NOT NULL
  AND Birthdate_Day IS NOT NULL
  AND Birthdate_Year <> 0
  AND Birthdate_Month <> 0
  AND Birthdate_Day <> 0) as gtowey_test_dob USING (id);

-- This returns the DOB as NULL, like I'd expect it the view to:
-- '00000000', NULL
-- '12345678', '1960-01-01'
-- '87654321', '1985-12-31'

Thank you for the problem report. Verified just as described with recent 5.1.45 from bzr:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database p9629;
Query OK, 1 row affected (0.01 sec)

mysql> use p9629;
Database changed
mysql> CREATE TABLE  `p9629`.`gtowey_test` (
    ->   `id` varchar(12) NOT NULL,
    ->   `Birthdate_Year` int(4) default NULL,
    ->   `Birthdate_Day` int(2) default NULL,
    ->   `Birthdate_Month` int(2) default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into gtowey_test
    -> values ('12345678', 1960, 1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into gtowey_test values ('87654321', 1985, 31, 12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into gtowey_test values ('00000000', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM gtowey_test;
+----------+----------------+---------------+-----------------+
| id       | Birthdate_Year | Birthdate_Day | Birthdate_Month |
+----------+----------------+---------------+-----------------+
| 12345678 |           1960 |             1 |               1 |
| 87654321 |           1985 |            31 |              12 |
| 00000000 |              0 |             0 |               0 |
+----------+----------------+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   id,
    ->   str_to_date(CONCAT_WS('-', Birthdate_Year, Birthdate_Month, Birthdate_Day), '%Y-%m-%d')
    -> as DOB
    -> FROM   `gtowey_test`
    -> WHERE
    ->     id IS NOT NULL
    -> AND Birthdate_Year IS NOT NULL
    -> AND Birthdate_Month IS NOT NULL
    -> AND Birthdate_Day IS NOT NULL
    -> AND Birthdate_Year <> 0
    -> AND Birthdate_Month <> 0
    -> AND Birthdate_Day <> 0;
+----------+------------+
| id       | DOB        |
+----------+------------+
| 12345678 | 1960-01-01 |
| 87654321 | 1985-12-31 |
+----------+------------+
2 rows in set (0.00 sec)

mysql> CREATE OR REPLACE ALGORITHM=MERGE VIEW gtowey_test_dob AS
    -> SELECT
    ->   id,
    ->   str_to_date(CONCAT_WS('-', Birthdate_Year, Birthdate_Month, Birthdate_Day), '%Y-%m-%d')
    -> as DOB
    -> FROM   `gtowey_test`
    -> WHERE
    ->     id IS NOT NULL
    -> AND Birthdate_Year IS NOT NULL
    -> AND Birthdate_Month IS NOT NULL
    -> AND Birthdate_Day IS NOT NULL
    -> AND Birthdate_Year <> 0
    -> AND Birthdate_Month <> 0
    -> AND Birthdate_Day <> 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM gtowey_test_dob;
+----------+------------+
| id       | DOB        |
+----------+------------+
| 12345678 | 1960-01-01 |
| 87654321 | 1985-12-31 |
+----------+------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   id,
    ->   Birthdate_Year,
    ->   DOB
    -> FROM gtowey_test
    -> LEFT JOIN gtowey_test_dob USING (id);
+----------+----------------+------------+
| id       | Birthdate_Year | DOB        |
+----------+----------------+------------+
| 12345678 |           1960 | 1960-01-01 |
| 87654321 |           1985 | 1985-12-31 |
| 00000000 |              0 | 0000-00-00 |
+----------+----------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   id,
    ->   DOB
    -> FROM gtowey_test
    -> LEFT JOIN (
    ->   SELECT
    ->     id,
    ->     str_to_date(CONCAT_WS('-', Birthdate_Year, Birthdate_Month, Birthdate_Day),
    -> '%Y-%m-%d') as DOB
    ->   FROM   `gtowey_test`
    ->   WHERE
    ->       id IS NOT NULL
    ->   AND Birthdate_Year IS NOT NULL
    ->   AND Birthdate_Month IS NOT NULL
    ->   AND Birthdate_Day IS NOT NULL
    ->   AND Birthdate_Year <> 0
    ->   AND Birthdate_Month <> 0
    ->   AND Birthdate_Day <> 0) as gtowey_test_dob USING (id);
+----------+------------+
| id       | DOB        |
+----------+------------+
| 00000000 | NULL       |
| 12345678 | 1960-01-01 |
| 87654321 | 1985-12-31 |
+----------+------------+
3 rows in set (0.01 sec)