How to create projection for proper using of UPPER function?
Here is small example of how you can improve the query speed.

One way i do it is by using bucket columns based on default value at load time.(this can make your load slower thou).

see sample code

drop table test_tbl;
CREATE TABLE
    test_tbl
    (
        id INT,
        name VARCHAR(20),
        bkt_col int default(
        CASE
            WHEN name=upper(name)
            THEN 1
            WHEN name!=upper(name)
            THEN 0
        END)
    );


Create the projection to attend you predicate, segmented on replicated is up to you table type/size

CREATE PROJECTION public.test_tbl 
(
 id,
 name,
 bkt_col
)
AS
 SELECT test_tbl.id,
        test_tbl.name,
        test_tbl.bkt_col
 FROM public.test_tbl
 ORDER BY test_tbl.bkt_col,
          test_tbl.id,
          test_tbl.name
SEGMENTED BY hash(test_tbl.bkt_col, test_tbl.id, test_tbl.name) ALL NODES KSAFE 1;


Insert some data

insert into  test_tbl(id,name)  values (1,'BOB') ;
insert into  test_tbl(id,name)  values (1,'bob') ;
id  name  bkt_col  
--  ----  -------  
1   bob   0        
1   BOB   1  


To query it use this sql, this way you avoid upper at run time and your stats will be well used.

SELECT
    *
FROM
    test_tbl where bkt_col = 1 and name IN ('BOB')