[MDEV-7678] Wrong result with @variable in subquery (Subquery with @variable acts weird) Created: 2015-03-07  Updated: 2022-11-20  Resolved: 2022-11-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - XtraDB
Affects Version/s: 10.0
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Slava Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: None

Attachments: File queries.sql    

 Description   

Sorry I couldn't come up with better ticket title.
Following problem can be reproduced on MariaDB 10.0.15, 10.0.16, 10.0.17.
I also tested this on MySQL upstream and the problem does NOT appear on 5.5.42, 5.6.14 and 5.6.20, i.e. everything works as expected.

> ################
 
> # prepare tables and data
 
> create database if not exists test;
 
Query OK, 1 row affected, 1 warning (0.00 sec)
 
 
 
> use test;
 
Database changed
 
>
 
> drop table if exists t1;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
> create table t1(
 
    ->   id integer primary key,
 
    ->   value integer
 
    -> ) engine=innodb; # engine=innodb - important
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
> insert t1 set id=777;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
> insert t1 set id=888;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
>
 
> drop table if exists t2;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
> create table t2 (
 
    ->   field1 integer,
 
    ->   field2 integer,
 
    ->   field3 integer,
 
    ->   primary key(field1, field2) # index on field1 and field2 only
 
    -> ) engine=innodb; # engine=innodb - important
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
> insert t2 set field1=777, field2=1, field3=1;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
> insert t2 set field1=888, field2=1, field3=1;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # check our data
 
> select * from t1;
 
╔═════╤═══════╗
 
║ id  │ value ║
 
╟─────┼───────╢
 
║ 777 │  NULL ║
 
║ 888 │  NULL ║
 
╚═════╧═══════╝
 
2 rows in set (0.00 sec)
 
 
 
> select * from t2;
 
╔════════╤════════╤════════╗
 
║ field1 │ field2 │ field3 ║
 
╟────────┼────────┼────────╢
 
║    777 │      1 │      1 ║
 
║    888 │      1 │      1 ║
 
╚════════╧════════╧════════╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # this is okay
 
> select * from t1 join t2 on id=field1 where (@foo:=field3);
 
╔═════╤═══════╤════════╤════════╤════════╗
 
║ id  │ value │ field1 │ field2 │ field3 ║
 
╟─────┼───────┼────────┼────────┼────────╢
 
║ 777 │  NULL │    777 │      1 │      1 ║
 
║ 888 │  NULL │    888 │      1 │      1 ║
 
╚═════╧═══════╧════════╧════════╧════════╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
> # (QUERY #1) select 1 - ok
 
> select 1 from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═══╗
 
║ 1 ║
 
╟───╢
 
║ 1 ║
 
║ 1 ║
 
╚═══╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
> # (QUERY #2) select id - ok
 
> select id from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═════╗
 
║ id  ║
 
╟─────╢
 
║ 777 ║
 
║ 888 ║
 
╚═════╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
> # (QUERY #3) select all fields - FAIL
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
>
 
> # ensure subquery worked well
 
> select field1 from t2 where (@foo:=field3);
 
╔════════╗
 
║ field1 ║
 
╟────────╢
 
║    777 ║
 
║    888 ║
 
╚════════╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # drop column "value" in t1
 
> alter table t1 drop column value;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
>
 
> # (QUERY #3) now it works!
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═════╗
 
║ id  ║
 
╟─────╢
 
║ 777 ║
 
║ 888 ║
 
╚═════╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # restore column
 
> alter table t1 add column value integer;
 
