| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE YTS798 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, yts798.sql |
| -- 59-byte ID |
| -- TEd Version # |
| |
| -- AUTHORIZATION CTS1 |
| set schema CTS1; |
| 0 rows inserted/updated/deleted |
| ij> --O SELECT USER FROM HU.ECCO; |
| VALUES USER; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| CTS1 |
| ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment |
| ROLLBACK WORK; |
| ij> DELETE FROM TV; |
| 5 rows inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (1,'a'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (2,'b'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (3,'c'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (4,'d'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (5,'e'); |
| 1 row inserted/updated/deleted |
| ij> DELETE FROM TW; |
| 5 rows inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('b',2); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('g',1); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('f',2); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('h',4); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('i',5); |
| 1 row inserted/updated/deleted |
| ij> -- date_time print |
| |
| -- TEST:7559 <scalar subquery> in <select list> of single-row select! |
| |
| SELECT DISTINCT A, |
| (SELECT D FROM TW |
| WHERE E = X.A) |
| --O FROM TV AS X, TW AS Y |
| FROM TV X, TW Y |
| --O WHERE 1 < |
| --O (SELECT COUNT (*) FROM TV, TW |
| WHERE exists |
| (SELECT * FROM TV, TW |
| WHERE A = X.A |
| AND A = E); |
| ERROR 21000: Scalar subquery is only allowed to return a single row. |
| ij> -- PASS:7559 If ERROR - cardinality violation? |
| |
| SELECT DISTINCT A, |
| (SELECT D FROM TW |
| WHERE E = X.A) |
| --O FROM TV AS X, TW AS Y |
| FROM TV X, TW Y |
| WHERE A = 1; |
| A |2 |
| ---------------- |
| 1 |g |
| ij> -- PASS:7559 If A = 1 and D = 'g'? |
| |
| SELECT DISTINCT A, |
| (SELECT D FROM TW |
| WHERE E = X.A) |
| --O FROM TV AS X, TW AS Y |
| FROM TV X, TW Y |
| WHERE A = 3; |
| A |2 |
| ---------------- |
| 3 |NULL |
| ij> -- PASS:7559 If A = 3 and D = NULL? |
| |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 7559 <<< END TEST |
| -- ********************************************* |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |