Bug #35700 Incorrect startup and default values for sql_select_limit
Submitted: 31 Mar 2008 10:14 Modified: 11 Jul 2011 11:02
Reporter: Rizwan Maredia Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.22 OS:Linux (redhat-linux-gnu, pc-linux-gnu)
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: sql_select_limit

[31 Mar 2008 10:14] Rizwan Maredia
Description:
sql_select_limit has an inappropriate startup and default value of -1. Negative values are also accepted, although negative values acts like 'unlimited'.

How to repeat:

mysql> SELECT @@sql_select_limit;
+--------------------+
| @@sql_select_limit |
+--------------------+
|                 -1 | 
+--------------------+
1 row in set (0.01 sec)

mysql> SET @@sql_select_limit = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_select_limit;
+--------------------+
| @@sql_select_limit |
+--------------------+
|                 10 | 
+--------------------+
1 row in set (0.01 sec)

mysql> SET @@sql_select_limit = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_select_limit;
+--------------------+
| @@sql_select_limit |
+--------------------+
|                 -1 | 
+--------------------+
1 row in set (0.00 sec)

mysql> SET @@sql_select_limit = -10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_select_limit;
+--------------------+
| @@sql_select_limit |
+--------------------+
|                -10 | 
+--------------------+
1 row in set (0.00 sec)

Suggested fix:
Negative values should either give error or should mapped to valid range.
[31 Mar 2008 10:41] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.1.23, and inform about the results.
[31 Mar 2008 10:47] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with version 5.1.23:

=====mysql-5.1.23-rc-linux-i686-glibc23/=====
=====bug35700=====
SELECT @@sql_select_limit;
@@sql_select_limit
18446744073709551615
SET @@sql_select_limit = 10;
SELECT @@sql_select_limit;
@@sql_select_limit
10
SET @@sql_select_limit = DEFAULT;
SELECT @@sql_select_limit;
@@sql_select_limit
18446744073709551615
SET @@sql_select_limit = -10;
SELECT @@sql_select_limit;
@@sql_select_limit
18446744073709551606

Although problem is repeatable with version 5.1.22.

Anyway report has be reclassified as documentation, because manual is not clear which value should be displayed for 
SELECT @@sql_select_limit; statement in DEFAULT case.
[18 Apr 2008 20:02] Paul DuBois
I have updated the description for SQL_SELECT_LIMIT in the manual:

The maximum number of rows to return from SELECT statements. The
default value for a new connection is the maximum number of rows that
the server allows per table, which depends on the server
configuration and may be affected if the server build was configured
with --with-big-tables. Typical default values are (2^32)−1 or
(2^64)−1. If you have changed the limit, the default value can be
restored by assigning a value of DEFAULT.
[11 Jul 2011 11:02] Jon Olav Hauglid
Not repeatable on trunk:

mysql> SELECT @@sql_select_limit;
+----------------------+
| @@sql_select_limit   |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> SET @@sql_select_limit = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_select_limit;
+----------------------+
| @@sql_select_limit   |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> SET @@sql_select_limit = -10;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect sql_select_limit value: '-10' |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

Seems to have been fixed in WL#4738.