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

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL)
Fix Version/s: N/A
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)

Attachments

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

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

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Slava

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2015-03-07 02:59

Updated:: 2022-11-20 13:10

Resolved:: 2022-11-20 13:10

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.