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
Triage: Triaged: D3 (Medium)

[23 Dec 2007 11:14] Philip Stoev
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
[23 Dec 2007 22:10] Miguel Solorzano
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>
[29 Aug 2008 20:13] Kevin Lewis
LarsErik,  Please investigate this strange behavior.  You may be able to get help or pointers from Sergey Petrunia, who is an optimizer expert.
[15 Jan 2009 14:56] Lars-Erik Bjørk
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