Commented by: Sean Leyne (seanleyne)

How many rows where in T2 table?

Depending on the number of rows in the table, a NATURAL scan would seem appropriate, regardless of the index definitions/collations.

Commented by: @pavel-zotov

> Depending on the number of rows in the table, a NATURAL scan would seem appropriate

Not in this case :-)
And this is exactly COLLATE clause in field definition that prevents from usage of index:

C:\FBTESTING\qa\fbt-repo\tmp>isql localhost/3333:e30
Database: localhost/3333:e30
SQL> recreate table t2(a varchar(10) character set utf8); commit;
SQL> set count on;
SQL> insert into t2 select left(uuid_to_char(gen_uuid()),10) from rdb$types,rdb$types; commit;
Records affected: 64516
SQL> set count off;
SQL> create index t2_a on t2(a); commit;
SQL> set planonly;
SQL> select a from t2 group by a;

PLAN (T2 ORDER T2_A)

Commented by: Sean Leyne (seanleyne)

Had you posted the non-collate example with the original post, we could have saved these exchanges ;-)

Commented by: @asfernandes

This is know limitation documented in the code:

// ASF: We currently can't use non-unique ["separate unique"] index for GROUP BY and DISTINCT with
// multi-level and insensitive collation. In NAV, keys are verified with memcmp
// but there we don't know length of each level.
if (sort->unique && (tt->getFlags() & TEXTTYPE_SEPARATE_UNIQUE))
{
usableIndex = false;
break;
}

Commented by: @pavel-zotov

> We currently can't use non-unique ["separate unique"] index for GROUP BY and DISTINCT with multi-level and insensitive collation

Adriano,

can you please clarify what is "SEPARATE unique" index ?

-------
BTW (sorry if offtop): seems that DISTINCT still does not use ANY index at all, not only for text fields:

SQL> recreate table t1(x int); commit;
SQL> insert into t1 select rand()*1000 from rdb$types, rdb$types; commit;
SQL> create index t1_x_unq on t1(x); commit;
SQL> set planonly;
SQL> select x from t1 group by x;

PLAN (T1 ORDER T1_X_UNQ)

SQL> select distinct x from t1;

PLAN SORT (T1 NATURAL)