MySQL Bugs: #65431: Error 1430 while selecting from a federated table with index on a bit column

Bug #65431	Error 1430 while selecting from a federated table with index on a bit column
Submitted:	27 May 2012 2:59	Modified:	27 May 2012 7:23
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysqltest: At line 9: query 'SELECT d+0 FROM t1 WHERE d < 10000' failed: 1030: Got error 1430 from storage engine

Here is what the general log contains (note the constant in the last record):

2 Query     CREATE DATABASE fed
2 Query     CREATE TABLE fed.t1 (d BIT(32), KEY(d)) ENGINE=MyISAM
2 Query     CREATE TABLE t1 (d BIT(32), KEY(d)) 
ENGINE=FEDERATED 
CONNECTION='mysql://root@127.0.0.1:13000/fed/t1'
2 Query     SELECT d+0 FROM t1 WHERE d < 10000
3 Connect   root@localhost on fed
3 Query     SELECT `d` FROM `t1` WHERE 1=0
3 Query     SHOW TABLE STATUS LIKE 't1'
3 Query     SELECT `d` FROM `t1` WHERE  (`d` IS NOT NULL ) AND ( (`d` < '^@^@'^P') )

How to repeat:
CREATE DATABASE fed;
CREATE TABLE fed.t1 (d BIT(32), KEY(d)) ENGINE=MyISAM;

eval 
CREATE TABLE t1 (d BIT(32), KEY(d)) ENGINE=FEDERATED
CONNECTION='mysql://root@127.0.0.1:$MASTER_MYPORT/fed/t1';

SELECT d+0 FROM t1 WHERE d < 10000;

Thank you for the bug report. Verified with 5.5.26 on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, 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> CREATE TABLE t1 (d BIT(32), KEY(d)) ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE tfed (d BIT(32), KEY(d)) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:3306/test/t1';
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT d+0 FROM t1 WHERE d < 10000;
Empty set (0.00 sec)

mysql> SELECT d+0 FROM tfed WHERE d < 10000;
ERROR 1030 (HY000): Got error 1430 from storage engine