[MDEV-7678] Wrong result with @variable in subquery (Subquery with @variable acts weird) - JIRA

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0
Fix Version/s: 10.0
Component/s: Optimizer, Storage Engine - XtraDB
Labels:
None

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)

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

queries.sql
3 kB
2015-03-07 03:01

Activity

Hide

Permalink

Elena Stepanova added a comment - 2015-03-09 18:37 - edited

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)

Show

Elena Stepanova added a comment - 2015-03-09 18:37 - edited 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)

Wrong result with @variable in subquery (Subquery with @variable acts weird)

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates

Agile