SET NAMES UTF8;

CREATE DATABASE 'inet4://localhost:3055/test'
USER "SYSDBA" PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET UTF8;

RECREATE TABLE MANS (
  CODE_MAN BIGINT NOT NULL,
  CODE_SEX SMALLINT NOT NULL,
  NAME VARCHAR(50) NOT NULL COLLATE UNICODE_CI,
  CONSTRAINT PK_MANS PRIMARY KEY(CODE_MAN)
);

CREATE INDEX IDX_SEX_AND_NAME ON MANS(CODE_SEX, NAME);

INSERT INTO MANS (CODE_MAN, CODE_SEX, NAME)
VALUES (1, 1, 'Bob');

INSERT INTO MANS (CODE_MAN, CODE_SEX, NAME)
VALUES (2, 1, 'John');

INSERT INTO MANS (CODE_MAN, CODE_SEX, NAME)
VALUES (3, 2, 'Barbara');

INSERT INTO MANS (CODE_MAN, CODE_SEX, NAME)
VALUES (4, 2, 'Anna');

COMMIT;

-- Good
SELECT *
FROM MANS
WHERE CODE_SEX = 1 AND NAME STARTS 'B';

             CODE_MAN CODE_SEX NAME
===================== ======== ==================================================
                    1        1 Bob


-- Good
SELECT *
FROM MANS
WHERE CODE_SEX = 2 AND NAME STARTS 'B';

             CODE_MAN CODE_SEX NAME
===================== ======== ==================================================
                    3        2 Barbara


-- Error
SELECT *
FROM MANS
WHERE CODE_SEX IN (1, 2) AND NAME STARTS 'B';


             CODE_MAN CODE_SEX NAME
===================== ======== ==================================================
                    1        1 Bob

-- Good
SELECT *
FROM MANS
WHERE (CODE_SEX = 1 OR CODE_SEX = 2) AND NAME STARTS 'B';


             CODE_MAN CODE_SEX NAME
===================== ======== ==================================================
                    1        1 Bob
                    3        2 Barbara