Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.9, 10.10, 10.11, 11.0, 11.1, 11.2.1
Description
A table with an index with nullable fields does not correctly handle the "<=>" null-safe equals operator when used in an inner join "on" clause. This issue does not seem to occur in version 11.0.2.
Setup:
CREATE TEMPORARY TABLE `Building` ( |
`id` int(10) unsigned NOT NULL, |
`number` int(10) unsigned DEFAULT 0, |
`name` varchar(47) DEFAULT NULL, |
`street` mediumint(8) unsigned DEFAULT NULL, |
PRIMARY KEY (`id`), |
KEY `streetNumber` (`street`,`number`,`name`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; |
 |
INSERT INTO Building (id, number, name, street) VALUES (100733476, 14, NULL, 1115569); |
The following query is expected to return one row, but instead returns no rows:
SELECT
|
b1.id
|
FROM
|
Building b1
|
INNER JOIN Building b2 ON ( |
b1.street = b2.street
|
AND b1.number <=> b2.number |
AND b1.name <=> b2.name |
)
|
Expected: 1 row "id: 100733476"
Found: no rows
The same query without using the "streetNumber" index does return a single row:
SELECT
|
b1.id
|
FROM
|
Building b1 IGNORE INDEX (streetNumber) |
INNER JOIN Building b2 IGNORE INDEX (streetNumber) ON ( |
b1.street = b2.street
|
AND b1.number <=> b2.number |
AND b1.name <=> b2.name |
)
|
Expected: 1 row "id: 100733476"
Returns: 1 row "id: 100733476"
The same query with <=> operator replaced with an equivalent expression does return a single row:
SELECT
|
b1.id
|
FROM
|
Building b1
|
INNER JOIN Building b2 ON ( |
b1.street = b2.street
|
AND b1.number <=> b2.number |
AND (b1.name = b2.name OR (b1.name IS NULL AND b2.name IS NULL)) |
)
|
Expected: 1 row "id: 100733476"
Returns: 1 row "id: 100733476"
Attachments
Issue Links
- duplicates
-
-
- Closed
-