select.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 771 行 · 第 1/2 页

 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)

--
-- Test some corner cases that have been known to confuse the planner
--
-- ORDER BY on a constant doesn't really need any sorting
SELECT 1 AS x ORDER BY x;
 x 
---
 1
(1 row)

-- But ORDER BY on a set-valued expression does
create function sillysrf(int) returns setof int as
  'values (1),(10),(2),($1)' language sql immutable;
select sillysrf(42);
 sillysrf 
----------
        1
       10
        2
       42
(4 rows)

select sillysrf(-1) order by 1;
 sillysrf 
----------
       -1
        1
        2
       10
(4 rows)

drop function sillysrf(int);