MySQL Bugs: #83851: Select with multiple conditions against federated table returns empty result set

Bug #83851	Select with multiple conditions against federated table returns empty result set
Submitted:	16 Nov 2016 15:57	Modified:	16 Nov 2016 20:12
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.5/5.6/5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Filing on behalf of our user, initially reported as https://jira.mariadb.org/browse/MDEV-11224

Results of 'How to repeat':

-- use composite index, only one condition: OK
MySQL [test]> SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

MySQL [test]> -- use composite index, a first column, a is null: WRONG
MySQL [test]> SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.01 sec)

MySQL [test]> -- use composite index, a last column, a is null: WRONG
MySQL [test]> SELECT * FROM test.local_table USE INDEX (_ba)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.01 sec)

How to repeat:
-- 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';
 
-- 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

miguel@ural:~/dbs $ ./57c
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution PULL: 2016-NOV-11

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database test;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > use test
Database changed
mysql 5.7 > 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;
Query OK, 2 rows affected (0.85 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 > select * from remote_table;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
|    2 | 3 |
+------+---+
2 rows in set (0.00 sec)

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.18 Source distribution PULL: 2016-NOV-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > USE test
Database changed
mysql 5.7 > CREATE TABLE 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='mysql://miguel:miguel@192.168.2.156:3357/test/remote_table';
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 > -- use composite index, only one condition: OK
mysql 5.7 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.04 sec)

mysql 5.7 > -- returns 1 row
mysql 5.7 > -- use composite index, a first column, a is null: WRONG
mysql 5.7 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.00 sec)

mysql 5.7 > -- returns no rows
mysql 5.7 > -- should return 1 row
mysql 5.7 > -- use composite index, a last column, a is null: WRONG
mysql 5.7 > SELECT * FROM test.local_table USE INDEX (_ba)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.00 sec)

mysql 5.7 > -- returns no rows
mysql 5.7 > -- should return 1 row
mysql 5.7 > SELECT * FROM test.local_table;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
|    2 | 3 |
+------+---+
2 rows in set (0.00 sec)

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution PULL: 2016-NOV-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE test
Database changed
mysql 5.6 > CREATE TABLE 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='mysql://miguel:miguel@192.168.2.156:3356/test/remote_table';
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 > -- use composite index, only one condition: OK
mysql 5.6 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.03 sec)

mysql 5.6 > -- returns 1 row
mysql 5.6 > -- use composite index, a first column, a is null: WRONG
mysql 5.6 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.00 sec)

mysql 5.6 > -- returns no rows
mysql 5.6 > -- should return 1 row
mysql 5.6 > -- use composite index, a last column, a is null: WRONG
mysql 5.6 > SELECT * FROM test.local_table USE INDEX (_ba)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.00 sec)

mysql 5.6 > -- returns no rows
mysql 5.6 > -- should return 1 row

mysql 5.6 > SELECT * FROM test.local_table;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
|    2 | 3 |
+------+---+
2 rows in set (0.00 sec)

mysql 5.6 >

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.1-dmr-debug Source distribution PULL: 2016-NOV-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE test
Database changed
mysql 8.0 > CREATE TABLE 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='mysql://miguel:miguel@192.168.2.156:3380/test/remote_table';
Query OK, 0 rows affected (0.19 sec)

mysql 8.0 > -- use composite index, only one condition: OK
mysql 8.0 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.29 sec)

mysql 8.0 > -- returns 1 row
mysql 8.0 > -- use composite index, a first column, a is null: WRONG
mysql 8.0 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.04 sec)

mysql 8.0 > -- returns no rows
mysql 8.0 > -- should return 1 row
mysql 8.0 > -- use composite index, a last column, a is null: WRONG
mysql 8.0 > SELECT * FROM test.local_table USE INDEX (_ba)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.00 sec)

mysql 8.0 > -- returns no rows
mysql 8.0 > -- should return 1 row

iguel@ural:~/dbs $ ./55c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.55 Source distribution PULL: 2016-NOV-11

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > USE test
Database changed
mysql 5.5 > CREATE TABLE 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='mysql://miguel:miguel@192.168.2.50:3550/test/remote_table';
Query OK, 0 rows affected (0.12 sec)

mysql 5.5 > -- use composite index, only one condition: OK
mysql 5.5 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (5.51 sec)

mysql 5.5 > -- returns 1 row
mysql 5.5 > -- use composite index, a first column, a is null: WRONG
mysql 5.5 > SELECT * FROM test.local_table USE INDEX (_ab)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.01 sec)

mysql 5.5 > -- returns no rows
mysql 5.5 > -- should return 1 row
mysql 5.5 > -- use composite index, a last column, a is null: WRONG
mysql 5.5 > SELECT * FROM test.local_table USE INDEX (_ba)
    -> WHERE a IS NULL AND b BETWEEN 1 AND 3;
Empty set (0.01 sec)

mysql 5.5 > -- returns no rows
mysql 5.5 > -- should return 1 row
mysql 5.5 > SELECT * FROM test.local_table;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
|    2 | 3 |
+------+---+
2 rows in set (0.00 sec)

mysql 5.5 >

Thank you for the bug report.