Improve SELECT DISTINCT to consider hash aggregation, as well as sort/uniq,

[postgresql] / src / test / regress / output / misc.source

--
-- MISC
--
--
-- BTREE
--
UPDATE onek
   SET unique1 = onek.unique1 + 1;
UPDATE onek
   SET unique1 = onek.unique1 - 1;
--
-- BTREE partial
--
-- UPDATE onek2
--   SET unique1 = onek2.unique1 + 1;
--UPDATE onek2 
--   SET unique1 = onek2.unique1 - 1;
--
-- BTREE shutting out non-functional updates
--
-- the following two tests seem to take a long time on some 
-- systems.    This non-func update stuff needs to be examined
-- more closely.                        - jolly (2/22/96)
-- 
UPDATE tmp
   SET stringu1 = reverse_name(onek.stringu1)
   FROM onek
   WHERE onek.stringu1 = 'JBAAAA' and
          onek.stringu1 = tmp.stringu1;
UPDATE tmp
   SET stringu1 = reverse_name(onek2.stringu1)
   FROM onek2
   WHERE onek2.stringu1 = 'JCAAAA' and
          onek2.stringu1 = tmp.stringu1;
DROP TABLE tmp;
--UPDATE person*
--   SET age = age + 1;
--UPDATE person*
--   SET age = age + 3
--   WHERE name = 'linda';
--
-- copy
--
COPY onek TO '@abs_builddir@/results/onek.data';
DELETE FROM onek;
COPY onek FROM '@abs_builddir@/results/onek.data';
SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1;
 unique1 
---------
       0
       1
(2 rows)

DELETE FROM onek2;
COPY onek2 FROM '@abs_builddir@/results/onek.data';
SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1;
 unique1 
---------
       0
       1
(2 rows)

COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';
DELETE FROM stud_emp;
COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';
SELECT * FROM stud_emp;
 name  | age |  location  | salary | manager | gpa | percent 
-------+-----+------------+--------+---------+-----+---------
 jeff  |  23 | (8,7.7)    |    600 | sharon  | 3.5 |        
 cim   |  30 | (10.5,4.7) |    400 |         | 3.4 |        
 linda |  19 | (0.9,6.1)  |    100 |         | 2.9 |        
(3 rows)

-- COPY aggtest FROM stdin;
-- 56   7.8
-- 100  99.097
-- 0    0.09561
-- 42   324.78
-- .
-- COPY aggtest TO stdout;
--
-- inheritance stress test
--
SELECT * FROM a_star*;
 class | a  
-------+----
 a     |  1
 a     |  2
 a     |   
 b     |  3
 b     |  4
 b     |   
 b     |   
 c     |  5
 c     |  6
 c     |   
 c     |   
 d     |  7
 d     |  8
 d     |  9
 d     | 10
 d     |   
 d     | 11
 d     | 12
 d     | 13
 d     |   
 d     |   
 d     |   
 d     | 14
 d     |   
 d     |   
 d     |   
 d     |   
 e     | 15
 e     | 16
 e     | 17
 e     |   
 e     | 18
 e     |   
 e     |   
 f     | 19
 f     | 20
 f     | 21
 f     | 22
 f     |   
 f     | 24
 f     | 25
 f     | 26
 f     |   
 f     |   
 f     |   
 f     | 27
 f     |   
 f     |   
 f     |   
 f     |   
(50 rows)

SELECT * 
   FROM b_star* x
   WHERE x.b = text 'bumble' or x.a < 3;
 class | a |   b    
-------+---+--------
 b     |   | bumble
(1 row)

SELECT class, a 
   FROM c_star* x 
   WHERE x.c ~ text 'hi';
 class | a  
-------+----
 c     |  5
 c     |   
 d     |  7
 d     |  8
 d     | 10
 d     |   
 d     | 12
 d     |   
 d     |   
 d     |   
 e     | 15
 e     | 16
 e     |   
 e     |   
 f     | 19
 f     | 20
 f     | 21
 f     |   
 f     | 24
 f     |   
 f     |   
 f     |   
(22 rows)

SELECT class, b, c
   FROM d_star* x
   WHERE x.a < 100;
 class |    b    |     c      
-------+---------+------------
 d     | grumble | hi sunita
 d     | stumble | hi koko
 d     | rumble  | 
 d     |         | hi kristin
 d     | fumble  | 
 d     |         | hi avi
 d     |         | 
 d     |         | 
(8 rows)

SELECT class, c FROM e_star* x WHERE x.c NOTNULL;
 class |      c      
