[MDEV-27100] Subquery using the ALL keyword on UUID columns produces a wrong result Created: 2021-11-20  Updated: 2022-08-24  Resolved: 2022-08-24

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.7
Fix Version/s: 10.10.1, 10.7.6, 10.8.5, 10.9.3

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27101 Subquery using the ALL keyword on TIM... Closed
relates to MDEV-27098 Subquery using the ALL keyword on TIM... Closed
relates to MDEV-27099 Subquery using the ALL keyword on INE... Closed

 Description   

The problem reported in MDEV-27098 for TIME is repeatable with the UUID data type.

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (d UUID);
 
INSERT INTO t1 VALUES ('00000000-0000-0000-0000-111111111111'), ('11111111-0000-0000-0000-000000000000');
 
SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);


+--------------------------------------+
 
| d                                    |
 
+--------------------------------------+
 
| 00000000-0000-0000-0000-111111111111 |
 
| 11111111-0000-0000-0000-000000000000 |
 
+--------------------------------------+

The expected result should contain only one with with the maximum value '00000000-0000-0000-0000-111111111111'.

SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);


+--------------------------------------+
 
| d                                    |
 
+--------------------------------------+
 
| 00000000-0000-0000-0000-111111111111 |
 
| 11111111-0000-0000-0000-000000000000 |
 
+--------------------------------------+

The expected result should contain only one with with the maximum value '11111111-0000-0000-0000-000000000000'.

 Comments   
Comment by Trevor Gross [ 2022-05-10 ]

I am noticing a possibly related issue with `OR` on the selects where a subquery is involved. Please let me know if this merits a separate issue.

Setup for the test:

CREATE TABLE Persons (
 
    ID uuid NOT NULL,
 
    LastName varchar(255) NOT NULL,
 
    FirstName varchar(255),
 
    Age int,
 
    PRIMARY KEY (ID)
 
);
 
 
 
CREATE TABLE Orders (
 
    OrderID int NOT NULL,
 
    OrderNumber varchar(255) NOT NULL,
 
    PersonID uuid,
 
    PRIMARY KEY (OrderID),
 
    FOREIGN KEY (PersonID) REFERENCES Persons(ID)
 
);
 
 
 
INSERT INTO Persons (id, LastName, FirstName) VALUES ('00000000-0000-0000-0000-000000000001', 'last1', 'first1'), ('00000000-0000-0000-0000-000000000002', 'last2', 'first2');
 
INSERT INTO Orders (OrderID, OrderNumber, PersonID) VALUES (1, '123', '00000000-0000-0000-0000-000000000001'), (2, '456', '00000000-0000-0000-0000-000000000001'), (3, '789', '00000000-0000-0000-0000-000000000002'), (4, '012', '00000000-0000-0000-0000-000000000002');

There are now 2 people and 4 orders. The following returns two of the orders, as expected:

SELECT * FROM Orders
 
WHERE (EXISTS (
 
	SELECT 1 FROM Persons
 
	WHERE
 
		Persons.ID = Orders.PersonID
 
		AND Persons.FirstName LIKE "first1"
 
	)
 
);

However, adding an `OR` to the where clause returns 0 results. There should be no changes in output.

SELECT * FROM Orders
 
WHERE (EXISTS (
 
	SELECT 1 FROM Persons
 
	WHERE
 
		Persons.ID = Orders.PersonID
 
		AND Persons.FirstName LIKE "first1"
 
	)
 
) OR FALSE;

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