Admittedly, I’ve only tested on Firebird 2.5.2 and 2.5.4 and the particular column used has ISO8859_1 for both CHARACTER SET and COLLATION.
SELECT distinct MyChar1Field FROM MyTable GROUP BY 1 returned three rows with what looked like a space, whereas SELECT distinct MyChar1Field FROM MyTable only returned one such row. A bit closer look at the data revealed that one of the entries actually contained hexadecimal 00 and not space. Changing this one value corrected the error, but I’m still puzzled about this difference. Moreover, similar queries behave similarly different: SELECT distinct MyChar1Field || ‘’ FROM MyTable GROUP BY 1 returns one row SELECT MyChar1Field || ‘’ FROM MyTable GROUP BY 1 returns three rows SELECT MyChar1Field || ‘ ’ FROM MyTable GROUP BY 1 returns three rows SELECT MyChar1Field || ‘ ?’ FROM MyTable GROUP BY 1 returns one row It is understandable that x00 is treated differently, but it doesn't make sense to me that it makes other records randomly get into one out of two groups, nor do I understand why DISTINCT/GROUP BY should behave differently.. Is this a known issue, or even fixed in newer Firebird versions? I didn’t see anything in the bug tracker, but I didn’t do any thorough search. Set
