MySQL Bugs: #33489: Falcon queries misinterpret max_join

Bug #33489	Falcon queries misinterpret max_join_size
Submitted:	23 Dec 2007 11:14	Modified:	26 May 2010 17:49
Reporter:	Philip Stoev	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4/6.0BK	OS:	Any
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_HANDLER

Description:
If max_join_size is set to a realistic value (the default is simply too high), Falcon queries will fail even if the number of their rows is far below max_join_size.

max_join_size must be set to about 1000 times greater value than the actual join_size being used. It is likely that anyone who uses a fine-tuned max_join_size different from the default will be unable to issue any SELECT queries.

Innodb is not affected.

How to repeat:
mysql> create table t1 (f1 integer) engine=falcon; # Notice, empty table

mysql> set max_join_size=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

mysql> insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> set max_join_size=10000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Thank you for the bug report.

c:\dbs>6.0t\bin\mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.5-alpha-team-tree-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (f1 integer) engine=falcon;
Query OK, 0 rows affected (0.09 sec)

mysql> set max_join_size=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE
f the SELECT is okay
mysql>

LarsErik,  Please investigate this strange behavior.  You may be able to get help or pointers from Sergey Petrunia, who is an optimizer expert.

Looking briefly at this bug, it looks like Falcon has a strange implementation of scan_time().
If I understand this correctly, this function should return the expected number of IOs necessary to read a given table, so that the optimizer can chose the correct access method. This number also seems to be used to check if we will exceed the max_join_size. 

Falcon calculates this as:

number of records in table * 1000

which to me seems to be a tad high