SELECT * FROM MAIN_TABLE
LEFT JOIN PRODUCTS 
ON MAIN_TABLE.code = PRODUCTS.code
WHERE MAIN_TABLE.code LIKE '%abc%'

-- либо

WHERE MAIN_TABLE.code LIKE '%ABC%'

create table t_CI (
  id VarChar(10) character set UTF8 primary key collate UNICODE_CI,
  payload integer
)

create table t_CS (
  id VarChar(10) character set UTF8 primary key collate UNICODE,
  payload integer
)

-- что primary key надо вставлять МЕЖДУ charset и collate - это интересно кто так догадался?

insert into t_CS values ( 'ABCD', -100 );

insert into t_CI values ( 'ABCD', +22 );


select * from T_CS,  T_CI where T_CS.id = T_CI.id

select * from T_CS
 left join T_CI on T_CS.id = T_CI.id

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id like '%BC%'

select * from T_CS
 left join T_CI on T_CS.id = T_CI.id
 where T_CS.id like '%BC%'

-- OK: data ~ data

select * from T_CS
 left join T_CI on T_CS.id = T_CI.id
 where T_CS.id like '%bc%'

-- OK:  NULL ~ NULL

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id like '%bc%'

-- ?????  data ~ NULL  ?????

Plan:
PLAN JOIN (T_CI NATURAL, T_CS INDEX (RDB$PRIMARY3))

Adapted plan:
PLAN JOIN (T_CI NATURAL, T_CS INDEX (INTEG_21))

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id starting with 'abc'

-- OK: data ~ data

/*
Plan:
PLAN JOIN (T_CI INDEX (RDB$PRIMARY2), T_CS INDEX (RDB$PRIMARY3))

Adapted plan:
PLAN JOIN (T_CI INDEX (INTEG_19), T_CS INDEX (INTEG_21))
*/ 

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id like '%bc%'

-- ERROR: data ~ NULL

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id collate unicode like '%bc%'

-- OK: NULL ~ NULL

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id collate unicode_ci like '%bc%'

-- OK: data ~ data

WHERE MAIN_TABLE.code LIKE '%abc%'

WHERE MAIN_TABLE.code LIKE (select singleton_value from dictionary where ...)

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id like '%bc%'

-- ERROR: data ~ NULL

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id collate unicode like '%bc%'

-- OK: NULL ~ NULL

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id collate unicode_ci like '%bc%'

-- OK: data ~ data

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id like _UTF8 '%bc%'  --collate unicode

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id like _UTF8 '%bc%' collate unicode_ci

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
where T_CI.id like _UTF8 '%bc%' collate unicode

PLAN JOIN (T_CI NATURAL, T_CS INDEX (RDB$PRIMARY3))

ID	PAYLOAD	ID1	PAYLOAD1
ABCD	22	ABCD	-100

select * from T_CI
 left join T_CS on T_CS.id = T_CI.id
 where T_CI.id collate unicode like _UTF8 '%bc%' --collate unicode

PLAN JOIN (T_CI NATURAL, T_CS INDEX (RDB$PRIMARY3))

ID	PAYLOAD	ID1	PAYLOAD1