zonkyio/embedded-postgres

ORDER BY not working with capital case

rayaanrizwan1234 opened this issue · 5 comments

rayaanrizwan1234 commented

When doing an ORDER BY ASC it seems to get back the ones with capital case first. For example, if we had a column and a record with 'Z' and another with 'a', doing an ORDER BY ASC on that column will bring the record with 'Z' first then the other. Any way to fix this?

org.opentest4j.AssertionFailedError: expected: <[SimpleLookup(code=null - null, description=Desc, active=true), SimpleLookup(code=null - RG, description=Desc, active=true)]> but was: <[SimpleLookup(code=null - RG, description=Desc, active=true), SimpleLookup(code=null - null, description=Desc, active=true)]> at app//org.junit.jupiter.api.AssertionFailureBuilder.build(AssertionFailureBuilder.java:151) at app//org.junit.jupiter.api.AssertionFailureBuilder.buildAndThrow(AssertionFailureBuilder.java:132) at app//org.junit.jupiter.api.AssertEquals.failNotEqual(AssertEquals.java:197) at app//org.junit.jupiter.api.AssertEquals.assertEquals(AssertEquals.java:182) at app//org.junit.jupiter.api.AssertEquals.assertEquals(AssertEquals.java:177) at app//org.junit.jupiter.api.Assertions.assertEquals(Assertions.java:1145)

lesiak commented

This is not a bug - it is a consequence how letters are represented.

For example in Unicode:
List of Unicode characters - Latin Script

  • Z - Latin Capital letter Z - code 90
  • a - Latin Small Letter A - code 97

Thus Z goes before a.

If you want to compare ignoring the case, convert both sides to same case using UPPER(columnA) or LOWER(columnA).

If you want more details:
You may be using a different character set for strings: 24.3.1. Supported Character Sets
However, the code points for Latin Script are shared across many character sets, and were introduced by ASCII encoding

  • 👍2
rayaanrizwan1234 commented

Thanks for getting back on this. What character set does the embedded Postgres use? and is there a way to specify which one it uses?