MDEV-14907 FEDERATEDX doesn't respect DISTINCT

Federated and Federatex cannot be used with ROR scans

Federated::position() and Federatex::position() is storing in 'ref' a
pointer into a local result set buffer. This means that one cannot
compare 'ref' from different handler instances to see if they point to the
same physical record.

This bug caused federated.federatedx to return wrong results when the
optimizer tried to use index_merge to resolve some queries.

Fixed by introducing table flag HA_NON_COMPARABLE_ROWID and using this
with the above handlers.

Todo:
- Fix multi_delete(), multi_update and read_records() to use primary key
  instead of 'ref' if case HA_NON_COMPARABLE_ROWID is set. The current
  code only works if we have only one range (like table scan) for the
  tables that will be updated in the second pass.
- Enable DBUG_ASSERT() in ha_federated::cmp_ref() and
  ha_federatedx::cmp_ref().

7 files changed, 138 insertions, 3 deletions

diff --git a/mysql-test/suite/federated/optimizer.result b/mysql-test/suite/federated/optimizer.result
new file mode 100644
index 00000000000..5d7072e0b35
--- /dev/null
+++ b/mysql-test/suite/federated/optimizer.result
@@ -0,0 +1,52 @@
+connect  master,127.0.0.1,root,,test,$MASTER_MYPORT,;
+connect  slave,127.0.0.1,root,,test,$SLAVE_MYPORT,;
+connection master;
+CREATE DATABASE federated;
+connection slave;
+CREATE DATABASE federated;
+connection default;
+#
+# MDEV-14907 FEDERATEDX doesn't respect DISTINCT
+#
+CREATE TABLE t1 (
+`foo_id` bigint(20) unsigned NOT NULL,
+`foo_name` varchar(255) DEFAULT NULL,
+`parent_foo_id` bigint(20) unsigned DEFAULT NULL,
+PRIMARY KEY (`foo_id`),
+KEY `foo_name` (`foo_name`),
+KEY `parent_foo_id` (`parent_foo_id`)
+) DEFAULT CHARSET=utf8;
+CREATE TABLE `fed_t1` ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root@127.0.0.1:MASTER_PORT/test/t1';
+INSERT INTO t1 VALUES (968903, 'STRING - 0', 822857);
+INSERT INTO t1 VALUES (968953, 'STRING - 1', 822857);
+INSERT INTO t1 VALUES (971603, 'STRING - 2', 822857);
+INSERT INTO t1 VALUES (971803, 'STRING - 3', 822857);
+INSERT INTO t1 VALUES (975103, 'STRING - 4', 822857);
+INSERT INTO t1 VALUES (822857, 'STRING', NULL);
+select foo_id,parent_foo_id,foo_name from t1 where parent_foo_id = 822857 or foo_name like 'STRING%';
+foo_id	parent_foo_id	foo_name
+968903	822857	STRING - 0
+968953	822857	STRING - 1
+971603	822857	STRING - 2
+971803	822857	STRING - 3
+975103	822857	STRING - 4
+822857	NULL	STRING
+explain
+select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	fed_t1	ALL	foo_name,parent_foo_id	NULL	NULL	NULL	6	Using where
+select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%';
+foo_id	parent_foo_id	foo_name
+968903	822857	STRING - 0
+968953	822857	STRING - 1
+971603	822857	STRING - 2
+971803	822857	STRING - 3
+975103	822857	STRING - 4
+822857	NULL	STRING
+DROP TABLE fed_t1, t1;
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
diff --git a/mysql-test/suite/federated/optimizer.test b/mysql-test/suite/federated/optimizer.test
new file mode 100644
index 00000000000..4263060fab5
--- /dev/null
+++ b/mysql-test/suite/federated/optimizer.test
@@ -0,0 +1,39 @@
+#
+#Test optimizer flags related to federated tables
+#
+
+--source have_federatedx.inc
+--source include/federated.inc
+
+connection default;
+
+--echo #
+--echo # MDEV-14907 FEDERATEDX doesn't respect DISTINCT
+--echo #
+
+CREATE TABLE t1 (
+  `foo_id` bigint(20) unsigned NOT NULL,
+  `foo_name` varchar(255) DEFAULT NULL,
+  `parent_foo_id` bigint(20) unsigned DEFAULT NULL,
+  PRIMARY KEY (`foo_id`),
+  KEY `foo_name` (`foo_name`),
+  KEY `parent_foo_id` (`parent_foo_id`)
+) DEFAULT CHARSET=utf8;
+ 
+--replace_result $MASTER_MYPORT MASTER_PORT
+eval CREATE TABLE `fed_t1` ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1';
+INSERT INTO t1 VALUES (968903, 'STRING - 0', 822857);
+INSERT INTO t1 VALUES (968953, 'STRING - 1', 822857);
+INSERT INTO t1 VALUES (971603, 'STRING - 2', 822857);
+INSERT INTO t1 VALUES (971803, 'STRING - 3', 822857);
+INSERT INTO t1 VALUES (975103, 'STRING - 4', 822857);
+INSERT INTO t1 VALUES (822857, 'STRING', NULL);
+
+select foo_id,parent_foo_id,foo_name from t1 where parent_foo_id = 822857 or foo_name like 'STRING%';
+
+explain
+select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%';
+select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%';
+DROP TABLE fed_t1, t1;
+
+source include/federated_cleanup.inc;
diff --git a/sql/handler.h b/sql/handler.h
index 9b44b3fd297..815641b49a8 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -329,7 +329,17 @@ enum enum_alter_inplace_result {
 
 /* Support native hash index */
 #define HA_CAN_HASH_KEYS        (1ULL << 58)
-#define HA_LAST_TABLE_FLAG HA_CAN_HASH_KEYS
+/*
+  Rowid's are not comparable. This is set if the rowid is unique to the
+  current open handler, like it is with federated where the rowid is a
+  pointer to a local result set buffer. The effect of having this set is
+  that the optimizer will not consirer the following optimizations for
+  the table:
+  ror scans or filtering
+*/
+#define HA_NON_COMPARABLE_ROWID (1ULL << 59)
+
+#define HA_LAST_TABLE_FLAG HA_NON_COMPARABLE_ROWID
 
 /* bits in index_flags(index_number) for what you can do with index */
 #define HA_READ_NEXT            1       /* TODO really use this flag */
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 5f13c9b3441..d3104019edb 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2678,6 +2678,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   records= head->stat_records();
   if (!records)
     records++;					/* purecov: inspected */
+  if (head->file->ha_table_flags() & HA_NON_COMPARABLE_ROWID)
+    only_single_index_range_scan= 1;
 
   if (head->force_index || force_quick_range)
     scan_time= read_time= DBL_MAX;
@@ -11312,6 +11314,9 @@ static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts)
                                 table_key->user_defined_key_parts);
   uint pk_number;
   
