/* This file is part of VoltDB.
 * Copyright (C) 2008-2017 VoltDB Inc.
 *
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the
 * "Software"), to deal in the Software without restriction, including
 * without limitation the rights to use, copy, modify, merge, publish,
 * distribute, sublicense, and/or sell copies of the Software, and to
 * permit persons to whom the Software is furnished to do so, subject to
 * the following conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
 * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
 * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 */

package org.voltdb.planner;

import java.util.List;

import org.voltdb.VoltType;
import org.voltdb.expressions.AbstractExpression;
import org.voltdb.expressions.AbstractSubqueryExpression;
import org.voltdb.expressions.ComparisonExpression;
import org.voltdb.expressions.ConstantValueExpression;
import org.voltdb.expressions.ParameterValueExpression;
import org.voltdb.expressions.TupleValueExpression;
import org.voltdb.plannodes.AbstractJoinPlanNode;
import org.voltdb.plannodes.AbstractPlanNode;
import org.voltdb.plannodes.AbstractScanPlanNode;
import org.voltdb.plannodes.AggregatePlanNode;
import org.voltdb.plannodes.LimitPlanNode;
import org.voltdb.plannodes.NestLoopIndexPlanNode;
import org.voltdb.plannodes.NestLoopPlanNode;
import org.voltdb.plannodes.NodeSchema;
import org.voltdb.plannodes.ProjectionPlanNode;
import org.voltdb.plannodes.SchemaColumn;
import org.voltdb.plannodes.SeqScanPlanNode;
import org.voltdb.types.ExpressionType;
import org.voltdb.types.PlanNodeType;
import org.voltdb.types.QuantifierType;

public class TestPlansInExistsSubQueries extends PlannerTestCase {

    public void testInExistsGuard() {
        String errorMsg = PlanAssembler.IN_EXISTS_SCALAR_ERROR_MESSAGE;
        String sql;
        List<AbstractPlanNode> pns;

        sql = "select p2.c from p2 where p2.c > ? and exists (select c from r1 where r1.c = p2.c)";
        pns = compileToFragments(sql);
        assertEquals(2, pns.size());

        sql = "select p2.c from p2 where p2.a in (select c from r1)";
        pns = compileToFragments(sql);
        assertEquals(2, pns.size());

        sql = "select r2.c from r2 where r2.c > ? and exists (select c from p1 where p1.c = r2.c)";
        failToCompile(sql, errorMsg);

        sql = "select * from P1 as parent where (A,C) in (select 2, C from r2 where r2.c > parent.c group by c)";
        pns = compileToFragments(sql);
        assertEquals(2, pns.size());

        sql = "select r2.c from r2 where r2.c > ? and exists (select c from r1 where r1.c = r2.c and "
                + "exists (select c from p1 where p1.c = r1.c ))";
        failToCompile(sql, errorMsg);
    }

