Ordering with arithmetic expression #6396

New Issue

Closed

opened 2026-01-22 15:32:28 +01:00 by admin · 8 comments

admin commented 2026-01-22 15:32:28 +01:00

Owner

Copy Link

Originally created by @sgehrig on GitHub (Feb 3, 2020).

Bug Report

Q	A
BC Break	no
Version	v2.7.0

Summary

Ordering with arithmetic expression does not work as I'd expect it to work after reading the DQL EBNF.

Current behavior

This is a simplified example of the actual query.

SELECT 
    j,
    (SELECT SUM(i.amount) FROM Invoice i WHERE i.job = j) AS HIDDEN invoiced_amount,
    (SELECT COALESCE(SUM(t.orderedAmount), j.orderedAmount) FROM Task t WHERE t.job = j) AS HIDDEN current_ordered_amount
FROM Jobs j
ORDER BY  current_ordered_amount - invoiced_amount ASC

Running the query, [Syntax Error] line 0, col xxx: Error: Expected end of string, got '-' is thrown.

How to reproduce

Run a query similar so the one above with an arithmetic expression in the ORDER BY clause.

Expected behavior

I'd expect the query to work because of what's stated in the documentation:

OrderByClause ::= "ORDER" "BY" OrderByItem {"," OrderByItem}*
OrderByItem ::= (SimpleArithmeticExpression | ...) ["ASC" | "DESC"]
SimpleArithmeticExpression ::= ArithmeticTerm {("+" | "-") ArithmeticTerm}*
ArithmeticTerm ::= ArithmeticFactor {("*" | "/") ArithmeticFactor}*
ArithmeticFactor ::= [("+" | "-")] ArithmeticPrimary
ArithmeticPrimary ::= ... | ResultVariable | ...
/* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */
ResultVariable = identifier

Is there something I missed? Or misinterpreted?

Originally created by @sgehrig on GitHub (Feb 3, 2020). ### Bug Report | Q | A |------------ | ------ | BC Break | no | Version | v2.7.0 #### Summary Ordering with arithmetic expression does not work as I'd expect it to work after reading the [DQL EBNF](https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/dql-doctrine-query-language.html#ebnf). #### Current behavior This is a simplified example of the actual query. ```DQL SELECT j, (SELECT SUM(i.amount) FROM Invoice i WHERE i.job = j) AS HIDDEN invoiced_amount, (SELECT COALESCE(SUM(t.orderedAmount), j.orderedAmount) FROM Task t WHERE t.job = j) AS HIDDEN current_ordered_amount FROM Jobs j ORDER BY current_ordered_amount - invoiced_amount ASC ``` Running the query, `[Syntax Error] line 0, col xxx: Error: Expected end of string, got '-'` is thrown. #### How to reproduce Run a query similar so the one above with an arithmetic expression in the `ORDER BY` clause. #### Expected behavior I'd expect the query to work because of what's stated in the documentation: ``` OrderByClause ::= "ORDER" "BY" OrderByItem {"," OrderByItem}* OrderByItem ::= (SimpleArithmeticExpression | ...) ["ASC" | "DESC"] SimpleArithmeticExpression ::= ArithmeticTerm {("+" | "-") ArithmeticTerm}* ArithmeticTerm ::= ArithmeticFactor {("*" | "/") ArithmeticFactor}* ArithmeticFactor ::= [("+" | "-")] ArithmeticPrimary ArithmeticPrimary ::= ... | ResultVariable | ... /* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */ ResultVariable = identifier ``` Is there something I missed? Or misinterpreted?

admin added the Bug label 2026-01-22 15:32:28 +01:00

admin closed this issue 2026-01-22 15:32:28 +01:00

admin commented 2026-01-22 15:32:29 +01:00

Author

Owner

Copy Link

@sgehrig commented on GitHub (Feb 3, 2020):

I've created a test case:

c60272b4b8

