Hi,

I'm a little confused about how GROUP BY treats white space in Firebird
1.5.4. Consider the following test:

--Create table
DROP TABLE test_whitespace_groupby;
CREATE TABLE test_whitespace_groupby ( mycol VARCHAR(10) );

--Insert non-whitespace values
INSERT INTO test_whitespace_groupby VALUES ('1');
INSERT INTO test_whitespace_groupby VALUES ('2');
INSERT INTO test_whitespace_groupby VALUES ('2');
INSERT INTO test_whitespace_groupby VALUES ('3');
INSERT INTO test_whitespace_groupby VALUES ('3');
INSERT INTO test_whitespace_groupby VALUES ('3');

--Insert whitespace values
INSERT INTO test_whitespace_groupby VALUES (''); --No chars
INSERT INTO test_whitespace_groupby VALUES (' '); --1 space
INSERT INTO test_whitespace_groupby VALUES (' '); --2 spaces
INSERT INTO test_whitespace_groupby VALUES (' ');
INSERT INTO test_whitespace_groupby VALUES (' '); --3 spaces
INSERT INTO test_whitespace_groupby VALUES (' ');
INSERT INTO test_whitespace_groupby VALUES (' ');

COMMIT;

To confirm that the data has loaded correctly run the following:

DROP EXTERNAL FUNCTION STRLEN;

DECLARE EXTERNAL FUNCTION STRLEN
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_STRLEN' MODULE_NAME 'IB_UDF';

COMMIT;

SELECT
MYCOL,
STRLEN(MYCOL) AS MYCOL_LENGTH
FROM TEST_WHITESPACE_GROUPBY;

This should show the values with the lengths of the columns.

The issue is illustrated when the following query is run:

SELECT
mycol,
strlen(mycol) as mycol_len,
count(*)
FROM test_whitespace_groupby
GROUP BY mycol

I was expecting the white space to be treated as 4 different values but
it seems that all the white space values get lumped into a single value
with a length of 3 characters.

This is the result of the query:

mycolmycol_lencount 37111212313
Is there any way that I can change this behaviour?

Regards,
Dom.



[Non-text portions of this message have been removed]