@FixFor( "MODE-869" )
@Test
public void shouldProducePlanWhenUsingSubqueryInSubquery() {

    // Define the schemata ...
    schemata = schemataBuilder.addTable("someTable", "column1", "column2", "column3")
                              .addTable("otherTable", "columnA", "columnB").addTable("stillOther", "columnX", "columnY")
                              .build();
    // Define the innermost subquery command ...
    QueryCommand subquery2 = builder.select("columnY").from("stillOther").where().propertyValue("stillOther", "columnX")
                                    .isLessThan().cast(3).asLong().end().query();
    builder = new QueryBuilder(typeSystem);
    // Define the outer subquery command ...
    QueryCommand subquery1 = builder.select("columnA").from("otherTable").where().propertyValue("otherTable", "columnB")
                                    .isEqualTo(subquery2).end().query();
    builder = new QueryBuilder(typeSystem);
    // Define the query command (which uses the subquery) ...
    query = builder.selectStar().from("someTable").where().path("someTable").isLike(subquery1).end().query();
    initQueryContext();
    plan = planner.createPlan(queryContext, query);
    // print = true;
    print(plan);
    assertThat(problems.hasErrors(), is(false));
    assertThat(problems.isEmpty(), is(true));
    // The top node should be the dependent query ...
    assertThat(plan.getType(), is(Type.DEPENDENT_QUERY));
    assertThat(plan.getChildCount(), is(2));
    // The first child of the top node should be a dependent query ...
    PlanNode depQuery1 = plan.getFirstChild();
    assertThat(depQuery1.getType(), is(PlanNode.Type.DEPENDENT_QUERY));
    assertThat(depQuery1.getChildCount(), is(2));
    // The first child should be the plan for the 2nd subquery (since it has to be executed first) ...
    PlanNode subqueryPlan2 = depQuery1.getFirstChild();
    assertProjectNode(subqueryPlan2, "columnY");
    assertThat(subqueryPlan2.getProperty(Property.VARIABLE_NAME, String.class), is(Subquery.VARIABLE_PREFIX + "2"));
    assertThat(subqueryPlan2.getChildCount(), is(1));
    assertThat(subqueryPlan2.getSelectors(), is(selectors("stillOther")));
    PlanNode criteriaNode2 = subqueryPlan2.getFirstChild();
    assertThat(criteriaNode2.getType(), is(PlanNode.Type.SELECT));
    assertThat(criteriaNode2.getChildCount(), is(1));
    assertThat(criteriaNode2.getSelectors(), is(selectors("stillOther")));
    assertThat(criteriaNode2.getProperty(Property.SELECT_CRITERIA),
               is((Object)lessThan(property("stillOther", "columnX"), literal(3L))));
    PlanNode subquerySource2 = criteriaNode2.getFirstChild();
    assertSourceNode(subquerySource2, "stillOther", null, "columnX", "columnY");
    assertThat(subquerySource2.getChildCount(), is(0));
    // The second child of the dependent query should be the plan for the subquery ...
    PlanNode subqueryPlan1 = depQuery1.getLastChild();
    assertProjectNode(subqueryPlan1, "columnA");
    assertThat(subqueryPlan1.getProperty(Property.VARIABLE_NAME, String.class), is(Subquery.VARIABLE_PREFIX + "1"));
    assertThat(subqueryPlan1.getChildCount(), is(1));
    assertThat(subqueryPlan1.getSelectors(), is(selectors("otherTable")));
    PlanNode criteriaNode1 = subqueryPlan1.getFirstChild();
    assertThat(criteriaNode1.getType(), is(PlanNode.Type.SELECT));
    assertThat(criteriaNode1.getChildCount(), is(1));
    assertThat(criteriaNode1.getSelectors(), is(selectors("otherTable")));
    assertThat(criteriaNode1.getProperty(Property.SELECT_CRITERIA),
               is((Object)equals(property("otherTable", "columnB"), var(Subquery.VARIABLE_PREFIX + "2"))));
    PlanNode subquerySource1 = criteriaNode1.getFirstChild();
    assertSourceNode(subquerySource1, "otherTable", null, "columnA", "columnB");
    assertThat(subquerySource1.getChildCount(), is(0));
    // The second child of the top node should be the plan for the regular query ...
    PlanNode queryPlan = plan.getLastChild();
    assertProjectNode(queryPlan, "column1", "column2", "column3");
    assertThat(queryPlan.getType(), is(PlanNode.Type.PROJECT));
    assertThat(queryPlan.getChildCount(), is(1));
    assertThat(queryPlan.getSelectors(), is(selectors("someTable")));
    PlanNode criteriaNode = queryPlan.getFirstChild();
    assertThat(criteriaNode.getType(), is(PlanNode.Type.SELECT));
    assertThat(criteriaNode.getChildCount(), is(1));
    assertThat(criteriaNode.getSelectors(), is(selectors("someTable")));
    assertThat(criteriaNode.getProperty(Property.SELECT_CRITERIA),
               is((Object)like(nodePath("someTable"), var(Subquery.VARIABLE_PREFIX + "1"))));
    PlanNode source = criteriaNode.getFirstChild();
    assertSourceNode(source, "someTable", null, "column1", "column2", "column3");
    assertThat(source.getChildCount(), is(0));
}