Bug #1789 Diffrent behaviour of SQL_SELECT_LIMIT in combination with LIMIT

• Submitted: 10 Nov 2003 0:07
• Modified: 13 Nov 2003 15:22
• Reporter: Georg Richter
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1
• OS: Linux (Linux)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[10 Nov 2003 0:07] Georg Richter

Description:
SQL_SELECT_LIMIT in combination with LIMIT doesn't work correctly.

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

mysql> insert into a values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> set sql_select_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a limit 2;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (2.97 sec)

In above command LIMIT overrides SQL_SELECT_LIMIT

mysql> create table b select * from a limit 2;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from b;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

In this case SQL_SELECT_LIMIT overrides LIMIT

[13 Nov 2003 15:22] Dean Ellis

Table b is created with 2 rows (as per the LIMIT in the CREATE .. SELECT statement).  Your final SELECT does not have a LIMIT so is using the SQL_SELECT_LIMIT setting.

Thank you.