Bug #1908 SELECT CLAUSE

• Submitted: 21 Nov 2003 4:28
• Modified: 21 Nov 2003 5:43
• Reporter: Fernando Prata
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.15-nt
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[21 Nov 2003 4:28] Fernando Prata

Description:
When I query the table with

SELECT * FROM banc_table WHERE banc='BFB' LIMIT 0,30

I get record number 10.

But if I query the table with

SELECT * FROM banc_table WHERE banc='BFB' LIMIT 5,10

I get nothing.

Why does that happen? Is there any configuration problem?

Best regards

How to repeat:
Any simple table query will do.

[21 Nov 2003 5:38] Alexander Keremidarski

Please provide more details including table structure (SHOW CREATE TABLE output) and some data enough to repeat the problem.

mysql> create table bug1908 (id int);

mysql> insert into bug1908 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
mysql> select * from bug1908 limit 0,8;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set (0.00 sec)
 
mysql> select * from bug1908 limit 5,8;
+------+
| id   |
+------+
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
5 rows in set (0.00 sec)

Works as expected;

[21 Nov 2003 6:01] Fernando Prata

I have tested it that way (SELECT * FROM <table> LIMIT 5,10). It works OK. 

But when I put the WHERE clause it works no more (SELECT * FROM <table> WHERE <clause> LIMIT 5,10).

[21 Nov 2003 9:11] Paul DuBois

What does your query produce with the WHERE clause but without the LIMIT clause?

If the query produces fewer than 5 rows, then what you observe is the expected
result.  Remember that LIMIT is applied very late in query processing. In particular,
it is applied after the WHERE.