Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Given a single filter on an $or operation the resulted sql will be wrong. #792

Closed
charmpitz opened this issue Aug 30, 2020 · 0 comments
Closed
Assignees
Labels
bug Something isn't working

Comments

@charmpitz
Copy link
Contributor

Describe the bug
I'm dynamically creating some filters and it seems that some queries are wrong when the $or operator contains an array with a single element like in the scenario below.

Stack trace
Given the following arguments:

{
    "$and": [
        { "user": { "id": { "$eq": 1 } } },
        { "$or": [{ "$and": [{ "id": { "$lt": 6 } }] }] }
    ]
}
{ "limit": 3, "orderBy": { "id": "desc" } }

We receive the following SQL:

select "e0".* from "schema"."article" as "e0" 
    left join "schema"."user" as "e1" 
        on "e0"."user_id" = "e1"."id" 
where 
    "e1"."id" = 1 or 
    ("e0"."id" < 6) 
order by 
    "e0"."id" desc 
limit 3

To Reproduce
Steps to reproduce the behavior:

  1. Instantiate a query builder.
  2. Use the input from above.

Expected behavior
Even though the query is over-complicated (due to being dynamically generated) it should be correctly parsed and transformed into the following SQL instead (using an and instead of an or):

select "e0".* from "schema"."article" as "e0" 
    left join "schema"."user" as "e1" 
        on "e0"."user_id" = "e1"."id" 
where 
    "e1"."id" = 1 and 
    ("e0"."id" < 6) 
order by 
    "e0"."id" desc 
limit 3

Additional context
For example, in a more complex scenario where the $or has an array with more than 1 element everything seems to be ok:
Given:

{
    "$and": [
        { "user": { "id": { "$eq": 1 } } },
        {
            "$or": [
                { "$and": [{ "id": { "$lt": 6 } }] },
                {
                    "$and": [
                        { "id": { "$eq": 6 } },
                        { "createdAt": { "$gt": "2020-08-26T20:01:48.863Z" } }
                    ]
                }
            ]
        }
    ]
}
{ "limit": 3, "orderBy": { "id": "desc" } }

It is correctly transformed into:

select "e0".* from "schema"."article" as "e0" 
    left join "schema"."user" as "e1" 
        on "e0"."user_id" = "e1"."id" 
where 
    "e1"."id" = 1 and 
    (
        ("e0"."id" < 6) or 
        (
            "e0"."id" = 6 and 
            "e0"."created_at" > '2020-08-26T20:01:48.863Z'
        )
    ) 
order by 
    "e0"."id" desc, 
limit 3

Versions

Dependency Version
node 14.7.0
@mikro-orm/cli ^4.0.0-rc.4
@mikro-orm/core ^4.0.0-rc.4
@mikro-orm/migrations ^4.0.0-rc.4
@mikro-orm/nestjs ^4.0.0-alpha.3
@mikro-orm/postgresql ^4.0.0-rc.4
@mikro-orm/reflection ^4.0.0-rc.4
typescript ^3.7.4
@charmpitz charmpitz added the bug Something isn't working label Aug 30, 2020
@charmpitz charmpitz changed the title Given a single filter on a $or operation the resulted query is wrong. Given a single filter on a $or operation the resulted query will be wrong. Aug 30, 2020
@charmpitz charmpitz changed the title Given a single filter on a $or operation the resulted query will be wrong. Given a single filter on an $or operation the resulted query will be wrong. Aug 30, 2020
@charmpitz charmpitz changed the title Given a single filter on an $or operation the resulted query will be wrong. Given a single filter on an $or operation the resulted sql will be wrong. Aug 30, 2020
@B4nan B4nan closed this as completed in c8d3a34 Aug 31, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants