[MCOL-4602] IN conditional returns empty set but INNER JOIN works correctly Created: 2021-03-11  Updated: 2022-05-06  Resolved: 2021-06-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 5.5.1
Fix Version/s: 6.1.1

Type: Bug Priority: Major
Reporter: Rob Schwyzer Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Multiple. Specific environment used for this bug report is AWS t2.micro VM running CentOS 7 with MariaDB 10.5.8 Enterprise Server installed with single-node ColumnStore v5.5.1 installed and configured. Note this has been tested on machines with significantly more RAM without any change in outcome.

Attachments: File dwh_8.sql    

 Description   

The depth of the query seems to matter. For example, the below works-

SELECT
 
  id
 
FROM
 
  people
 
WHERE
 
  id IN 
(
 
  SELECT
 
    id
 
  FROM
 
    people
 
  LEFT OUTER JOIN
 
    blacklist
 
  ON
 
    people.hash = blacklist.hash
 
  WHERE
 
    people.hash NOT IN
 
  (
 
    SELECT
 
      blacklist.hash id
 
    FROM
 
      blacklist
 
    WHERE
 
      blacklist.email NOT LIKE '%mariadb%'
 
  )
 
);

But this does not-

SELECT
 
  id
 
FROM
 
  people
 
WHERE
 
  id IN
 
(
 
  SELECT
 
    id
 
  FROM
 
  (
 
    SELECT
 
      id
 
    FROM
 
      people
 
    LEFT OUTER JOIN
 
      blacklist
 
    ON
 
      people.hash = blacklist.hash
 
    WHERE
 
      people.hash NOT IN
 
    (
 
      SELECT
 
        id
 
      FROM
 
      (
 
        SELECT
 
          blacklist.hash id
 
        FROM
 
          blacklist
 
        WHERE
 
          blacklist.email NOT LIKE '%mariadb%'
 
      ) AS S_3
 
    )
 
  ) AS S_2
 
);

This returns an empty set.

Yet restructuring the query for INNER JOIN resolves this limitation-

SELECT
 
  id
 
FROM
 
  people
 
INNER JOIN
 
(
 
  SELECT
 
    id
 
  FROM
 
  (
 
    SELECT
 
      id
 
    FROM
 
      people
 
    LEFT OUTER JOIN
 
      blacklist
 
    ON
 
      people.hash = blacklist.hash
 
    WHERE
 
      people.hash NOT IN
 
    (
 
      SELECT
 
        id
 
      FROM
 
      (
 
        SELECT
 
          blacklist.hash id
 
        FROM
 
          blacklist
 
        WHERE
 
          blacklist.email NOT LIKE '%mariadb%'
 
      ) AS S_3
 
    )
 
  ) AS S_2
 
) AS S_3;

No warnings or similar are given on the command-line suggesting a limit has been tripped when trying to do the problem query, and likewise nothing is present in ColumnStore's logs.

 Comments   
Comment by Daniel Lee (Inactive) [ 2021-06-11 ]

Build verified: 6.1.1 ( Drone #2573)

Generated at Thu Feb 08 02:51:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.