Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.31, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
-
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:
This, however, does not:
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.
Attachments
Activity
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) |
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.
|
@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!
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.
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.