    public void testExistsWithUserParams() {
        {
            // Parent query with user's param and subquery with parent TVE
            AbstractPlanNode pn = compile("select r2.c from r2 where r2.c > ? and exists (select c from r1 where r1.c = r2.c)");
            pn = pn.getChild(0);
            assertTrue(pn instanceof AbstractScanPlanNode);
            AbstractScanPlanNode nps = (AbstractScanPlanNode) pn;
            // Check param indexes
            AbstractExpression e = nps.getPredicate();
            AbstractExpression le = e.getLeft();
            if (le.getExpressionType().equals(ExpressionType.COMPARE_GREATERTHAN))  {
                assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
                AbstractExpression pve = le.getRight();
                assertEquals(ExpressionType.VALUE_PARAMETER, pve.getExpressionType());
                assertEquals(new Integer(0), ((ParameterValueExpression)pve).getParameterIndex());
                AbstractExpression re = e.getRight();
                assertEquals(ExpressionType.OPERATOR_EXISTS, re.getExpressionType());
                AbstractSubqueryExpression se = (AbstractSubqueryExpression) re.getLeft();
                assertEquals(1, se.getArgs().size());
                assertEquals(1, se.getParameterIdxList().size());
                assertEquals(Integer.valueOf(1), se.getParameterIdxList().get(0));
            }
            else{
                le = e.getRight();
                assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
                AbstractExpression pve = le.getRight();
                assertEquals(ExpressionType.VALUE_PARAMETER, pve.getExpressionType());
                assertEquals(new Integer(0), ((ParameterValueExpression)pve).getParameterIndex());
                AbstractExpression re = e.getLeft();
                assertEquals(ExpressionType.OPERATOR_EXISTS, re.getExpressionType());
                AbstractSubqueryExpression se = (AbstractSubqueryExpression) re.getLeft();
                assertEquals(1, se.getArgs().size());
                assertEquals(1, se.getParameterIdxList().size());
                assertEquals(Integer.valueOf(1), se.getParameterIdxList().get(0));
            }
        }
        {
            // Subqueries  with  grand-parent TVE
            AbstractPlanNode pn = compile("select r1.c from r1 where  " +
                    "exists ( select 1 from r2 where exists" +
                    "(select 1 from r3 where r3.a = r1.c))");
            pn = pn.getChild(0);
            assertTrue(pn instanceof AbstractScanPlanNode);
            AbstractScanPlanNode spn = (AbstractScanPlanNode) pn;
            // Check param indexes
            AbstractExpression e = spn.getPredicate();
            assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
            AbstractSubqueryExpression se = (AbstractSubqueryExpression) e.getLeft();
            List<AbstractExpression> args = se.getArgs();
            assertEquals(1, args.size());
            assertEquals(ExpressionType.VALUE_TUPLE, args.get(0).getExpressionType());
            TupleValueExpression tve = (TupleValueExpression) args.get(0);
            assertEquals("R1", tve.getTableName());
            assertEquals("C", tve.getColumnName());
            List<Integer> params = se.getParameterIdxList();
            assertEquals(1, params.size());
            assertEquals(new Integer(0), params.get(0));
            // Child subquery
            pn = se.getSubqueryNode();
            assertTrue(pn instanceof AbstractScanPlanNode);
            spn = (AbstractScanPlanNode) pn;
            e = spn.getPredicate();
            assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
            se = (AbstractSubqueryExpression) e.getLeft();
            // Grand parent subquery
            pn = se.getSubqueryNode();
            assertTrue(pn instanceof AbstractScanPlanNode);
            spn = (AbstractScanPlanNode) pn;
            e = spn.getPredicate();
            assertEquals(ExpressionType.COMPARE_EQUAL, e.getExpressionType());
            e = e.getRight();
            assertEquals(ExpressionType.VALUE_PARAMETER, e.getExpressionType());
            assertEquals(new Integer(0), ((ParameterValueExpression) e).getParameterIndex());
        }
        {
            // Subqueries  with  parent & grand-parent TVE
            AbstractPlanNode pn = compile("select r1.c from r1 where  " +
                    "exists ( select 1 from r2 where r1.d = r2.c and exists" +
                    "(select 1 from r3 where r3.a = r1.c))");
            pn = pn.getChild(0);
            assertTrue(pn instanceof AbstractScanPlanNode);
            AbstractScanPlanNode spn = (AbstractScanPlanNode) pn;
            // Check param indexes
            AbstractExpression e = spn.getPredicate();
            assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
            AbstractSubqueryExpression se = (AbstractSubqueryExpression) e.getLeft();
            List<AbstractExpression> args = se.getArgs();
            assertEquals(2, args.size());
            assertEquals(ExpressionType.VALUE_TUPLE, args.get(0).getExpressionType());
            TupleValueExpression tve = (TupleValueExpression) args.get(0);
            assertEquals("R1", tve.getTableName());
            assertEquals("C", tve.getColumnName());
            tve = (TupleValueExpression) args.get(1);
            assertEquals("R1", tve.getTableName());
            assertEquals("D", tve.getColumnName());
            List<Integer> params = se.getParameterIdxList();
            assertEquals(2, params.size());
            assertEquals(new Integer(0), params.get(0));
            assertEquals(new Integer(1), params.get(1));
            // Child subquery
            pn = se.getSubqueryNode();
            assertTrue(pn instanceof AbstractScanPlanNode);
            spn = (AbstractScanPlanNode) pn;
            e = spn.getPredicate();
            assertEquals(ExpressionType.COMPARE_EQUAL, e.getRight().getExpressionType());
            AbstractExpression ce = e.getRight().getRight();
            assertEquals(ExpressionType.VALUE_PARAMETER, ce.getExpressionType());
            assertEquals(new Integer(1), ((ParameterValueExpression) ce).getParameterIndex());
            // Grand parent subquery
            AbstractExpression gce = e.getLeft();
            assertEquals(ExpressionType.OPERATOR_EXISTS, gce.getExpressionType());
            se = (AbstractSubqueryExpression) gce.getLeft();
            pn = se.getSubqueryNode();
            assertTrue(pn instanceof AbstractScanPlanNode);
            spn = (AbstractScanPlanNode) pn;
            e = spn.getPredicate();
            assertEquals(ExpressionType.COMPARE_EQUAL, e.getExpressionType());
            e = e.getRight();
            assertEquals(ExpressionType.VALUE_PARAMETER, e.getExpressionType());
            assertEquals(new Integer(0), ((ParameterValueExpression) e).getParameterIndex());
        }
        {
            AbstractPlanNode pn = compile("select r2.a from r2 where exists " +
                    "( SELECT 1 from R2 WHERE r2.c = ?)");
            pn = pn.getChild(0);
            assertEquals(PlanNodeType.SEQSCAN, pn.getPlanNodeType());
            SeqScanPlanNode spl = (SeqScanPlanNode) pn;
            AbstractExpression e = spl.getPredicate();
            assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
            AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) e.getLeft();
            assertEquals(0, subExpr.getParameterIdxList().size());
            // Subquery
            pn = subExpr.getSubqueryNode();
            assertEquals(PlanNodeType.SEQSCAN, pn.getPlanNodeType());
            spl = (SeqScanPlanNode) pn;
            e = spl.getPredicate();
            assertEquals(ExpressionType.COMPARE_EQUAL, e.getExpressionType());
            assertEquals(ExpressionType.VALUE_PARAMETER, e.getRight().getExpressionType());
        }
    }

    public void testParamTveInOutputSchema() {
        AbstractPlanNode pn = compile("select r2.a from r2, r1 where r2.a = r1.a or " +
                "exists (select 1 from r2 where exists(select 1 from r2 where r2.a = r1.c))");
        pn = pn.getChild(0);
        verifyOutputSchema(pn, "A");
        pn = pn.getChild(0);
        assertTrue(pn instanceof NestLoopPlanNode);
        NestLoopPlanNode nlp = (NestLoopPlanNode) pn;
        // looking for the r1.c to be part of the pn output schema
        // it is required by the second subquery
        verifyOutputSchema(nlp, "A", "A", "C");
    }

    public void testInToExist() {
        AbstractPlanNode pn = compile("select r2.c from r2 where r2.a in (select c from r1)");
        pn = pn.getChild(0);
        assertTrue(pn instanceof AbstractScanPlanNode);
        AbstractScanPlanNode spl = (AbstractScanPlanNode) pn;
        // Check param indexes
        AbstractExpression e = spl.getPredicate();
        assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
        AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) e.getLeft();
        assertEquals(1, subExpr.getArgs().size());
        assertEquals(1, subExpr.getParameterIdxList().size());
        assertEquals(Integer.valueOf(0), subExpr.getParameterIdxList().get(0));
        AbstractExpression tve = subExpr.getArgs().get(0);
        assertTrue(tve instanceof TupleValueExpression);
        assertEquals("R2", ((TupleValueExpression)tve).getTableName());
        assertEquals("A", ((TupleValueExpression)tve).getColumnName());
    }

    public void testInToExistWithUnion() {
        AbstractPlanNode pn = compile("select r2.c from r2 where r2.a in (select c from r1 union (select c from r3 limit 1 offset 2) intersect select c from r2)");
        pn = pn.getChild(0);
        assertTrue(pn instanceof AbstractScanPlanNode);
        AbstractScanPlanNode spl = (AbstractScanPlanNode) pn;
        // Check param indexes
        AbstractExpression e = spl.getPredicate();
        assertEquals(ExpressionType.CONJUNCTION_OR, e.getExpressionType());
        AbstractExpression l = e.getLeft();
        assertEquals(ExpressionType.OPERATOR_EXISTS, l.getExpressionType());
        AbstractExpression r = e.getRight();
        assertEquals(ExpressionType.CONJUNCTION_AND, r.getExpressionType());
        l = r.getLeft();
        assertEquals(ExpressionType.COMPARE_EQUAL, l.getExpressionType());
        assertEquals(QuantifierType.ANY, ((ComparisonExpression) l).getQuantifier());
        r = r.getRight();
        assertEquals(ExpressionType.OPERATOR_EXISTS, r.getExpressionType());
    }

    public void testInToExistWithOffset() {
        AbstractPlanNode pn = compile("select r2.c from r2 where r2.a in (select c from r1 limit 1 offset 3)");
        pn = pn.getChild(0);
        assertTrue(pn instanceof AbstractScanPlanNode);
        AbstractScanPlanNode spl = (AbstractScanPlanNode) pn;
        // Check param indexes
        AbstractExpression e = spl.getPredicate();
        assertEquals(ExpressionType.COMPARE_EQUAL, e.getExpressionType());
        assertEquals(QuantifierType.ANY, ((ComparisonExpression) e).getQuantifier());
    }

    public void testInToExistsComplex() {
        AbstractPlanNode pn = compile("select * from R1 where (A,C) in (select 2, C from r2 where r2.c > r1.c group by c)");
        pn = pn.getChild(0);
        assertTrue(pn instanceof AbstractScanPlanNode);
        AbstractScanPlanNode spn = (AbstractScanPlanNode) pn;
        AbstractExpression e = spn.getPredicate();
        assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
        AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) e.getLeft();
        assertEquals(3, subExpr.getArgs().size());
        assertEquals(3, subExpr.getParameterIdxList().size());
    }

    public void testNotExists() {
        AbstractPlanNode pn = compile("select r2.c from r2 where not exists (select c from r1 where r1.c = r2.c)");
        pn = pn.getChild(0);
        assertTrue(pn instanceof AbstractScanPlanNode);
        AbstractScanPlanNode nps = (AbstractScanPlanNode) pn;
        AbstractExpression e = nps.getPredicate();
        assertEquals(ExpressionType.OPERATOR_NOT, e.getExpressionType());
        AbstractExpression le = e.getLeft();
        assertEquals(ExpressionType.OPERATOR_EXISTS, le.getExpressionType());
        AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) le.getLeft();
        assertEquals(1, subExpr.getArgs().size());
        assertEquals(1, subExpr.getParameterIdxList().size());
        assertEquals(Integer.valueOf(0), subExpr.getParameterIdxList().get(0));
    }

    public void testExistsJoin() {
        {
            AbstractPlanNode pn = compile("select r1.a from r1,r2 where exists (" +
                    "select 1 from r3 where r1.d = r3.c and r2.a = r3.c)");
            pn = pn.getChild(0).getChild(0);
            assertEquals(PlanNodeType.NESTLOOP, pn.getPlanNodeType());
            AbstractExpression e = ((NestLoopPlanNode) pn).getJoinPredicate();
            assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
            AbstractSubqueryExpression se = (AbstractSubqueryExpression) e.getLeft();
            List<AbstractExpression> args = se.getArgs();
            assertEquals(2, args.size());
            // order of elements in TubleVauleExperssion is not consistent on Java 7 and Java 8
            TupleValueExpression tve = (TupleValueExpression)args.get(0);
            if (tve.getTableName().equals("R2")) {
                assertEquals("A", tve.getColumnName());
                tve = (TupleValueExpression)args.get(1);
                assertEquals("R1", tve.getTableName());
                assertEquals("D", tve.getColumnName());
            }
            else {
                assertEquals("R1", tve.getTableName());
                assertEquals("D", tve.getColumnName());
                tve = (TupleValueExpression)args.get(1);
                assertEquals("R2", tve.getTableName());
                assertEquals("A", tve.getColumnName());
            }
            assertEquals(2, se.getParameterIdxList().size());
            // Child query
            pn = se.getSubqueryNode();
            e = ((AbstractScanPlanNode)pn).getPredicate();
            AbstractExpression le = e.getLeft();
            assertEquals(ExpressionType.VALUE_PARAMETER, le.getRight().getExpressionType());
            AbstractExpression re = e.getRight();
            assertEquals(ExpressionType.VALUE_PARAMETER, re.getRight().getExpressionType());
        }
        {
            AbstractPlanNode pn = compile("select r1.a from r1,r2 where r1.a = r2.a and " +
                    "exists ( select 1 from r3 where r1.a = r3.a)");

            pn = pn.getChild(0).getChild(0);
            assertTrue(pn instanceof NestLoopIndexPlanNode);
            pn = pn.getChild(0);
            assertTrue(pn instanceof SeqScanPlanNode);
            AbstractExpression pred = ((SeqScanPlanNode) pn).getPredicate();
            assertNotNull(pred);
            assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
        }
        {
            AbstractPlanNode pn = compile("select r1.a from r1,r2 where " +
                    "exists ( select 1 from r3 where r1.a = r3.a and r2.c = r3.c)");

            pn = pn.getChild(0).getChild(0);
            assertTrue(pn instanceof NestLoopPlanNode);
            AbstractExpression pred = ((NestLoopPlanNode) pn).getJoinPredicate();
            assertNotNull(pred);
            assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
            AbstractSubqueryExpression se = (AbstractSubqueryExpression) pred.getLeft();
            List<AbstractExpression> args = se.getArgs();
            assertEquals(2, args.size());
            TupleValueExpression tve = (TupleValueExpression)args.get(0);
            if (tve.getTableName().equals("R2")) {
                assertEquals("C", tve.getColumnName());
                tve = (TupleValueExpression)args.get(1);
                assertEquals("R1", tve.getTableName());
                assertEquals("A", tve.getColumnName());
            }
            else {
                assertEquals("R1", tve.getTableName());
                assertEquals("A", tve.getColumnName());
                tve = (TupleValueExpression)args.get(1);
                assertEquals("R2", tve.getTableName());
                assertEquals("C", tve.getColumnName());
            }
            assertEquals(2, se.getParameterIdxList().size());
            // Child query
            pn = se.getSubqueryNode();
            pred = ((AbstractScanPlanNode)pn).getPredicate();
            AbstractExpression le = pred.getLeft();
            assertEquals(ExpressionType.VALUE_PARAMETER, le.getRight().getExpressionType());
            AbstractExpression re = pred.getRight();
            assertEquals(ExpressionType.VALUE_PARAMETER, re.getRight().getExpressionType());
        }
    }

    public void testInJoin() {
        {
            // IN gets converted to EXISTS
            AbstractPlanNode pn = compile("select r1.d from r1, r2 where r1.a IN " +
                    "(select a from r3 where r3.c = r2.d);");
            pn = pn.getChild(0).getChild(0);
            assertTrue(pn instanceof NestLoopPlanNode);
            AbstractExpression pred = ((NestLoopPlanNode) pn).getJoinPredicate();
            assertNotNull(pred);
            assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
            AbstractSubqueryExpression se = (AbstractSubqueryExpression) pred.getLeft();
            List<AbstractExpression> args = se.getArgs();
            assertEquals(2, args.size());
            TupleValueExpression tve = (TupleValueExpression)args.get(0);
            assertEquals("R2", tve.getTableName());
            assertEquals("D", tve.getColumnName());
            tve = (TupleValueExpression)args.get(1);
            assertEquals("R1", tve.getTableName());
            assertEquals("A", tve.getColumnName());
            assertEquals(2, se.getParameterIdxList().size());
            // Child query
            pn = se.getSubqueryNode();
            pred = ((AbstractScanPlanNode)pn).getPredicate();
            AbstractExpression le = pred.getLeft();
            assertEquals(ExpressionType.VALUE_PARAMETER, le.getRight().getExpressionType());
            AbstractExpression re = pred.getRight();
            assertEquals(ExpressionType.VALUE_PARAMETER, re.getLeft().getExpressionType());
        }
        {
            // OFFSET prevents In-to-EXISTS transformation
            AbstractPlanNode pn = compile("select r1.d from r1, r2 where r1.a IN " +
                    "(select a from r3 where r3.c = r2.d limit 1 offset 2);");
            /* enable to debug */ System.out.println(pn.toExplainPlanString());
            pn = pn.getChild(0).getChild(0);
            assertTrue(pn instanceof NestLoopPlanNode);
            AbstractExpression pred = ((NestLoopPlanNode) pn).getJoinPredicate();
            assertNotNull(pred);
            assertEquals(ExpressionType.COMPARE_EQUAL, pred.getExpressionType());
            assertEquals(QuantifierType.ANY, ((ComparisonExpression) pred).getQuantifier());
            TupleValueExpression tve = (TupleValueExpression) pred.getLeft();
            assertEquals("R1", tve.getTableName());
            assertEquals("A", tve.getColumnName());
            // Child query
            AbstractSubqueryExpression se = (AbstractSubqueryExpression)pred.getRight();
            assertEquals(1, se.getParameterIdxList().size());
            List<AbstractExpression> args = se.getArgs();
            assertEquals(1, args.size());
            tve = (TupleValueExpression)args.get(0);
            assertEquals("R2", tve.getTableName());
            assertEquals("D", tve.getColumnName());
            pn = se.getSubqueryNode();
            assertEquals(PlanNodeType.SEQSCAN, pn.getPlanNodeType());
            pred = ((AbstractScanPlanNode)pn).getPredicate();
            tve = (TupleValueExpression) pred.getLeft();
            assertEquals("R3", tve.getTableName());
            assertEquals("C", tve.getColumnName());
            ParameterValueExpression pve = (ParameterValueExpression) pred.getRight();
            assertEquals(new Integer(0), pve.getParameterIndex());
        }
    }

    public void testInAggregated() {
        AbstractPlanNode pn = compile("select a, sum(c) as sc1 from r1 where (a, c) in " +
                "( SELECT a, count(c) as sc2 " +
                "from  r1  GROUP BY a ORDER BY a DESC) GROUP BY A;");
        pn = pn.getChild(0);
        assertTrue(pn instanceof AbstractScanPlanNode);

        AbstractExpression e = ((AbstractScanPlanNode)pn).getPredicate();
        assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
        AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) e.getLeft();
        AbstractPlanNode sn = subExpr.getSubqueryNode();
        // Added LIMIT 1
        assertTrue(sn instanceof LimitPlanNode);
        assertEquals(1, ((LimitPlanNode)sn).getLimit());
        sn = sn.getChild(0);
        assertTrue(sn instanceof SeqScanPlanNode);
        AggregatePlanNode aggNode = AggregatePlanNode.getInlineAggregationNode(sn);
        assertNotNull(aggNode.getPostPredicate());
    }
    public static String HavingErrorMsg = "SQL HAVING clauses with subquery expressions are not allowed.";

    public void testInHaving() {
        String sql;
        AbstractPlanNode pn;
        AggregatePlanNode aggNode;

        sql = "select a from r1 group by a having max(c) in (select c from r2 )";
        failToCompile(sql, HavingErrorMsg);

            // ENG-8306: Uncomment next block when HAVING with subquery is supported
//            AbstractPlanNode pn = compile(sql);
//            pn = pn.getChild(0);
//            assertTrue(pn instanceof ProjectionPlanNode);
//            pn = pn.getChild(0);
//            assertTrue(pn instanceof SeqScanPlanNode);
//            AggregatePlanNode aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
//            assertNotNull(aggNode);
//            NodeSchema ns = aggNode.getOutputSchema();
//            assertEquals(2, ns.size());
//            SchemaColumn aggColumn = ns.getColumns().get(1);
//            assertEquals("$$_MAX_$$_1", aggColumn.getColumnAlias());
//            AbstractExpression having = aggNode.getPostPredicate();
//            assertEquals(ExpressionType.OPERATOR_EXISTS, having.getExpressionType());
//            AbstractExpression se = having.getLeft();
//            assertEquals(1, se.getArgs().size());
//            assertTrue(se.getArgs().get(0) instanceof TupleValueExpression);
//            TupleValueExpression argTve = (TupleValueExpression) se.getArgs().get(0);
//            assertEquals(1, argTve.getColumnIndex());
//            assertEquals("$$_MAX_$$_1", argTve.getColumnAlias());

        // HAVING expression evaluates to TRUE and dropped
        sql = "select a from r1 " +
                " group by a " +
                " having exists (select max(a) from r2) or max(c) > 0";
        pn = compile(sql);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
        assertNotNull(aggNode);
        assertTrue(aggNode.getPostPredicate() == null);

        // HAVING expression evaluates to FALSE and retained
        sql = "select a from r1 " +
                " group by a " +
                " having exists (select max(a) from r2 limit 0) and max(c) > 0";
        pn = compile(sql);
        pn = pn.getChild(0);
        assertTrue(pn instanceof ProjectionPlanNode);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
        assertNotNull(aggNode);
        AbstractExpression having = aggNode.getPostPredicate();
        assertTrue(ConstantValueExpression.isBooleanFalse(having));
    }

    public void testHavingInSubquery() {
        String sql;
        AbstractPlanNode pn;
        AggregatePlanNode aggNode;
        List<AbstractExpression> args;
        AbstractExpression pred;
        AbstractSubqueryExpression sqe;
        AbstractExpression postExpr;
        AbstractExpression re;
        AbstractExpression le;

        // filter on agg of expression involving grand-parent tve
        sql = "select a from r1 where exists " +
                "(select 1 from r2 where exists  " +
                " (select 1 from r3 group by c having min(a) > r1.d)) ";
        pn = compile(sql);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        pred = ((SeqScanPlanNode)pn).getPredicate();
        // child
        assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
        sqe = (AbstractSubqueryExpression) pred.getLeft();
        //* enable to debug */ System.out.println(se.explain(""));
        args = sqe.getArgs();
        assertEquals(1, args.size());
        assertEquals(1, sqe.getParameterIdxList().size());
        assertEquals("D", ((TupleValueExpression)args.get(0)).getColumnName());
        pn = sqe.getSubqueryNode();
        assertTrue(pn instanceof SeqScanPlanNode);
        pred = ((SeqScanPlanNode)pn).getPredicate();
        // grand child
        assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
        sqe = (AbstractSubqueryExpression) pred.getLeft();
        pn = sqe.getSubqueryNode();
        pn = pn.getChild(0).getChild(0);
        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
        assertNotNull(aggNode);
        postExpr = aggNode.getPostPredicate();
        assertNotNull(postExpr);
        assertEquals(ExpressionType.COMPARE_GREATERTHAN,
                postExpr.getExpressionType());
        re = postExpr.getRight();
        assertEquals(ExpressionType.VALUE_PARAMETER, re.getExpressionType());
        assertEquals(new Integer(0), ((ParameterValueExpression)re).getParameterIndex());

        // filter on agg of expression involving parent tve
        sql = "select a from r1 where c in " +
                " (select max(c) from r2 group by e having min(a) > r1.d)";
        pn = compile(sql);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        pred = ((SeqScanPlanNode)pn).getPredicate();
        assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
        sqe = (AbstractSubqueryExpression) pred.getLeft();
        args = sqe.getArgs();
        assertEquals(2, args.size());
        assertEquals(2, sqe.getParameterIdxList().size());
        assertEquals("D", ((TupleValueExpression)args.get(0)).getColumnName());
        assertEquals("C", ((TupleValueExpression)args.get(1)).getColumnName());
        pn = sqe.getSubqueryNode();
        pn = pn.getChild(0);
        assertTrue(pn instanceof LimitPlanNode);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
        assertNotNull(aggNode);
        assertEquals(3, aggNode.getOutputSchema().size());
        postExpr = aggNode.getPostPredicate();
        assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
        le = postExpr.getLeft();
        assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
        assertEquals(new Integer(0), ((ParameterValueExpression)le.getRight()).getParameterIndex());
        re = postExpr.getRight();
        assertEquals(ExpressionType.COMPARE_EQUAL, re.getExpressionType());
        assertEquals(new Integer(1), ((ParameterValueExpression)re.getLeft()).getParameterIndex());

        // filter on agg of expression involving user parameter ('?')
        sql = "select a from r1 where c in " +
                " (select max(c) from r2 group by e having min(a) > ?) ";
        pn = compile(sql);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        pred = ((SeqScanPlanNode)pn).getPredicate();
        assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
        sqe = (AbstractSubqueryExpression)pred.getLeft();
        assertEquals(1, sqe.getParameterIdxList().size());
        assertEquals(new Integer(1), sqe.getParameterIdxList().get(0));
        pn = sqe.getSubqueryNode();
        pn = pn.getChild(0).getChild(0);
        assertEquals(PlanNodeType.SEQSCAN, pn.getPlanNodeType());
        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
        assertNotNull(aggNode);
        postExpr = aggNode.getPostPredicate();
        assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
        // User PVE
        le = postExpr.getLeft();
        assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
        assertEquals(ExpressionType.VALUE_PARAMETER, le.getRight().getExpressionType());
        assertEquals(new Integer(0), ((ParameterValueExpression)le.getRight()).getParameterIndex());
        // Parent PVE
        re = postExpr.getRight();
        assertEquals(ExpressionType.COMPARE_EQUAL, re.getExpressionType());
        assertEquals(ExpressionType.VALUE_PARAMETER, re.getLeft().getExpressionType());
        assertEquals(new Integer(1), ((ParameterValueExpression)re.getLeft()).getParameterIndex());

        // filter on agg of local tve
        sql = "select a from r1 where c in " +
                " (select max(c) from r2 group by e having min(a) > 0)";
        pn = compile(sql);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        AbstractExpression p = ((SeqScanPlanNode)pn).getPredicate();
        assertEquals(ExpressionType.OPERATOR_EXISTS, p.getExpressionType());
        AbstractExpression subquery = p.getLeft();
        pn = ((AbstractSubqueryExpression)subquery).getSubqueryNode();
        pn = pn.getChild(0);
        assertTrue(pn instanceof LimitPlanNode);
        pn = pn.getChild(0);
        assertTrue(pn instanceof SeqScanPlanNode);
        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
        assertNotNull(aggNode);
        assertEquals(3, aggNode.getOutputSchema().size());
        postExpr = aggNode.getPostPredicate();
        assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());

        failToCompile("select max(c) from r1 group by a " +
                " having count(*) = (select c from r2 where r2.c = r1.a)",
                HavingErrorMsg);

        /**
         * Uncomment these tests when ENG-8306 is finished
         */