@sgehrig commented on GitHub (Feb 3, 2020): I've created a test case: https://github.com/sgehrig/orm/commit/c60272b4b8d5c125f3b0afc118f84fece39176d1

admin commented 2026-01-22 15:32:29 +01:00

Author

Owner

Copy Link

@SenseException commented on GitHub (Feb 3, 2020):

Hi @sgehrig, if you like you can create an PR for your test case that can be later used for an implementation. But instead of letting the test count the results, I would expect the test to check the correct order of the result.

Have you tried a simpler query like SELECT f FROM Foo f ORDER BY f.id - 1?

@SenseException commented on GitHub (Feb 3, 2020): Hi @sgehrig, if you like you can create an PR for your test case that can be later used for an implementation. But instead of letting the test count the results, I would expect the test to check the correct order of the result. Have you tried a simpler query like `SELECT f FROM Foo f ORDER BY f.id - 1`?

admin commented 2026-01-22 15:32:30 +01:00

Author

Owner

Copy Link

@sgehrig commented on GitHub (Feb 4, 2020):

Hi @SenseException, sure no problem. I've also added some more test cases and found some interesting things:

• ORDER BY p.id + p.id ASC ✅
• ORDER BY 1 + p.id ASC 🚫
• ORDER BY p.id + 1 ASC ✅
• ORDER BY s + 1 DESC 🚫 (where s is a ResultVariable)
• ORDER BY 1 + s DESC 🚫 (where s is a ResultVariable)
• ORDER BY s + p.id DESC 🚫 (where s is a ResultVariable)
• ORDER BY p.id + s DESC ✅ (where s is a ResultVariable)

These cases are all represented in the test case PR.

@sgehrig commented on GitHub (Feb 4, 2020): Hi @SenseException, sure no problem. I've also added some more test cases and found some interesting things: - `ORDER BY p.id + p.id ASC ` ✅ - `ORDER BY 1 + p.id ASC` 🚫 - `ORDER BY p.id + 1 ASC` ✅ - `ORDER BY s + 1 DESC` 🚫 (where `s` is a `ResultVariable`) - `ORDER BY 1 + s DESC` 🚫 (where `s` is a `ResultVariable`) - `ORDER BY s + p.id DESC` 🚫 (where `s` is a `ResultVariable`) - `ORDER BY p.id + s DESC` ✅ (where `s` is a `ResultVariable`) These cases are all represented in the test case PR.

admin commented 2026-01-22 15:32:30 +01:00

Author

Owner

Copy Link

@sgehrig commented on GitHub (Feb 4, 2020):

@SenseException please see https://github.com/doctrine/orm/pull/8012

@sgehrig commented on GitHub (Feb 4, 2020): @SenseException please see https://github.com/doctrine/orm/pull/8012

admin commented 2026-01-22 15:32:30 +01:00

Author

Owner

Copy Link

@sgehrig commented on GitHub (Feb 11, 2020):

I've been doing some debugging using the test cases provided in #8012. I'm not an expert in the DQL parser though, but I assume the bug somehow originates from

fdad48278b/lib/Doctrine/ORM/Query/Parser.php (L1497)

When running the successful test cases $peek returns the arithmetic operator (+ for example) and therefore the arithmetic expression is detected correctly. When running the failing test cases $peek however returns the DESC keyword skipping the whole arithmetic expression.

Just my two cents.

@sgehrig commented on GitHub (Feb 11, 2020): I've been doing some debugging using the test cases provided in #8012. I'm not an expert in the DQL parser though, but I assume the bug somehow originates from https://github.com/doctrine/orm/blob/fdad48278b6c0d0d7075de584e6982969bf430c6/lib/Doctrine/ORM/Query/Parser.php#L1497 When running the successful test cases `$peek` returns the arithmetic operator (`+` for example) and therefore the arithmetic expression is detected correctly. When running the failing test cases `$peek` however returns the `DESC` keyword skipping the whole arithmetic expression. Just my two cents.

admin commented 2026-01-22 15:32:31 +01:00

