Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.1
-
None
-
None
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.