create collation coll_ns for utf8 from unicode 'NUMERIC-SORT=1';
commit;
create domain dm_ns as varchar(10) character set utf8 collate coll_ns;
commit;
recreate table t1(
       a dm_ns
      ,b dm_ns
); commit;

insert into t1(a, b) 
select rdb$relation_id, rdb$relation_id
from rdb$relations; 
commit;
-- core-3947: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups    
create index t1_a_non_unq on t1(a);
create UNIQUE index t1_b_unq on t1(b);
commit;

---------------------------------------------------

recreate table t2(
    a varchar(10) character set utf8 collate unicode_ci
   ,b varchar(10) character set utf8 collate unicode_ci
); commit;

insert into t2(a, b) 
select left(uuid_to_char(gen_uuid()),10), left(uuid_to_char(gen_uuid()),10) 
from rdb$types; 
commit;
-- CORE-4787: non-unique index on field which was defined with "collate unicode_ci" is not used in GROUP BY on that field
create index t2_a on t2(a); 
create UNIQUE index t2_b_unq on t2(b); 
commit;

---------------------------------------------------


set planonly;
set echo on;

-- 1. core-3947: UNIQUE indices on field that was defined with 'NUMERIC-SORT' attribute 
--               will not be usable for ORDER, only for lookups    

-- 1.1. Check for PLAN INDEX:

select * from t1 where a = '10';

select * from t1 where b = '10';


-- 1.2. Check for PLAN ORDER:

select a from t1 group by a;

select b from t1 group by b;


-- 2. CORE-4787: NON_UNIQUE index on field that was defined with 'collate unicode_ci' 
--               is not used in GROUP BY on that field

select a from t2 group by a;

select b from t2 group by b;

/*********************************

Output:
=======

select * from t1 where a = '10';

PLAN (T1 INDEX (T1_A_NON_UNQ))

select * from t1 where b = '10';

PLAN (T1 INDEX (T1_B_UNQ))

select a from t1 group by a;

PLAN (T1 ORDER T1_A_NON_UNQ)

select b from t1 group by b;

PLAN SORT (T1 NATURAL)


select a from t2 group by a;

PLAN SORT (T2 NATURAL)

select b from t2 group by b;

PLAN (T2 ORDER T2_B_UNQ)

***********************************/