Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.3.12, 5.5, 10.0
-
None
-
Arch Linux
Description
I have two SQL statements that is logically identical but MariaDB give different results.
The first statement produces no result while the second produced one.
I have test the same data on another DBMS and the results of the two are the same.
The two SQL statements are
1.
select |
way_id
|
from
|
way_tags_test
|
where
|
k = 'highway' and |
v in ( |
select type from way_types |
) and |
way_id in ( |
select way_id from taxi.way_tags_test where k = 'name' |
)
|
;
|
2.
select |
way_id
|
from
|
way_tags_test
|
where
|
k = 'name' and |
way_id in ( |
select |
way_id
|
from |
way_tags_test
|
where |
k='highway' and |
v in ( |
select type from way_types |
)
|
)
|
;
|
The table contents are as below:
way_tags_test have two rows:
99979604 highway living_street 2
|
99979604 name 九华山 2
|
way_types have only one row:
1 motorway
|
DDLs for table are as below:
CREATE TABLE `way_tags_test` ( |
`way_id` bigint(20) NOT NULL, |
`k` varchar(255) DEFAULT NULL, |
`v` varchar(255) DEFAULT NULL, |
`version` bigint(20) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
CREATE TABLE `way_types` ( |
`id` int(11) NOT NULL, |
`type` varchar(32) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |