Bug #15597 select * ... limit and select count(*) ... have different results

• Submitted: 8 Dec 2005 17:01
• Modified: 9 Dec 2005 10:43
• Reporter: Oleg Marchuk
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.11-standard
• OS: alt linux
• CPU Architecture: Any

[8 Dec 2005 17:01] Oleg Marchuk

Description:
select count(*) ... returns 35
select * ... limit 0,100 returns 35 records
select * ... limit 0,25 returns 7 records

How to repeat:
Use attached files

[8 Dec 2005 17:02] Oleg Marchuk

Related data

Attachment: log1TestCase.sql (text/x-sql), 3.19 KiB.

[8 Dec 2005 17:02] Oleg Marchuk

Table and queries which cause error

Attachment: log2TestCase.sql (text/x-sql), 10.50 KiB.

[8 Dec 2005 17:09] Oleg Marchuk

And select * 25, 10 ... returns last 10 records.

[8 Dec 2005 17:38] MySQL Verification Team

I was unable to repeat with current source server. Can you test with
the latest release?

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.17-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select logentry0_.id as id, logentry0_.description as descript2_14_,
    -> logentry0_.targetClassName as targetCl3_14_, logentry0_.targetId as targetId14_,
    -> logentry0_.stamp as stamp14_, logentry0_.user as user14_,
    -> logentry0_.role as role14_ from log logentry0_, roles role1_ where logentry0_.role=role1_.id
    -> and (role1_.role like '%admin%') order by logentry0_.id asc limit 0,34\G
<cut>
           id: 34
descript2_14_: ????? ? ???????: id=2, login=oleg, email=[e-mail], <cut>
targetCl3_14_: user
  targetId14_: 2
     stamp14_: 2005-12-08 18:35:02
      user14_: 2
      role14_: 4
34 rows in set (0.01 sec)

mysql> select logentry0_.id as id, logentry0_.description as descript2_14_,
    -> logentry0_.targetClassName as targetCl3_14_, logentry0_.targetId as targetId14_,
    -> logentry0_.stamp as stamp14_, logentry0_.user as user14_, logentry0_.role as
    -> role14_ from log logentry0_, roles role1_ where logentry0_.role=role1_.id and
    -> (role1_.role like '%admin%') order by logentry0_.id asc limit 0,100\G
<cut>

*************************** 35. row ***************************
           id: 35
descript2_14_: ????? ? ???????: id=2, login=oleg, email=[e-mail], <cut>
targetCl3_14_: user
  targetId14_: 2
     stamp14_: 2005-12-08 18:37:24
      user14_: 2
      role14_: 4
35 rows in set (0.01 sec)

mysql> select count(*) from log logentry0_, roles role1_ where logentry0_.role=role1_.id
    -> and (role1_.role like '%admin%') order by logentry0_.id asc\G
*************************** 1. row ***************************
count(*): 35
1 row in set (0.01 sec)

mysql> select * from log logentry0_, roles role1_ where logentry0_.role=role1_.id
    -> and (role1_.role like '%admin%') order by logentry0_.id asc limit 0,34\G
<cut>

*************************** 34. row ***************************
             id: 34
    description: ????? ? ???????: id=2, login=oleg, email=[e-mail], <cut>
targetClassName: user
       targetId: 2
          stamp: 2005-12-08 18:35:02
           user: 2
           role: 4
             id: 4
           role: admin
34 rows in set (0.01 sec)

mysql> select * from log logentry0_, roles role1_ where logentry0_.role=role1_.id and
    -> (role1_.role like '%admin%') order by logentry0_.id asc limit 0,100\G

<cut>

*************************** 35. row ***************************
             id: 35
    description: ????? ? ???????: id=2, login=oleg, email=[e-mail], <cut>
targetClassName: user
       targetId: 2
          stamp: 2005-12-08 18:37:24
           user: 2
           role: 4
             id: 4
           role: admin
35 rows in set (0.01 sec)

mysql>

[9 Dec 2005 9:43] Oleg Marchuk

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.1.15-standard |
+-----------------+
1 row in set (0.00 sec)

Execute queries correctly.

[9 Dec 2005 10:43] MySQL Verification Team

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Thank you for the feedback.