# reduced test from suite NIST SET AUTOCOMMIT = 0; --disable_abort_on_error ## reduced test let $new_dba= HU; --error 0,1396 eval DROP USER $new_dba@localhost; eval CREATE USER $new_dba@localhost identified by 'PWD'; eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (HU,localhost,HU,PWD,test); --disable_warnings DROP SCHEMA IF EXISTS HU; --enable_warnings CREATE SCHEMA HU ; USE HU ; CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL UNIQUE, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15)); # DELETE FROM STAFF; # DELETE FROM PROJ; INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale'); INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna'); INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa'); INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale'); INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna'); INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale'); COMMIT WORK; SELECT COUNT(*) FROM PROJ; -- PASS:Setup if count = 6? SELECT COUNT(*) FROM STAFF; -- PASS:Setup if count = 5? # -- MODULE DML090 -- TEST:0564 Outer ref. directly contained in HAVING clause! # original version SELECT EMPNUM, GRADE*1000 FROM HU.STAFF WHERE GRADE * 1000 > ANY (SELECT SUM(BUDGET) FROM HU.PROJ GROUP BY CITY, PTYPE HAVING HU.PROJ.CITY = HU.STAFF.CITY); # reduced version without HU. SELECT EMPNUM, GRADE*1000 FROM STAFF WHERE GRADE * 1000 > ANY (SELECT SUM(BUDGET) FROM PROJ GROUP BY CITY, PTYPE HAVING PROJ.CITY = STAFF.CITY); -- PASS:0564 If EMPNUM = E3 and GRADE * 1000 = 13000? -- restore ROLLBACK WORK; # connection default; exit; ## complete test let $new_dba= HU; --error 0,1396 eval DROP USER $new_dba@localhost; eval CREATE USER $new_dba@localhost identified by 'PWD'; eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (HU,localhost,HU,PWD,test); --disable_warnings DROP SCHEMA IF EXISTS HU; --enable_warnings CREATE SCHEMA HU ; USE HU ; CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL UNIQUE, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15)); DELETE FROM STAFF; DELETE FROM PROJ; INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale'); INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna'); INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa'); INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale'); INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna'); INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale'); COMMIT WORK; SELECT COUNT(*) FROM PROJ; -- PASS:Setup if count = 6? SELECT COUNT(*) FROM STAFF; -- PASS:Setup if count = 5? # -- MODULE DML090 -- TEST:0564 Outer ref. directly contained in HAVING clause! SELECT EMPNUM, GRADE*1000 FROM HU.STAFF WHERE GRADE * 1000 > ANY (SELECT SUM(BUDGET) FROM HU.PROJ GROUP BY CITY, PTYPE HAVING HU.PROJ.CITY = HU.STAFF.CITY); -- PASS:0564 If EMPNUM = E3 and GRADE * 1000 = 13000? -- restore ROLLBACK WORK; connection default;