//        // parent correlated TVE in the aggregate expression.
//        sql = "select max(c) from r1 group by a " +
//                " having count(*) = (select c from r2 where r2.c = r1.a)";
//        pn = compile(sql);
//        pn = pn.getChild(0);
//        assertTrue(pn instanceof ProjectionPlanNode);
//        pn = pn.getChild(0);
//        assertTrue(pn instanceof SeqScanPlanNode);
//        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
//        assertNotNull(aggNode);
//        assertNotNull(aggNode instanceof HashAggregatePlanNode);
//        assertEquals(3, aggNode.getOutputSchema().size()); // group by key, max, count
//
//        postExpr = aggNode.getPostPredicate();
//        assertEquals(ExpressionType.COMPARE_EQUAL, postExpr.getExpressionType());
//        assertTrue(postExpr.getLeft() instanceof TupleValueExpression);
//        assertTrue(postExpr.getRight() instanceof SelectSubqueryExpression);
    }

    public void testExistsSimplification() {
        AbstractPlanNode pn;
        AbstractJoinPlanNode jpn;

        // LIMIT is 0 EXISTS => FALSE
        pn = compile("select a from r1 where exists " +
                " (select a, c  from r2 limit 0) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);

        // LIMIT is 0 EXISTS => FALSE
        pn = compile("select a from r1 where exists " +
                " (select count(*)  from r2 limit 0) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);

        //EXISTS => TRUE, join predicate is TRUE or EXPR = > TRUE and dropped
        pn = compile("select r1.a from r1 join r2 on (exists " +
                " (select max(a)  from r2) or r2.a > 0)");
        assertTrue(pn.getChild(0).getChild(0) instanceof AbstractJoinPlanNode);
        jpn = (AbstractJoinPlanNode) pn.getChild(0).getChild(0);
        assertTrue(jpn.getWherePredicate() == null);

            //EXISTS => FALSE, join predicate is retained
        pn = compile("select r1.a from r1 join r2 on exists " +
                " (select max(a)  from r2 offset 1) ");
        assertTrue(pn.getChild(0).getChild(0) instanceof NestLoopPlanNode);
        jpn = (NestLoopPlanNode) pn.getChild(0).getChild(0);
        verifyCVEPredicate(jpn.getJoinPredicate(), false);

        // table-agg-without-having-groupby OFFSET > 0 => FALSE
        pn = compile("select a from r1 where exists " +
                " (select count(*)  from r2 offset 1) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);

        // table-agg-without-having-groupby  => TRUE
        pn = compile("select a from r1 where exists " +
                " (select max(a)  from r2) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        assertTrue(((SeqScanPlanNode) pn.getChild(0)).getPredicate() == null);

        // table-agg-without-having-groupby by limit is a parameter => EXISTS
        pn = compile("select a from r1 where exists " +
                " (select max(a)  from r2 limit ?) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        AbstractExpression pred = ((SeqScanPlanNode) pn.getChild(0)).getPredicate();
        assertNotNull(pred);
        assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());

        // Subquery => select 1 from r2 limit 1 offset 2
        pn = compile("select a from r1 where exists " +
                " (select a, c  from r2 order by a offset 2) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyTrivialSchemaLimitOffset(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 1, 2);

        // User's limit ?
        // Subquery => EXISTS (select 1 from r2 limit ?)
        pn = compile("select a from r1 where exists " +
                " (select a, c  from r2 order by a limit ?) ");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyTrivialSchemaLimitOffset(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), -1, 0);

        // Subquery subquery-without-having with group by and no limit
        //   => select a, max(c) from r2 group by a limit 1
        pn = compile("select a from r1 where exists " +
                " (select a, max(c) from r2 group by a order by max(c))");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyAggregateSubquery(((SeqScanPlanNode)pn.getChild(0)).getPredicate(), 2, 1, false);

        // Subquery subquery-without-having with group by and offset 3 => subquery-without-having with group by and offset 3
        pn = compile("select a from r1 where exists " +
                " (select a, max(c) from r2 group by a order by max(c) offset 2)");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        verifyAggregateSubquery(((SeqScanPlanNode)pn.getChild(0)).getPredicate(), 2, 1, false);

        // Subquery subquery-with-having with group by => subquery-with-having with group by
        pn = compile("select a from r1 where exists " +
                " (select a, max(c) from r2 group by a having max(c) > 2 order by max(c))");
        assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
        // weakened for now around the unification of the input column to the HAVING clause:
        verifyAggregateSubquery(((SeqScanPlanNode)pn.getChild(0)).getPredicate(), 2, 1, true);
        //verifyAggregateSubquery(((SeqScanPlanNode)pn.getChild(0)).getPredicate(), 3, 1, true);
    }

    // HSQL failed to parse these statement
    public void testHSQLFailed() {
        failToCompile("select a from r1 where exists (" +
                "select 1 from r2 group by r2.a having max(r1.a + r2.a) in (" +
                " select max(a) from r3))",
                "expression not in aggregate or GROUP BY columns");

        failToCompile("select a from r1 group by a " +
                " having exists (select c from r2 where r2.c = max(r1.a))",
                "subquery with WHERE expression with aggregates on parent columns are not supported");

        // This may not actually an HSQL failure
        // -- at any rate, it gets detected later in the planner.
        failToCompile("select * from r1 join r2 on exists " +
                " (select a from r2 where a > 1) ",
                "Join with filters that do not depend on joined tables is not supported in VoltDB");
    }

    // Disabled for now
    //    public void testDeleteWhereIn() {
    //        List<AbstractPlanNode> lpn = compileToFragments("delete from r1 where a in (select a from r2 where r1.c = r2.c)");
    //        assertTrue(lpn.size() == 2);
    //        AbstractPlanNode n = lpn.get(1).getChild(0);
    //        assertTrue(n instanceof DeletePlanNode);
    //        n = n.getChild(0);
    //        assertTrue(n instanceof AbstractScanPlanNode);
    //        AbstractScanPlanNode spn = (AbstractScanPlanNode) n;
    //        AbstractExpression e = spn.getPredicate();
    //        assertEquals(ExpressionType.SUBQUERY, e.getExpressionType());
    //    }

    // Disabled for now
    //    public void testUpdateWhereIn() {
    //      List<AbstractPlanNode> lpn = compileToFragments("update r1 set c = 1 where a in (select a from r2 where r1.c = r2.c)");
    //      assertTrue(lpn.size() == 2);
    //      AbstractPlanNode n = lpn.get(1).getChild(0);
    //      assertTrue(n instanceof UpdatePlanNode);
    //      n = n.getChild(0);
    //      assertTrue(n instanceof AbstractScanPlanNode);
    //      AbstractScanPlanNode spn = (AbstractScanPlanNode) n;
    //      AbstractExpression e = spn.getPredicate();
    //      assertEquals(ExpressionType.SUBQUERY, e.getExpressionType());
    //    }

    private void verifyCVEPredicate(AbstractExpression p, boolean value) {
        assertNotNull(p);
        assertEquals(ExpressionType.VALUE_CONSTANT, p.getExpressionType());
        assertEquals(VoltType.BOOLEAN, p.getValueType());
        assertEquals(Boolean.toString(value), ((ConstantValueExpression) p).getValue());
    }

    private void verifyAggregateSubquery(AbstractExpression exists,
            int columnCount, int groupByCount, boolean hasHaving) {
        assertNotNull(exists);
        assertEquals(ExpressionType.OPERATOR_EXISTS, exists.getExpressionType());
        AbstractSubqueryExpression se = (AbstractSubqueryExpression)exists.getLeft();
        AbstractPlanNode sn = se.getSubqueryNode();
        assertTrue(sn instanceof AbstractScanPlanNode);
        AbstractPlanNode inline = sn.getInlinePlanNode(PlanNodeType.AGGREGATE);
        assertNotNull(inline);
        assertEquals(columnCount, inline.getOutputSchema().size());
        AggregatePlanNode agg = (AggregatePlanNode) inline;
        assertEquals(groupByCount, agg.getGroupByExpressions().size());
        assertEquals(hasHaving, agg.getPostPredicate() != null);
    }

    private void verifyTrivialSchemaLimitOffset(AbstractExpression exists,
            int limit, int offset) {
        assertNotNull(exists);
        assertEquals(ExpressionType.OPERATOR_EXISTS, exists.getExpressionType());
        AbstractSubqueryExpression se = (AbstractSubqueryExpression)exists.getLeft();
        AbstractPlanNode pn = se.getSubqueryNode();
        assertTrue(pn instanceof SeqScanPlanNode);
        AbstractPlanNode inline = pn.getInlinePlanNode(PlanNodeType.PROJECTION);
        assertNotNull(inline);
        inline = pn.getInlinePlanNode(PlanNodeType.LIMIT);
        assertNotNull(inline);
        assertEquals(limit, ((LimitPlanNode) inline).getLimit());
        assertEquals(offset, ((LimitPlanNode) inline).getOffset());
    }

    private void verifyOutputSchema(AbstractPlanNode pn, String... columns) {
        NodeSchema ns = pn.getOutputSchema();
        List<SchemaColumn> scs = ns.getColumns();
        for (int i = 0; i < scs.size(); ++i) {
            SchemaColumn col = scs.get(i);
            assertEquals(columns[i], col.getColumnName());
            assertEquals(4, col.getSize());
            assertEquals(VoltType.INTEGER, col.getType());
            assertTrue(col.getExpression() instanceof TupleValueExpression);
            assertTrue(((TupleValueExpression)col.getExpression()).getColumnIndex() != -1);
        }
    }


    @Override
    protected void setUp() throws Exception {
        setupSchema(TestPlansSubQueries.class.getResource("testplans-subqueries-ddl.sql"), "dd", false);
        AbstractPlanNode.enableVerboseExplainForDebugging();
        AbstractExpression.enableVerboseExplainForDebugging();
    }

}
