path: root/src/test/regress/expected/select.out

--
-- SELECT
--
-- btree index
-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
--
SELECT * FROM onek
   WHERE onek.unique1 < 10
   ORDER BY onek.unique1;
 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)

--
-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
--
SELECT onek.unique1, onek.stringu1 FROM onek
   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
(20 rows)

--
-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
--
SELECT onek.unique1, onek.stringu1 FROM onek
   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
(19 rows)

	
--
-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
-- sort +1d -2 +0nr -1
--
SELECT onek.unique1, onek.string4 FROM onek
   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
(19 rows)

	
--
-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
-- sort +1dr -2 +0n -1
--
SELECT onek.unique1, onek.string4 FROM onek
   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
(19 rows)

	
--
-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
-- sort +0nr -1 +1d -2
--
SELECT onek.unique1, onek.string4 FROM onek
   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
(20 rows)

--
-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
-- sort +0n -1 +1dr -2
--
SELECT onek.unique1, onek.string4 FROM onek
   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
(20 rows)

--
-- test partial btree indexes
--
-- As of 7.2, planner probably won't pick an indexscan without stats,
-- so ANALYZE first.  Also, we want to prevent it from picking a bitmapscan
-- followed by sort, because that could hide index ordering problems.
--
ANALYZE onek2;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_sort TO off;
--
-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
--
SELECT onek2.* FROM onek2 WHERE onek2.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)

--
-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
--
SELECT onek2.unique1, onek2.stringu1 FROM onek2
    WHERE onek2.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
(20 rows)

--
-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
--
SELECT onek2.unique1, onek2.stringu1 FROM onek2
   WHERE onek2.unique1 > 980;
 unique1 | stringu1 
---------+----------
     981 | TLAAAA
     982 | ULAAAA
     983 | VLAAAA
     984 | WLAAAA
     985 | XLAAAA
     986 | YLAAAA
     987 | ZLAAAA
     988 | AMAAAA
     989 | BMAAAA
     990 | CMAAAA
     991 | DMAAAA
     992 | EMAAAA
     993 | FMAAAA
     994 | GMAAAA
     995 | HMAAAA
     996 | IMAAAA
     997 | JMAAAA
     998 | KMAAAA
     999 | LMAAAA
(19 rows)

RESET enable_seqscan;
RESET enable_bitmapscan;
RESET enable_sort;
SELECT two, stringu1, ten, string4
   INTO TABLE tmp
   FROM onek;
--
-- 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)

--
-- 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)

--
-- Test some cases involving whole-row Var referencing a subquery
--
select foo from (select 1) as foo;
 foo 
-----
 (1)
(1 row)

select foo from (select null) as foo;
 foo 
-----
 ()
(1 row)

select foo from (select 'xyzzy',1,null) as foo;
    foo     
------------
 (xyzzy,1,)
(1 row)

--
-- Test VALUES lists
--
select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)
    WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 |  i  |   j    
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------
     147 |       0 |   1 |    3 |   7 |      7 |       7 |       47 |         147 |       147 |      147 |  14 |   15 | RFAAAA   | AAAAAA   | AAAAxx  | 147 | RFAAAA
     931 |       1 |   1 |    3 |   1 |     11 |       1 |       31 |         131 |       431 |      931 |   2 |    3 | VJAAAA   | BAAAAA   | HHHHxx  | 931 | VJAAAA
(2 rows)

-- a more complex case
-- looks like we're coding lisp :-)
select * from onek,
  (values ((select i from
    (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
    order by i asc limit 1))) bar (i)
  where onek.unique1 = bar.i;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
       2 |     326 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |         2 |        2 |   4 |    5 | CAAAAA   | OMAAAA   | OOOOxx  | 2
(1 row)

-- try VALUES in a subquery
select * from onek
    where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
    order by unique1;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       1 |     214 |   1 |    1 |   1 |      1 |       1 |        1 |           1 |         1 |        1 |   2 |    3 | BAAAAA   | GIAAAA   | OOOOxx
      20 |     306 |   0 |    0 |   0 |      0 |       0 |       20 |          20 |        20 |       20 |   0 |    1 | UAAAAA   | ULAAAA   | OOOOxx
      99 |     101 |   1 |    3 |   9 |     19 |       9 |       99 |          99 |        99 |       99 |  18 |   19 | VDAAAA   | XDAAAA   | HHHHxx
(3 rows)

-- VALUES is also legal as a standalone query or a set-operation member
VALUES (1,2), (3,4+4), (7,77.7);
 column1 | column2 
---------+---------
       1 |       2
       3 |       8
       7 |    77.7
(3 rows)

VALUES (1,2), (3,4+4), (7,77.7)
UNION ALL
SELECT 2+2, 57
UNION ALL
SELECT * FROM int8_tbl;
     column1      |      column2      
------------------+-------------------
                1 |                 2
                3 |                 8
                7 |              77.7
                4 |                57
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(9 rows)

--
-- Test ORDER BY options
--
CREATE TEMP TABLE foo (f1 int);
INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
SELECT * FROM foo ORDER BY f1;
 f1 
----
  1
  3
  7
 10
 42
   
   
(7 rows)

SELECT * FROM foo ORDER BY f1 ASC;	-- same thing
 f1 
----
  1
  3
  7
 10
 42
   
   
(7 rows)

SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 f1 
----
   
   
  1
  3
  7
 10
 42
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC;
 f1 
----
   
   
 42
 10
  7
  3
  1
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 f1 
----
 42
 10
  7
  3
  1
   
   
(7 rows)

-- check if indexscans do the right things
CREATE INDEX fooi ON foo (f1);
SET enable_sort = false;
SELECT * FROM foo ORDER BY f1;
 f1 
----
  1
  3
  7
 10
 42
   
   
(7 rows)

SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 f1 
----
   
   
  1
  3
  7
 10
 42
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC;
 f1 
----
   
   
 42
 10
  7
  3
  1
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 f1 
----
 42
 10
  7
  3
  1
   
   
(7 rows)

DROP INDEX fooi;
CREATE INDEX fooi ON foo (f1 DESC);
SELECT * FROM foo ORDER BY f1;
 f1 
----
  1
  3
  7
 10
 42
   
   
(7 rows)

SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 f1 
----
   
   
  1
  3
  7
 10
 42
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC;
 f1 
----
   
   
 42
 10
  7
  3
  1
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 f1 
----
 42
 10
  7
  3
  1
   
   
(7 rows)

DROP INDEX fooi;
CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
SELECT * FROM foo ORDER BY f1;
 f1 
----
  1
  3
  7
 10
 42
   
   
(7 rows)

SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 f1 
----
   
   
  1
  3
  7
 10
 42
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC;
 f1 
----
   
   
 42
 10
  7
  3
  1
(7 rows)

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 f1 
----
 42
 10
  7
  3
  1
   
   
(7 rows)