ZF-7666: LIMIT 0 ; fails to be generated

Issue Type: Bug Created: 2009-08-23T10:12:18.000+0000 Last Updated: 2012-05-30T12:53:00.000+0000 Status: Open Fix version(s): Reporter: Daniel Lo (danlo) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: - ZF-9796



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:

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

Which generates the following SQL:

<pre class="highlight">
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

<pre class="highlight">
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.




Posted by Benjamin Eberlei (beberlei) on 2009-09-18T13:06:15.000+0000

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

Posted by Daniel Lo (danlo) on 2009-09-19T11:35:49.000+0000

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.


Posted by Martin Supiot (martinsupiot) on 2010-09-19T07:57:23.000+0000

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...

Posted by Hector Virgen (djvirgen) on 2010-10-04T09:31:02.000+0000

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().

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.