-------+-------------
 e     | hi carol
 e     | hi bob
 e     | hi michelle
 e     | hi elisa
 f     | hi claire
 f     | hi mike
 f     | hi marcel
 f     | hi keith
 f     | hi marc
 f     | hi allison
 f     | hi jeff
 f     | hi carl
(12 rows)

SELECT * FROM f_star* x WHERE x.c ISNULL;
 class | a  | c |  e  |                     f                     
-------+----+---+-----+-------------------------------------------
 f     | 22 |   |  -7 | ((111,555),(222,666),(333,777),(444,888))
 f     | 25 |   |  -9 | 
 f     | 26 |   |     | ((11111,33333),(22222,44444))
 f     |    |   | -11 | ((1111111,3333333),(2222222,4444444))
 f     | 27 |   |     | 
 f     |    |   | -12 | 
 f     |    |   |     | ((11111111,33333333),(22222222,44444444))
 f     |    |   |     | 
(8 rows)

-- grouping and aggregation on inherited sets have been busted in the past...
SELECT sum(a) FROM a_star*;
 sum 
-----
 355
(1 row)

SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class;
 class | sum 
-------+-----
 a     |   3
 b     |   7
 c     |  11
 d     |  84
 e     |  66
 f     | 184
(6 rows)

ALTER TABLE f_star RENAME COLUMN f TO ff;
ALTER TABLE e_star* RENAME COLUMN e TO ee;
ALTER TABLE d_star* RENAME COLUMN d TO dd;
ALTER TABLE c_star* RENAME COLUMN c TO cc;
ALTER TABLE b_star* RENAME COLUMN b TO bb;
ALTER TABLE a_star* RENAME COLUMN a TO aa;
SELECT class, aa
   FROM a_star* x
   WHERE aa ISNULL;
 class | aa 
-------+----
 a     |   
 b     |   
 b     |   
 c     |   
 c     |   
 d     |   
 d     |   
 d     |   
 d     |   
 d     |   
 d     |   
 d     |   
 d     |   
 e     |   
 e     |   
 e     |   
 f     |   
 f     |   
 f     |   
 f     |   
 f     |   
 f     |   
 f     |   
 f     |   
(24 rows)

-- As of Postgres 7.1, ALTER implicitly recurses,
-- so this should be same as ALTER a_star*
ALTER TABLE a_star RENAME COLUMN aa TO foo;
SELECT class, foo
   FROM a_star* x
   WHERE x.foo >= 2;
 class | foo 
-------+-----
 a     |   2
 b     |   3
 b     |   4
 c     |   5
 c     |   6
 d     |   7
 d     |   8
 d     |   9
 d     |  10
 d     |  11
 d     |  12
 d     |  13
 d     |  14
 e     |  15
 e     |  16
 e     |  17
 e     |  18
 f     |  19
 f     |  20
 f     |  21
 f     |  22
 f     |  24
 f     |  25
 f     |  26
 f     |  27
(25 rows)

ALTER TABLE a_star RENAME COLUMN foo TO aa;
SELECT * 
   from a_star*
   WHERE aa < 1000;
 class | aa 
-------+----
 a     |  1
 a     |  2
 b     |  3
 b     |  4
 c     |  5
 c     |  6
 d     |  7
 d     |  8
 d     |  9
 d     | 10
 d     | 11
 d     | 12
 d     | 13
 d     | 14
 e     | 15
 e     | 16
 e     | 17
 e     | 18
 f     | 19
 f     | 20
 f     | 21
 f     | 22
 f     | 24
 f     | 25
 f     | 26
 f     | 27
(26 rows)

ALTER TABLE f_star ADD COLUMN f int4;
UPDATE f_star SET f = 10;
ALTER TABLE e_star* ADD COLUMN e int4;
--UPDATE e_star* SET e = 42;
SELECT * FROM e_star*;
 class | aa |     cc      | ee  | e 
-------+----+-------------+-----+---
 e     | 15 | hi carol    |  -1 |  
 e     | 16 | hi bob      |     |  
 e     | 17 |             |  -2 |  
 e     |    | hi michelle |  -3 |  
 e     | 18 |             |     |  
 e     |    | hi elisa    |     |  
 e     |    |             |  -4 |  
 f     | 19 | hi claire   |  -5 |  
 f     | 20 | hi mike     |  -6 |  
 f     | 21 | hi marcel   |     |  
 f     | 22 |             |  -7 |  
 f     |    | hi keith    |  -8 |  
 f     | 24 | hi marc     |     |  
 f     | 25 |             |  -9 |  
 f     | 26 |             |     |  
 f     |    | hi allison  | -10 |  
 f     |    | hi jeff     |     |  
 f     |    |             | -11 |  
 f     | 27 |             |     |  
 f     |    | hi carl     |     |  
 f     |    |             | -12 |  
 f     |    |             |     |  
 f     |    |             |     |  
