From 3097788f66f419bc2c4b072d15c02cc63dccee8e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 23 Mar 2000 07:42:13 +0000 Subject: [PATCH] subselect regress test was kind of silly; it claimed to test correlation cases but actually did no such thing. Make it test some more cases than before (including things that didn't work in 6.5). --- src/test/regress/expected/subselect.out | 72 ++++++++++++++++++++------------- src/test/regress/sql/subselect.sql | 32 +++++++++------ 2 files changed, 63 insertions(+), 41 deletions(-) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 00c2cb6718..96070f7706 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -78,40 +78,60 @@ SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL | 3 (6 rows) --- Correlated subselects -SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" +SELECT '' AS three, f1, f2 FROM SUBSELECT_TBL - WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = f1); + WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL + WHERE f3 IS NOT NULL); + three | f1 | f2 +-------+----+---- + | 1 | 2 + | 6 | 7 + | 8 | 9 +(3 rows) + +-- Correlated subselects +SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field" + FROM SUBSELECT_TBL upper + WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1); six | Correlated Field | Second Field -----+------------------+-------------- - | 1 | 3 - | 2 | 4 - | 3 | 5 + | 1 | 2 + | 2 | 3 + | 3 | 4 | 1 | 1 | 2 | 2 | 3 | 3 (6 rows) SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" - FROM SUBSELECT_TBL - WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(f2 AS float) = f3); + FROM SUBSELECT_TBL upper + WHERE f1 IN + (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3); six | Correlated Field | Second Field -----+------------------+-------------- - | 1 | 3 | 2 | 4 | 3 | 5 | 1 | 1 | 2 | 2 | 3 | 3 -(6 rows) +(5 rows) SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" - FROM SUBSELECT_TBL - WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = CAST(f3 AS integer)); -ERROR: dtoi4: unable to convert null + FROM SUBSELECT_TBL upper + WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL + WHERE f2 = CAST(f3 AS integer)); + six | Correlated Field | Second Field +-----+------------------+-------------- + | 1 | 3 + | 2 | 4 + | 3 | 5 + | 6 | 8 +(4 rows) + SELECT '' AS five, f1 AS "Correlated Field" FROM SUBSELECT_TBL - WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); + WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL + WHERE f3 IS NOT NULL); five | Correlated Field ------+------------------ | 2 @@ -121,31 +141,27 @@ SELECT '' AS five, f1 AS "Correlated Field" | 3 (5 rows) -SELECT '' AS three, f1 AS "Correlated Field" - FROM SUBSELECT_TBL - WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); - three | Correlated Field --------+------------------ - | 1 - | 6 - | 8 -(3 rows) - -- -- Use some existing tables in the regression test -- SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" FROM SUBSELECT_TBL ss - WHERE f1 NOT IN (SELECT f1 FROM INT4_TBL WHERE f1 != ss.f1); + WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL WHERE f1 != ss.f1); eight | Correlated Field | Second Field -------+------------------+-------------- - | 1 | 3 | 2 | 4 | 3 | 5 - | 1 | 1 | 2 | 2 | 3 | 3 | 6 | 8 | 8 | -(8 rows) +(6 rows) + +select q1, float8(count(*)) / (select count(*) from int8_tbl) +from int8_tbl group by q1; + q1 | ?column? +------------------+---------- + 123 | 0.4 + 4567890123456789 | 0.6 +(2 rows) diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 898c9d32d6..b3a4ff5284 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -39,27 +39,31 @@ SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 IN (SELECT f1 FROM SUBSELECT_TBL)); +SELECT '' AS three, f1, f2 + FROM SUBSELECT_TBL + WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL + WHERE f3 IS NOT NULL); + -- Correlated subselects -SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" - FROM SUBSELECT_TBL - WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = f1); +SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field" + FROM SUBSELECT_TBL upper + WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1); SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" - FROM SUBSELECT_TBL - WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(f2 AS float) = f3); + FROM SUBSELECT_TBL upper + WHERE f1 IN + (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3); SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" - FROM SUBSELECT_TBL - WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = CAST(f3 AS integer)); + FROM SUBSELECT_TBL upper + WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL + WHERE f2 = CAST(f3 AS integer)); SELECT '' AS five, f1 AS "Correlated Field" FROM SUBSELECT_TBL - WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); - -SELECT '' AS three, f1 AS "Correlated Field" - FROM SUBSELECT_TBL - WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); + WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL + WHERE f3 IS NOT NULL); -- -- Use some existing tables in the regression test @@ -67,5 +71,7 @@ SELECT '' AS three, f1 AS "Correlated Field" SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" FROM SUBSELECT_TBL ss - WHERE f1 NOT IN (SELECT f1 FROM INT4_TBL WHERE f1 != ss.f1); + WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL WHERE f1 != ss.f1); +select q1, float8(count(*)) / (select count(*) from int8_tbl) +from int8_tbl group by q1; -- 2.11.0