+  if (param->table->file->ha_table_flags() & HA_NON_COMPARABLE_ROWID)
+    return false;
+
   for (KEY_PART_INFO *kp= table_key->key_part; kp < key_part; kp++)
   {
     uint16 fieldnr= param->table->key_info[keynr].
diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc
index 9faab828871..75f2ab71ecd 100644
--- a/sql/rowid_filter.cc
+++ b/sql/rowid_filter.cc
@@ -347,6 +347,9 @@ void TABLE::init_cost_info_for_usable_range_rowid_filters(THD *thd)
   usable_range_filter_keys.clear_all();
   key_map::Iterator it(quick_keys);
 
+  if (file->ha_table_flags() & HA_NON_COMPARABLE_ROWID)
+    return;                                     // Cannot create filtering
+
   /*
     From all indexes that can be used for range accesses select only such that
     - range filter pushdown is supported by the engine for them     (1)
diff --git a/storage/federated/ha_federated.h b/storage/federated/ha_federated.h
index 3beb2ca9570..8d6c28556c4 100644
--- a/storage/federated/ha_federated.h
+++ b/storage/federated/ha_federated.h
@@ -146,7 +146,7 @@ public:
             HA_NO_PREFIX_CHAR_KEYS | HA_PRIMARY_KEY_REQUIRED_FOR_DELETE |
             HA_NO_TRANSACTIONS /* until fixed by WL#2952 */ |
             HA_PARTIAL_COLUMN_READ | HA_NULL_IN_KEY |
-            HA_CAN_ONLINE_BACKUPS |
+            HA_CAN_ONLINE_BACKUPS | HA_NON_COMPARABLE_ROWID |
             HA_CAN_REPAIR);
   }
   /*
@@ -238,6 +238,19 @@ public:
   int rnd_next_int(uchar *buf);
   int rnd_pos(uchar *buf, uchar *pos);                            //required
   void position(const uchar *record);                            //required
+  /*
+    A ref is a pointer inside a local buffer. It is not comparable to
+    other ref's. This is never called as HA_NON_COMPARABLE_ROWID is set.
+  */
+  int cmp_ref(const uchar *ref1, const uchar *ref2)
+  {
+#ifdef NOT_YET
+    DBUG_ASSERT(0);
+    return 0;
+#else
+    return handler::cmp_ref(ref1,ref2);         /* Works if table scan is used */
+#endif
+  }
   int info(uint);                                              //required
   int extra(ha_extra_function operation);
 
diff --git a/storage/federatedx/ha_federatedx.h b/storage/federatedx/ha_federatedx.h
index 7b6504db93d..c9d80dd8282 100644
--- a/storage/federatedx/ha_federatedx.h
+++ b/storage/federatedx/ha_federatedx.h
@@ -338,7 +338,7 @@ public:
             | HA_REC_NOT_IN_SEQ | HA_AUTO_PART_KEY | HA_CAN_INDEX_BLOBS |
             HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE | HA_CAN_REPAIR |
             HA_PRIMARY_KEY_REQUIRED_FOR_DELETE | HA_CAN_ONLINE_BACKUPS |
-            HA_PARTIAL_COLUMN_READ | HA_NULL_IN_KEY);
+            HA_PARTIAL_COLUMN_READ | HA_NULL_IN_KEY | HA_NON_COMPARABLE_ROWID);
   }
   /*
     This is a bitmap of flags that says how the storage engine
@@ -428,6 +428,19 @@ public:
   int rnd_next(uchar *buf);                                      //required
   int rnd_pos(uchar *buf, uchar *pos);                            //required
   void position(const uchar *record);                            //required
+  /*
+    A ref is a pointer inside a local buffer. It is not comparable to
+    other ref's. This is never called as HA_NON_COMPARABLE_ROWID is set.
+  */
+  int cmp_ref(const uchar *ref1, const uchar *ref2)
+  {
+#ifdef NOT_YET
+    DBUG_ASSERT(0);
+    return 0;
+#else
+    return handler::cmp_ref(ref1,ref2);         /* Works if table scan is used */
+#endif
+  }
   int info(uint);                                              //required
   int extra(ha_extra_function operation);