[#MDEV-12963] Query bug since 10.0.31

[MDEV-12963] Query bug since 10.0.31 Created: 2017-05-31 Updated: 2017-06-08 Resolved: 2017-06-08
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.0.31, 10.2
Fix Version/s:	5.5.57

Type:

Bug

Priority:

Major

Reporter:

Chris Peden

Assignee:

Igor Babaev

Resolution:

Fixed

Votes:

1

Labels:

None

Environment:

Centos 7, cpanel

Description

I use a software called elevatedX. I recently ran a cpanel upgrade that upgraded MariaDB to 10.0.31. Since then the counting query in elevatedx runs but doesn't return any results. Prior to this everything was working find. ElevatedX has said its a bug in MariaDB. Here is what they told me. I apologize for lack of depth as I am not a dev.

This query works:

SELECT * FROM (totals) WHERE

TotalDate IN (select MAX(TotalDate) from totals WHERE TotalDate <= '2017-05-26');

This, however, does not:

SELECT * FROM (totals) WHERE

TotalDate IN (select MAX(TotalDate) from totals WHERE TotalDate <= '2017-05-26') OR TotalDate IS NULL;

This is a bug in your version of MariaDB. Please consult your host.

Any information on if this can be fixed or how I can downgrade would be appreciated.

Comments

Comment by Mark El-Wakil [ 2017-06-01 ]

Here's how to reproduce this on 10.0.31:

Table schema:

CREATE TABLE `test_bug_table` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`TotalDate` date DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `Opt1` (`TotalDate`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

Populate data:
insert into test_bug_table (Id, TotalDate) VALUES (NULL, NOW()), (NULL, NOW()), (NULL, NOW()), (NULL, NOW()), (NULL, NOW());

Query that works:
SELECT * FROM (test_bug_table) WHERE
TotalDate IN (select MAX(TotalDate) from test_bug_table WHERE TotalDate <= NOW());

Query that does not work:
SELECT * FROM (test_bug_table) WHERE
TotalDate IN (select MAX(TotalDate) from test_bug_table WHERE TotalDate <= NOW()) OR TotalDate IS NULL;

The query above works on 10.0.30, but breaks on 10.0.31.

The issue here is that specifying "OR TotalDate IS NULL" should not exclude results from the subquery. This looks like a pretty big regression.

Comment by Alice Sherepa [ 2017-06-01 ]

I can repeat in on 10.0.31, the problem does not appear in 10.1.23, 10.2.6, 10.0.30
It must be some problem with index on that field, when I add ignore index, results are correct.

CREATE TABLE `t2` (d date DEFAULT NULL)

alter table t2 add index (d)

insert into t2 (d) VALUES (NULL), (NOW()), (NULL)

select * from t2

--------------

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

| d          |

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

| NULL       |

| NULL       |

| 2017-06-01 |

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

3 rows in set (0.01 sec)

--------------

SELECT * FROM (t2) WHERE

d IN (select MAX(d) from t2 WHERE d <= NOW()) OR d IS NULL

--------------

+------+

| d    |

+------+

| NULL |

| NULL |

+------+

2 rows in set (0.16 sec)

--------------

explain SELECT * FROM (t2) WHERE

d IN (select MAX(d) from t2 WHERE d <= NOW()) OR d IS NULL

--------------

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

| id   | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                        |

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

|    1 | PRIMARY            | t2    | index | d             | d    | 4       | NULL |    3 | Using where; Using index     |

|    2 | DEPENDENT SUBQUERY | NULL  | NULL  | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |

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

2 rows in set (0.00 sec)

--------------

SELECT * FROM (t2) WHERE

d IN (select MAX(d) from t2 ignore index (d) WHERE d <= NOW()) OR d IS NULL

--------------

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

| d          |

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

| NULL       |

| NULL       |

| 2017-06-01 |

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

3 rows in set (0.00 sec)

Comment by Elena Stepanova [ 2017-06-05 ]

Thanks for the report and test case. Reproducible as described (I used the test case from Alice's comment, just slightly modified).

CREATE TABLE `t2` (d date DEFAULT NULL);

alter table t2 add index (d);

insert into t2 (d) VALUES (NULL), ('2012-12-12'), (NULL);

SELECT * FROM (t2) WHERE

d IN (select MAX(d) from t2 WHERE d <= '2012-12-12') OR d IS NULL;

Expected result
+------------+
\| d \|
+------------+
\| NULL \|
\| NULL \|
\| 2012-12-12 \|
+------------+
3 rows in set (0.08 sec)

Actual result
+------+
\| d \|
+------+
\| NULL \|
\| NULL \|
+------+

The problem was introduced in 5.5 by the revision below, so it's just the matter of time when it appears in higher versions. By now it's in all main trees.

commit e0352fb07961f09ff6481136dc22f3c0db376def

Author: Igor Babaev <igor@askmonty.org>

Date:   Mon May 15 09:51:01 2017 -0700

    Fixed the bug mdev-7599.

Comment by Igor Babaev [ 2017-06-08 ]

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

Comment by Mark El-Wakil [ 2017-06-08 ]

@igor Thank you very much for the fix.

Two things:
1. Since you pushed the fix into the 5.5 tree, will this fix also apply for forward versions such as 10.0, 10.1, 10.2, 10.3, etc?
2. Is there any way a test could written for this so that there aren't any regressions in future versions?

Thanks!

Comment by Sergei Golubchik [ 2017-06-08 ]

1. Yes, absolutely. Please see https://jira.mariadb.org for the currently planned release schedule.
2. Yes, absolutely. See the actual commit — it includes the test case, so this particular regression can not appear ever again.

Generated at Thu Feb 08 08:01:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.