Bug #32426 FEDERATED query returns corrupt results for ORDER BY on a TEXT or VARCHAR field

Description:
A simple SELECT * from a FEDERATED table gives corrupt results when you ORDER BY a TEXT field.

How to repeat:
On the Server:

mysql> create table t1 (c1 text);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('abc'),('gh'),('f'),('ijk'),('de');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| c1   |
+------+
| abc  | 
| gh   | 
| f    | 
| ijk  | 
| de   | 
+------+
5 rows in set (0.00 sec)

mysql> select * from t1 order by c1;
+------+
| c1   |
+------+
| abc  | 
| de   | 
| f    | 
| gh   | 
| ijk  | 
+------+
5 rows in set (0.00 sec)

On the Client:

mysql> create table t1 (c1 text) engine=federated connection='mysql://root@127.0.0.1:3307/test/t1';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
| abc  | 
| gh   | 
| f    | 
| ijk  | 
| de   | 
+------+
5 rows in set (0.00 sec)

mysql> select * from t1 order by c1;
+------+
| c1   |
+------+
| dek  | 
| de   | 
| d    | 
| de   | 
| dek  | 
+------+
5 rows in set (0.00 sec)

On the Client again:

If you change the FEDERATED table definition to use CHAR instead of TEXT, everything is fine.

mysql> create table t2 (c1 char(10)) engine=federated connection='mysql://root@127.0.0.1:3307/test/t1';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+------+
| c1   |
+------+
| abc  | 
| gh   | 
| f    | 
| ijk  | 
| de   | 
+------+
5 rows in set (0.00 sec)

mysql> select * from t2 order by c1;
+------+
| c1   |
+------+
| abc  | 
| de   | 
| f    | 
| gh   | 
| ijk  | 
+------+
5 rows in set (0.00 sec)

Suggested fix:
Seems to be broken Client side in the FEDERATED engine.  General Query Log on the Server show only "SELECT * FROM t1" queries being issued; ie, ORDER BY happens locally.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39966

ChangeSet@1.2602, 2007-12-14 10:50:56+04:00, ramil@mysql.com +4 -0
  Fix for bug #32426: "FEDERATED query returns corrupt results for
  ORDER BY on a TEXT field"
  
  Problem: storing a reference to a row which is used in fielsort 
  we miss BLOB/TEXT data.
  
  Fix: store a reference as current result set address and data 
  cursor position.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44438

ChangeSet@1.2600, 2008-03-26 14:37:47+04:00, ramil@mysql.com +4 -0
  Fix for bug #32426: "FEDERATED query returns corrupt results for
  ORDER BY on a TEXT field"
  
  Problem: storing a reference to a row which is used in fielsort 
  we miss BLOB/TEXT data.
  
  Fix: store a reference as current result set address and data 
  cursor position.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51725

2767 Ramil Kalimullin	2008-08-15
        Fix for bug #32426: "FEDERATED query returns corrupt results for
        ORDER BY on a TEXT field"
        
        Problem: storing a reference to a row which is used in fielsort 
        we miss BLOB/TEXT data.
        
        Fix: store a reference as current result set address and data 
        cursor position.

Pushed into 6.0.6-alpha  (revid:ramil@mysql.com-20080815121806-r07gbqrl29hezjjh) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)

Noted in 6.0.6 chanagelog.

Query results from a FEDERATED table were corrupt if the query
included an ORDER BY on a TEXT column.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102419

3369 Ramil Kalimullin	2010-03-05
      Fix for bug#32426: "FEDERATED query returns corrupt results
       for ORDER BY on a TEXT or VARCHAR field" backported to 5.1.

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100307164059-cri8typa32cypq0l) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

An entry has been added to the 5.5.4, 5.5.5, and 6.0.14 changelogs:

        A query on a FEDERATED table in which the
        data was ordered by a TEXT column returned
        incorrect results. For example, a query such as the following
        would result in incorrect results if column
        column1 was a TEXT column:

SELECT * FROM table1 ORDER BY column1;

5.5.4 changelog entry was moved to 5.5.5.

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:azundris@mysql.com-20100305174529-3foh5420hds1bgwo) (merge vers: 5.1.45) (pib:16)

The changelog entry has been updated to include version 5.1.46.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)