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