Hi,

I've just discovered a bug with SQL_CALC_FOUND_ROWS.

How-to-repeat :

SELECT SQL_CALC_FOUND_ROWS titre,numeropost,auteur,icone,nbrep,maxnumrep,date,vue,ouvert,lastauteur,son dage FROM forumconthardwarefr7 WHERE numeropost IN (66553,66562,66565,66593,66600,66601,66603,66606,66611,66620,69631,69632,696 35,69636,69723,70499,71309,71526,71549,71552,71834,71859,72695,72696,73076,7 3317,73327,73923,73979,73983,74069,74160,74166,74359,74361,74752,75389,75400 ,76753,77030,77038,77261,77295,77304,77328,77534,77619,77624,77958,78258,785 82,78585,79161,79516,79519,79526,80096,80097,80351,80359,80369,80370,80377,8 0387,80391,80687,80976,81005,81012,81020,81119,81153,81443,81531,82288,83613 ,83833,83868,84285,84625,84629,85016,85325,85376) ORDER BY maxnumrep DESC LIMIT 0, 30;

+------------------------------------------------------------------------+-- ----------+-----------+-------+-------+-----------+---------------------+--- --+--------+------------------+---------+ | titre | numeropost | auteur | icone | nbrep | maxnumrep | date | vue | ouvert | lastauteur | sondage | +------------------------------------------------------------------------+-- ----------+-----------+-------+-------+-----------+---------------------+--- --+--------+------------------+---------+ | vous aimez le vent ? | 85376 | HumanRage | 14 | 30 | 2426135 | 2002-02-03 02:20:00 | 172 | 1 | HumanRage | 1 | | [sondage] ceux ki floodent c des gamins dans leur tete | 85325 | HumanRage | 5 | 11 | 2424335 | 2002-02-02 22:50:24 | 100 | 1 | chapi-chapo | 1 |

<cut>

30 rows in set (0.00 sec)

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

Ouch ! 45370 rows with my WHERE ... IN () syntax ;)

The result is the same with a LIMIT 30,30;

Now the same request but with a change only in the LIMIT :

mysql> SELECT SQL_CALC_FOUND_ROWS titre,numeropost,auteur,icone,nbrep,maxnumrep,date,vue,ouvert,lastauteur,son dage FROM forumconthardwarefr7 WHERE numeropost IN (66553,66562,66565,66593,66600,66601,66603,66606,66611,66620,69631,69632,696 35,69636,69723,70499,71309,71526,71549,71552,71834,71859,72695,72696,73076,7 3317,73327,73923,73979,73983,74069,74160,74166,74359,74361,74752,75389,75400 ,76753,77030,77038,77261,77295,77304,77328,77534,77619,77624,77958,78258,785 82,78585,79161,79516,79519,79526,80096,80097,80351,80359,80369,80370,80377,8 0387,80391,80687,80976,81005,81012,81020,81119,81153,81443,81531,82288,83613 ,83833,83868,84285,84625,84629,85016,85325,85376) ORDER BY maxnumrep DESC LIMIT 60, 30; +------------------------------------------------------------------------+-- ----------+-----------+-------+-------+-----------+---------------------+--- --+--------+-------------------+---------+ | titre | numeropost | auteur | icone | nbrep | maxnumrep | date | vue | ouvert | lastauteur | sondage | +------------------------------------------------------------------------+-- ----------+-----------+-------+-------+-----------+---------------------+--- --+--------+-------------------+---------+ | chanson de deathmetal en flash avec karaoke ! trop trop lol !!!! | 71549 | HumanRage | 10 | 11 | 1794583 | 2001-12-16 16:10:47 | 112 | 1 | HumanRage | 0 | | qui ecoute/a de la zik d'ambience ? [shoutcast inside] | 71526 | HumanRage | 5 | 7 | 1794519 | 2001-12-16 16:01:05 | 64 | 1 | HumanRage | 0 | | 1) oinj 2) livre 3) dodo ==> ciao a tous !!! | 71552 | HumanRage | 1 | 2 | 1786536 | 2001-12-15 01:36:20 | 16 | 1 | dawa | 0 | | dans 11j c noel putaiiiiiiiiiiiinnnnnnnn | 71309 | HumanRage | 4 | 26 | 1778516 | 2001-12-14 01:35:32 | 103 | 1 | dawa | 0 | | hey mickeynox, t'essaie d'apparaitre dernier post sur TOUTE la page 1? | 70499 | Humanrage | 5 | 28 | 1752906 | 2001-12-11 03:16:16 | 89 | 1 | TT__MickeyNox__TT | 0 | | 2h moins le quart, l'heure du petard | 69723 | HumanRage | 1 | 5 | 1727149 | 2001-12-08 02:01:23 | 35 | 1 | 010010011 | 0 | +------------------------------------------------------------------------+-- ----------+-----------+-------+-------+-----------+---------------------+--- --+--------+-------------------+---------+ 6 rows in set (0.11 sec)

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

Now the result is right.

I'm using MySQL 4.0.2-alpha, but the result is the same with the MySQL 4.0.1 binaries.

I uploaded the forumconthardwarefr7 table in ftp://support.mysql.com/pub/mysql/secret/sql_calc_found_rows_bug.tar.gz

PS : Sorry if this is the second time you receive the mail, but the first time, I sent it with AOL so I wonder if the list received it...

Best Regards,

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