Bug in MySQL 4.x with SQL_CALC_FOUND_ROWS and LIMIT 0

Hi,

According to MySQL manual :

SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in
the result, disregarding any LIMIT clause.

However, if LIMIT 0 is specified, FOUND_ROWS returns always 0

How-to-repeat:

mysql> SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) LIMIT 0;
Empty set (0.00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)

whereas :

mysql> SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

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

Another bug (but well, in this case SQL_CALC_FOUND_ROWS is not really needed
;)) : if FROM is not specify in the query, FOUND_ROWS() returns always 0.

mysql> SELECT SQL_CALC_FOUND_ROWS 1 LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)

Regards,
Jocelyn


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12980@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in MySQL 4.x with SQL_CALC_FOUND_ROWS and LIMIT 0

Jocelyn Fournier writes:
> Hi,
>
> According to MySQL manual :
>
> SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in
> the result, disregarding any LIMIT clause.
>
> However, if LIMIT 0 is specified, FOUND_ROWS returns always 0
>
> How-to-repeat:
>
> mysql> SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) LIMIT 0;
> Empty set (0.00 sec)
>
> mysql> SELECT FOUND_ROWS();
> +--------------+
> | FOUND_ROWS() |
> +--------------+
> | 0 |
> +--------------+
> 1 row in set (0.00 sec)
>
> Regards,
> Jocelyn
>

Hi!

The above is a bug and is fixable, but would require some major
changes in 4.0 and 4.1.

For the moment we should document it, though ....

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12981@lists.mysql.com
To unsubscribe, e-mail