[MDEV-14911] zero_date is considered as NULL, depending on optimizer_switch - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 5.5.59
Component/s: Optimizer
Labels:
None

Description

DROP TABLE IF EXISTS  t1;

SELECT  CAST('0000-00-00 00:00:00' as datetime) is null;

CREATE TABLE t1 (d1 datetime NOT NULL);

INSERT INTO t1 VALUES ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');

SET SESSION optimizer_switch='derived_merge=off';

SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;  ## result 2, expected result 2

SET SESSION optimizer_switch='derived_merge=on';

SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;  ## result 0, expected result 2

DROP TABLE t1;

MariaDB [test]> SELECT  CAST('0000-00-00 00:00:00' as datetime) is null;

+-------------------------------------------------+

| CAST('0000-00-00 00:00:00' as datetime) is null |

+-------------------------------------------------+

|                                               0 |

+-------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> CREATE TABLE t1 (d1 datetime NOT NULL);

Query OK, 0 rows affected (0.13 sec)

MariaDB [test]> INSERT INTO t1 VALUES ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');

Query OK, 3 rows affected (0.03 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> SET SESSION optimizer_switch='derived_merge=off';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row in set (0.00 sec)

MariaDB [test]> SET SESSION optimizer_switch='derived_merge=on';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

Attachments

Issue Links

relates to

MDEV-4962 Wrong result (missing rows) on LEFT JOINs and <non-nullable datetime field> IS NULL

Closed

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) added a comment - 2018-01-12 19:17

We have the following for the table t1

MariaDB [test]> select * from t1;

+---------------------+

| d1                  |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

| 1979-09-03 20:49:36 |

+---------------------+

MariaDB [test]> select * from t1 where d1 is null;

+---------------------+

| d1                  |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

+---------------------+

MariaDB [test]> create view v1 as select * from t1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from v1;

+---------------------+

| d1                  |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

| 1979-09-03 20:49:36 |

+---------------------+

MariaDB [test]> select * from v1 where d1 is null;

Empty set (0.00 sec)

MariaDB [test]> create algorithm=merge view v2 as select * from t1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from v2;

+---------------------+

| d1                  |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

| 1979-09-03 20:49:36 |

+---------------------+

MariaDB [test]> select * from v2 where d1 is null;

MariaDB [test]> create algorithm=temptable view v3 as select * from t1;

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> select * from v3;

+---------------------+

| d1                  |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

| 1979-09-03 20:49:36 |

+---------------------+

MariaDB [test]> select * from v3 where d1 is null;

+---------------------+

| d1                  |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

+---------------------+

So for the views v1,v2 we have wrong results for the query
select * from v1/v2 where d1 is null.

Igor Babaev (Inactive) added a comment - 2018-01-12 19:17 We have the following for the table t1 MariaDB [test]> select * from t1; +---------------------+ | d1 | +---------------------+ | 0000-00-00 00:00:00 | | 0000-00-00 00:00:00 | | 1979-09-03 20:49:36 | +---------------------+ MariaDB [test]> select * from t1 where d1 is null; +---------------------+ | d1 | +---------------------+ | 0000-00-00 00:00:00 | | 0000-00-00 00:00:00 | +---------------------+ MariaDB [test]> create view v1 as select * from t1; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select * from v1; +---------------------+ | d1 | +---------------------+ | 0000-00-00 00:00:00 | | 0000-00-00 00:00:00 | | 1979-09-03 20:49:36 | +---------------------+ MariaDB [test]> select * from v1 where d1 is null; Empty set (0.00 sec) MariaDB [test]> create algorithm=merge view v2 as select * from t1; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select * from v2; +---------------------+ | d1 | +---------------------+ | 0000-00-00 00:00:00 | | 0000-00-00 00:00:00 | | 1979-09-03 20:49:36 | +---------------------+ MariaDB [test]> select * from v2 where d1 is null; MariaDB [test]> create algorithm=temptable view v3 as select * from t1; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> select * from v3; +---------------------+ | d1 | +---------------------+ | 0000-00-00 00:00:00 | | 0000-00-00 00:00:00 | | 1979-09-03 20:49:36 | +---------------------+ MariaDB [test]> select * from v3 where d1 is null; +---------------------+ | d1 | +---------------------+ | 0000-00-00 00:00:00 | | 0000-00-00 00:00:00 | +---------------------+ So for the views v1,v2 we have wrong results for the query select * from v1/v2 where d1 is null.

Oleksandr Byelkin added a comment - 2018-01-16 14:26

OK to push

Oleksandr Byelkin added a comment - 2018-01-16 14:26 OK to push

Igor Babaev (Inactive) added a comment - 2018-01-18 18:11

A fix for this bug was pushed into the 5.5 tree.

Igor Babaev (Inactive) added a comment - 2018-01-18 18:11 A fix for this bug was pushed into the 5.5 tree.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Alice Sherepa

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-01-10 12:27

Updated:: 2018-01-18 18:11

Resolved:: 2018-01-18 18:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server