[MDEV-22264] MariaDB 10.3+ LEFT JOIN and user defined variable returns extra rows Created: 2020-04-16  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Storage Engine - InnoDB
Affects Version/s: 10.3.18, 10.4.11, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Evgeny Alperovich Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Fedora Core 29


 Description   

Using MariaDB 10.0 the following SQL code returns just one row:

DROP TABLE IF EXISTS TestTable1;
 
CREATE TABLE `TestTable1` (
 
  `SomeColumn` int(11) NOT NULL,
 
  PRIMARY KEY (`SomeColumn`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
INSERT INTO TestTable1 (`SomeColumn`) VALUES ('42');
 
 
 
SELECT * FROM TestTable1 LEFT JOIN (SELECT @p:=123 AS moo)  pd ON TRUE;
 
 
 
# SomeColumn, moo
 
42, 123

However, the same code in MariaDB 10.3 and 10.4 (unfortunately I don't have access to MariaDB 10.1 or 10.2) returns two rows!

# SomeColumn, moo
 
42, 123
 
42, 123

Note that this only happens when using a user defined variable. For instance, the following code only produces a single row, as expected:

DROP TABLE IF EXISTS TestTable1;
 
CREATE TABLE `TestTable1` (
 
  `SomeColumn` int(11) NOT NULL,
 
  PRIMARY KEY (`SomeColumn`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
INSERT INTO TestTable1 (`SomeColumn`) VALUES ('42');
 
 
 
SELECT * FROM TestTable1 LEFT JOIN (SELECT 123 AS moo)  pd ON TRUE;

We have a lot of code that depends on this behavior and my goal is to make MariaDB 10.3+ behave 'the old way' and return only one row in this case.

NB: You can easily try this out yourself with https://dbfiddle.uk/

 Comments   
Comment by Alice Sherepa [ 2020-04-17 ]

Thanks for the report! I repeated on 10.3-10.5, on 5.5-10.2 rows are not doubled, with InnoDB, not with MyIsam

--source include/have_innodb.inc
 
 
 
create table t1 (a int) engine=innodb;
 
insert into t1 values (1),(2),(3),(4);
 
 
select * from t1 left join (select @p:=123)pd on true;


MariaDB [test]> select * from t1 left join (select @p:=123)pd on true;
 
+------+---------+
 
| a    | @p:=123 |
 
+------+---------+
 
|    1 |     123 |
 
|    2 |     123 |
 
|    3 |     123 |
 
|    4 |     123 |
 
|    1 |     123 |
 
|    2 |     123 |
 
|    3 |     123 |
 
|    4 |     123 |
 
+------+---------+
 
8 rows in set (0.001 sec)

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