# test case derived from NIST 
#      test script dml075
#      TEST:0434 GROUP BY with HAVING EXISTS-correlated set function!



--disable_abort_on_error

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings


######## extreme simplified tests case
CREATE TABLE t1
( PNUM BIGINT, HOURS    BIGINT);

CREATE TABLE t2 (PNUM  BIGINT);


######## SELECT getting an error message ########
# fails with ERROR HY000: Invalid use of group function
SELECT PNUM, SUM(HOURS) FROM t1
GROUP BY PNUM
HAVING EXISTS (SELECT 1 FROM t2
               WHERE SUM(t1.HOURS) = t2.PNUM );

# 1.  remove the SUM around the correlated column from the subquery
# fails with ERROR 42S22: Unknown column 't1.HOURS' in 'where clause'
SELECT PNUM, SUM(HOURS) FROM t1
GROUP BY PNUM
HAVING EXISTS (SELECT 1 FROM t2
               WHERE t1.HOURS = t2.PNUM );

# 2.  remove the GROUP BY
# fails with ERROR 42S22: Unknown column 't1.HOURS' in 'where clause'
SELECT PNUM, SUM(HOURS) FROM t1
HAVING EXISTS (SELECT 1 FROM t2
               WHERE t1.HOURS = t2.PNUM );

# Please set $activate to 1, if you want to see the statements for
# comparison. They are slightly modified and give a correct result.
let $activate= 0;
while ($activate)
{
  #   exchange the SUM from the outer query with the constant 1 and
  #   remove the GROUP BY
  # gives a correct result
  SELECT 1 as "my_col" FROM t1
  HAVING EXISTS (SELECT 1 FROM t2
                 WHERE t2.PNUM = t1.HOURS );

  #   exchange the correlated column from the subquery with the constant 10
  # gives a correct result
  SELECT PNUM, SUM(HOURS) FROM t1
  GROUP BY PNUM
  HAVING EXISTS (SELECT 1 FROM t2
               WHERE 10 = t2.PNUM );

  #   shift the correlated subquery into the WHERE qualification and drop HAVING
  # gives a correct result
  SELECT PNUM, SUM(HOURS) FROM t1
  WHERE EXISTS (SELECT 1 FROM t2
              WHERE t1.HOURS = t2.PNUM )
  GROUP BY PNUM;

  ###############################################################################
  # So it looks like MySQL dislikes the combination:
  # aggregat function within the outer query and correlated subquery after HAVING
  ###############################################################################

  let $activate= 0;
}

# Please set $activate to 1, if you want to see the original NIST test case
# getting the bad return code. NIST assumes, that the syntax is correct.
let $activate= 0;
while ($activate)
{
  DROP TABLE t1, t2;

  CREATE TABLE t1
  (EMPNUM   CHAR(3) NOT NULL,
  PNUM     CHAR(3) NOT NULL,
  HOURS    DECIMAL(5),
  UNIQUE(EMPNUM,PNUM));
  
  CREATE TABLE t2
  (PNUM     CHAR(3) NOT NULL UNIQUE,
  PNAME    CHAR(20),
  PTYPE    CHAR(6),
  BUDGET   DECIMAL(9),
  CITY     CHAR(15));

  # fails with ERROR HY000: Invalid use of group function
  SELECT PNUM, SUM(HOURS) FROM t1
  GROUP BY PNUM
  HAVING EXISTS (SELECT PNAME FROM t2
                 WHERE t2.PNUM = t1.PNUM
                   AND SUM(t1.HOURS) > t2.BUDGET / 200);

  let $activate= 0;
}