Join index is so restricted

CREATE TABLE TBLA
(
bank_shikib_shi CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
tengun CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
tenban CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL
）

CREATE TABLE TBLB
(
bnkptn CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
tengun CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
tenban CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)

we got above two tables and we need a join index

so the DDL is..


create join index testjoinindex as
select
...
...
...
from
TBLA t1
left outer join
TBLB t2
on
t1.bank_shikib_shi=t2.bnkptn
and t1.daihyosha_tenban=t2.tenban
primary index (bank_shikib_shi, tenban, cif_no)
;

we got a error:
CREATE INDEX Failed.  [5464] Error in Join Index DDL, A name specified for an index was not found in the select list.

the reason is:
TBLA IS CASESPECIFIC BUT TBLB IS NOT NOT CASESPECIFIC

WTF

Tweet