Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.21, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
-
Gentoo linux
Description
MariaDB brings values passed on where clause as result field values in queries multi table with INNER join against empty table (innodb engine).
also tested against aria and myisam (both works as expected)
At the moment, I can't check against newer MariaDB versions.
How to reproduce:
-- Create temporary tables
|
create table tmp1 (idTmp1 int unsigned not null primary key); |
create table tmp2 (idTmp2 int unsigned not null primary key, |
idTmp1 int unsigned not null, |
vlr decimal(12,2), |
foreign key (idTmp1) references tmp1(idTmp1)); |
|
|
-- Insert some data
|
insert into tmp1 values (1); |
|
|
-- without where works as expected
|
select tmp1.idTmp1, tmp2.idTmp2, sum(tmp2.vlr) |
from tmp1 |
inner join tmp2 on tmp2.idTmp1=tmp1.idTmp1; |
-- returns Null, Null, Null
|
|
|
-- with an equality condition in tmp1 brings unexpected data in first field
|
select tmp1.idTmp1, tmp2.idTmp2, sum(tmp2.vlr) |
from tmp1 |
inner join tmp2 on tmp2.idTmp1=tmp1.idTmp1 |
where tmp1.idTmp1=1; |
-- returns 1, Null, Null
|
|
|
-- with a range (simulating equality) in tmp1 brings unexpected data in first field
|
select tmp1.idTmp1, tmp2.idTmp2, sum(tmp2.vlr) |
from tmp1 |
inner join tmp2 on tmp2.idTmp1=tmp1.idTmp1 |
where tmp1.idTmp1 between 1 and 1; |
-- returns 1, Null, Null
|
|
|
-- with an range (different range) in tmp1 works as expected
|
select tmp1.idTmp1, tmp2.idTmp2, sum(tmp2.vlr) |
from tmp1 |
inner join tmp2 on tmp2.idTmp1=tmp1.idTmp1 |
where tmp1.idTmp1 between 0 and 2; |
-- returns Null, Null, Null |