From 67ac38085cce4622023817c70d1394e32a981cd9 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Thu, 6 Jan 2000 06:40:54 +0000 Subject: [PATCH] Update for new psql formatting. --- src/test/regress/expected/case.out | 338 +++++----- src/test/regress/expected/errors.out | 4 +- src/test/regress/expected/random.out | 36 +- src/test/regress/expected/select.out | 614 ++++++++++-------- src/test/regress/expected/select_distinct.out | 205 +++--- .../regress/expected/select_distinct_on.out | 25 +- src/test/regress/expected/select_having.out | 60 +- src/test/regress/expected/select_implicit.out | 447 +++++++------ src/test/regress/expected/select_into.out | 11 +- src/test/regress/expected/subselect.out | 203 +++--- src/test/regress/expected/union.out | 346 +++++----- 11 files changed, 1243 insertions(+), 1046 deletions(-) diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out index 061bbbebf8..0ad8be9eec 100644 --- a/src/test/regress/expected/case.out +++ b/src/test/regress/expected/case.out @@ -1,97 +1,107 @@ -QUERY: CREATE TABLE CASE_TBL ( +-- +-- CASE +-- Test the case statement +-- +CREATE TABLE CASE_TBL ( i integer, f double precision ); -QUERY: CREATE TABLE CASE2_TBL ( +CREATE TABLE CASE2_TBL ( i integer, j integer ); -QUERY: INSERT INTO CASE_TBL VALUES (1, 10.1); -QUERY: INSERT INTO CASE_TBL VALUES (2, 20.2); -QUERY: INSERT INTO CASE_TBL VALUES (3, -30.3); -QUERY: INSERT INTO CASE_TBL VALUES (4, NULL); -QUERY: INSERT INTO CASE2_TBL VALUES (1, -1); -QUERY: INSERT INTO CASE2_TBL VALUES (2, -2); -QUERY: INSERT INTO CASE2_TBL VALUES (3, -3); -QUERY: INSERT INTO CASE2_TBL VALUES (2, -4); -QUERY: INSERT INTO CASE2_TBL VALUES (1, NULL); -QUERY: INSERT INTO CASE2_TBL VALUES (NULL, -6); -QUERY: SELECT '3' AS "One", +INSERT INTO CASE_TBL VALUES (1, 10.1); +INSERT INTO CASE_TBL VALUES (2, 20.2); +INSERT INTO CASE_TBL VALUES (3, -30.3); +INSERT INTO CASE_TBL VALUES (4, NULL); +INSERT INTO CASE2_TBL VALUES (1, -1); +INSERT INTO CASE2_TBL VALUES (2, -2); +INSERT INTO CASE2_TBL VALUES (3, -3); +INSERT INTO CASE2_TBL VALUES (2, -4); +INSERT INTO CASE2_TBL VALUES (1, NULL); +INSERT INTO CASE2_TBL VALUES (NULL, -6); +-- +-- Simplest examples without tables +-- +SELECT '3' AS "One", CASE WHEN 1 < 2 THEN 3 END AS "Simple WHEN"; -One|Simple WHEN ----+----------- - 3| 3 + One | Simple WHEN +-----+------------- + 3 | 3 (1 row) -QUERY: SELECT '' AS "One", +SELECT '' AS "One", CASE WHEN 1 > 2 THEN 3 END AS "Simple default"; -One |Simple default -------+-------------- -| + One | Simple default +--------+---------------- + | (1 row) -QUERY: SELECT '3' AS "One", +SELECT '3' AS "One", CASE WHEN 1 < 2 THEN 3 ELSE 4 END AS "Simple ELSE"; -One|Simple ELSE ----+----------- - 3| 3 + One | Simple ELSE +-----+------------- + 3 | 3 (1 row) -QUERY: SELECT '4' AS "One", +SELECT '4' AS "One", CASE WHEN 1 > 2 THEN 3 ELSE 4 END AS "ELSE default"; -One|ELSE default ----+------------ - 4| 4 + One | ELSE default +-----+-------------- + 4 | 4 (1 row) -QUERY: SELECT '6' AS "One", +SELECT '6' AS "One", CASE WHEN 1 > 2 THEN 3 WHEN 4 < 5 THEN 6 ELSE 7 END AS "Two WHEN with default"; -One|Two WHEN with default ----+--------------------- - 6| 6 + One | Two WHEN with default +-----+----------------------- + 6 | 6 (1 row) -QUERY: SELECT '' AS "Five", +-- +-- Examples of targets involving tables +-- +SELECT '' AS "Five", CASE WHEN i >= 3 THEN i END AS ">= 3 or Null" FROM CASE_TBL; -Five|>= 3 or Null -----+------------ - | - | - | 3 - | 4 + Five | >= 3 or Null +------+-------------- + | + | + | 3 + | 4 (4 rows) -QUERY: SELECT '' AS "Five", +SELECT '' AS "Five", CASE WHEN i >= 3 THEN (i + i) ELSE i END AS "Simplest Math" FROM CASE_TBL; -Five|Simplest Math -----+------------- - | 1 - | 2 - | 6 - | 8 + Five | Simplest Math +------+--------------- + | 1 + | 2 + | 6 + | 8 (4 rows) -QUERY: SELECT '' AS "Five", i AS "Value", +SELECT '' AS "Five", i AS "Value", CASE WHEN (i < 0) THEN 'small' WHEN (i = 0) THEN 'zero' WHEN (i = 1) THEN 'one' @@ -99,15 +109,15 @@ QUERY: SELECT '' AS "Five", i AS "Value", ELSE 'big' END AS "Category" FROM CASE_TBL; -Five|Value|Category -----+-----+-------- - | 1|one - | 2|two - | 3|big - | 4|big + Five | Value | Category +------+-------+---------- + | 1 | one + | 2 | two + | 3 | big + | 4 | big (4 rows) -QUERY: SELECT '' AS "Five", +SELECT '' AS "Five", CASE WHEN ((i < 0) or (i < 0)) THEN 'small' WHEN ((i = 0) or (i = 0)) THEN 'zero' WHEN ((i = 1) or (i = 1)) THEN 'one' @@ -115,141 +125,155 @@ QUERY: SELECT '' AS "Five", ELSE 'big' END AS "Category" FROM CASE_TBL; -Five|Category -----+-------- - |one - |two - |big - |big + Five | Category +------+---------- + | one + | two + | big + | big (4 rows) -QUERY: SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; -i|f --+- -4| +-- +-- Examples of qualifications involving tables +-- +-- +-- NULLIF() and COALESCE() +-- Shorthand forms for typical CASE constructs +-- defined in the SQL92 standard. +-- +SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; + i | f +---+--- + 4 | (1 row) -QUERY: SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; -i|f --+- +SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; + i | f +---+--- (0 rows) -QUERY: SELECT COALESCE(a.f, b.i, b.j) +SELECT COALESCE(a.f, b.i, b.j) FROM CASE_TBL a, CASE2_TBL b; - case ------ - 10.1 - 20.2 --30.3 - 1 - 10.1 - 20.2 --30.3 - 2 - 10.1 - 20.2 --30.3 - 3 - 10.1 - 20.2 --30.3 - 2 - 10.1 - 20.2 --30.3 - 1 - 10.1 - 20.2 --30.3 - -6 + case +------- + 10.1 + 20.2 + -30.3 + 1 + 10.1 + 20.2 + -30.3 + 2 + 10.1 + 20.2 + -30.3 + 3 + 10.1 + 20.2 + -30.3 + 2 + 10.1 + 20.2 + -30.3 + 1 + 10.1 + 20.2 + -30.3 + -6 (24 rows) -QUERY: SELECT * +SELECT * FROM CASE_TBL a, CASE2_TBL b WHERE COALESCE(a.f, b.i, b.j) = 2; -i|f|i| j --+-+-+-- -4| |2|-2 -4| |2|-4 + i | f | i | j +---+---+---+---- + 4 | | 2 | -2 + 4 | | 2 | -4 (2 rows) -QUERY: SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)", +SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)", NULLIF(b.i, 4) AS "NULLIF(b.i,4)" FROM CASE_TBL a, CASE2_TBL b; -five|NULLIF(a.i,b.i)|NULLIF(b.i,4) -----+---------------+------------- - | | 1 - | 2| 1 - | 3| 1 - | 4| 1 - | 1| 2 - | | 2 - | 3| 2 - | 4| 2 - | 1| 3 - | 2| 3 - | | 3 - | 4| 3 - | 1| 2 - | | 2 - | 3| 2 - | 4| 2 - | | 1 - | 2| 1 - | 3| 1 - | 4| 1 - | 1| - | 2| - | 3| - | 4| + five | NULLIF(a.i,b.i) | NULLIF(b.i,4) +------+-----------------+--------------- + | | 1 + | 2 | 1 + | 3 | 1 + | 4 | 1 + | 1 | 2 + | | 2 + | 3 | 2 + | 4 | 2 + | 1 | 3 + | 2 | 3 + | | 3 + | 4 | 3 + | 1 | 2 + | | 2 + | 3 | 2 + | 4 | 2 + | | 1 + | 2 | 1 + | 3 | 1 + | 4 | 1 + | 1 | + | 2 | + | 3 | + | 4 | (24 rows) -QUERY: SELECT '' AS "Two", * +SELECT '' AS "Two", * FROM CASE_TBL a, CASE2_TBL b WHERE COALESCE(f,b.i) = 2; -Two|i|f|i| j ----+-+-+-+-- - |4| |2|-2 - |4| |2|-4 + Two | i | f | i | j +-----+---+---+---+---- + | 4 | | 2 | -2 + | 4 | | 2 | -4 (2 rows) -QUERY: UPDATE CASE_TBL +-- +-- Examples of updates involving tables +-- +UPDATE CASE_TBL SET i = CASE WHEN i >= 3 THEN (- i) ELSE (2 * i) END; -QUERY: SELECT * FROM CASE_TBL; - i| f ---+----- - 2| 10.1 - 4| 20.2 --3|-30.3 --4| +SELECT * FROM CASE_TBL; + i | f +----+------- + 2 | 10.1 + 4 | 20.2 + -3 | -30.3 + -4 | (4 rows) -QUERY: UPDATE CASE_TBL +UPDATE CASE_TBL SET i = CASE WHEN i >= 2 THEN (2 * i) ELSE (3 * i) END; -QUERY: SELECT * FROM CASE_TBL; - i| f ----+----- - 4| 10.1 - 8| 20.2 - -9|-30.3 --12| +SELECT * FROM CASE_TBL; + i | f +-----+------- + 4 | 10.1 + 8 | 20.2 + -9 | -30.3 + -12 | (4 rows) -QUERY: UPDATE CASE_TBL +UPDATE CASE_TBL SET i = CASE WHEN b.i >= 2 THEN (2 * j) ELSE (3 * j) END FROM CASE2_TBL b WHERE j = -CASE_TBL.i; -QUERY: SELECT * FROM CASE_TBL; - i| f ----+----- - 8| 20.2 - -9|-30.3 --12| - -8| 10.1 +SELECT * FROM CASE_TBL; + i | f +-----+------- + 8 | 20.2 + -9 | -30.3 + -12 | + -8 | 10.1 (4 rows) -QUERY: DROP TABLE CASE_TBL; -QUERY: DROP TABLE CASE2_TBL; +-- +-- Clean up +-- +DROP TABLE CASE_TBL; +DROP TABLE CASE2_TBL; diff --git a/src/test/regress/expected/errors.out b/src/test/regress/expected/errors.out index 56bf226bd9..a2e56bb315 100644 --- a/src/test/regress/expected/errors.out +++ b/src/test/regress/expected/errors.out @@ -1,8 +1,6 @@ -- --- errors.source +-- ERRORS -- --- $Header: /cvsroot/pgsql/src/test/regress/expected/errors.out,v 1.15 2000/01/05 17:31:08 thomas Exp $ - -- bad in postquel, but ok in postsql select 1 -- diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out index a0592ac369..0f4dce0bbe 100644 --- a/src/test/regress/expected/random.out +++ b/src/test/regress/expected/random.out @@ -1,23 +1,35 @@ -QUERY: SELECT count(*) FROM onek; -count ------ - 1000 +-- +-- RANDOM +-- Test the random function +-- +-- count the number of tuples originally +SELECT count(*) FROM onek; + count +------- + 1000 (1 row) -QUERY: SELECT count(*) AS random INTO RANDOM_TBL +-- select roughly 1/10 of the tuples +-- Assume that the "onek" table has 1000 tuples +-- and try to bracket the correct number so we +-- have a regression test which can pass/fail +-- - thomas 1998-08-17 +SELECT count(*) AS random INTO RANDOM_TBL FROM onek WHERE oidrand(onek.oid, 10); -QUERY: INSERT INTO RANDOM_TBL (random) +-- select again, the count should be different +INSERT INTO RANDOM_TBL (random) SELECT count(*) FROM onek WHERE oidrand(onek.oid, 10); -QUERY: SELECT random, count(random) FROM RANDOM_TBL +-- now test the results for randomness in the correct range +SELECT random, count(random) FROM RANDOM_TBL GROUP BY random HAVING count(random) > 1; -random|count -------+----- + random | count +--------+------- (0 rows) -QUERY: SELECT random FROM RANDOM_TBL +SELECT random FROM RANDOM_TBL WHERE random NOT BETWEEN 80 AND 120; -random ------- + random +-------- (0 rows) diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 703a5b22aa..d6953ab5a5 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -1,303 +1,367 @@ -QUERY: SELECT onek.* WHERE onek.unique1 < 10; -unique1|unique2|two|four|ten|twenty|hundred|thousand|twothousand|fivethous|tenthous|odd|even|stringu1|stringu2|string4 --------+-------+---+----+---+------+-------+--------+-----------+---------+--------+---+----+--------+--------+------- - 0| 998| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1|AAAAAA |KMBAAA |OOOOxx - 1| 214| 1| 1| 1| 1| 1| 1| 1| 1| 1| 2| 3|BAAAAA |GIAAAA |OOOOxx - 2| 326| 0| 2| 2| 2| 2| 2| 2| 2| 2| 4| 5|CAAAAA |OMAAAA |OOOOxx - 3| 431| 1| 3| 3| 3| 3| 3| 3| 3| 3| 6| 7|DAAAAA |PQAAAA |VVVVxx - 4| 833| 0| 0| 4| 4| 4| 4| 4| 4| 4| 8| 9|EAAAAA |BGBAAA |HHHHxx - 5| 541| 1| 1| 5| 5| 5| 5| 5| 5| 5| 10| 11|FAAAAA |VUAAAA |HHHHxx - 6| 978| 0| 2| 6| 6| 6| 6| 6| 6| 6| 12| 13|GAAAAA |QLBAAA |OOOOxx - 7| 647| 1| 3| 7| 7| 7| 7| 7| 7| 7| 14| 15|HAAAAA |XYAAAA |VVVVxx - 8| 653| 0| 0| 8| 8| 8| 8| 8| 8| 8| 16| 17|IAAAAA |DZAAAA |HHHHxx - 9| 49| 1| 1| 9| 9| 9| 9| 9| 9| 9| 18| 19|JAAAAA |XBAAAA |HHHHxx +-- +-- SELECT +-- +-- btree index +-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1 +-- +SELECT onek.* WHERE onek.unique1 < 10; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx + 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx + 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx + 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx + 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx + 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx + 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx + 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx + 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx + 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx (10 rows) -QUERY: SELECT onek.unique1, onek.stringu1 - WHERE onek.unique1 < 20 +-- +-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 +-- +SELECT onek.unique1, onek.stringu1 + WHERE onek.unique1 < 20 ORDER BY unique1 using >; -unique1|stringu1 --------+-------- - 19|TAAAAA - 18|SAAAAA - 17|RAAAAA - 16|QAAAAA - 15|PAAAAA - 14|OAAAAA - 13|NAAAAA - 12|MAAAAA - 11|LAAAAA - 10|KAAAAA - 9|JAAAAA - 8|IAAAAA - 7|HAAAAA - 6|GAAAAA - 5|FAAAAA - 4|EAAAAA - 3|DAAAAA - 2|CAAAAA - 1|BAAAAA - 0|AAAAAA + unique1 | stringu1 +---------+---------- + 19 | TAAAAA + 18 | SAAAAA + 17 | RAAAAA + 16 | QAAAAA + 15 | PAAAAA + 14 | OAAAAA + 13 | NAAAAA + 12 | MAAAAA + 11 | LAAAAA + 10 | KAAAAA + 9 | JAAAAA + 8 | IAAAAA + 7 | HAAAAA + 6 | GAAAAA + 5 | FAAAAA + 4 | EAAAAA + 3 | DAAAAA + 2 | CAAAAA + 1 | BAAAAA + 0 | AAAAAA (20 rows) -QUERY: SELECT onek.unique1, onek.stringu1 - WHERE onek.unique1 > 980 +-- +-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 +-- +SELECT onek.unique1, onek.stringu1 + WHERE onek.unique1 > 980 ORDER BY stringu1 using <; -unique1|stringu1 --------+-------- - 988|AMAAAA - 989|BMAAAA - 990|CMAAAA - 991|DMAAAA - 992|EMAAAA - 993|FMAAAA - 994|GMAAAA - 995|HMAAAA - 996|IMAAAA - 997|JMAAAA - 998|KMAAAA - 999|LMAAAA - 981|TLAAAA - 982|ULAAAA - 983|VLAAAA - 984|WLAAAA - 985|XLAAAA - 986|YLAAAA - 987|ZLAAAA + unique1 | stringu1 +---------+---------- + 988 | AMAAAA + 989 | BMAAAA + 990 | CMAAAA + 991 | DMAAAA + 992 | EMAAAA + 993 | FMAAAA + 994 | GMAAAA + 995 | HMAAAA + 996 | IMAAAA + 997 | JMAAAA + 998 | KMAAAA + 999 | LMAAAA + 981 | TLAAAA + 982 | ULAAAA + 983 | VLAAAA + 984 | WLAAAA + 985 | XLAAAA + 986 | YLAAAA + 987 | ZLAAAA (19 rows) -QUERY: SELECT onek.unique1, onek.string4 - WHERE onek.unique1 > 980 + +-- +-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | +-- sort +1d -2 +0nr -1 +-- +SELECT onek.unique1, onek.string4 + WHERE onek.unique1 > 980 ORDER BY string4 using <, unique1 using >; -unique1|string4 --------+------- - 999|AAAAxx - 995|AAAAxx - 983|AAAAxx - 982|AAAAxx - 981|AAAAxx - 998|HHHHxx - 997|HHHHxx - 993|HHHHxx - 990|HHHHxx - 986|HHHHxx - 996|OOOOxx - 991|OOOOxx - 988|OOOOxx - 987|OOOOxx - 985|OOOOxx - 994|VVVVxx - 992|VVVVxx - 989|VVVVxx - 984|VVVVxx + unique1 | string4 +---------+--------- + 999 | AAAAxx + 995 | AAAAxx + 983 | AAAAxx + 982 | AAAAxx + 981 | AAAAxx + 998 | HHHHxx + 997 | HHHHxx + 993 | HHHHxx + 990 | HHHHxx + 986 | HHHHxx + 996 | OOOOxx + 991 | OOOOxx + 988 | OOOOxx + 987 | OOOOxx + 985 | OOOOxx + 994 | VVVVxx + 992 | VVVVxx + 989 | VVVVxx + 984 | VVVVxx (19 rows) -QUERY: SELECT onek.unique1, onek.string4 + +-- +-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | +-- sort +1dr -2 +0n -1 +-- +SELECT onek.unique1, onek.string4 WHERE onek.unique1 > 980 ORDER BY string4 using >, unique1 using <; -unique1|string4 --------+------- - 984|VVVVxx - 989|VVVVxx - 992|VVVVxx - 994|VVVVxx - 985|OOOOxx - 987|OOOOxx - 988|OOOOxx - 991|OOOOxx - 996|OOOOxx - 986|HHHHxx - 990|HHHHxx - 993|HHHHxx - 997|HHHHxx - 998|HHHHxx - 981|AAAAxx - 982|AAAAxx - 983|AAAAxx - 995|AAAAxx - 999|AAAAxx + unique1 | string4 +---------+--------- + 984 | VVVVxx + 989 | VVVVxx + 992 | VVVVxx + 994 | VVVVxx + 985 | OOOOxx + 987 | OOOOxx + 988 | OOOOxx + 991 | OOOOxx + 996 | OOOOxx + 986 | HHHHxx + 990 | HHHHxx + 993 | HHHHxx + 997 | HHHHxx + 998 | HHHHxx + 981 | AAAAxx + 982 | AAAAxx + 983 | AAAAxx + 995 | AAAAxx + 999 | AAAAxx (19 rows) -QUERY: SELECT onek.unique1, onek.string4 + +-- +-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | +-- sort +0nr -1 +1d -2 +-- +SELECT onek.unique1, onek.string4 WHERE onek.unique1 < 20 ORDER BY unique1 using >, string4 using <; -unique1|string4 --------+------- - 19|OOOOxx - 18|VVVVxx - 17|HHHHxx - 16|OOOOxx - 15|VVVVxx - 14|AAAAxx - 13|OOOOxx - 12|AAAAxx - 11|OOOOxx - 10|AAAAxx - 9|HHHHxx - 8|HHHHxx - 7|VVVVxx - 6|OOOOxx - 5|HHHHxx - 4|HHHHxx - 3|VVVVxx - 2|OOOOxx - 1|OOOOxx - 0|OOOOxx + unique1 | string4 +---------+--------- + 19 | OOOOxx + 18 | VVVVxx + 17 | HHHHxx + 16 | OOOOxx + 15 | VVVVxx + 14 | AAAAxx + 13 | OOOOxx + 12 | AAAAxx + 11 | OOOOxx + 10 | AAAAxx + 9 | HHHHxx + 8 | HHHHxx + 7 | VVVVxx + 6 | OOOOxx + 5 | HHHHxx + 4 | HHHHxx + 3 | VVVVxx + 2 | OOOOxx + 1 | OOOOxx + 0 | OOOOxx (20 rows) -QUERY: SELECT onek.unique1, onek.string4 - WHERE onek.unique1 < 20 +-- +-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | +-- sort +0n -1 +1dr -2 +-- +SELECT onek.unique1, onek.string4 + WHERE onek.unique1 < 20 ORDER BY unique1 using <, string4 using >; -unique1|string4 --------+------- - 0|OOOOxx - 1|OOOOxx - 2|OOOOxx - 3|VVVVxx - 4|HHHHxx - 5|HHHHxx - 6|OOOOxx - 7|VVVVxx - 8|HHHHxx - 9|HHHHxx - 10|AAAAxx - 11|OOOOxx - 12|AAAAxx - 13|OOOOxx - 14|AAAAxx - 15|VVVVxx - 16|OOOOxx - 17|HHHHxx - 18|VVVVxx - 19|OOOOxx + unique1 | string4 +---------+--------- + 0 | OOOOxx + 1 | OOOOxx + 2 | OOOOxx + 3 | VVVVxx + 4 | HHHHxx + 5 | HHHHxx + 6 | OOOOxx + 7 | VVVVxx + 8 | HHHHxx + 9 | HHHHxx + 10 | AAAAxx + 11 | OOOOxx + 12 | AAAAxx + 13 | OOOOxx + 14 | AAAAxx + 15 | VVVVxx + 16 | OOOOxx + 17 | HHHHxx + 18 | VVVVxx + 19 | OOOOxx (20 rows) -QUERY: SELECT two, stringu1, ten, string4 +-- +-- partial btree index +-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1 +-- +--SELECT onek2.* WHERE onek2.unique1 < 10; +-- +-- partial btree index +-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 +-- +--SELECT onek2.unique1, onek2.stringu1 +-- WHERE onek2.unique1 < 20 +-- ORDER BY unique1 using >; +-- +-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 +-- +--SELECT onek2.unique1, onek2.stringu1 +-- WHERE onek2.unique1 > 980 +-- ORDER BY stringu1 using <; + +SELECT two, stringu1, ten, string4 INTO TABLE tmp FROM onek; -QUERY: SELECT p.name, p.age FROM person* p; -name |age --------+--- -mike | 40 -joe | 20 -sally | 34 -sandra | 19 -alex | 30 -sue | 50 -denise | 24 -sarah | 88 -teresa | 38 -nan | 28 -leah | 68 -wendy | 78 -melissa| 28 -joan | 18 -mary | 8 -jane | 58 -liza | 38 -jean | 28 -jenifer| 38 -juanita| 58 -susan | 78 -zena | 98 -martie | 88 -chris | 78 -pat | 18 -zola | 58 -louise | 98 -edna | 18 -bertha | 88 -sumi | 38 -koko | 88 -gina | 18 -rean | 48 -sharon | 78 -paula | 68 -julie | 68 -belinda| 38 -karen | 48 -carina | 58 -diane | 18 -esther | 98 -trudy | 88 -fanny | 8 -carmen | 78 -lita | 25 -pamela | 48 -sandy | 38 -trisha | 88 -vera | 78 -velma | 68 -sharon | 25 -sam | 30 -bill | 20 -fred | 28 -larry | 60 -jeff | 23 -cim | 30 -linda | 19 +-- +-- awk '{print $1,$2;}' person.data | +-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | +-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | +-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data +-- +-- SELECT name, age FROM person*; ??? check if different +SELECT p.name, p.age FROM person* p; + name | age +---------+----- + mike | 40 + joe | 20 + sally | 34 + sandra | 19 + alex | 30 + sue | 50 + denise | 24 + sarah | 88 + teresa | 38 + nan | 28 + leah | 68 + wendy | 78 + melissa | 28 + joan | 18 + mary | 8 + jane | 58 + liza | 38 + jean | 28 + jenifer | 38 + juanita | 58 + susan | 78 + zena | 98 + martie | 88 + chris | 78 + pat | 18 + zola | 58 + louise | 98 + edna | 18 + bertha | 88 + sumi | 38 + koko | 88 + gina | 18 + rean | 48 + sharon | 78 + paula | 68 + julie | 68 + belinda | 38 + karen | 48 + carina | 58 + diane | 18 + esther | 98 + trudy | 88 + fanny | 8 + carmen | 78 + lita | 25 + pamela | 48 + sandy | 38 + trisha | 88 + vera | 78 + velma | 68 + sharon | 25 + sam | 30 + bill | 20 + fred | 28 + larry | 60 + jeff | 23 + cim | 30 + linda | 19 (58 rows) -QUERY: SELECT p.name, p.age FROM person* p ORDER BY age using >, name; -name |age --------+--- -esther | 98 -louise | 98 -zena | 98 -bertha | 88 -koko | 88 -martie | 88 -sarah | 88 -trisha | 88 -trudy | 88 -carmen | 78 -chris | 78 -sharon | 78 -susan | 78 -vera | 78 -wendy | 78 -julie | 68 -leah | 68 -paula | 68 -velma | 68 -larry | 60 -carina | 58 -jane | 58 -juanita| 58 -zola | 58 -sue | 50 -karen | 48 -pamela | 48 -rean | 48 -mike | 40 -belinda| 38 -jenifer| 38 -liza | 38 -sandy | 38 -sumi | 38 -teresa | 38 -sally | 34 -alex | 30 -cim | 30 -sam | 30 -fred | 28 -jean | 28 -melissa| 28 -nan | 28 -lita | 25 -sharon | 25 -denise | 24 -jeff | 23 -bill | 20 -joe | 20 -linda | 19 -sandra | 19 -diane | 18 -edna | 18 -gina | 18 -joan | 18 -pat | 18 -fanny | 8 -mary | 8 +-- +-- awk '{print $1,$2;}' person.data | +-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | +-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | +-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data | +-- sort +1nr -2 +-- +SELECT p.name, p.age FROM person* p ORDER BY age using >, name; + name | age +---------+----- + esther | 98 + louise | 98 + zena | 98 + bertha | 88 + koko | 88 + martie | 88 + sarah | 88 + trisha | 88 + trudy | 88 + carmen | 78 + chris | 78 + sharon | 78 + susan | 78 + vera | 78 + wendy | 78 + julie | 68 + leah | 68 + paula | 68 + velma | 68 + larry | 60 + carina | 58 + jane | 58 + juanita | 58 + zola | 58 + sue | 50 + karen | 48 + pamela | 48 + rean | 48 + mike | 40 + belinda | 38 + jenifer | 38 + liza | 38 + sandy | 38 + sumi | 38 + teresa | 38 + sally | 34 + alex | 30 + cim | 30 + sam | 30 + fred | 28 + jean | 28 + melissa | 28 + nan | 28 + lita | 25 + sharon | 25 + denise | 24 + jeff | 23 + bill | 20 + joe | 20 + linda | 19 + sandra | 19 + diane | 18 + edna | 18 + gina | 18 + joan | 18 + pat | 18 + fanny | 8 + mary | 8 (58 rows) diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out index f148fa9849..4dcbd3a30e 100644 --- a/src/test/regress/expected/select_distinct.out +++ b/src/test/regress/expected/select_distinct.out @@ -1,103 +1,126 @@ -QUERY: SELECT DISTINCT two FROM tmp; -two ---- - 0 - 1 +-- +-- SELECT_DISTINCT +-- +-- +-- awk '{print $3;}' onek.data | sort -n | uniq +-- +SELECT DISTINCT two FROM tmp; + two +----- + 0 + 1 (2 rows) -QUERY: SELECT DISTINCT ten FROM tmp; -ten ---- - 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 +-- +-- awk '{print $5;}' onek.data | sort -n | uniq +-- +SELECT DISTINCT ten FROM tmp; + ten +----- + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 (10 rows) -QUERY: SELECT DISTINCT string4 FROM tmp; -string4 -------- -AAAAxx -HHHHxx -OOOOxx -VVVVxx +-- +-- awk '{print $16;}' onek.data | sort -d | uniq +-- +SELECT DISTINCT string4 FROM tmp; + string4 +--------- + AAAAxx + HHHHxx + OOOOxx + VVVVxx (4 rows) -QUERY: SELECT DISTINCT two, string4, ten +-- +-- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | +-- sort +0n -1 +1d -2 +2n -3 +-- +SELECT DISTINCT two, string4, ten FROM tmp ORDER BY two using <, string4 using <, ten using <; -two|string4|ten ----+-------+--- - 0|AAAAxx | 0 - 0|AAAAxx | 2 - 0|AAAAxx | 4 - 0|AAAAxx | 6 - 0|AAAAxx | 8 - 0|HHHHxx | 0 - 0|HHHHxx | 2 - 0|HHHHxx | 4 - 0|HHHHxx | 6 - 0|HHHHxx | 8 - 0|OOOOxx | 0 - 0|OOOOxx | 2 - 0|OOOOxx | 4 - 0|OOOOxx | 6 - 0|OOOOxx | 8 - 0|VVVVxx | 0 - 0|VVVVxx | 2 - 0|VVVVxx | 4 - 0|VVVVxx | 6 - 0|VVVVxx | 8 - 1|AAAAxx | 1 - 1|AAAAxx | 3 - 1|AAAAxx | 5 - 1|AAAAxx | 7 - 1|AAAAxx | 9 - 1|HHHHxx | 1 - 1|HHHHxx | 3 - 1|HHHHxx | 5 - 1|HHHHxx | 7 - 1|HHHHxx | 9 - 1|OOOOxx | 1 - 1|OOOOxx | 3 - 1|OOOOxx | 5 - 1|OOOOxx | 7 - 1|OOOOxx | 9 - 1|VVVVxx | 1 - 1|VVVVxx | 3 - 1|VVVVxx | 5 - 1|VVVVxx | 7 - 1|VVVVxx | 9 + two | string4 | ten +-----+---------+----- + 0 | AAAAxx | 0 + 0 | AAAAxx | 2 + 0 | AAAAxx | 4 + 0 | AAAAxx | 6 + 0 | AAAAxx | 8 + 0 | HHHHxx | 0 + 0 | HHHHxx | 2 + 0 | HHHHxx | 4 + 0 | HHHHxx | 6 + 0 | HHHHxx | 8 + 0 | OOOOxx | 0 + 0 | OOOOxx | 2 + 0 | OOOOxx | 4 + 0 | OOOOxx | 6 + 0 | OOOOxx | 8 + 0 | VVVVxx | 0 + 0 | VVVVxx | 2 + 0 | VVVVxx | 4 + 0 | VVVVxx | 6 + 0 | VVVVxx | 8 + 1 | AAAAxx | 1 + 1 | AAAAxx | 3 + 1 | AAAAxx | 5 + 1 | AAAAxx | 7 + 1 | AAAAxx | 9 + 1 | HHHHxx | 1 + 1 | HHHHxx | 3 + 1 | HHHHxx | 5 + 1 | HHHHxx | 7 + 1 | HHHHxx | 9 + 1 | OOOOxx | 1 + 1 | OOOOxx | 3 + 1 | OOOOxx | 5 + 1 | OOOOxx | 7 + 1 | OOOOxx | 9 + 1 | VVVVxx | 1 + 1 | VVVVxx | 3 + 1 | VVVVxx | 5 + 1 | VVVVxx | 7 + 1 | VVVVxx | 9 (40 rows) -QUERY: SELECT DISTINCT p.age FROM person* p ORDER BY age using >; -age ---- - 98 - 88 - 78 - 68 - 60 - 58 - 50 - 48 - 40 - 38 - 34 - 30 - 28 - 25 - 24 - 23 - 20 - 19 - 18 - 8 +-- +-- awk '{print $2;}' person.data | +-- awk '{if(NF!=1){print $2;}else{print;}}' - emp.data | +-- awk '{if(NF!=1){print $2;}else{print;}}' - student.data | +-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $5;}else{print;}}' - stud_emp.data | +-- sort -n -r | uniq +-- +SELECT DISTINCT p.age FROM person* p ORDER BY age using >; + age +----- + 98 + 88 + 78 + 68 + 60 + 58 + 50 + 48 + 40 + 38 + 34 + 30 + 28 + 25 + 24 + 23 + 20 + 19 + 18 + 8 (20 rows) diff --git a/src/test/regress/expected/select_distinct_on.out b/src/test/regress/expected/select_distinct_on.out index 70dcab7fe1..067f74db4f 100644 --- a/src/test/regress/expected/select_distinct_on.out +++ b/src/test/regress/expected/select_distinct_on.out @@ -1,15 +1,18 @@ -QUERY: SELECT DISTINCT ON string4 two, string4, ten +-- +-- SELECT_DISTINCT_ON +-- +SELECT DISTINCT ON string4 two, string4, ten FROM tmp ORDER BY two using <, string4 using <, ten using <; -two|string4|ten ----+-------+--- - 0|AAAAxx | 0 - 0|HHHHxx | 0 - 0|OOOOxx | 0 - 0|VVVVxx | 0 - 1|AAAAxx | 1 - 1|HHHHxx | 1 - 1|OOOOxx | 1 - 1|VVVVxx | 1 + two | string4 | ten +-----+---------+----- + 0 | AAAAxx | 0 + 0 | HHHHxx | 0 + 0 | OOOOxx | 0 + 0 | VVVVxx | 0 + 1 | AAAAxx | 1 + 1 | HHHHxx | 1 + 1 | OOOOxx | 1 + 1 | VVVVxx | 1 (8 rows) diff --git a/src/test/regress/expected/select_having.out b/src/test/regress/expected/select_having.out index 37a5a442f7..3f069996fc 100644 --- a/src/test/regress/expected/select_having.out +++ b/src/test/regress/expected/select_having.out @@ -1,37 +1,41 @@ -QUERY: CREATE TABLE test_having (a int, b int, c char(8), d char); -QUERY: INSERT INTO test_having VALUES (0, 1, 'XXXX', 'A'); -QUERY: INSERT INTO test_having VALUES (1, 2, 'AAAA', 'b'); -QUERY: INSERT INTO test_having VALUES (2, 2, 'AAAA', 'c'); -QUERY: INSERT INTO test_having VALUES (3, 3, 'BBBB', 'D'); -QUERY: INSERT INTO test_having VALUES (4, 3, 'BBBB', 'e'); -QUERY: INSERT INTO test_having VALUES (5, 3, 'bbbb', 'F'); -QUERY: INSERT INTO test_having VALUES (6, 4, 'cccc', 'g'); -QUERY: INSERT INTO test_having VALUES (7, 4, 'cccc', 'h'); -QUERY: INSERT INTO test_having VALUES (8, 4, 'CCCC', 'I'); -QUERY: INSERT INTO test_having VALUES (9, 4, 'CCCC', 'j'); -QUERY: SELECT b, c FROM test_having +-- +-- SELECT_HAVING +-- +-- load test data +CREATE TABLE test_having (a int, b int, c char(8), d char); +INSERT INTO test_having VALUES (0, 1, 'XXXX', 'A'); +INSERT INTO test_having VALUES (1, 2, 'AAAA', 'b'); +INSERT INTO test_having VALUES (2, 2, 'AAAA', 'c'); +INSERT INTO test_having VALUES (3, 3, 'BBBB', 'D'); +INSERT INTO test_having VALUES (4, 3, 'BBBB', 'e'); +INSERT INTO test_having VALUES (5, 3, 'bbbb', 'F'); +INSERT INTO test_having VALUES (6, 4, 'cccc', 'g'); +INSERT INTO test_having VALUES (7, 4, 'cccc', 'h'); +INSERT INTO test_having VALUES (8, 4, 'CCCC', 'I'); +INSERT INTO test_having VALUES (9, 4, 'CCCC', 'j'); +SELECT b, c FROM test_having GROUP BY b, c HAVING count(*) = 1; -b|c --+-------- -1|XXXX -3|bbbb + b | c +---+---------- + 1 | XXXX + 3 | bbbb (2 rows) -QUERY: SELECT lower(c), count(c) FROM test_having +SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a); -lower |count ---------+----- -bbbb | 3 -cccc | 4 -xxxx | 1 + lower | count +----------+------- + bbbb | 3 + cccc | 4 + xxxx | 1 (3 rows) -QUERY: SELECT c, max(a) FROM test_having +SELECT c, max(a) FROM test_having GROUP BY c HAVING count(*) > 2 OR min(a) = max(a); -c |max ---------+--- -XXXX | 0 -bbbb | 5 + c | max +----------+----- + XXXX | 0 + bbbb | 5 (2 rows) -QUERY: DROP TABLE test_having; +DROP TABLE test_having; diff --git a/src/test/regress/expected/select_implicit.out b/src/test/regress/expected/select_implicit.out index 427198fb4a..adf0f79477 100644 --- a/src/test/regress/expected/select_implicit.out +++ b/src/test/regress/expected/select_implicit.out @@ -1,271 +1,320 @@ -QUERY: CREATE TABLE test_missing_target (a int, b int, c char(8), d char); -QUERY: INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A'); -QUERY: INSERT INTO test_missing_target VALUES (1, 2, 'AAAA', 'b'); -QUERY: INSERT INTO test_missing_target VALUES (2, 2, 'AAAA', 'c'); -QUERY: INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D'); -QUERY: INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e'); -QUERY: INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F'); -QUERY: INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g'); -QUERY: INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h'); -QUERY: INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I'); -QUERY: INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j'); -QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c; -c |count ---------+----- -AAAA | 2 -BBBB | 2 -CCCC | 2 -XXXX | 1 -bbbb | 1 -cccc | 2 +-- +-- SELECT_IMPLICIT +-- Test cases for queries with ordering terms missing from the target list. +-- This used to be called "junkfilter.sql". +-- The parser uses the term "resjunk" to handle these cases. +-- - thomas 1998-07-09 +-- +-- load test data +CREATE TABLE test_missing_target (a int, b int, c char(8), d char); +INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A'); +INSERT INTO test_missing_target VALUES (1, 2, 'AAAA', 'b'); +INSERT INTO test_missing_target VALUES (2, 2, 'AAAA', 'c'); +INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D'); +INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e'); +INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F'); +INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g'); +INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h'); +INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I'); +INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j'); +-- w/ existing GROUP BY target +SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c; + c | count +----------+------- + AAAA | 2 + BBBB | 2 + CCCC | 2 + XXXX | 1 + bbbb | 1 + cccc | 2 (6 rows) -QUERY: SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c; -count ------ - 2 - 2 - 2 - 1 - 1 - 2 +-- w/o existing GROUP BY target using a relation name in GROUP BY clause +SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c; + count +------- + 2 + 2 + 2 + 1 + 1 + 2 (6 rows) -QUERY: SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b; +-- w/o existing GROUP BY target and w/o existing a different ORDER BY target +-- failure expected +SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b; ERROR: Attribute test_missing_target.b must be GROUPed or used in an aggregate function -QUERY: SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b; -count ------ - 1 - 2 - 3 - 4 +-- w/o existing GROUP BY target and w/o existing same ORDER BY target +SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b; + count +------- + 1 + 2 + 3 + 4 (4 rows) -QUERY: SELECT test_missing_target.b, count(*) +-- w/ existing GROUP BY target using a relation name in target +SELECT test_missing_target.b, count(*) FROM test_missing_target GROUP BY b ORDER BY b; -b|count --+----- -1| 1 -2| 2 -3| 3 -4| 4 + b | count +---+------- + 1 | 1 + 2 | 2 + 3 | 3 + 4 | 4 (4 rows) -QUERY: SELECT c FROM test_missing_target ORDER BY a; -c --------- -XXXX -AAAA -AAAA -BBBB -BBBB -bbbb -cccc -cccc -CCCC -CCCC +-- w/o existing GROUP BY target +SELECT c FROM test_missing_target ORDER BY a; + c +---------- + XXXX + AAAA + AAAA + BBBB + BBBB + bbbb + cccc + cccc + CCCC + CCCC (10 rows) -QUERY: SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc; -count ------ - 4 - 3 - 2 - 1 +-- w/o existing ORDER BY target +SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc; + count +------- + 4 + 3 + 2 + 1 (4 rows) -QUERY: SELECT count(*) FROM test_missing_target ORDER BY 1 desc; -count ------ - 10 +-- group using reference number +SELECT count(*) FROM test_missing_target ORDER BY 1 desc; + count +------- + 10 (1 row) -QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 1; -c |count ---------+----- -AAAA | 2 -BBBB | 2 -CCCC | 2 -XXXX | 1 -bbbb | 1 -cccc | 2 +-- order using reference number +SELECT c, count(*) FROM test_missing_target GROUP BY 1; + c | count +----------+------- + AAAA | 2 + BBBB | 2 + CCCC | 2 + XXXX | 1 + bbbb | 1 + cccc | 2 (6 rows) -QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 3; +-- group using reference number out of range +-- failure expected +SELECT c, count(*) FROM test_missing_target GROUP BY 3; ERROR: GROUP BY position 3 is not in target list -QUERY: SELECT count(*) FROM test_missing_target x, test_missing_target y +-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition +-- failure expected +SELECT count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY b ORDER BY b; ERROR: Column 'b' is ambiguous -QUERY: SELECT a, a FROM test_missing_target +-- order w/ target under ambiguous condition +-- failure NOT expected +SELECT a, a FROM test_missing_target ORDER BY a; -a|a --+- -0|0 -1|1 -2|2 -3|3 -4|4 -5|5 -6|6 -7|7 -8|8 -9|9 + a | a +---+--- + 0 | 0 + 1 | 1 + 2 | 2 + 3 | 3 + 4 | 4 + 5 | 5 + 6 | 6 + 7 | 7 + 8 | 8 + 9 | 9 (10 rows) -QUERY: SELECT a/2, a/2 FROM test_missing_target +-- order expression w/ target under ambiguous condition +-- failure NOT expected +SELECT a/2, a/2 FROM test_missing_target ORDER BY a/2; -?column?|?column? ---------+-------- - 0| 0 - 0| 0 - 1| 1 - 1| 1 - 2| 2 - 2| 2 - 3| 3 - 3| 3 - 4| 4 - 4| 4 + ?column? | ?column? +----------+---------- + 0 | 0 + 0 | 0 + 1 | 1 + 1 | 1 + 2 | 2 + 2 | 2 + 3 | 3 + 3 | 3 + 4 | 4 + 4 | 4 (10 rows) -QUERY: SELECT a/2, a/2 FROM test_missing_target +-- group expression w/ target under ambiguous condition +-- failure NOT expected +SELECT a/2, a/2 FROM test_missing_target GROUP BY a/2; -?column?|?column? ---------+-------- - 0| 0 - 1| 1 - 2| 2 - 3| 3 - 4| 4 + ?column? | ?column? +----------+---------- + 0 | 0 + 1 | 1 + 2 | 2 + 3 | 3 + 4 | 4 (5 rows) -QUERY: SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y +-- group w/ existing GROUP BY target under ambiguous condition +SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b; -b|count --+----- -1| 1 -2| 2 -3| 3 -4| 4 + b | count +---+------- + 1 | 1 + 2 | 2 + 3 | 3 + 4 | 4 (4 rows) -QUERY: SELECT count(*) FROM test_missing_target x, test_missing_target y +-- group w/o existing GROUP BY target under ambiguous condition +SELECT count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b; -count ------ - 1 - 2 - 3 - 4 + count +------- + 1 + 2 + 3 + 4 (4 rows) -QUERY: SELECT count(*) INTO TABLE test_missing_target2 -FROM test_missing_target x, test_missing_target y +-- group w/o existing GROUP BY target under ambiguous condition +-- into a table +SELECT count(*) INTO TABLE test_missing_target2 +FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b; -QUERY: SELECT * FROM test_missing_target2; -count ------ - 1 - 2 - 3 - 4 +SELECT * FROM test_missing_target2; + count +------- + 1 + 2 + 3 + 4 (4 rows) -QUERY: SELECT a%2, count(b) FROM test_missing_target GROUP BY test_missing_target.a%2; -?column?|count ---------+----- - 0| 5 - 1| 5 +-- Functions and expressions +-- w/ existing GROUP BY target +SELECT a%2, count(b) FROM test_missing_target GROUP BY test_missing_target.a%2; + ?column? | count +----------+------- + 0 | 5 + 1 | 5 (2 rows) -QUERY: SELECT count(c) FROM test_missing_target GROUP BY lower(test_missing_target.c); -count ------ - 2 - 3 - 4 - 1 +-- w/o existing GROUP BY target using a relation name in GROUP BY clause +SELECT count(c) FROM test_missing_target GROUP BY lower(test_missing_target.c); + count +------- + 2 + 3 + 4 + 1 (4 rows) -QUERY: SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; +-- w/o existing GROUP BY target and w/o existing a different ORDER BY target +-- failure expected +SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; ERROR: Attribute test_missing_target.b must be GROUPed or used in an aggregate function -QUERY: SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; -count ------ - 1 - 5 - 4 +-- w/o existing GROUP BY target and w/o existing same ORDER BY target +SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; + count +------- + 1 + 5 + 4 (3 rows) -QUERY: SELECT lower(test_missing_target.c), count(c) +-- w/ existing GROUP BY target using a relation name in target +SELECT lower(test_missing_target.c), count(c) FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); -lower |count ---------+----- -aaaa | 2 -bbbb | 3 -cccc | 4 -xxxx | 1 + lower | count +----------+------- + aaaa | 2 + bbbb | 3 + cccc | 4 + xxxx | 1 (4 rows) -QUERY: SELECT a FROM test_missing_target ORDER BY upper(d); -a -- -0 -1 -2 -3 -4 -5 -6 -7 -8 -9 +-- w/o existing GROUP BY target +SELECT a FROM test_missing_target ORDER BY upper(d); + a +--- + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 (10 rows) -QUERY: SELECT count(b) FROM test_missing_target +-- w/o existing ORDER BY target +SELECT count(b) FROM test_missing_target GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc; -count ------ - 7 - 3 + count +------- + 7 + 3 (2 rows) -QUERY: SELECT count(x.a) FROM test_missing_target x, test_missing_target y +-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition +-- failure expected +SELECT count(x.a) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY b/2 ORDER BY b/2; ERROR: Column 'b' is ambiguous -QUERY: SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y +-- group w/ existing GROUP BY target under ambiguous condition +SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b/2; -?column?|count ---------+----- - 0| 1 - 1| 5 - 2| 4 + ?column? | count +----------+------- + 0 | 1 + 1 | 5 + 2 | 4 (3 rows) -QUERY: SELECT count(b) FROM test_missing_target x, test_missing_target y +-- group w/o existing GROUP BY target under ambiguous condition +-- failure expected due to ambiguous b in count(b) +SELECT count(b) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b/2; ERROR: Column 'b' is ambiguous -QUERY: SELECT count(x.b) INTO TABLE test_missing_target3 -FROM test_missing_target x, test_missing_target y +-- group w/o existing GROUP BY target under ambiguous condition +-- into a table +SELECT count(x.b) INTO TABLE test_missing_target3 +FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b/2; -QUERY: SELECT * FROM test_missing_target3; -count ------ - 1 - 5 - 4 +SELECT * FROM test_missing_target3; + count +------- + 1 + 5 + 4 (3 rows) -QUERY: DROP TABLE test_missing_target; -QUERY: DROP TABLE test_missing_target2; -QUERY: DROP TABLE test_missing_target3; +-- Cleanup +DROP TABLE test_missing_target; +DROP TABLE test_missing_target2; +DROP TABLE test_missing_target3; diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index bdc0f327af..aa157c9eed 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -1,10 +1,13 @@ -QUERY: SELECT * +-- +-- SELECT_INTO +-- +SELECT * INTO TABLE tmp1 FROM tmp WHERE onek.unique1 < 2; -QUERY: DROP TABLE tmp1; -QUERY: SELECT * +DROP TABLE tmp1; +SELECT * INTO TABLE tmp1 FROM tmp WHERE onek2.unique1 < 2; -QUERY: DROP TABLE tmp1; +DROP TABLE tmp1; diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 03d7a54aba..00c2cb6718 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1,142 +1,151 @@ -QUERY: SELECT 1 AS one WHERE 1 IN (SELECT 1); -one ---- - 1 +-- +-- SUBSELECT +-- +SELECT 1 AS one WHERE 1 IN (SELECT 1); + one +----- + 1 (1 row) -QUERY: SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1); -zero ----- +SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1); + zero +------ (0 rows) -QUERY: SELECT 1 AS zero WHERE 1 IN (SELECT 2); -zero ----- +SELECT 1 AS zero WHERE 1 IN (SELECT 2); + zero +------ (0 rows) -QUERY: CREATE TABLE SUBSELECT_TBL ( +-- Set up some simple test tables +CREATE TABLE SUBSELECT_TBL ( f1 integer, f2 integer, f3 float ); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8); -QUERY: INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL); -QUERY: SELECT '' AS eight, * FROM SUBSELECT_TBL; -eight|f1|f2|f3 ------+--+--+-- - | 1| 2| 3 - | 2| 3| 4 - | 3| 4| 5 - | 1| 1| 1 - | 2| 2| 2 - | 3| 3| 3 - | 6| 7| 8 - | 8| 9| +INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3); +INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4); +INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5); +INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1); +INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2); +INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3); +INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8); +INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL); +SELECT '' AS eight, * FROM SUBSELECT_TBL; + eight | f1 | f2 | f3 +-------+----+----+---- + | 1 | 2 | 3 + | 2 | 3 | 4 + | 3 | 4 | 5 + | 1 | 1 | 1 + | 2 | 2 | 2 + | 3 | 3 | 3 + | 6 | 7 | 8 + | 8 | 9 | (8 rows) -QUERY: SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL +-- Uncorrelated subselects +SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL WHERE f1 IN (SELECT 1); -two|Constant Select ----+--------------- - | 1 - | 1 + two | Constant Select +-----+----------------- + | 1 + | 1 (2 rows) -QUERY: SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL +SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL); -six|Uncorrelated Field ----+------------------ - | 1 - | 2 - | 3 - | 1 - | 2 - | 3 + six | Uncorrelated Field +-----+-------------------- + | 1 + | 2 + | 3 + | 1 + | 2 + | 3 (6 rows) -QUERY: SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL +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)); -six|Uncorrelated Field ----+------------------ - | 1 - | 2 - | 3 - | 1 - | 2 - | 3 + six | Uncorrelated Field +-----+-------------------- + | 1 + | 2 + | 3 + | 1 + | 2 + | 3 (6 rows) -QUERY: SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" +-- 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); -six|Correlated Field|Second Field ----+----------------+------------ - | 1| 3 - | 2| 4 - | 3| 5 - | 1| 1 - | 2| 2 - | 3| 3 + six | Correlated Field | Second Field +-----+------------------+-------------- + | 1 | 3 + | 2 | 4 + | 3 | 5 + | 1 | 1 + | 2 | 2 + | 3 | 3 (6 rows) -QUERY: SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" +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); -six|Correlated Field|Second Field ----+----------------+------------ - | 1| 3 - | 2| 4 - | 3| 5 - | 1| 1 - | 2| 2 - | 3| 3 + six | Correlated Field | Second Field +-----+------------------+-------------- + | 1 | 3 + | 2 | 4 + | 3 | 5 + | 1 | 1 + | 2 | 2 + | 3 | 3 (6 rows) -QUERY: SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" +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 -QUERY: SELECT '' AS five, f1 AS "Correlated Field" +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); -five|Correlated Field -----+---------------- - | 2 - | 3 - | 1 - | 2 - | 3 + five | Correlated Field +------+------------------ + | 2 + | 3 + | 1 + | 2 + | 3 (5 rows) -QUERY: SELECT '' AS three, f1 AS "Correlated Field" +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 + three | Correlated Field +-------+------------------ + | 1 + | 6 + | 8 (3 rows) -QUERY: SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" +-- +-- 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); -eight|Correlated Field|Second Field ------+----------------+------------ - | 1| 3 - | 2| 4 - | 3| 5 - | 1| 1 - | 2| 2 - | 3| 3 - | 6| 8 - | 8| + eight | Correlated Field | Second Field +-------+------------------+-------------- + | 1 | 3 + | 2 | 4 + | 3 | 5 + | 1 | 1 + | 2 | 2 + | 3 | 3 + | 6 | 8 + | 8 | (8 rows) diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 8b0f6d7161..bc342f5c43 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -1,241 +1,249 @@ -QUERY: SELECT 1 AS two UNION SELECT 2; -two ---- - 1 - 2 +-- +-- UNION +-- +-- Simple UNION constructs +SELECT 1 AS two UNION SELECT 2; + two +----- + 1 + 2 (2 rows) -QUERY: SELECT 1 AS one UNION SELECT 1; -one ---- - 1 +SELECT 1 AS one UNION SELECT 1; + one +----- + 1 (1 row) -QUERY: SELECT 1 AS two UNION ALL SELECT 2; -two ---- - 1 - 2 +SELECT 1 AS two UNION ALL SELECT 2; + two +----- + 1 + 2 (2 rows) -QUERY: SELECT 1 AS two UNION ALL SELECT 1; -two ---- - 1 - 1 +SELECT 1 AS two UNION ALL SELECT 1; + two +----- + 1 + 1 (2 rows) -QUERY: SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; -three ------ - 1 - 2 - 3 +SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; + three +------- + 1 + 2 + 3 (3 rows) -QUERY: SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; -two ---- - 1 - 2 +SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; + two +----- + 1 + 2 (2 rows) -QUERY: SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; -three ------ - 1 - 2 - 2 +SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; + three +------- + 1 + 2 + 2 (3 rows) -QUERY: SELECT 1.1 AS two UNION SELECT 2.2; -two ---- -1.1 -2.2 +SELECT 1.1 AS two UNION SELECT 2.2; + two +----- + 1.1 + 2.2 (2 rows) -QUERY: SELECT 1.1 AS two UNION SELECT 2; -two ---- -1.1 - 2 +-- Mixed types +SELECT 1.1 AS two UNION SELECT 2; + two +----- + 1.1 + 2 (2 rows) -QUERY: SELECT 1 AS two UNION SELECT 2.2; -two ---- - 1 - 2 +SELECT 1 AS two UNION SELECT 2.2; + two +----- + 1 + 2 (2 rows) -QUERY: SELECT 1 AS one UNION SELECT 1.1; -one ---- - 1 +SELECT 1 AS one UNION SELECT 1.1; + one +----- + 1 (1 row) -QUERY: SELECT 1.1 AS two UNION ALL SELECT 2; -two ---- -1.1 - 2 +SELECT 1.1 AS two UNION ALL SELECT 2; + two +----- + 1.1 + 2 (2 rows) -QUERY: SELECT 1 AS two UNION ALL SELECT 1; -two ---- - 1 - 1 +SELECT 1 AS two UNION ALL SELECT 1; + two +----- + 1 + 1 (2 rows) -QUERY: SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; -three ------ - 1 - 2 - 3 +SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; + three +------- + 1 + 2 + 3 (3 rows) -QUERY: SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; -two ---- - 1 - 2 +SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; + two +----- + 1 + 2 (2 rows) -QUERY: SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; -three ------ - 1 - 2 - 2 +SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; + three +------- + 1 + 2 + 2 (3 rows) -QUERY: SELECT f1 AS five FROM FLOAT8_TBL +-- +-- Try testing from tables... +-- +SELECT f1 AS five FROM FLOAT8_TBL UNION SELECT f1 FROM FLOAT8_TBL; -five ---------------------- --1.2345678901234e+200 --1004.3 --34.84 --1.2345678901234e-200 -0 + five +----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 (5 rows) -QUERY: SELECT f1 AS ten FROM FLOAT8_TBL +SELECT f1 AS ten FROM FLOAT8_TBL UNION ALL SELECT f1 FROM FLOAT8_TBL; -ten ---------------------- -0 --34.84 --1004.3 --1.2345678901234e+200 --1.2345678901234e-200 -0 --34.84 --1004.3 --1.2345678901234e+200 --1.2345678901234e-200 + ten +----------------------- + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 (10 rows) -QUERY: SELECT f1 AS nine FROM FLOAT8_TBL +SELECT f1 AS nine FROM FLOAT8_TBL UNION SELECT f1 FROM INT4_TBL; -nine ---------------------- --1.2345678901234e+200 --2147483647 --123456 --1004.3 --34.84 --1.2345678901234e-200 -0 -123456 -2147483647 + nine +----------------------- + -1.2345678901234e+200 + -2147483647 + -123456 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + 123456 + 2147483647 (9 rows) -QUERY: SELECT f1 AS ten FROM FLOAT8_TBL +SELECT f1 AS ten FROM FLOAT8_TBL UNION ALL SELECT f1 FROM INT4_TBL; -ten ---------------------- -0 --34.84 --1004.3 --1.2345678901234e+200 --1.2345678901234e-200 -0 -123456 --123456 -2147483647 --2147483647 + ten +----------------------- + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + 0 + 123456 + -123456 + 2147483647 + -2147483647 (10 rows) -QUERY: SELECT f1 AS five FROM FLOAT8_TBL +SELECT f1 AS five FROM FLOAT8_TBL WHERE f1 BETWEEN -1e6 AND 1e6 UNION SELECT f1 FROM INT4_TBL WHERE f1 BETWEEN 0 AND 1000000; - five ---------------------- - -1004.3 - -34.84 --1.2345678901234e-200 - 0 - 123456 + five +----------------------- + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + 123456 (5 rows) -QUERY: SELECT f1 AS five FROM VARCHAR_TBL +SELECT f1 AS five FROM VARCHAR_TBL UNION SELECT f1 FROM CHAR_TBL; -five ----- -a -a -ab -ab -abcd + five +------ + a + a + ab + ab + abcd (5 rows) -QUERY: SELECT f1 AS three FROM VARCHAR_TBL +SELECT f1 AS three FROM VARCHAR_TBL UNION SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; -three ------ -a -ab -abcd + three +------- + a + ab + abcd (3 rows) -QUERY: SELECT f1 AS eight FROM VARCHAR_TBL +SELECT f1 AS eight FROM VARCHAR_TBL UNION ALL SELECT f1 FROM CHAR_TBL; -eight ------ -a -ab -abcd -abcd -a -ab -abcd -abcd + eight +------- + a + ab + abcd + abcd + a + ab + abcd + abcd (8 rows) -QUERY: SELECT f1 AS five FROM TEXT_TBL +SELECT f1 AS five FROM TEXT_TBL UNION SELECT f1 FROM VARCHAR_TBL UNION SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; -five ------------------ -a -ab -abcd -doh! -hi de ho neighbor + five +------------------- + a + ab + abcd + doh! + hi de ho neighbor (5 rows) -- 2.40.0