Bug #54682 set sql_select_limit=0 does not work

• Submitted: 22 Jun 2010 0:47
• Modified: 4 Aug 2010 16:52
• Reporter: Vladislav Vaintroub
• Status: Closed
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.5
• OS: Any
• Assigned to: Guilhem Bichot
• CPU Architecture: Any
• Tags: connector/net broken, regression

[22 Jun 2010 0:47] Vladislav Vaintroub

Description:
set sql_select_limit=0 is broken in 5.5, 

it does not work anymore 

and brings a warning.

This breaks Connector/NET, which  
1)uses this trick implement CommandBehavior.SchemaOnly
option described here 
http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

(only reas re2sult set metadata)

2)treats warnings as errors.

It used to work in 5.1, so it is a regression.

How to repeat:
mysql> set sql_select_limit=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

[28 Jun 2010 14:08] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112354

3281 Guilhem Bichot	2010-06-28
      BUG#54682 "set sql_select_limit=0 does not work"; let SQL_SELECT_LIMIT=0
      work like it does in 5.1.
     @ mysql-test/suite/sys_vars/r/sql_select_limit_func.result
        before the fix, the SET would emit a warning (0 being rounded up to 1)
        and SELECTs would return one row.
     @ sql/sys_vars.cc
        0 is allowed, it means an implicit LIMIT 0 (i.e. no rows returned)

[29 Jun 2010 11:57] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112449

3274 Guilhem Bichot	2010-06-28
      BUG#54682 "set sql_select_limit=0 does not work"; let SQL_SELECT_LIMIT=0
      work like it does in 5.1.
     @ mysql-test/suite/sys_vars/r/sql_select_limit_func.result
        before the fix, the SET would emit a warning (0 being rounded up to 1)
        and SELECTs would return one row.
     @ sql/sys_vars.cc
        0 is allowed, it means an implicit LIMIT 0 (i.e. no rows returned)

[29 Jun 2010 12:19] Guilhem Bichot

pushed to next-mr-bugfixing

[8 Jul 2010 12:36] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113121

3098 Guilhem Bichot	2010-07-08
      backport of [e-mail]-20100628140739-i9vy8ugxp1v5aspb
      from next-mr-bugfixing:
      BUG#54682 "set sql_select_limit=0 does not work"; let SQL_SELECT_LIMIT=0
      work like it does in 5.1.
     @ mysql-test/suite/sys_vars/r/sql_select_limit_func.result
        before the fix, the SET would emit a warning (0 being rounded up to 1)
        and SELECTs would return one row.
     @ sql/sys_vars.cc
        0 is allowed, it means an implicit LIMIT 0 (i.e. no rows returned)

[8 Jul 2010 12:39] Guilhem Bichot

backported to mysql-trunk-bugfixing

[23 Jul 2010 12:23] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100723121820-jryu2fuw3pc53q9w) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

[23 Jul 2010 12:30] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100723121929-90e9zemk3jkr2ocy) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (pib:18)

[31 Jul 2010 16:34] Paul DuBois

Noted in 5.5.6 changelog.

SET sql_select_limit = 0 did not work.

[4 Aug 2010 8:04] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

[4 Aug 2010 8:20] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

[4 Aug 2010 16:52] Paul DuBois

Not present in any released 5.6.x version.