[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

Alice Sherepa created issue - 2018-01-10 12:27

Alice Sherepa made changes - 2018-01-10 13:13

Field	Original Value	New Value
Summary	[draft] '0000-00-00 00:00:00' is considered as NULL	[draft] '0000-00-00 00:00:00' is considered as NULL, depending on

Alice Sherepa made changes - 2018-01-10 13:13

Summary

[draft] '0000-00-00 00:00:00' is considered as NULL, depending on

[draft] '0000-00-00 00:00:00' is considered as NULL, depending on optimizer_switch

Alice Sherepa made changes - 2018-01-10 13:31

Description

{code:sql}

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');
SELECT count(*) FROM t1 AS a1 WHERE d1 IS NULL ;

SET SESSION optimizer_switch='derived_merge=off';
SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ; ## result 2, expected result 0
SET SESSION optimizer_switch='derived_merge=on';
SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ; ## result 0
DROP TABLE t1;

{code}

{noformat}
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]> SELECT count(*) 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=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)
{noformat}

{code:sql}

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 0
SET SESSION optimizer_switch='derived_merge=on';
SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ; ## result 0
DROP TABLE t1;

{code}

{noformat}
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)
{noformat}

Alice Sherepa made changes - 2018-01-10 13:37

Summary

[draft] '0000-00-00 00:00:00' is considered as NULL, depending on optimizer_switch

zero_date is considered as NULL, depending on optimizer_switch

Alice Sherepa made changes - 2018-01-10 13:38

Link

This issue relates to ~~MDEV-4962~~ [ ~~MDEV-4962~~ ]

Alice Sherepa made changes - 2018-01-10 13:38

Assignee

Alice Sherepa [ alice ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2018-01-12 18:21

Status

Open [ 1 ]

In Progress [ 3 ]

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.

Igor Babaev (Inactive) made changes - 2018-01-12 23:59

Description

{code:sql}

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 0
SET SESSION optimizer_switch='derived_merge=on';
SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ; ## result 0
DROP TABLE t1;

{code}

{noformat}
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)
{noformat}

{code:sql}

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;

{code}

{noformat}
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)
{noformat}

Igor Babaev (Inactive) made changes - 2018-01-13 00:00

Assignee

Igor Babaev [ igor ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2018-01-16 14:25

Status

In Progress [ 3 ]

In Review [ 10002 ]

Oleksandr Byelkin made changes - 2018-01-16 14:26

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2018-01-16 14:26

Assignee

Oleksandr Byelkin [ sanja ]

Igor Babaev [ igor ]

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.

Igor Babaev (Inactive) made changes - 2018-01-18 18:11

Component/s		Optimizer [ 10200 ]
Fix Version/s		5.5.59 [ 22612 ]
Fix Version/s	5.5 [ 15800 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 84813 ]

MariaDB v4 [ 153537 ]

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration