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

    node = optimize("SELECT c11, c12 FROM t1 WHERE c13 IN (SELECT c21 FROM t2 WHERE c22 < CAST('3' AS LONG)) AND c12 = (SELECT c22 FROM t2 WHERE c23 = 'extra')");
    // Create the expected plan ...
    print = true;
    PlanNode expected = new PlanNode(Type.DEPENDENT_QUERY, selector("t1"), selector("t2"));
    PlanNode subquery1 = new PlanNode(Type.ACCESS, expected, selector("t2"));
    subquery1.setProperty(Property.VARIABLE_NAME, Subquery.VARIABLE_PREFIX + "1");
    PlanNode project1 = new PlanNode(Type.PROJECT, subquery1, selector("t2"));
    project1.setProperty(Property.PROJECT_COLUMNS, columns(column("t2", "c22")));
    PlanNode select1 = new PlanNode(Type.SELECT, project1, selector("t2"));
    select1.setProperty(Property.SELECT_CRITERIA, new Comparison(new PropertyValue(selector("t2"), "c23"), Operator.EQUAL_TO,
                                                                 new Literal("extra")));
    PlanNode source1 = new PlanNode(Type.SOURCE, select1, selector("t2"));
    source1.setProperty(Property.SOURCE_NAME, selector("t2"));
    source1.setProperty(Property.SOURCE_COLUMNS, context.getSchemata().getTable(selector("t2")).getColumns());
    PlanNode depQuery2 = new PlanNode(Type.DEPENDENT_QUERY, expected, selector("t1"), selector("t2"));
    PlanNode subquery2 = new PlanNode(Type.ACCESS, depQuery2, selector("t2"));
    subquery2.setProperty(Property.VARIABLE_NAME, Subquery.VARIABLE_PREFIX + "2");
    PlanNode project2 = new PlanNode(Type.PROJECT, subquery2, selector("t2"));
    project2.setProperty(Property.PROJECT_COLUMNS, columns(column("t2", "c21")));
    PlanNode select2 = new PlanNode(Type.SELECT, project2, selector("t2"));
    select2.setProperty(Property.SELECT_CRITERIA, new Comparison(new PropertyValue(selector("t2"), "c22"),
                                                                 Operator.LESS_THAN, new Literal(3L)));
    PlanNode source2 = new PlanNode(Type.SOURCE, select2, selector("t2"));
    source2.setProperty(Property.SOURCE_NAME, selector("t2"));
    source2.setProperty(Property.SOURCE_COLUMNS, context.getSchemata().getTable(selector("t2")).getColumns());
    PlanNode mainQuery = new PlanNode(Type.ACCESS, depQuery2, selector("t1"));
    PlanNode project = new PlanNode(Type.PROJECT, mainQuery, selector("t1"));
    project.setProperty(Property.PROJECT_COLUMNS, columns(column("t1", "c11"), column("t1", "c12")));
    PlanNode firstSelect = new PlanNode(Type.SELECT, project, selector("t1"));
    firstSelect.setProperty(Property.SELECT_CRITERIA, new SetCriteria(new PropertyValue(selector("t1"), "c13"),
                                                                      new BindVariableName(Subquery.VARIABLE_PREFIX + "2")));
    PlanNode secondSelect = new PlanNode(Type.SELECT, firstSelect, selector("t1"));
    secondSelect.setProperty(Property.SELECT_CRITERIA, new Comparison(new PropertyValue(selector("t1"), "c12"),
                                                                      Operator.EQUAL_TO,
                                                                      new BindVariableName(Subquery.VARIABLE_PREFIX + "1")));
    PlanNode source = new PlanNode(Type.SOURCE, secondSelect, selector("t1"));
    source.setProperty(Property.SOURCE_NAME, selector("t1"));
    source.setProperty(Property.SOURCE_COLUMNS, context.getSchemata().getTable(selector("t1")).getColumns());
    // Compare the expected and actual plan ...
    assertPlanMatches(expected);
}