[MDEV-17074] Select on a view return unexpected result Created: 2018-08-27  Updated: 2018-08-27

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 5.5, 10.0, 10.1, 10.1.29, 10.2, 10.3
Fix Version/s: 5.5

Type: Bug Priority: Major
Reporter: sycxyc Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux 4.16.0 Debian x86_64 GNU/Linux


 Description   

Test case:

create table t1 (id int primary key);
 
create table t2 (id int primary key);
 
insert t1 values (1),(2),(3);
 
insert t2 values (1);
 
create view t1_view as select a.id from t1 a join t2 order by a.id;
 
 
 
MariaDB [tmp]> select count(*) from t1_view where id not in (select id from t1);
 
+----------+
 
| count(*) |
 
+----------+
 
|        3 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [tmp]> select count(*) from t1_view a where not exists (select id from t1 b where a.id=b.id);
 
+----------+
 
| count(*) |
 
+----------+
 
|        3 |
 
+----------+
 
1 row in set (0.00 sec)

These results should be 0. The following runs correctly.

MariaDB [tmp]> select count(*) from t1_view where 0+id not in (select id from t1);
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [tmp]> select count(*) from t1_view a where not exists (select id from t1 b where 0+a.id=b.id);
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Alice Sherepa [ 2018-08-27 ]

Thanks for the report and test case!
Reproducible with Innodb, not with MyIsam, on MariaDB 5.5-10.3
Results are correct when using optimizer_switch='materialization=off'

--source include/have_innodb.inc
 
create table t1 (id int)engine=innodb;
 
create table t2 (id int)engine=innodb;
 
insert t1 values (1),(2),(3);
 
insert t2 values (1);
 
create view v1 as select t1.id from t1 join t2 order by t1.id;
 
 
 
select count(*) from v1 where id not in (select id from t1);
 
select count(*) from v1 where 0+id not in (select id from t1);
 
select * from v1 where id not in (select id from t1);
 
select * from v1 where not exists (select id from t1 where v1.id=t1.id);

5.5

select count(*) from v1 where id not in (select id from t1);
 
count(*)
 
3
 
select count(*) from v1 where 0+id not in (select id from t1);
 
count(*)
 
0

10.2-10.3 

select count(*) from v1 where id not in (select id from t1); #correct
 
count(*)
 
0
 
 
 
select * from v1 where id not in (select id from t1); #wrong
 
id
 
1
 
2
 
3
 
 
 
MariaDB [test]> explain extended select * from v1 where id not in (select id from t1);
 
+------+--------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type  | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+--------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using temporary; Using filesort                 |
 
|    1 | PRIMARY      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    2 | MATERIALIZED | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |                                                 |
 
+------+--------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
3 rows in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): select `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`t2` where !<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,`test`.`t1`.`id` in ( <materialize> (select `test`.`t1`.`id` from `test`.`t1` ), <primary_index_lookup>(`test`.`t1`.`id` in <temporary table> on distinct_key where `test`.`t1`.`id` = `<subquery2>`.`id`)))) order by `test`.`t1`.`id`
 
MariaDB [test]> explain extended select count(*) from v1 where id not in (select id from t1);
 
+------+--------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type  | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+--------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                                                 |
 
|    1 | PRIMARY      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    2 | MATERIALIZED | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |                                                 |
 
+------+--------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
3 rows in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` where !<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,`test`.`t1`.`id` in ( <materialize> (select `test`.`t1`.`id` from `test`.`t1` ), <primary_index_lookup>(`test`.`t1`.`id` in <temporary table> on distinct_key where `test`.`t1`.`id` = `<subquery2>`.`id`)))) order by `test`.`t1`.`id`

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