Query OK, 0 rows affected (0.02 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
>
 
> # (QUERY #3) make sure it doesn't work again
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # drop column "field2" in t2, effectively make primary key no longer composite
 
> alter table t2 drop column field2;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
>
 
> # (QUERY #3) now it works!
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═════╤═══════╗
 
║ id  │ value ║
 
╟─────┼───────╢
 
║ 777 │  NULL ║
 
║ 888 │  NULL ║
 
╚═════╧═══════╝
 
2 rows in set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # restore column and the index
 
> alter table t2 add column field2 integer after field1;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
> update t2 set field2=1;
 
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2  Warnings: 0
 
 
 
> alter table t2 drop primary key;
 
Query OK, 2 rows affected (0.01 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
> alter table t2 add primary key(field1, field2);
 
Query OK, 2 rows affected (0.01 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
>
 
> # (QUERY #3) make sure it doesn't work again
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # convert t1 to myisam
 
> alter table t1 engine=myisam;
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
>
 
> # (QUERY #1, #2 and #3) whoa, none of them work!
 
> select 1 from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
> select id from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # ok, let's back to innodb
 
> alter table t1 engine=innodb;
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
>
 
> # (QUERY #1, #2 and #3) #1 and #2 work, #3 doesn't work
 
> select 1 from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═══╗
 
║ 1 ║
 
╟───╢
 
║ 1 ║
 
║ 1 ║
 
╚═══╝
 
2 rows in set (0.00 sec)
 
 
 
> select id from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═════╗
 
║ id  ║
 
╟─────╢
 
║ 777 ║
 
║ 888 ║
 
╚═════╝
 
2 rows in set (0.00 sec)
 
 
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # make it 1 row in each table
 
> delete from t1 where id=777;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
> delete from t2 where field1=777;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
>
 
> # (QUERY #3) yay, it works again
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
╔═════╤═══════╗
 
║ id  │ value ║
 
╟─────┼───────╢
 
║ 888 │  NULL ║
 
╚═════╧═══════╝
 
1 row in set (0.00 sec)
 
 
 
>
 
>
 
> ################
 
> # restore deleted rows
 
> insert t1 set id=777;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
> insert t2 set field1=777, field2=1, field3=1;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
>
 
> # (QUERY #3) make sure it doesn't work again
 
> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2015-03-09 ]

Thanks for the report and the test case.

Note: Could only reproduce it with XtraDB (not with InnoDB).

An extract from the test case above:

drop table if exists t1;
 
create table t1(
 
       id integer primary key,
 
       value integer
 
) engine=innodb; # engine=innodb - important
 
insert t1 set id=777;
 
insert t1 set id=888;
 
 
 
drop table if exists t2;
 
create table t2 (
 
       field1 integer,
 
       field2 integer,
 
       field3 integer,
 
       primary key(field1, field2) # index on field1 and field2 only
 
) engine=innodb; 
insert t2 set field1=777, field2=1, field3=1;
 
insert t2 set field1=888, field2=1, field3=1;
 
 
 
select id from t1 where id in(select field1 from t2 where (@foo:=field3));
 
select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 

MariaDB [test]> select id from t1 where id in(select field1 from t2 where (@foo:=field3));
 
+-----+
 
| id  |
 
+-----+
 
| 777 |
 
| 888 |
 
+-----+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
Empty set (0.00 sec)
 

MariaDB [test]> explain extended
 
    -> select id from t1 where id in(select field1 from t2 where (@foo:=field3));
 
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------+
 
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
 
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------+
 
|    1 | PRIMARY      | t1          | index  | PRIMARY       | PRIMARY      | 4       | NULL |    2 |   100.00 | Using index |
 
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |   100.00 |             |
 
|    2 | MATERIALIZED | t2          | ALL    | PRIMARY       | NULL         | NULL    | NULL |    2 |   100.00 | Using where |
 
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------+
 
3 rows in set, 1 warning (0.00 sec)
 

MariaDB [test]> explain extended
 
    -> select * from t1 where id in(select field1 from t2 where (@foo:=field3));
 
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type  | table       | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | NULL | NULL    | NULL |    2 |   100.00 |                                                 |
 
|    1 | PRIMARY      | t1          | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    2 | MATERIALIZED | t2          | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |   100.00 | Using where                                     |
 
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
3 rows in set, 1 warning (0.00 sec)

Comment by Sergei Golubchik [ 2022-11-20 ]

Works correctly in 10.3.37

Generated at Thu Feb 08 07:21:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.