Bug #629 max_join_size and sql_select_limit affect CREATE SELECT

• Submitted: 11 Jun 2003 0:15
• Modified: 3 Jul 2003 7:05
• Reporter: Guilhem Bichot
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0
• OS: Any (all)
• CPU Architecture: Any

[11 Jun 2003 0:15] Guilhem Bichot

Description:
I don't know if this expected or not. It also affects INSERT SELECT.

How to repeat:
MASTER> create table u(a int);
Query OK, 0 rows affected (0.00 sec)

MASTER> insert into u values(1);
Query OK, 1 row affected (0.00 sec)

MASTER> insert into u values(1);
Query OK, 1 row affected (0.01 sec)

MASTER> insert into u values(1);
Query OK, 1 row affected (0.00 sec)

MASTER> insert into u values(1);
Query OK, 1 row affected (0.00 sec)

MASTER> set max_join_size=2;
Query OK, 0 rows affected (0.00 sec)

MASTER> select * from u,u as u1;
ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

MASTER> create table v as select u1.a from u, u as u1;
ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

MASTER> set sql_select_limit=2;
Query OK, 0 rows affected (0.00 sec)

MASTER> create table v as select * from u;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

[3 Jul 2003 7:05] Michael Widenius

I think MySQL is (probably) doing the correct thing here.

- Transforming a query by adding CREATE TABLE ... before it doesn't change how the query works.
- max_join_size is to limit MySQL to not accept 'wrong' queries, so this is ok
- sql_select_limit is ok as users doesn't normally have this set.  If you set it, I think we should respect that for all selects as setting this should be equal to adding LIMIT to the end of all SELECT queries.