DDC-43: offset and limit are in the wrong order (at least SQLite) #53

New Issue

2026-01-22T12:25:28+01:00

admin commented

2026-01-22 12:25:28 +01:00

Originally created by @doctrinebot on GitHub (Oct 12, 2009).

Originally assigned to: @jwage on GitHub.

Jira issue originally created by user itoijala:

The following code creates an error:

Code:

$query = Itoijala_Singletons::getDoctrine()->createQueryBuilder();
$query->select('e')->from('Model\Guestbook\Entry e');
$query->andWhere('e.dateDeleted IS NULL')->andWhere('e.unlocked = TRUE');
$query->orderBy('e.dateCreated', 'DESC')->setFirstResult(($this->_getParam('page') - 1) * 15);
$query->setMaxResults(15);

$this->view->entries = $query->getQuery()->getResult();

Error:
SQLSTATE[HY000]: General error: 1 near "OFFSET": syntax error

The DQL:
SELECT e FROM Model\Guestbook\Entry e WHERE (e.dateDeleted IS NULL) AND (e.unlocked = TRUE) ORDER BY e.dateCreated DESC

The SQL:
SELECT g0_.id AS id0, g0_.name AS name1, g0_.email AS email2, g0_.homepage AS homepage3, g0_.content AS content4, g0_.comment AS comment5, g0_.unlocked AS unlocked6, g0_.date_created AS date_created7, g0_.date_updated AS date_updated8, g0_.date_deleted AS date_deleted9, g0_.creator_id AS creator_id10, g0_.updater_id AS updater_id11, g0_.deleter_id AS deleter_id12 FROM guestbook_entries g0_ WHERE (g0_.date_deleted IS NULL) AND (g0_.unlocked = 1) ORDER BY g0_.date_created DESC OFFSET 0 LIMIT 15

SQLite expects offset to come after limit. writeLimitClause in AbstractPlatform works correctly and is not overriden by SqlitePlatform. This means that the bug is somewhere else. writeLimitClause is not called.

Originally created by @doctrinebot on GitHub (Oct 12, 2009). Originally assigned to: @jwage on GitHub. Jira issue originally created by user itoijala: The following code creates an error: Code: ``` $query = Itoijala_Singletons::getDoctrine()->createQueryBuilder(); $query->select('e')->from('Model\Guestbook\Entry e'); $query->andWhere('e.dateDeleted IS NULL')->andWhere('e.unlocked = TRUE'); $query->orderBy('e.dateCreated', 'DESC')->setFirstResult(($this->_getParam('page') - 1) * 15); $query->setMaxResults(15); $this->view->entries = $query->getQuery()->getResult(); ``` Error: SQLSTATE[HY000]: General error: 1 near "OFFSET": syntax error The DQL: SELECT e FROM Model\Guestbook\Entry e WHERE (e.dateDeleted IS NULL) AND (e.unlocked = TRUE) ORDER BY e.dateCreated DESC The SQL: SELECT g0_.id AS id0, g0_.name AS name1, g0_.email AS email2, g0_.homepage AS homepage3, g0_.content AS content4, g0_.comment AS comment5, g0_.unlocked AS unlocked6, g0_.date_created AS date_created7, g0_.date_updated AS date_updated8, g0_.date_deleted AS date_deleted9, g0_.creator_id AS creator_id10, g0_.updater_id AS updater_id11, g0_.deleter_id AS deleter_id12 FROM guestbook_entries g0_ WHERE (g0_.date_deleted IS NULL) AND (g0_.unlocked = 1) ORDER BY g0_.date_created DESC OFFSET 0 LIMIT 15 SQLite expects offset to come after limit. writeLimitClause in AbstractPlatform works correctly and is not overriden by SqlitePlatform. This means that the bug is somewhere else. writeLimitClause is not called.

admin added the Bug label 2026-01-22 12:25:28 +01:00

admin closed this issue

2026-01-22 12:25:28 +01:00

admin commented

2026-01-22 12:25:29 +01:00