Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27100

Subquery using the ALL keyword on UUID columns produces a wrong result

Details

    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'.

      Attachments

        Issue Links

          Activity

            tgross35 Trevor Gross added a comment - - edited

            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;
            

            tgross35 Trevor Gross added a comment - - edited 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 ;

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.