content: /*
   * Licensed to the Apache Software Foundation (ASF) under one or more
   * contributor license agreements.  See the NOTICE file distributed with
   * this work for additional information regarding copyright ownership.
   * The ASF licenses this file to you under the Apache License, Version 2.0
   * (the "License"); you may not use this file except in compliance with
   * the License.  You may obtain a copy of the License at
   *
   * http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   * See the License for the specific language governing permissions and
   * limitations under the License.
   */
  package org.apache.calcite.test;
  
  import org.apache.calcite.adapter.spark.SparkRel;
  import org.apache.calcite.util.Util;
  
  import org.junit.jupiter.api.Disabled;
  import org.junit.jupiter.api.Test;
  
  /**
   * Tests for using Calcite with Spark as an internal engine, as implemented by
   * the {@link org.apache.calcite.adapter.spark} package.
   *
   * <p>Under JDK 23 and higher, this test requires
   * "{@code -Djava.security.manager=allow}" command-line arguments due to
   * Hadoop's use of deprecated methods in {@link javax.security.auth.Subject}.
   * These arguments are set automatically if you run via Gradle.
   */
  class SparkAdapterTest {
    private static final String VALUES0 = "(values (1, 'a'), (2, 'b'))";
  
    private static final String VALUES1 =
        "(values (1, 'a'), (2, 'b')) as t(x, y)";
  
    private static final String VALUES2 =
        "(values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as t(x, y)";
  
    private static final String VALUES3 =
        "(values (1, 'a'), (2, 'b')) as v(w, z)";
  
    private static final String VALUES4 =
        "(values (1, 'a'), (2, 'b'), (3, 'b'), (4, 'c'), (2, 'c')) as t(x, y)";
  
    private CalciteAssert.AssertQuery sql(String sql) {
      return CalciteAssert.that()
          .with(CalciteAssert.Config.SPARK)
          .query(sql);
    }
  
    /**
     * Tests a VALUES query evaluated using Spark.
     * There are no data sources.
     */
    @Test void testValues() {
      // Insert a spurious reference to a class in Calcite's Spark adapter.
      // Otherwise this test doesn't depend on the Spark module at all, and
      // Javadoc gets confused.
      Util.discard(SparkRel.class);
  
      final String sql = "select *\n"
          + "from " + VALUES0;
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])";
  
      final String[] expectedResult = {
          "EXPR$0=1; EXPR$1=a",
          "EXPR$0=2; EXPR$1=b"
      };
  
      sql(sql).returnsOrdered(expectedResult)
          .explainContains(plan);
    }
  
    /** Tests values followed by filter, evaluated by Spark. */
    @Test void testValuesFilter() {
      final String sql = "select *\n"
          + "from " + VALUES1 + "\n"
          + "where x < 2";
  
      final String[] expectedResult = {"X=1; Y=a"};
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[<($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testSelectDistinct() {
      final String sql = "select distinct *\n"
          + "from " + VALUES2;
  
      final String plan = "PLAN="
          + "EnumerableAggregate(group=[{0, 1}])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=1; Y=b",
          "X=2; Y=b",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests about grouping and aggregate functions
  
    @Test void testGroupBy() {
      final String sql = "select sum(x) as SUM_X, min(y) as MIN_Y, max(y) as MAX_Y, "
          + "count(*) as CNT_Y, count(distinct y) as CNT_DIST_Y\n"
          + "from " + VALUES2 + "\n"
          + "group by x";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):INTEGER NOT NULL], expr#7=[CAST($t2):CHAR(1) NOT NULL], expr#8=[CAST($t3):CHAR(1) NOT NULL], expr#9=[CAST($t4):BIGINT NOT NULL], SUM_X=[$t6], MIN_Y=[$t7], MAX_Y=[$t8], CNT_Y=[$t9], CNT_DIST_Y=[$t5])\n"
          + "  EnumerableAggregate(group=[{0}], SUM_X=[MIN($2) FILTER $7], MIN_Y=[MIN($3) FILTER $7], MAX_Y=[MIN($4) FILTER $7], CNT_Y=[MIN($5) FILTER $7], CNT_DIST_Y=[COUNT($1) FILTER $6])\n"
          + "    EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6, $t7)], expr#9=[1], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8], $g_1=[$t10])\n"
          + "      EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], SUM_X=[$SUM0($0)], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
          + "        EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n";
  
      final String[] expectedResult = {
          "SUM_X=2; MIN_Y=a; MAX_Y=b; CNT_Y=2; CNT_DIST_Y=2",
          "SUM_X=6; MIN_Y=b; MAX_Y=c; CNT_Y=3; CNT_DIST_Y=2"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testAggFuncNoGroupBy() {
      final String sql = "select sum(x) as SUM_X, min(y) as MIN_Y, max(y) as MAX_Y, "
          + "count(*) as CNT_Y, count(distinct y) as CNT_DIST_Y\n"
          + "from " + VALUES2;
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t3):BIGINT NOT NULL], proj#0..2=[{exprs}], CNT_Y=[$t5], CNT_DIST_Y=[$t4])\n"
          + "  EnumerableAggregate(group=[{}], SUM_X=[MIN($1) FILTER $6], MIN_Y=[MIN($2) FILTER $6], MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($0) FILTER $5])\n"
          + "    EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t2, $t6)], expr#8=[null:INTEGER], expr#9=[CASE($t7, $t8, $t1)], expr#10=[=($t5, $t6)], expr#11=[1], expr#12=[=($t5, $t11)], Y=[$t0], SUM_X=[$t9], MIN_Y=[$t3], MAX_Y=[$t4], CNT_Y=[$t2], $g_0=[$t10], $g_1=[$t12])\n"
          + "      EnumerableAggregate(group=[{1}], groups=[[{1}, {}]], SUM_X=[$SUM0($0)], agg#1=[COUNT()], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], $g=[GROUPING($1)])\n"
          + "        EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n";
  
      final String expectedResult = "SUM_X=8; MIN_Y=a; MAX_Y=c; CNT_Y=5; CNT_DIST_Y=3";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testGroupByOrderByAsc() {
      final String sql = "select x, count(*) as CNT_Y\n"
          + "from " + VALUES2 + "\n"
          + "group by x\n"
          + "order by x asc";
  
      final String plan = "";
  
      final String expectedResult = "X=1; CNT_Y=2\n"
          + "X=2; CNT_Y=3\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testGroupByMinMaxCountCountDistinctOrderByAsc() {
      final String sql = "select x, min(y) as MIN_Y, max(y) as MAX_Y, count(*) as CNT_Y, "
          + "count(distinct y) as CNT_DIST_Y\n"
          + "from " + VALUES2 + "\n"
          + "group by x\n"
          + "order by x asc";
  
      final String plan = "PLAN="
          + "EnumerableSort(sort0=[$0], dir0=[ASC])\n"
          + "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):CHAR(1) NOT NULL], expr#6=[CAST($t2):CHAR(1) NOT NULL], expr#7=[CAST($t3):BIGINT NOT NULL], X=[$t0], MIN_Y=[$t5], MAX_Y=[$t6], CNT_Y=[$t7], CNT_DIST_Y=[$t4])\n"
          + "    EnumerableAggregate(group=[{0}], MIN_Y=[MIN($2) FILTER $6], MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($1) FILTER $5])\n"
          + "      EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5, $t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7], $g_1=[$t9])\n"
          + "        EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
          + "          EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "X=1; MIN_Y=a; MAX_Y=b; CNT_Y=2; CNT_DIST_Y=2\n"
          + "X=2; MIN_Y=b; MAX_Y=c; CNT_Y=3; CNT_DIST_Y=2\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testGroupByMiMaxCountCountDistinctOrderByDesc() {
      final String sql = "select x, min(y) as MIN_Y, max(y) as MAX_Y, count(*) as CNT_Y, "
          + "count(distinct y) as CNT_DIST_Y\n"
          + "from " + VALUES2 + "\n"
          + "group by x\n"
          + "order by x desc";
  
      final String plan = "PLAN="
          + "EnumerableSort(sort0=[$0], dir0=[DESC])\n"
          + "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):CHAR(1) NOT NULL], expr#6=[CAST($t2):CHAR(1) NOT NULL], expr#7=[CAST($t3):BIGINT NOT NULL], X=[$t0], MIN_Y=[$t5], MAX_Y=[$t6], CNT_Y=[$t7], CNT_DIST_Y=[$t4])\n"
          + "    EnumerableAggregate(group=[{0}], MIN_Y=[MIN($2) FILTER $6], MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($1) FILTER $5])\n"
          + "      EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5, $t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7], $g_1=[$t9])\n"
          + "        EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
          + "          EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "X=2; MIN_Y=b; MAX_Y=c; CNT_Y=3; CNT_DIST_Y=2\n"
          + "X=1; MIN_Y=a; MAX_Y=b; CNT_Y=2; CNT_DIST_Y=2\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testGroupByHaving() {
      final String sql = "select x\n"
          + "from " + VALUES2 + "\n"
          + "group by x\n"
          + "having count(*) > 2";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2:BIGINT], expr#3=[>($t1, $t2)], X=[$t0], $condition=[$t3])\n"
          + "  EnumerableAggregate(group=[{0}], agg#0=[COUNT()])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "X=2";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests about set operators (UNION, UNION ALL, INTERSECT)
  
    @Test void testUnionAll() {
      final String sql = "select *\n"
          + "from " + VALUES1 + "\n"
          + " union all\n"
          + "select *\n"
          + "from " + VALUES2;
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=1; Y=a",
          "X=1; Y=b",
          "X=2; Y=b",
          "X=2; Y=b",
          "X=2; Y=c",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testUnion() {
      final String sql = "select *\n"
          + "from " + VALUES1 + "\n"
          + " union\n"
          + "select *\n"
          + "from " + VALUES2;
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=1; Y=b",
          "X=2; Y=b",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testIntersect() {
      final String sql = "select *\n"
          + "from " + VALUES1 + "\n"
          + " intersect\n"
          + "select *\n"
          + "from " + VALUES2;
  
      final String plan = "PLAN="
          + "EnumerableIntersect(all=[false])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=2; Y=b"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests about sorting
  
    @Test void testSortXAscProjectY() {
      final String sql = "select y\n"
          + "from " + VALUES2 + "\n"
          + "order by x asc";
  
      final String plan = "PLAN="
          + "EnumerableSort(sort0=[$1], dir0=[ASC])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "Y=a\n"
          + "Y=b\n"
          + "Y=b\n"
          + "Y=c\n"
          + "Y=c\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testSortXDescYDescProjectY() {
      final String sql = "select y\n"
          + "from " + VALUES2 + "\n"
          + "order by x desc, y desc";
  
      final String plan = "PLAN="
          + "EnumerableSort(sort0=[$1], sort1=[$0], dir0=[DESC], dir1=[DESC])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "Y=c\n"
          + "Y=c\n"
          + "Y=b\n"
          + "Y=b\n"
          + "Y=a\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testSortXDescYAscProjectY() {
      final String sql = "select y\n"
          + "from " + VALUES2 + "\n"
          + "order by x desc, y";
  
      final String plan = "PLAN="
          + "EnumerableSort(sort0=[$1], sort1=[$0], dir0=[DESC], dir1=[ASC])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "Y=b\n"
          + "Y=c\n"
          + "Y=c\n"
          + "Y=a\n"
          + "Y=b\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testSortXAscYDescProjectY() {
      final String sql = "select y\n"
          + "from " + VALUES2 + "\n"
          + "order by x, y desc";
  
      final String plan = "PLAN="
          + "EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "Y=b\n"
          + "Y=a\n"
          + "Y=c\n"
          + "Y=c\n"
          + "Y=b\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    // Tests involving joins
  
    @Test void testJoinProject() {
      final String sql = "select t.y, v.z\n"
          + "from " + VALUES2 + "\n"
          + "  join " + VALUES3 + " on t.x = v.w";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..3=[{inputs}], Y=[$t3], Z=[$t1])\n"
          + "  EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "Y=a; Z=a",
          "Y=b; Z=a",
          "Y=b; Z=b",
          "Y=c; Z=b",
          "Y=c; Z=b"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testJoinProjectAliasProject() {
      final String sql = "select r.z\n"
          + "from (\n"
          + "  select *\n"
          + "  from " + VALUES2 + "\n"
          + "    join " + VALUES3 + " on t.x = v.w) as r";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..3=[{inputs}], Z=[$t1])\n"
          + "  EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "Z=a",
          "Z=a",
          "Z=b",
          "Z=b",
          "Z=b"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests involving LIMIT/OFFSET
  
    @Test void testLimit() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "where x = 1\n"
          + "limit 1";
  
      final String plan = "PLAN="
          + "EnumerableLimit(fetch=[1])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[=($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }";
  
      final String expectedResult = "X=1; Y=a";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testOrderByLimit() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "order by y\n"
          + "limit 1";
  
      final String plan = "PLAN="
          + "EnumerableLimit(fetch=[1])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "X=1; Y=a\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testOrderByOffset() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "order by y\n"
          + "offset 2";
  
      final String plan = "PLAN="
          + "EnumerableLimit(offset=[2])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "X=1; Y=b\n"
          + "X=2; Y=c\n"
          + "X=2; Y=c\n";
  
      sql(sql).returns(expectedResult)
          .explainContains(plan);
    }
  
    // Tests involving "complex" filters in WHERE clause
  
    @Test void testFilterBetween() {
      final String sql = "select *\n"
          + "from " + VALUES4 + "\n"
          + "where x between 3 and 4";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[Sarg[[3..4]]], expr#3=[SEARCH($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 3, 'b' }, { 4, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=3; Y=b",
          "X=4; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testFilterIsIn() {
      final String sql = "select *\n"
          + "from " + VALUES4 + "\n"
          + "where x in (3, 4)";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[Sarg[3, 4]], expr#3=[SEARCH($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 3, 'b' }, { 4, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=3; Y=b",
          "X=4; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testFilterTrue() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "where true";
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=1; Y=b",
          "X=2; Y=b",
          "X=2; Y=c",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testFilterFalse() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "where false";
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[]])\n\n";
  
      final String expectedResult = "";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testFilterOr() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "where x = 1 or x = 2";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[Sarg[1, 2]], expr#3=[SEARCH($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=1; Y=b",
          "X=2; Y=b",
          "X=2; Y=c",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testFilterIsNotNull() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "where x is not null";
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1; Y=a",
          "X=1; Y=b",
          "X=2; Y=b",
          "X=2; Y=c",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testFilterIsNull() {
      final String sql = "select *\n"
          + "from " + VALUES2 + "\n"
          + "where x is null";
  
      final String plan = "PLAN="
          + "EnumerableValues(tuples=[[]])\n\n";
  
      final String expectedResult = "";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests on more complex queries as UNION operands
  
    @Test void testUnionWithFilters() {
      final String sql = "select *\n"
          + "from " + VALUES1 + "\n"
          + "where x > 1\n"
          + " union all\n"
          + "select *\n"
          + "from " + VALUES2 + "\n"
          + "where x > 1";
  
      final String plan = "PLAN="
          + "EnumerableUnion(all=[true])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n";
  
      final String[] expectedResult = {
          "X=2; Y=b",
          "X=2; Y=b",
          "X=2; Y=c",
          "X=2; Y=c"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testUnionWithFiltersProject() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x > 1\n"
          + " union\n"
          + "select x\n"
          + "from " + VALUES2 + "\n"
          + "where x > 1";
  
      final String plan = "PLAN="
          + "EnumerableUnion(all=[false])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, $t2)], X=[$t0], $condition=[$t3])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
          + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, $t2)], X=[$t0], $condition=[$t3])\n"
          + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
  
      final String expectedResult = "X=2";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests involving arithmetic operators
  
    @Test void testArithmeticPlus() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x + 1 > 1";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t0, $t2)], expr#4=[>($t3, $t2)], X=[$t0], $condition=[$t4])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1",
          "X=2"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testArithmeticMinus() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x - 1 > 0";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[-($t0, $t2)], expr#4=[0], expr#5=[>($t3, $t4)], X=[$t0], $condition=[$t5])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
  
      final String expectedResult = "X=2";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testArithmeticMul() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x * x > 1";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[*($t0, $t0)], expr#3=[1], expr#4=[>($t2, $t3)], X=[$t0], $condition=[$t4])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
  
      final String expectedResult = "X=2";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Test void testArithmeticDiv() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x / x = 1";
  
      final String plan = "PLAN="
          + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[/($t0, $t0)], expr#3=[1], expr#4=[=($t2, $t3)], X=[$t0], $condition=[$t4])\n"
          + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
  
      final String[] expectedResult = {
          "X=1",
          "X=2"
      };
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    // Tests involving sub-queries (both correlated and non correlated)
  
    @Disabled("[CALCITE-2184] ClassCastException: RexSubQuery cannot be cast to RexLocalRef")
    @Test void testFilterExists() {
      final String sql = "select *\n"
          + "from " + VALUES4 + "\n"
          + "where exists (\n"
          + "  select *\n"
          + "  from " + VALUES3 + "\n"
          + "  where w < x\n"
          + ")";
  
      final String plan = "PLAN=todo\n\n";
  
      final String expectedResult = "X=2; Y=b\n"
          + "X=2; Y=c\n"
          + "X=3; Y=b\n"
          + "X=4; Y=c";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Disabled("[CALCITE-2184] ClassCastException: RexSubQuery cannot be cast to RexLocalRef")
    @Test void testFilterNotExists() {
      final String sql = "select *\n"
          + "from " + VALUES4 + "\n"
          + "where not exists (\n"
          + "  select *\n"
          + "  from " + VALUES3 + "\n"
          + "  where w > x\n"
          + ")";
  
      final String plan = "PLAN=todo\n\n";
  
      final String expectedResult = "X=1; Y=a";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Disabled("[CALCITE-2184] ClassCastException: RexSubQuery cannot be cast to RexLocalRef")
    @Test void testSubQueryAny() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x <= any (\n"
          + "  select x\n"
          + "  from " + VALUES2 + "\n"
          + ")";
  
      final String plan = "PLAN=todo\n\n";
  
      final String expectedResult = "X=1\n"
          + "X=2";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  
    @Disabled("[CALCITE-2184] ClassCastException: RexSubQuery cannot be cast to RexLocalRef")
    @Test void testSubQueryAll() {
      final String sql = "select x\n"
          + "from " + VALUES1 + "\n"
          + "where x <= all (\n"
          + "  select x\n"
          + "  from " + VALUES2 + "\n"
          + ")";
  
      final String plan = "PLAN=todo\n\n";
  
      final String expectedResult = "X=2";
  
      sql(sql).returnsUnordered(expectedResult)
          .explainContains(plan);
    }
  }
  