From aef1982be0a5ff5bd25fb7658d8792fd9ab40c3f Mon Sep 17 00:00:00 2001 From: Venkata Sidagam Date: Thu, 26 Jul 2012 15:09:22 +0530 Subject: Bug #12876932 - INCORRECT SELECT RESULT ON FEDERATED TABLE Problem description: Table 't' created with two colums having compound index on both the columns under innodb/myisam engine at remote machine. In the local machine same table is created undet the federated engine. A select having where clause with along 'AND' operation gives wrong results on local machine. Analysis: The given query at federated engine is wrongly transformed by federated::create_where_from_key() function and the same was sent to the remote machine. Hence the local machine is showing wrong results. Given query "select c1 from t where c1 <= 2 and c2 = 1;" Query transformed, after ha_federated::create_where_from_key() function is: SELECT `c1`, `c2` FROM `t` WHERE (`c1` IS NOT NULL ) AND ( (`c1` >= 2) AND (`c2` <= 1) ) and the same sent to real_query(). In the above the '<=' and '=' conditions were transformed to '>=' and '<=' respectively. ha_federated::create_where_from_key() function behaving as below: The key_range is having both the start_key and end_key. The start_key is used to get "(`c1` IS NOT NULL )" part of the where clause, this transformation is correct. The end_key is used to get "( (`c1` >= 2) AND (`c2` <= 1) )", which is wrong, here the given conditions('<=' and '=') are changed as wrong conditions('>=' and '<='). The end_key is having {key = 0x39fa6d0 "", length = 10, keypart_map = 3, flag = HA_READ_AFTER_KEY} The store_length is having value '5'. Based on store_length and length values the condition values is applied in HA_READ_AFTER_KEY switch case. The switch case 'HA_READ_AFTER_KEY' is applicable to only the last part of the end_key and for previous parts it is going to 'HA_READ_KEY_OR_NEXT' case, here the '>=' is getting added as a condition instead of '<='. Fix: Updated the 'if' condition in 'HA_READ_AFTER_KEY' case to affect for all parts of the end_key. i.e 'i > 0' will used for end_key, Hence added it in the if condition. mysql-test/suite/federated/federated.test: modified the federated.inc file location mysql-test/suite/federated/federated_archive.test: modified the federated.inc file location mysql-test/suite/federated/federated_bug_13118.test: modified the federated.inc file location mysql-test/suite/federated/federated_bug_25714.test: modified the federated.inc file location mysql-test/suite/federated/federated_bug_35333.test: modified the federated.inc file location mysql-test/suite/federated/federated_debug.test: modified the federated.inc file location mysql-test/suite/federated/federated_innodb.test: modified the federated.inc file location mysql-test/suite/federated/federated_server.test: modified the federated.inc file location mysql-test/suite/federated/federated_transactions.test: modified the federated.inc file location mysql-test/suite/federated/include/federated.inc: moved the file from federated suite to federated/include folder mysql-test/suite/federated/include/federated_cleanup.inc: moved the file from federated suite to federated/include folder mysql-test/suite/federated/include/have_federated_db.inc: moved the file from federated suite to federated/include folder storage/federated/ha_federated.cc: updated the 'if condition' in ha_federated::create_where_from_key() function. --- storage/federated/ha_federated.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'storage/federated') diff --git a/storage/federated/ha_federated.cc b/storage/federated/ha_federated.cc index ae65aeb4cbb..7dd4f6b7149 100644 --- a/storage/federated/ha_federated.cc +++ b/storage/federated/ha_federated.cc @@ -1364,7 +1364,7 @@ bool ha_federated::create_where_from_key(String *to, break; } DBUG_PRINT("info", ("federated HA_READ_AFTER_KEY %d", i)); - if (store_length >= length) /* end key */ + if ((store_length >= length) || (i > 0)) /* for all parts of end key*/ { if (emit_key_part_name(&tmp, key_part)) goto err; -- cgit v1.2.1