(23 rows)

ALTER TABLE a_star* ADD COLUMN a text;
NOTICE:  merging definition of column "a" for child "d_star"
--UPDATE b_star*
--   SET a = text 'gazpacho'
--   WHERE aa > 4;
SELECT class, aa, a FROM a_star*;
 class | aa | a 
-------+----+---
 a     |  1 | 
 a     |  2 | 
 a     |    | 
 b     |  3 | 
 b     |  4 | 
 b     |    | 
 b     |    | 
 c     |  5 | 
 c     |  6 | 
 c     |    | 
 c     |    | 
 d     |  7 | 
 d     |  8 | 
 d     |  9 | 
 d     | 10 | 
 d     |    | 
 d     | 11 | 
 d     | 12 | 
 d     | 13 | 
 d     |    | 
 d     |    | 
 d     |    | 
 d     | 14 | 
 d     |    | 
 d     |    | 
 d     |    | 
 d     |    | 
 e     | 15 | 
 e     | 16 | 
 e     | 17 | 
 e     |    | 
 e     | 18 | 
 e     |    | 
 e     |    | 
 f     | 19 | 
 f     | 20 | 
 f     | 21 | 
 f     | 22 | 
 f     |    | 
 f     | 24 | 
 f     | 25 | 
 f     | 26 | 
 f     |    | 
 f     |    | 
 f     |    | 
 f     | 27 | 
 f     |    | 
 f     |    | 
 f     |    | 
 f     |    | 
(50 rows)

--
-- versions
--
--
-- postquel functions
--
--
-- mike does post_hacking,
-- joe and sally play basketball, and
-- everyone else does nothing.
--
SELECT p.name, name(p.hobbies) FROM ONLY person p;
 name  |    name     
-------+-------------
 mike  | posthacking
 joe   | basketball
 sally | basketball
(3 rows)

--
-- as above, but jeff also does post_hacking.
--
SELECT p.name, name(p.hobbies) FROM person* p;
 name  |    name     
-------+-------------
 mike  | posthacking
 joe   | basketball
 sally | basketball
 jeff  | posthacking
(4 rows)

--
-- the next two queries demonstrate how functions generate bogus duplicates.
-- this is a "feature" ..
--
SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
  ORDER BY 1,2;
    name     |     name      
-------------+---------------
 basketball  | hightops
 posthacking | advil
 posthacking | peet's coffee
 skywalking  | guts
(4 rows)

SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
    name     |     name      
-------------+---------------
 posthacking | advil
 posthacking | peet's coffee
 posthacking | advil
 posthacking | peet's coffee
 basketball  | hightops
 basketball  | hightops
 skywalking  | guts
(7 rows)

--
-- mike needs advil and peet's coffee,
-- joe and sally need hightops, and
-- everyone else is fine.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
 name  |    name     |     name      
-------+-------------+---------------
 mike  | posthacking | advil
 mike  | posthacking | peet's coffee
 joe   | basketball  | hightops
 sally | basketball  | hightops
(4 rows)

--
-- as above, but jeff needs advil and peet's coffee as well.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
 name  |    name     |     name      
-------+-------------+---------------
 mike  | posthacking | advil
 mike  | posthacking | peet's coffee
 joe   | basketball  | hightops
 sally | basketball  | hightops
 jeff  | posthacking | advil
 jeff  | posthacking | peet's coffee
(6 rows)

--
-- just like the last two, but make sure that the target list fixup and
-- unflattening is being done correctly.
--
SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
     name      | name  |    name     
---------------+-------+-------------
 advil         | mike  | posthacking
 peet's coffee | mike  | posthacking
 hightops      | joe   | basketball
 hightops      | sally | basketball
(4 rows)

SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
     name      | name  |    name     
---------------+-------+-------------
 advil         | mike  | posthacking
 peet's coffee | mike  | posthacking
 hightops      | joe   | basketball
 hightops      | sally | basketball
 advil         | jeff  | posthacking
 peet's coffee | jeff  | posthacking
(6 rows)

SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
     name      |    name     | name  
