Fixed casting issue: skipped Index Condition Pushdown optm for lossy conversions - #4359

Fixes #4350

Issue
In query:

CREATE TABLE t2(c0 INT);
CREATE TABLE t3(c0 DOUBLE);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1.0), (0.5);
SELECT COUNT(*) AS wrong_cnt
FROM (SELECT t2.c0 AS subq1_c1 FROM t2) AS subq1
WHERE subq1.subq1_c1 IN (SELECT t3.c0 FROM t3);

The output should be 1 cause(1 == 1.0) but count(*) was returning 2, at first it seems a casting issue but i figured that the after parser AST is created and then sent over to get "getprepared()" we have an optimization called Index Condition Pushdown which pushes the t3 row over the t2 and creates a scanIndex on t2, so instead of linearly scanning and validating IN for every row in t2 it does a reverse scan, it iterates in t3 and asks whether this is present in t2 index or not

IndexCursor.java nextCursor() function

else if (inResult != null) {
            while (inResult.next()) {
                Value v = inResult.currentRow()[0];
                if (v != ValueNull.INSTANCE) {
                    if (inColumn instanceof Column[]) {
                        v = Column.convert(session, (Column[]) inColumn, (ValueRow) v);
                    } else {
                        v = ((Column) inColumn).convert(session, v);
                    }
                    find(v);
                    break;

and if present then it return that row of t2 and then later ConditionQuery.java again do the IN query.

So the bug was lying in this reverse checking, while iterating in t3 it first converts the double to int and then asks t2. So t3[1.0 , 0.5] 1.0 -> 1 and 1 exits in 1 so that row in t2 is given to ConditionQuery.java to be processed and then again 0.5 -> 1 and it asks t2 again and yes 1 exists so it gives 1 again and processed again and hence 2 from t2 didnt got a chance of being processed and 1 got processed twice.

FIX
So what i thought was of to skip this "Index Condition Pushdown Optimization" for lossy conversions just like other majority Databases do, you can read their ICP docs.

I created a helper "isSafeIndexCondition" function in comparison.java which still validates the original logic of "havingsameOrdering" (they are of same family) and added this check : Left expression (here t2) if have same type as higherType among t2,t3 then we can create index (if t2 was double then creating index was safe).

NOTE:- But here comes a performance edge case in Integer Family: Even though SmallInNT is of lowerType than standard Int, if t2 was SMALLINT and t3 was BIGINT, index creation was also safe here because they belonged to the same integer family and hence conversion was not lossy. So we can safely create the index. And this check also handles if t2 was smallerType and t3 was higherType just like the issue.

All these files i made changes in somehow creates the index so i added validation in them with our helper "isSafeIndexCondition" from Comparison.java.

TESTS
I added test for all the files i have changed, and test cover all the cases of ConditioIn, ConditionInQuery, ConditinInList, ConditioInQuery, ConditioInArray, and Standard Comparisons of >, = etc.

NOTE:
During testing, I found a pre existing bug for static row lists like WHERE (INT, VARCHAR) IN ((DOUBLE, VARCHAR)). The DB should not create an index for this lossy compound row. This check should take place recursively inside haveSameOrdering(TypeInfo t1, TypeInfo t2) in TypeInfo.java, but it currently passes as true and gives wrong answers when pushing the lossy values into the index.

(Note: This doesn't affect IN (SELECT) subqueries because ConditionInQuery currently has a hardcoded limitation that aborts all multi column pushdowns).

My changes doesnt covers this case because it should be done in haveSameOrdering for ROW and can be fixed in a separate pr.