MySQL Bugs: #8726: dissappering query, hanging mysql client,other oddities with MAX_JOIN

Bug #8726	dissappering query, hanging mysql client,other oddities with MAX_JOIN_SIZE
Submitted:	23 Feb 2005 12:19	Modified:	11 Mar 2005 0:27
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
first: is MAX_JOIN_SIZE supposed to impose a limit on queries from just one table (no join)? the documentation speaks of limits in generic, so could include this, but doesnt point it out. (since the name of the variable implies a JOIN, the documentayiion should be clear on this)

Whatever it is meant to do, the effect it has on selects with no jooin is rather unpredictable, see below. (exmaple 1)

if you have a derved query, something very strange will happen. (Example 2). If your query will exceed the limit (as found above), the query will hang.
At least the client will wat forever to get an answer from the server. Because the processlist doesnt show the query, it shows a sleeping thread. Killing the sleeping thread will realese the client (tested on a local mysqlclient via local socket)

example 2a shows, this does also happen, if the derived query is a join

How to repeat:
# example 1
create table one (a int);
insert into one values (1),(2),(3),(4),(5),(1),(2),(3),(4),(5),(1),(2),(3),(4),(5),(1),(2),(3),(4),(5),(1),(2),(3),(4),(5),(1),(2),(3),(4),(5);

#table contains 30 vales;
 select * from one; # works
 SET local  MAX_JOIN_SIZE=29;
 select * from one; # works
 SET local  MAX_JOIN_SIZE=1;
 select * from one; # does NOT work
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

# it willnot work up to
 SET local  MAX_JOIN_SIZE=8;
# setting it to 9 or above, and it will work (value depents on the amount rows in the table)

##
#Example 2 (same table)

#in order to work the below query, the value now has to be 18 or above
 SET local  MAX_JOIN_SIZE=18;
 select * from (select * from one ) x;
#works

# for value from 9 to 17
 SET local  MAX_JOIN_SIZE=9;
 select * from (select * from one ) x;
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

# and with 8 or belo
 SET local  MAX_JOIN_SIZE=8;
 select * from (select * from one ) x;
# the mysql client does not return with an answer, it hangs

# do in anothe process
 show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 808 | root | localhost | xxx  | Query   |    0 | NULL  | show processlist |
| 836 | root | localhost | xxx  | Sleep   |  207 |       | NULL             |
+-----+------+-----------+------+---------+------+-------+------------------+

# the query is not shown, but if I issue
kill 836;
# the hanging client is released, so it was waiting for the server

# Example 2a
 SET local  MAX_JOIN_SIZE=1;
select * from (select * from one a, one b) x;
# hangs

# Example 2b
# this can be done without table
 SET local  MAX_JOIN_SIZE=1;
select * from (select 1 union select 2 union select 3) x;
# hangs

Suggested fix:
-

MAX_JOIN_SIZE is the total number of rows that must be examined by the query.

From the manual:

Don't allow SELECT statements that probably need to examine more than max_join_size row combinations or are likely to do more than max_join_size disk seeks.

Thanks for your bug report !

I forgot to mention that the above doesn't apply to results that are coming from query cache.

Thanks for the explain, thats how I would have read it, just got irritated when I discovered the above.
Also in terms of using subqueries, I would read it as:

The maximum amount of rows any individual subquery (correlated / derived / or individual part of a union) has to examine, as well as the total outer query/queries.

Otherwise the protrection against large operations would be broken.

"or are likely to do more than max_join_size disk seeks" could explain the  limits  not matching the row counts.

leaves only the problem with the hanging client.

Fix approved by Sergei

Pushed into 4.1.11

Noted in 4.1.11 changelog.

Also added a note to the max_join_size description
that it _does_ apply to single-table queries.