Hi Sergei: Great observation! We've encountered this same issue internally. We'll add this limitation in the VIDEX/README. Thanks you.
We think this may happen because MariaDB/MySQL don't completely separate query optimization from execution. When dealing with equality conditions on PK/UK columns, MySQL/MariaDB calls index_init/index_read functions that attempt to access actual data. Fow now, VIDEX simply returns `HA_ERR_WRONG_COMMAND`, resulting in the 1031 error. We've explored several potential solutions: 1. Skip direct index execution by setting `subquery_to_derived=on`, it works on MySQL 8.0, e.g.: ```sql > SET optimizer_switch='subquery_to_derived=off'; > explain select count(*) from customer,orders where c_custkey=o_custkey and > c_custkey=150000; (1031, "Table storage engine for 'customer' doesn't have this option") > SET optimizer_switch='subquery_to_derived=on'; > explain select count(*) from customer,orders where c_custkey=o_custkey and > c_custkey=150000; +----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | customer | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | Using index | | 1 | SIMPLE | orders | <null> | ref | ORDERS_FK1 | ORDERS_FK1 | 4 | const | 1 | 100.0 | Using index | +----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+ ``` 2. Keep the error as is - it's somewhat reasonable for index recommendation since queries that can use PK/UK often don't need additional indexes. 3. Let index_read directly return 0, indicating to the optimizer that no matching rows exist. 4. Provide virtual data in the buffer based on input conditions. (it may involve complex implementation work). We would appreciate your thoughts on the best solution path, for both MariaDB and MySQL. Best Rong _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
