[#MDEV-5261] Cannot use indexes with HTML tables in CONNECT storage engine

I have created a couple of HTML tables from an InnoDB table (employees and departments) - the original tables are from the employees DB used in MySQL training - https://dev.mysql.com/doc/employee/en/.

The tables are:

CREATE TABLE `employees` (

  `emp_no` int(11) NOT NULL,

  `birth_date` date NOT NULL,

  `first_name` varchar(14) NOT NULL,

  `last_name` varchar(16) NOT NULL,

  `hire_date` date NOT NULL

) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=XML `file_name`='/var/lib/mysql_connect/employees.html' `header`=yes `option_list`='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow'

and:

CREATE TABLE `departments` (

  `dept_no` char(4) NOT NULL,

  `dept_name` varchar(40) NOT NULL,

  UNIQUE KEY `dep` (`dept_no`)

) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=XML `file_name`='/var/lib/mysql_connect/departments.html' `header`=yes `option_list`='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow'

As you can see, I have added indexes with no problems.

When I try to use the indexes, I have all sort of problems:

=== Accessing the unique index in departments:

MariaDB [connect_examples]> select * from departments where dept_no = 'd006';

ERROR 1296 (HY000): Got error 122 'Table departments is not indexable' from CONNECT

=== Dropping the index in departments:

MariaDB [connect_examples]> drop index dep on departments;

ERROR 2013 (HY000): Lost connection to MySQL server during query

=== Using EXPLAIN to test the employees window:

MariaDB [connect_examples]> explain select * from employees where emp_no = 10005;

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

|    1 | SIMPLE      | employees | ALL  | employess_ix  | NULL | NULL    | NULL | 300024 | Using where |

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

1 row in set (42.73 sec)

The index from employees is not used and I cannot drop it.

Thanks!
-ivan

[MDEV-5261] Cannot use indexes with HTML tables in CONNECT storage engine Created: 2013-11-08 Updated: 2013-12-05 Resolved: 2013-12-04
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.5, 10.0.6
Fix Version/s:	10.0.6, 10.0.7

[MDEV-5261] Cannot use indexes with HTML tables in CONNECT storage engine Created: 2013-11-08 Updated: 2013-12-05 Resolved: 2013-12-04