src/test/regress/expected/pg_lsn_optimizer.out

--
-- PG_LSN
--
CREATE TABLE PG_LSN_TBL (f1 pg_lsn);
-- Largest and smallest input
INSERT INTO PG_LSN_TBL VALUES ('0/0');
INSERT INTO PG_LSN_TBL VALUES ('FFFFFFFF/FFFFFFFF');
-- Incorrect input
INSERT INTO PG_LSN_TBL VALUES ('G/0');
ERROR:  invalid input syntax for type pg_lsn: "G/0"
LINE 1: INSERT INTO PG_LSN_TBL VALUES ('G/0');
                                       ^
INSERT INTO PG_LSN_TBL VALUES ('-1/0');
ERROR:  invalid input syntax for type pg_lsn: "-1/0"
LINE 1: INSERT INTO PG_LSN_TBL VALUES ('-1/0');
                                       ^
INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
ERROR:  invalid input syntax for type pg_lsn: " 0/12345678"
LINE 1: INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
                                       ^
INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
ERROR:  invalid input syntax for type pg_lsn: "ABCD/"
LINE 1: INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
                                       ^
INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
ERROR:  invalid input syntax for type pg_lsn: "/ABCD"
LINE 1: INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
                                       ^
-- Min/Max aggregation
SELECT MIN(f1), MAX(f1) FROM PG_LSN_TBL;
 min |        max        
-----+-------------------
 0/0 | FFFFFFFF/FFFFFFFF
(1 row)

DROP TABLE PG_LSN_TBL;
-- Operators
SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F8'::pg_lsn != '0/16AE7F7';
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F7' < '0/16AE7F8'::pg_lsn;
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F8' > pg_lsn '0/16AE7F7';
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F7'::pg_lsn - '0/16AE7F8'::pg_lsn;
 ?column? 
----------
       -1
(1 row)

SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
 ?column? 
----------
        1
(1 row)

SELECT '0/16AE7F7'::pg_lsn + 16::numeric;
 ?column?  
-----------
 0/16AE807
(1 row)

SELECT 16::numeric + '0/16AE7F7'::pg_lsn;
 ?column?  
-----------
 0/16AE807
(1 row)

SELECT '0/16AE7F7'::pg_lsn - 16::numeric;
 ?column?  
-----------
 0/16AE7E7
(1 row)

SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 1::numeric;
     ?column?      
-------------------
 FFFFFFFF/FFFFFFFF
(1 row)

SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 2::numeric; -- out of range error
ERROR:  pg_lsn out of range
SELECT '0/1'::pg_lsn - 1::numeric;
 ?column? 
----------
 0/0
(1 row)

SELECT '0/1'::pg_lsn - 2::numeric; -- out of range error
ERROR:  pg_lsn out of range
SELECT '0/0'::pg_lsn + ('FFFFFFFF/FFFFFFFF'::pg_lsn - '0/0'::pg_lsn);
     ?column?      
-------------------
 FFFFFFFF/FFFFFFFF
(1 row)

SELECT 'FFFFFFFF/FFFFFFFF'::pg_lsn - ('FFFFFFFF/FFFFFFFF'::pg_lsn - '0/0'::pg_lsn);
 ?column? 
----------
 0/0
(1 row)

SELECT '0/16AE7F7'::pg_lsn + 'NaN'::numeric;
ERROR:  cannot add NaN to pg_lsn
SELECT '0/16AE7F7'::pg_lsn - 'NaN'::numeric;
ERROR:  cannot subtract NaN from pg_lsn
-- Check btree and hash opclasses
EXPLAIN (COSTS OFF)
SELECT DISTINCT (i || '/' || j)::pg_lsn f
  FROM generate_series(1, 10) i,
       generate_series(1, 10) j,
       generate_series(1, 5) k
  WHERE i <= 10 AND j > 0 AND j <= 10
  ORDER BY f;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Sort
   Sort Key: (((((generate_series_2.generate_series)::text || '/'::text) || (generate_series_1.generate_series)::text))::pg_lsn)
   ->  HashAggregate
         Group Key: ((((generate_series_2.generate_series)::text || '/'::text) || (generate_series_1.generate_series)::text))::pg_lsn
         ->  Nested Loop
               Join Filter: true
               ->  Nested Loop
                     Join Filter: true
                     ->  Result
                           Filter: (generate_series_2.generate_series <= 10)
                           ->  Function Scan on generate_series generate_series_2
                     ->  Materialize
                           ->  Result
                                 Filter: ((generate_series_1.generate_series > 0) AND (generate_series_1.generate_series <= 10))
                                 ->  Function Scan on generate_series generate_series_1
               ->  Function Scan on generate_series
 Optimizer: Pivotal Optimizer (GPORCA)
(17 rows)

SELECT DISTINCT (i || '/' || j)::pg_lsn f
  FROM generate_series(1, 10) i,
       generate_series(1, 10) j,
       generate_series(1, 5) k
  WHERE i <= 10 AND j > 0 AND j <= 10
  ORDER BY f;
   f   
-------
 1/1
 1/2
 1/3
 1/4
 1/5
 1/6
 1/7
 1/8
 1/9
 1/10
 2/1
 2/2
 2/3
 2/4
 2/5
 2/6
 2/7
 2/8
 2/9
 2/10
 3/1
 3/2
 3/3
 3/4
 3/5
 3/6
 3/7
 3/8
 3/9
 3/10
 4/1
 4/2
 4/3
 4/4
 4/5
 4/6
 4/7
 4/8
 4/9
 4/10
 5/1
 5/2
 5/3
 5/4
 5/5
 5/6
 5/7
 5/8
 5/9
 5/10
 6/1
 6/2
 6/3
 6/4
 6/5
 6/6
 6/7
 6/8
 6/9
 6/10
 7/1
 7/2
 7/3
 7/4
 7/5
 7/6
 7/7
 7/8
 7/9
 7/10
 8/1
 8/2
 8/3
 8/4
 8/5
 8/6
 8/7
 8/8
 8/9
 8/10
 9/1
 9/2
 9/3
 9/4
 9/5
 9/6
 9/7
 9/8
 9/9
 9/10
 10/1
 10/2
 10/3
 10/4
 10/5
 10/6
 10/7
 10/8
 10/9
 10/10
(100 rows)