Issues

ZF-7666: LIMIT 0 ; fails to be generated

Description

Denis reported in ZF-7650 that a receive(0) didn't work properly. I started checking all of the other Zend_Queue_Db adapter and found that the Zend_Db_Select() code suffers from a similar issue as well.

Our code executes the following:


            $maxMessages = 0
            $query->from($info['name'], array('*'))
                  ->where('queue_id=?', $this->getQueueId($queue->getName()))
                  ->where('handle IS NULL OR timeout+' . (int)$timeout . ' < ' . (int)$microtime)
                  ->limit($maxMessages);

Which generates the following SQL:


SELECT `message`.* FROM `message` WHERE (queue_id=96) AND (handle IS NULL OR timeout+30 < 1251045482) FOR UPDATE

The LIMIT 0 is missing.

Which causes all values to be returned.

I tried a LIMIT 0 in MySQL, and I got the following


mysql> SELECT `message`.* FROM `message` WHERE (queue_id=96) AND (handle IS NULL OR timeout+30 < 1251045482) LIMIT 0 FOR UPDATE;
Empty set (0.00 sec)

So, LIMIT 0 does work, it doesn't make a lot of sense, but it does work.

:)

-daniel

Comments

What is the use-case to retrieve 0 messages?! I cant see it :-)

There is no use-case. Mostly, it's just to avoid a the situation where someone accidentally puts in a limit of 0, and then gets back all the data. IT SHOULDN'T HAPPEN, but this is one of those annoying edge cases. Another instance, would be where someone does some "math" on the limit and ends up with zero.

-daniel

MySQL support LIMIT 0, so I think that ZF should implement it Is it better to implement explicitly a test before the query or letting the query returning 0 rows ? One is more simple, other more efficient...

This seems to be an issue with empty() returning true for zero in Zend_Db_Select#_renderLimitoffset(). Perhaps this should be changed to is_null().