[MDEV-16386] Incorrect SQL result when renaming column in a subquery (possibly with GROUP BY) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.7, 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.2.16
Component/s: Optimizer
Labels:
None
Environment:
Debian Jessie

Description

Hi!

In one of our production installations MariaDB 10.3.7 fails to filter rows correctly after upgrading from 5.5, in a case similar to the following:

create table tbl (a int, b varchar(255));

insert into tbl values (1, 'abc');

select * from (select 'abc' a, a b from tbl) t where a='abc';

It seems that MariaDB pushes the outer 'where' condition inside, takes the value type from tbl.a, thinks ='abc' should be an integer, issues an 'incorrect integer value' and returns 0 rows.

The issue still reproduces on our installation. However, I can't reproduce it from scratch using the script above.

So...
1) can you suggest me how to reproduce it so you could fix it?
2) did I miss something during the upgrade?

Attachments

Activity

Ascending order - Click to sort in descending order

Vitaliy Filippov added a comment - 2018-06-04 13:35 - edited

Ok, I've reproduced it from scratch:

CREATE TABLE `longdescs2` (

  `bug_id` int(11) NOT NULL,

  `who` int(11) NOT NULL,

  `bug_when` datetime NOT NULL,

  `thetext` mediumtext NOT NULL,

  `work_time` decimal(7,2) NOT NULL DEFAULT 0.00,

  `isprivate` tinyint(4) NOT NULL DEFAULT 0,

  `already_wrapped` tinyint(4) NOT NULL DEFAULT 0,

  `comment_id` int(11) NOT NULL AUTO_INCREMENT,

  `type` smallint(6) NOT NULL DEFAULT 0,

  `extra_data` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`comment_id`),

  KEY `longdescs_bug_when_idx` (`bug_when`),

  KEY `longdescs_who_idx` (`who`,`bug_id`),

  KEY `longdescs_who_bug_when_idx` (`who`,`bug_when`),

  KEY `longdescs_bug_id_idx` (`bug_id`,`work_time`)

) ENGINE=InnoDB AUTO_INCREMENT=2818770 DEFAULT CHARSET=utf8;

INSERT INTO `longdescs2` VALUES (247765,1355,'2018-06-01 10:25:30','abcdef',1.50,0,0,2818108,0,NULL),(250659,1355,'2018-06-01 13:09:26','abcdef',2.42,0,0,2818478,0,NULL),(251629,1355,'2018-06-01 13:58:48','abcdef',1.17,0,0,2818565,0,NULL),(251658,1355,'2018-06-01 15:55:47','abcdef',0.50,0,0,2818769,0,NULL);

select * from (SELECT comment_id, 'abc' AS Who, l.who AS WhoId, l.bug_id AS BugId, SUM(l.work_time) AS Worktime FROM longdescs2 l WHERE l.bug_when >= STR_TO_DATE('01.06.2018', '%d.%m.%Y') AND l.bug_when < STR_TO_DATE('04.06.2018', '%d.%m.%Y') AND l.work_time != 0 AND l.who = 1355 GROUP BY l.who, l.bug_id) k where k.Who in ('abc');

Now please fix it

