SQL LIMIT clause

The SQL LIMIT clause that appears in various places of yii does not appear to be valid for SQL-89 or SQL-92 and furthermore specific to only certain databases like MySQL (correct me if I am wrong). This appears in more places than the database schema for specific databases. Is there a way short of edits to all the yii code to remove the application of this clause?

I have never had problems with this in my life, there is a factory pattern for the database connection so I dont think Its MySQL specific. so why is it you want to change this?

Because I’m using a database that doesn’t support it. LIMIT is not SQL-89 or SQL-92 compliant and not supported in every database. It’s not MySQL specific as there are others that support it but if the code that yii used was SQL89/92 compliant then it wouldn’t be an issue. So in order use yii you need a database that supports it or you have to hack the code.

What database would that be?

Progress 9.1d.

Arent’ there a newer standard than one 20 years old?

What’s the rationale behind Progress not having support for LIMIT?

((I suddenly feel a strong urge to play with words, I don’t know why… Progress and Limit… Hmm …:rolleyes:))

Does Progress throw up when using LIMIT, or does it ignore it?

Can Yii detect and work around it?

If so, then a patch is in order.

Removing it would be er… limiting, as LIMIT is extremely useful IMO.

Which standard are you referring to?

Don’t know but it doesn’t appear to be in a standard.

I’m using PDO_ODBC and yii crashes.

Apparently not at the present.

That would be nice.

Probably but I don’t have a choice right now. I’m slowly chipping it out of all the sql. Don’t know what that will effect later. I’m sure to find out soon. The plus is that I have PDO_ODBC working!

It sucks to work with non standard things doesn’t it? feels strange that all other drivers work just fine except yours :P

No way of changing your database engine to something… else?

Not me, you. You referred to 89/92 - when we have 99, 2003, 2006 and 2008.


Does Progress support TOP ?

Then one could say: select top 10 from…

No there is no way. I’m trying to interface with a legacy app. If I could get it all working under yii at some point then yes I would jump rather quickly. But I need to run the legacy app while developing yii.

AFAIK "LIMIT" does not appear in any of the SQL standards. It appears to be a mysql/postgresql thing.

I’m not sure yet.

I thought that LIMIT was SQL, but apparently it’s MySQL specific?

Found this post, which shows how to convert from LIMIT to TOP in ms-sql:



I’m pretty sure it’s largely mysql.

Right but again this involves changing core yii code.

I believe it requires adding a new schema - take a look at yii/framework/db/schema.

I am sure the Yii team wouldn’t mind a contribution. :)

However, if LIMIT really is present in Yii core, then it should be asked to move to db/schema, IMO.

Yes, my hunch was right.

Take a look at db/schema/mssql/CMssqlCommandBuilder.php:

	public function applyLimit($sql, $limit, $offset)


		$limit = $limit!==null ? intval($limit) : -1;

		$offset = $offset!==null ? intval($offset) : -1;

		if ($limit > 0 && $offset <= 0) //just limit

			$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);

		else if($limit > 0 && $offset > 0)

			$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);

		return $sql;


If you’re lucky, you can base it on one of the existing schemas.

No I wrote my own schema but it’s also in db/schema/CDbCommandBuilder.php. I don’t think we should need to touch that if we write a new schema should we?

Oops - I missed that. :)

Is there a way to check that Yii is actually using your schema?

It should work, because I hear rumors that ms-sql is working, and that doesn’t feature LIMIT either.

I have a feeling that Yii is not tested much against other database backends than MySQL and SQLite. But I could be wrong.

Maybe a team member can comment?

Yes it’s using my schema.

I bet it doesn’t work but I could be wrong too.


I should also note that the OFFSET clause likely poses the same problem.