Bug: [QueryBuilder] if set limit to 0, it will return all data. Perhaps it should return 0 data.

[RikhzanAmir]

PHP Version

8.2

CodeIgniter4 Version

4

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

fpm-fcgi

Database

MariaDB 10.2.44

What happened?

$builder->limit(0, 0) , set the limit => 0 and the query will shows all the table rows. It should return no rows because limit is 0.

Steps to Reproduce

$builder->limit(0,0)

Expected Output

it should return no rows if it set limit to 0.

Anything else?

No response

[kenjis]

Thank you for reporting. This is very interesting.

$builder->limit(10, 20);
// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#limit

As I read it, I expect limit(0, 0) produces LIMIT 0, 0.
In reality, however, no LIMIT clause is generated.

[lonnieezell]

What is the practical purpose of doing a query where you don't want any results? I'm not sure that I can think of a reason to do that.

Think of it as "there's 0 limit on the number of results I'd like returned."

[kenjis]

This behavior has not changed since the beginning of 4.0.

CodeIgniter4/system/Database/BuilderTrait.php

Lines 2400 to 2404 in 4d2746f

	// LIMIT
	if ($this->QBLimit)
	{
	return $this->_limit($sql."\n");
	}

[kenjis]

In CI3, the following code generates SELECT * FROM "job" LIMIT 0 (sqlite3):

		$jobs = $this->db->limit(0, 0)
		                      ->get('job')
		                      ->result_array();

		// LIMIT
		if ($this->qb_limit !== FALSE OR $this->qb_offset)
		{
			return $this->_limit($sql."\n");
		}

https://github.com/bcit-ci/CodeIgniter/blob/63d037565dd782795021dcbd3b1ca6f8c8a509e4/system/database/DB_query_builder.php#L2440-L2444

[kenjis]

I don't know if this is a bug or not.

Since no one has reported it before, there are probably few use cases where 0 is specified.

[RikhzanAmir]

I am writing this query in MariaDB: "SELECT * FROM payment limit 0 offset 0" , and it shows no result.

So on Ci3, and Ci4 I am expecting $builder->limit(0,0) or $builder->limit($set, $offset) do the same.

Maybe, it is not a bug, some query do different depending on database we use.

https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

"LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns within applications that use a MySQL API that makes result set metadata available."

[kenjis]

Do you have a use case for limit 0 with Query Builder?

LIMIT is not in SQL standards. So we don't need to implement like MySQL's.
But since "limit 1" returns 1 row, it is logical that "limit 0" would return 0 rows.
However, the implementation will be more complicated than it is now.

[RikhzanAmir]

If that so, maybe do not change and stick to it. Because on my side it can be fix by adding 1 line of code.

$query = $this->db->table('order')->limit($quantity)->offset($offset)->get()->getResultArray();
return empty($quantity) ? [] : $query;

[kenjis]

if ($quantity === 0) {
    return [];
}
$query = $this->db->table('order')->limit($quantity)->offset($offset)->get()->getResultArray();

[kenjis]

I sent PR #8280 to fix this.