---------------+-------------+-------
 advil         | posthacking | mike
 peet's coffee | posthacking | mike
 hightops      | basketball  | joe
 hightops      | basketball  | sally
(4 rows)

SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
     name      |    name     | name  
---------------+-------------+-------
 advil         | posthacking | mike
 peet's coffee | posthacking | mike
 hightops      | basketball  | joe
 hightops      | basketball  | sally
 advil         | posthacking | jeff
 peet's coffee | posthacking | jeff
(6 rows)

SELECT user_relns() AS user_relns
   ORDER BY user_relns;
     user_relns      
---------------------
 a
 a_star
 abstime_tbl
 aggtest
 array_index_op_test
 array_op_test
 arrtest
 b
 b_star
 box_tbl
 bprime
 bt_f8_heap
 bt_i4_heap
 bt_name_heap
 bt_txt_heap
 c
 c_star
 char_tbl
 check2_tbl
 check_seq
 check_tbl
 circle_tbl
 city
 copy_tbl
 d
 d_star
 date_tbl
 default_seq
 default_tbl
 defaultexpr_tbl
 dept
 e_star
 emp
 equipment_r
 f_star
 fast_emp4000
 float4_tbl
 float8_tbl
 func_index_heap
 hash_f8_heap
 hash_i4_heap
 hash_name_heap
 hash_txt_heap
 hobbies_r
 iexit
 ihighway
 inet_tbl
 inhe
 inhf
 inhx
 insert_seq
 insert_tbl
 int2_tbl
 int4_tbl
 int8_tbl
 interval_tbl
 iportaltest
 log_table
 lseg_tbl
 main_table
 money_data
 num_data
 num_exp_add
 num_exp_div
 num_exp_ln
 num_exp_log10
 num_exp_mul
 num_exp_power_10_ln
 num_exp_sqrt
 num_exp_sub
 num_input_test
 num_result
 onek
 onek2
 path_tbl
 person
 point_tbl
 polygon_tbl
 ramp
 random_tbl
 real_city
 reltime_tbl
 road
 shighway
 slow_emp4000
 street
 stud_emp
 student
 subselect_tbl
 tenk1
 tenk2
 test_tsvector
 text_tbl
 time_tbl
 timestamp_tbl
 timestamptz_tbl
 timetz_tbl
 tinterval_tbl
 toyemp
 varchar_tbl
 xacttest
(101 rows)

SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
 name 
------
 guts
(1 row)

SELECT hobbies_by_name('basketball');
 hobbies_by_name 
-----------------
 joe
(1 row)

SELECT name, overpaid(emp.*) FROM emp;
  name  | overpaid 
--------+----------
 sharon | t
 sam    | t
 bill   | t
 jeff   | f
 cim    | f
 linda  | f
(6 rows)

--
-- Try a few cases with SQL-spec row constructor expressions
--
SELECT * FROM equipment(ROW('skywalking', 'mer'));
 name |   hobby    
------+------------
 guts | skywalking
(1 row)

SELECT name(equipment(ROW('skywalking', 'mer')));
 name 
------
 guts
(1 row)

SELECT *, name(equipment(h.*)) FROM hobbies_r h;
    name     | person |     name      
-------------+--------+---------------
 posthacking | mike   | advil
 posthacking | mike   | peet's coffee
 posthacking | jeff   | advil
 posthacking | jeff   | peet's coffee
 basketball  | joe    | hightops
 basketball  | sally  | hightops
 skywalking  |        | guts
(7 rows)

SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
    name     | person |     name      
-------------+--------+---------------
 posthacking | mike   | advil
 posthacking | mike   | peet's coffee
 posthacking | jeff   | advil
 posthacking | jeff   | peet's coffee
 basketball  | joe    | hightops
 basketball  | sally  | hightops
 skywalking  |        | guts
(7 rows)

--
-- check that old-style C functions work properly with TOASTed values
--
create table oldstyle_test(i int4, t text);
insert into oldstyle_test values(null,null);
insert into oldstyle_test values(0,'12');
insert into oldstyle_test values(1000,'12');
insert into oldstyle_test values(0, repeat('x', 50000));
select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test;
  i   | length | octet_length | oldstyle_length 
------+--------+--------------+-----------------
      |        |              |                
    0 |      2 |            2 |               2
 1000 |      2 |            2 |            1002
    0 |  50000 |        50000 |           50000
(4 rows)

drop table oldstyle_test;
--
-- functional joins
--
--
-- instance rules
--
--
-- rewrite rules
--