Vitaliy Filippov added a comment - 2018-06-04 13:35 - edited Ok, I've reproduced it from scratch: CREATE TABLE `longdescs2` ( `bug_id` int (11) NOT NULL , `who` int (11) NOT NULL , `bug_when` datetime NOT NULL , `thetext` mediumtext NOT NULL , `work_time` decimal (7,2) NOT NULL DEFAULT 0.00, `isprivate` tinyint(4) NOT NULL DEFAULT 0, `already_wrapped` tinyint(4) NOT NULL DEFAULT 0, `comment_id` int (11) NOT NULL AUTO_INCREMENT, `type` smallint (6) NOT NULL DEFAULT 0, `extra_data` varchar (255) DEFAULT NULL , PRIMARY KEY (`comment_id`), KEY `longdescs_bug_when_idx` (`bug_when`), KEY `longdescs_who_idx` (`who`,`bug_id`), KEY `longdescs_who_bug_when_idx` (`who`,`bug_when`), KEY `longdescs_bug_id_idx` (`bug_id`,`work_time`) ) ENGINE=InnoDB AUTO_INCREMENT=2818770 DEFAULT CHARSET=utf8; INSERT INTO `longdescs2` VALUES (247765,1355, '2018-06-01 10:25:30' , 'abcdef' ,1.50,0,0,2818108,0, NULL ),(250659,1355, '2018-06-01 13:09:26' , 'abcdef' ,2.42,0,0,2818478,0, NULL ),(251629,1355, '2018-06-01 13:58:48' , 'abcdef' ,1.17,0,0,2818565,0, NULL ),(251658,1355, '2018-06-01 15:55:47' , 'abcdef' ,0.50,0,0,2818769,0, NULL ); select * from ( SELECT comment_id, 'abc' AS Who, l.who AS WhoId, l.bug_id AS BugId, SUM (l.work_time) AS Worktime FROM longdescs2 l WHERE l.bug_when >= STR_TO_DATE( '01.06.2018' , '%d.%m.%Y' ) AND l.bug_when < STR_TO_DATE( '04.06.2018' , '%d.%m.%Y' ) AND l.work_time != 0 AND l.who = 1355 GROUP BY l.who, l.bug_id) k where k.Who in ( 'abc' ); Now please fix it

Alice Sherepa added a comment - 2018-06-06 10:12

vitalif, Thanks for the report and testcase!
Reproducible on 10.2, 10.3

CREATE TABLE t1 (who int);

INSERT INTO t1 VALUES (1),(1);

SELECT * FROM (SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k

WHERE k.who='abc';

explain extended

SELECT * FROM (SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k

WHERE k.who='abc';

drop table t1;

 10.2

MariaDB [test]> SELECT * FROM (SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k

    -> WHERE k.who='abc';

Empty set, 2 warnings (0.002 sec)

Warning (Code 1052): Column 'who' in order clause is ambiguous

Warning (Code 1292): Truncated incorrect DOUBLE value: 'abc'

MariaDB [test]> explain extended

    -> SELECT * FROM (SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k

    -> WHERE k.who='abc';

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

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

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

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where                     |

|    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary; Using filesort |

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

2 rows in set, 2 warnings (0.001 sec)

Warning (Code 1052): Column 'who' in order clause is ambiguous

Note (Code 1003): /* select#1 */ select `k`.`who` AS `who` from (/* select#2 */ select 'abc' AS `who` from `test`.`t1` group by `test`.`t1`.`who`) `k` where `k`.`who` = 'abc'

Alice Sherepa added a comment - 2018-06-06 10:12 vitalif , Thanks for the report and testcase! Reproducible on 10.2, 10.3 CREATE TABLE t1 (who int ); INSERT INTO t1 VALUES (1),(1); SELECT * FROM ( SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k WHERE k.who= 'abc' ; explain extended SELECT * FROM ( SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k WHERE k.who= 'abc' ; drop table t1; 10.2 MariaDB [test]> SELECT * FROM (SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k -> WHERE k.who='abc'; Empty set, 2 warnings (0.002 sec) Warning (Code 1052): Column 'who' in order clause is ambiguous Warning (Code 1292): Truncated incorrect DOUBLE value: 'abc' MariaDB [test]> explain extended -> SELECT * FROM (SELECT 'abc' AS who FROM t1 GROUP BY t1.who) k -> WHERE k.who='abc'; +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort | +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 2 rows in set, 2 warnings (0.001 sec) Warning (Code 1052): Column 'who' in order clause is ambiguous Note (Code 1003): /* select#1 */ select `k`.`who` AS `who` from (/* select#2 */ select 'abc' AS `who` from `test`.`t1` group by `test`.`t1`.`who`) `k` where `k`.`who` = 'abc'

Igor Babaev (Inactive) added a comment - 2018-06-14 19:55

ok to push into 10.2

Igor Babaev (Inactive) added a comment - 2018-06-14 19:55 ok to push into 10.2

Galina Shalygina (Inactive) added a comment - 2018-06-14 21:51

Pushed in 10.2

Galina Shalygina (Inactive) added a comment - 2018-06-14 21:51 Pushed in 10.2

MariaDB Server

Incorrect SQL result when renaming column in a subquery (possibly with GROUP BY)

Details

Description

Attachments

Activity

People

Dates

Git Integration