ydb/yql/essentials/tests/postgresql/cases/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)

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

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

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

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

-- corner case: VALUES with no columns
CREATE TEMP TABLE nocols();
--
-- 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 NULLS FIRST;
 f1 
----
   
   
  1
  3
  7
 10
 42
(7 rows)

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

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

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

select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
     494 |      11 |   0 |    2 |   4 |     14 |       4 |       94 |          94 |       494 |      494 |   8 |    9 | ATAAAA   | LAAAAA   | VVVVxx
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 unique2 
---------
      11
(1 row)

select * from onek2 where unique2 = 11 and stringu1 < 'B';
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
     494 |      11 |   0 |    2 |   4 |     14 |       4 |       94 |          94 |       494 |      494 |   8 |    9 | ATAAAA   | LAAAAA   | VVVVxx
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
 unique2 
---------
      11
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
 unique2 
---------
      11
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
 unique2 
---------
      11
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
 unique2 
---------
      11
(1 row)

select unique1, unique2 from onek2
  where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
 unique1 | unique2 
---------+---------
     494 |      11
       0 |     998
(2 rows)

select unique1, unique2 from onek2
  where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
 unique1 | unique2 
---------+---------
     494 |      11
       0 |     998
(2 rows)

-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
-- (see bug #5084)
select * from (values (2),(null),(1)) v(k) where k = k order by k;
 k 
---
 1
 2
(2 rows)

select * from (values (2),(null),(1)) v(k) where k = k;
 k 
---
 2
 1
(2 rows)