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

Database collation affects result of orderBy (case sensitive) #2369

Closed
mhwelander opened this issue May 5, 2020 · 9 comments
Closed

Database collation affects result of orderBy (case sensitive) #2369

mhwelander opened this issue May 5, 2020 · 9 comments
Labels
kind/feature A request for a new feature. topic: collation

Comments

@mhwelander
Copy link
Contributor

Problem

When I order by a title field, I get different results if I use PostgreSQL or SQLite because they have different default collation.

Default PostgreSQL:

Another one
another one
Bites the
bites the
Dust
dust

Default SQLite:

Another one
Bites the
Dust
another one
bites the
dust

Suggestion

An option to use case-insensitive sorting per instance of client or the ability to specify that I want to sort by title.toLower().

Notes

Have not done any testing on decimals/numbers.

@pantharshit00
Copy link
Contributor

pantharshit00 commented May 5, 2020

Hi,

Thanks for reporting this! It has already been reported before, so I am going to close this as a duplicate of prisma/prisma-client-js#343

@janpio
Copy link
Member

janpio commented May 6, 2020

The linked issue does not mention "collation" once, and does not actually describe the problem or the solution in the issue itself @pantharshit00. Are you sure this is an appropriate replacement? If so, it definitely needs an update in description.

@pantharshit00
Copy link
Contributor

"collation" is irrelevant as client will return the data returned by the database. You will need to change it on db level of you want to have a different behaviour.

@janpio
Copy link
Member

janpio commented May 6, 2020

Exactly. prisma/prisma-client-js#343 might offer a workaround when it is implemented, but the underlying problem of @mhwelander is a different one.

@pantharshit00
Copy link
Contributor

pantharshit00 commented May 6, 2020

Exactly. prisma/prisma-client-js#343 might offer a workaround when it is implemented, but the underlying problem of @mhwelander is a different one.

Is it really a problem though? Does client should care about of collation which underlying database uses? We can keep this open but I don't think this is something which is under the scope of Prisma Client

@janpio
Copy link
Member

janpio commented May 11, 2020

As it leads to confusing situations to users like @mhwelander I would say the behavior of Prisma here is relevant.

@mhwelander
Copy link
Contributor Author

Changing #343 to 'Add case sensitive/insensitive querying and sorting' as per Jan's suggestion works, just to make sure it is considered in the design (maybe I want filtering to be case-sensitive but ordering to be case-insensitive for the same query, for example).

I should have phrased my request differently - the request is that I would like the option to specify case-insensitive ordering just as I can do in regular SQLite (e.g. SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE) - no matter what the db collation is. I brought up collation only to emphasize that I know Prisma respecting SQLite's default sort order is not a bug.

Sorry about my title! :)

@pantharshit00 pantharshit00 reopened this May 14, 2020
@pantharshit00 pantharshit00 added the kind/feature A request for a new feature. label May 14, 2020
@pantharshit00
Copy link
Contributor

pantharshit00 commented May 14, 2020

This issue now tracks feature request for an option to specify COLLATE NOCASE in the queries

@thebiglabasky
Copy link

We did investigate the topic of case sensitivity extensively recently.
Coping with all possible underlying database settings is too big of a promise to make so we are looking into supporting case insensitive filtering first, monitored on this issue: prisma/prisma-client-js#690
Providing ways to allow users to perform a case insensitive sort needs to be assessed separately, but should also be monitored through prisma/prisma-client-js#690

I will close this issue as it is asking for a specific solution to the problem described in the issue mentioned above.
Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
kind/feature A request for a new feature. topic: collation
Projects
None yet
Development

No branches or pull requests

4 participants