Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.1, 10.2.2, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Description
Appends earlier versions as well.
Put together:
- a federated table
- a composite index
- a select with condition column IS NULL and at least one other condition, both in the composite index
and the result will be an empty result set, even if there should have been rows returned.
Bug does not occur, when:
- single column index is used or
- no index is used or
- condition IS NULL is only condition or
- condition IS NOT NULL is used instead
Test case:
-- use this server as remote and local server for test-case
|
-- bug behaviour is the same with 'real' remote server
|
DROP SERVER IF EXISTS local_server; |
CREATE SERVER local_server |
FOREIGN DATA WRAPPER mysql |
OPTIONS (
|
HOST '127.0.0.1', |
PORT 3306,
|
USER 'USER', |
PASSWORD 'PASS', |
DATABASE 'test' |
);
|
|
|
-- create sample table and fill with data
|
DROP TABLE IF EXISTS test.remote_table; |
CREATE TABLE test.remote_table ( |
a TINYINT DEFAULT NULL, |
b TINYINT NOT NULL, |
KEY _ab (a,b), |
KEY _ba (b,a), |
KEY _a (a), |
KEY _b (b) |
) AS |
SELECT NULL AS a, 1 AS b |
UNION
|
SELECT 2 AS a, 3 AS b; |
|
|
-- create federated table pointing to table above
|
DROP TABLE IF EXISTS test.local_table; |
CREATE TABLE test.local_table ( |
a TINYINT DEFAULT NULL, |
b TINYINT NOT NULL, |
KEY _ab (a,b), |
KEY _ba (b,a), |
KEY _a (a), |
KEY _b (b) |
) ENGINE=federated CONNECTION='local_server/remote_table'; |
|
|
-- remote table: OK
|
SELECT * FROM test.remote_table |
WHERE a IS NULL AND b BETWEEN 1 AND 3; |
-- returns 1 row
|
|
|
-- not using index: OK
|
SELECT * FROM test.local_table USE INDEX () |
WHERE a IS NULL AND b BETWEEN 1 AND 3; |
-- returns 1 row
|
|
|
-- use column index a: OK
|
SELECT * FROM test.local_table USE INDEX (_a) |
WHERE a IS NULL AND b BETWEEN 1 AND 3; |
-- returns 1 row
|
|
|
-- use column index b: OK
|
SELECT * FROM test.local_table USE INDEX (_b) |
WHERE a IS NULL AND b BETWEEN 1 AND 3; |
-- returns 1 row
|
|
|
-- use composite index, a is NOT null: OK
|
SELECT * FROM test.local_table USE INDEX (_ab) |
WHERE a IS NOT NULL AND b BETWEEN 1 AND 3; |
-- returns 1 row
|
|
|
-- use composite index, a=2: OK
|
SELECT * FROM test.local_table USE INDEX (_ab) |
WHERE a=2 AND b BETWEEN 1 AND 3; |
-- returns 1 row
|
|
|
-- use composite index, only one condition: OK
|
SELECT * FROM test.local_table USE INDEX (_ab) |
WHERE a IS NULL; |
-- returns 1 row
|
|
|
-- use composite index, a first column, a is null: WRONG
|
SELECT * FROM test.local_table USE INDEX (_ab) |
WHERE a IS NULL AND b BETWEEN 1 AND 3; |
-- returns no rows
|
-- should return 1 row
|
|
|
-- use composite index, a last column, a is null: WRONG
|
SELECT * FROM test.local_table USE INDEX (_ba) |
WHERE a IS NULL AND b BETWEEN 1 AND 3; |
-- returns no rows
|
-- should return 1 row |