Submitted by: Richard Wesley (hawkfish)

Relate to CORE1254

Attachments:
B24468.TDE

It appears that case instensitive collation can confuse the grouping logic when other columns are in play. The database is attached and the following output shows the issue.

First, the table. Note that the collation is case insensitive, so all grouping should be case insensitive.

SQL> show table "TableauExtract";
Group VARCHAR(1) CHARACTER SET UTF8 Nullable
COLLATE UNICODE_CI
ID DOUBLE PRECISION Nullable
Question DOUBLE PRECISION Nullable
Score DOUBLE PRECISION Nullable

Now, the broken query:

SQL> select "Group", "Question", SUM("Score") from "TableauExtract" group by 1,2;

Group Question SUM
====== ======================= =======================
a 1.000000000000000 174.0000000000000
a 2.000000000000000 122.0000000000000
a 3.000000000000000 220.0000000000000
A 1.000000000000000 141.0000000000000
A 2.000000000000000 110.0000000000000
A 3.000000000000000 164.0000000000000
t 1.000000000000000 157.0000000000000
t 2.000000000000000 173.0000000000000
t 3.000000000000000 175.0000000000000
T 1.000000000000000 122.0000000000000
T 2.000000000000000 154.0000000000000
T 3.000000000000000 57.00000000000000

There are twice as many rows as there should be. But, if there is only one grouping column, the problem goes away:

SQL> select "Group", SUM("Score") from "TableauExtract" group by 1;

Group SUM
====== =======================
A 931.0000000000000
T 838.0000000000000

Note also that the problem is NOT the grouping on a floating point number:

SQL> select "Question", SUM("Score") from "TableauExtract" group by 1;

           Question                     SUM

======================= =======================
1.000000000000000 594.0000000000000
2.000000000000000 559.0000000000000
3.000000000000000 616.0000000000000

This is happening in 2.1.1. It appears to be fixed in 2.5. Not sure about 2.1.2.

====== Test Details ======

As of current state (WI-T3.0.0.31832 beta-2), FB cah do GROUP BY
WITH using of index on multi-byte or insensitive collation only
when this index is: 1) UNIQUE and 2) ASCENDING.
Index still not used in DISTINCT statement, so it's not checked.
See comments in CORE4787.