Author

Owner

Copy Link

@sgehrig commented on GitHub (Apr 3, 2020):

As this issue becomes more and more critical for us, I'd really like to help here with a PR. But I'll need some guidance on the current source code to understand why it has been written the way it currently is.

@sgehrig commented on GitHub (Apr 3, 2020): As this issue becomes more and more critical for us, I'd really like to help here with a PR. But I'll need some guidance on the current source code to understand why it has been written the way it currently is.

admin commented 2026-01-22 15:32:31 +01:00

Author

Owner

Copy Link

@sgehrig commented on GitHub (Apr 8, 2020):

I just found that enclosing the expression in double brackets (((...))) seems to trick the parser into correctly handling the order by item:

• ORDER BY p.id + p.id ASC ✅
• ORDER BY 1 + p.id ASC 🚫
  • ORDER BY ((1 + p.id)) ASC ✅
• ORDER BY p.id + 1 ASC ✅
• ORDER BY s + 1 DESC 🚫 (where s is a ResultVariable)
  • ORDER BY ((s + 1)) DESC ✅
• ORDER BY 1 + s DESC 🚫 (where s is a ResultVariable)
  • ORDER BY ((1 + s)) DESC ✅
• ORDER BY s + p.id DESC 🚫 (where s is a ResultVariable)
  • ORDER BY ((s + p.id)) DESC ✅
• ORDER BY p.id + s DESC ✅ (where s is a ResultVariable)

Still, I'd consider this a bug.

@sgehrig commented on GitHub (Apr 8, 2020): I just found that enclosing the expression in double brackets (`((...))`) seems to trick the parser into correctly handling the order by item: - `ORDER BY p.id + p.id ASC ` ✅ - `ORDER BY 1 + p.id ASC` 🚫 - `ORDER BY ((1 + p.id)) ASC` ✅ - `ORDER BY p.id + 1 ASC` ✅ - `ORDER BY s + 1 DESC` 🚫 (where `s` is a `ResultVariable`) - `ORDER BY ((s + 1)) DESC` ✅ - `ORDER BY 1 + s DESC` 🚫 (where `s` is a `ResultVariable`) - `ORDER BY ((1 + s)) DESC` ✅ - `ORDER BY s + p.id DESC` 🚫 (where `s` is a `ResultVariable`) - `ORDER BY ((s + p.id)) DESC` ✅ - `ORDER BY p.id + s DESC` ✅ (where `s` is a `ResultVariable`) Still, I'd consider this a bug.

admin commented 2026-01-22 15:32:31 +01:00

Author

Owner

Copy Link

@mcorteel-harel commented on GitHub (Jan 12, 2021):

I found the same issue with UPDATE statements (and an unresolved question about it on StackOverfow). The following query:

UPDATE Invoice i SET i.amount = :margin * i.baseAmount

throws the following error:

Error: Expected end of string, got '*'

whereas these two work:

UPDATE Invoice i SET i.amount = i.baseAmount * :margin

UPDATE Invoice i SET i.amount = ((:margin * i.baseAmount))

NOTE: these are not from a real use case, just a simple example.

@mcorteel-harel commented on GitHub (Jan 12, 2021): I found the same issue with `UPDATE` statements (and [an unresolved question about it on StackOverfow](https://stackoverflow.com/questions/23009389/doctrine-dql-query-error-expected-end-of-string-got)). The following query: ``` UPDATE Invoice i SET i.amount = :margin * i.baseAmount ``` throws the following error: ``` Error: Expected end of string, got '*' ``` whereas these two work: ``` UPDATE Invoice i SET i.amount = i.baseAmount * :margin ``` ``` UPDATE Invoice i SET i.amount = ((:margin * i.baseAmount)) ``` NOTE: these are not from a real use case, just a simple example.

admin referenced this issue 2026-01-22 16:09:13 +01:00

[PR #8382] [GH-6396] Allow custom hydrators access to meta columns #10956

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Bug

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6396