Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.18, 10.4.11, 10.3, 10.4
-
None
-
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/