sql/src/test/quidem/org.apache.druid.sql.calcite.DecoupledPlanningCalciteJoinQueryTest/testSemiAndAntiJoinSimultaneouslyUsingExplicitJoins@default.iq

# testSemiAndAntiJoinSimultaneouslyUsingExplicitJoins@default case-crc:9311cae7
# quidem testcase reason: SLIGHTLY_WORSE_FILTER_PUSHED_TO_JOIN_OPERAND
!set debug true
!set defaultTimeout 300000
!set enableTimeBoundaryPlanning true
!set maxScatterGatherBytes 9223372036854775807
!set plannerStrategy DECOUPLED
!set sqlCurrentTimestamp 2000-01-01T00:00:00Z
!set sqlQueryId dummy
!set outputformat mysql
!use druidtest:///
SELECT dim1, COUNT(*) FROM
foo
INNER JOIN (SELECT MAX(__time) t FROM foo) t0 on t0.t = foo.__time
LEFT JOIN (SELECT MIN(__time) t FROM foo) t1 on t1.t = foo.__time
WHERE dim1 IN ('abc', 'def') AND t1.t is null
GROUP BY 1;
+------+--------+
| dim1 | EXPR$1 |
+------+--------+
| abc  |      1 |
+------+--------+
(1 row)

!ok
LogicalAggregate(group=[{1}], EXPR$1=[COUNT()])
  LogicalFilter(condition=[IS NULL($3)])
    LogicalJoin(condition=[=($3, $0)], joinType=[left])
      LogicalJoin(condition=[=($2, $0)], joinType=[inner])
        LogicalFilter(condition=[SEARCH($1, Sarg['abc':VARCHAR, 'def':VARCHAR]:VARCHAR)])
          LogicalProject(__time=[$0], dim1=[$1])
            LogicalTableScan(table=[[druid, foo]])
        LogicalAggregate(group=[{}], t=[MAX($0)])
          LogicalTableScan(table=[[druid, foo]])
      LogicalAggregate(group=[{}], t=[MIN($0)])
        LogicalTableScan(table=[[druid, foo]])

!logicalPlan
DruidAggregate(group=[{1}], EXPR$1=[COUNT()], druid=[logical])
  DruidFilter(condition=[IS NULL($3)])
    DruidJoin(condition=[=($0, $3)], joinType=[left])
      DruidJoin(condition=[=($0, $2)], joinType=[inner])
        DruidFilter(condition=[SEARCH($1, Sarg['abc':VARCHAR, 'def':VARCHAR]:VARCHAR)])
          DruidProject(__time=[$0], dim1=[$1], druid=[logical])
            DruidTableScan(table=[[druid, foo]], druid=[logical])
        DruidAggregate(group=[{}], t=[MAX($0)], druid=[logical])
          DruidTableScan(table=[[druid, foo]], druid=[logical])
      DruidAggregate(group=[{}], t=[MIN($0)], druid=[logical])
        DruidTableScan(table=[[druid, foo]], druid=[logical])

!druidPlan
{
  "queryType" : "groupBy",
  "dataSource" : {
    "type" : "join",
    "left" : {
      "type" : "join",
      "left" : {
        "type" : "query",
        "query" : {
          "queryType" : "scan",
          "dataSource" : {
            "type" : "table",
            "name" : "foo"
          },
          "intervals" : {
            "type" : "intervals",
            "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
          },
          "resultFormat" : "compactedList",
          "filter" : {
            "type" : "inType",
            "column" : "dim1",
            "matchValueType" : "STRING",
            "sortedValues" : [ "abc", "def" ]
          },
          "columns" : [ "__time", "dim1" ],
          "columnTypes" : [ "LONG", "STRING" ],
          "granularity" : {
            "type" : "all"
          },
          "legacy" : false
        }
      },
      "right" : {
        "type" : "query",
        "query" : {
          "queryType" : "timeBoundary",
          "dataSource" : {
            "type" : "table",
            "name" : "foo"
          },
          "intervals" : {
            "type" : "intervals",
            "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
          },
          "bound" : "maxTime",
          "granularity" : {
            "type" : "all"
          }
        }
      },
      "rightPrefix" : "j0.",
      "condition" : "(\"__time\" == \"j0.a0\")",
      "joinType" : "INNER"
    },
    "right" : {
      "type" : "query",
      "query" : {
        "queryType" : "timeBoundary",
        "dataSource" : {
          "type" : "table",
          "name" : "foo"
        },
        "intervals" : {
          "type" : "intervals",
          "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
        },
        "bound" : "minTime",
        "granularity" : {
          "type" : "all"
        }
      }
    },
    "rightPrefix" : "_j0.",
    "condition" : "(\"__time\" == \"_j0.a0\")",
    "joinType" : "LEFT"
  },
  "intervals" : {
    "type" : "intervals",
    "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
  },
  "filter" : {
    "type" : "null",
    "column" : "_j0.a0"
  },
  "granularity" : {
    "type" : "all"
  },
  "dimensions" : [ {
    "type" : "default",
    "dimension" : "dim1",
    "outputName" : "d0",
    "outputType" : "STRING"
  } ],
  "aggregations" : [ {
    "type" : "count",
    "name" : "a0"
  } ],
  "limitSpec" : {
    "type" : "NoopLimitSpec"
  }
}
!nativePlan