###### t/nist3.test ######

# 1. extreme simplified version
--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings

# Create the objects (database, tables ...)
  CREATE TABLE t1 (PNUM CHAR(3));

  CREATE TABLE t2 (PNUM CHAR(3));

# The COLUMN 'PNUM' within the SELECT list is needed !!
# The table within the subquery is needed !!
  let $my_stmt= SELECT PNUM
          FROM t2
          HAVING PNUM IN (SELECT 'P1'
                    FROM t1) ;
  # successful non prepared variant
  eval $my_stmt;


  # prepared variant
  eval PREPARE stmt1 FROM "$my_stmt" ;
  # here comes the crash
  EXECUTE stmt1;

# 2. the original version(table names modified) from the NIST tests
#    sql/dml018    TEST:0072
# Please uncomment the next line to check this version
# let $fulltest= 1;
while ($fulltest)
{
  --disable_warnings
  DROP TABLE t1,t2;
  --enable_warnings

  # Create the objects (database, tables ...)
    CREATE TABLE t1
     (PNUM     CHAR(3) NOT NULL UNIQUE,
      PNAME    CHAR(20),
      PTYPE    CHAR(6),
      BUDGET   DECIMAL(9),
      CITY     CHAR(15));


    CREATE TABLE t2
     (EMPNUM   CHAR(3) NOT NULL,
      PNUM     CHAR(3) NOT NULL,
      HOURS    DECIMAL(5),
      UNIQUE(EMPNUM,PNUM));

    # NIST comment
  -- TEST:0072 Nested HAVING IN with no outer reference!
     let $my_stmt=  SELECT t2.PNUM
            FROM t2
            GROUP BY t2.PNUM
            HAVING t2.PNUM IN (SELECT t1.PNUM
                      FROM t1
                      GROUP BY t1.PNUM
                      HAVING SUM(t1.BUDGET) > 25000);
    # successful non prepared variant
     eval $my_stmt;
    # NIST comment (expectation)
  -- PASS:0072 .... It must be succesful

    # prepared variant
    eval PREPARE stmt1 from "$my_stmt" ;
    # here comes the crash
    EXECUTE stmt1;
  let $fulltest= 0;
}