Column-level collation gives different results depending on whether pushdown is applied or not in Redshift connector

[mayankvadariya]

Each of the assert in below test fails with below error when test is executed by adding to TestRedshiftConnectorSmokeTest

-- failure 1 --
[Rows for query [SELECT t1.c_char_casesensitive, t1.c_char_caseinsensitive, t2.c_char_casesensitive, t2.c_char_caseinsensitive FROM test_schema.case_sensitive_1_lt48j58o8g t1 JOIN test_schema.case_sensitive_2_5d4djs7s99 t2 ON t1.c_char_caseinsensitive = t2.c_char_caseinsensitive]] 
Expecting actual:
  (MiXeD1    , MiXeD2    , mIxEd1    , MiXeD2    )
to contain exactly in any order:
  []
but the following elements were unexpected:
  (MiXeD1    , MiXeD2    , mIxEd1    , MiXeD2    )
at QueryAssertions$ResultAssert.lambda$matches$0(QueryAssertions$ResultAssert.java:740)

    @Test
    void testCaseSensitiveComparison()
    {
        try (TestTable table1 = new TestTable(
                TestingRedshiftServer::executeInRedshift,
                TEST_SCHEMA + "." + "case_sensitive_1_",
                "(c_varchar_casesensitive VARCHAR(10) COLLATE CASE_SENSITIVE, c_varchar_caseinsensitive VARCHAR(10) COLLATE CASE_INSENSITIVE, c_char_casesensitive CHAR(10) COLLATE CASE_SENSITIVE, c_char_caseinsensitive CHAR(10) COLLATE CASE_INSENSITIVE)",
                ImmutableList.of("'MiXeD1', 'MiXeD2', 'MiXeD1', 'MiXeD2'"));
            TestTable table2 = new TestTable(
                TestingRedshiftServer::executeInRedshift,
                TEST_SCHEMA + "." + "case_sensitive_2_",
                "(c_varchar_casesensitive VARCHAR(10) COLLATE CASE_SENSITIVE, c_varchar_caseinsensitive VARCHAR(10) COLLATE CASE_INSENSITIVE, c_char_casesensitive CHAR(10) COLLATE CASE_SENSITIVE, c_char_caseinsensitive CHAR(10) COLLATE CASE_INSENSITIVE)",
                ImmutableList.of("'mIxEd1', 'mIxEd2', 'mIxEd1', 'mIxEd2'")))
        {
            assertThat(query("SELECT * FROM " + table1.getName() + " WHERE c_varchar_caseinsensitive = 'mixed2'"))
                    .isFullyPushedDown();
            assertThat(query("SELECT * FROM " + table1.getName() + " WHERE c_char_caseinsensitive = 'mixed2'"))
                    .isFullyPushedDown();
            assertThat(query("SELECT t1.c_varchar_casesensitive, t1.c_varchar_caseinsensitive, t2.c_varchar_casesensitive, t2.c_varchar_caseinsensitive FROM " + table1.getName() + " t1 JOIN " + table2.getName() + " t2 ON t1.c_varchar_caseinsensitive = t2.c_varchar_caseinsensitive"))
                    .isFullyPushedDown();
            assertThat(query("SELECT t1.c_char_casesensitive, t1.c_char_caseinsensitive, t2.c_char_casesensitive, t2.c_char_caseinsensitive FROM " + table1.getName() + " t1 JOIN " + table2.getName() + " t2 ON t1.c_char_caseinsensitive = t2.c_char_caseinsensitive"))
                    .isFullyPushedDown();
        }
    }

Similar related issue is reported for Oracle #23567