[MDEV-5578] Handler next limit scan the full table Created: 2014-01-28  Updated: 2014-01-31  Resolved: 2014-01-30

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.7
Fix Version/s: None

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Elena Stepanova
Resolution: Not a Bug Votes: 0
Labels: upstream


 Description   

Populate a sysbench table 

handler `test`.`sbtest` open as t00000;                                                                                              
handler t00000 read `PRIMARY` >= (4995723) where `id` >= 4995723 and `id` <= 4995822 and (`id` between 4995723  and  4995822) limit 2;
 
+---------+---+---+----------------------------------------------------+
 
| id      | k | c | pad                                                |
 
+---------+---+---+----------------------------------------------------+
 
| 4995723 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
 
| 4995724 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
 
+---------+---+---+----------------------------------------------------+
 
2 rows in set (0,00 sec)
 
 
 
handler t00000 read `PRIMARY` next         where `id` >= 4995723 and `id` <= 4995822 and (`id` between 4995723  and  4995822) limit 100;
 
[1/28/14 10:31:50 AM] svaroqui: | 4995822 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
 
+---------+---+---+----------------------------------------------------+
 
98 rows in set (7,89 sec)
 
| Handler_read_next             | 5004278 |



 Comments   
Comment by Elena Stepanova [ 2014-01-30 ]

Observation: the value only goes crazy when limit reaches outside the range.

Comment by Sergei Golubchik [ 2014-01-30 ]

This is not a bug.

Remember three facts:

  • HANDLER ... READ key NEXT traverses the index tree from the point, where the last key access left it.
  • There is no optimizer, it's direct access to the storage engine indexes.
  • WHERE clause is simply a filter that removes rows that would've been otherwise returned.

So, what happens, you ask for the next 100 rows. Storage engine starts walking the index, finds and returns two 98 rows. But you've asked for a hundred. So it continues walking the index, but all other rows are rejected by the WHERE condition. There's no optimizer, HANDLER doesn't know that you're asking for a range, and it doesn't stop when it reaches the upper range limit, it simply goes until the very end of the index.

Comment by VAROQUI Stephane [ 2014-01-31 ]

Thanks i get it now !

Generated at Thu Feb 08 07:05:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.