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