*** /home/aardvark/pg_stuff/pg_sandbox/pgsql.inverse/src/test/regress/expected/window.out	2013-12-21 12:57:36.814939706 +0100
--- /home/aardvark/pg_stuff/pg_sandbox/pgsql.inverse/src/test/regress/results/window.out	2013-12-21 12:59:49.827750904 +0100
***************
*** 1078,1084 ****
    INNER JOIN pg_proc As ptrns ON agg.aggtransfn = ptrns.oid
    INNER JOIN pg_proc AS pntrns ON agg.agginvtransfn = pntrns.oid
    WHERE agg.agginvtransfn <> 0 AND ptrns.proargtypes <> pntrns.proargtypes;
!  proname | proname
  ---------+---------
  (0 rows)
  
--- 1078,1084 ----
    INNER JOIN pg_proc As ptrns ON agg.aggtransfn = ptrns.oid
    INNER JOIN pg_proc AS pntrns ON agg.agginvtransfn = pntrns.oid
    WHERE agg.agginvtransfn <> 0 AND ptrns.proargtypes <> pntrns.proargtypes;
!  proname | proname 
  ---------+---------
  (0 rows)
  
***************
*** 1089,1212 ****
    INNER JOIN pg_proc As ptrns ON agg.aggtransfn = ptrns.oid
    INNER JOIN pg_proc AS pntrns ON agg.agginvtransfn = pntrns.oid
    WHERE agg.agginvtransfn <> 0 AND ptrns.proisstrict <> pntrns.proisstrict;
!  proname | proname
  ---------+---------
  (0 rows)
  
  -- test inverse transition funtions handle NULLs properly
  SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg
  ---+--------------------
   1 | 1.5000000000000000
   2 | 2.0000000000000000
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg
  ---+--------------------
   1 | 1.5000000000000000
   2 | 2.0000000000000000
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg
  ---+--------------------
   1 | 1.5000000000000000
   2 | 2.0000000000000000
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,AVG(v::numeric(10,2)) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg
  ---+--------------------
   1 | 2.0000000000000000
   2 | 2.5000000000000000
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
!  i |    avg
  ---+------------
   1 | @ 1.5 secs
   2 | @ 2 secs
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!   i | sum
  ---+-----
   1 |   3
   2 |   2
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!   i | sum
  ---+-----
   1 |   3
   2 |   2
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum
  ---+-----
   1 |   3
   2 |   2
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v);
!  i |  sum
  ---+-------
   1 | $3.30
   2 | $2.20
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
!  i |   sum
  ---+----------
   1 | @ 3 secs
   2 | @ 2 secs
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::numeric(4,1)) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum
  ---+-----
   1 | 3.3
   2 | 2.2
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | count
  ---+-------
   1 |     2
   2 |     1
--- 1089,1212 ----
    INNER JOIN pg_proc As ptrns ON agg.aggtransfn = ptrns.oid
    INNER JOIN pg_proc AS pntrns ON agg.agginvtransfn = pntrns.oid
    WHERE agg.agginvtransfn <> 0 AND ptrns.proisstrict <> pntrns.proisstrict;
!  proname | proname 
  ---------+---------
  (0 rows)
  
  -- test inverse transition funtions handle NULLs properly
  SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg         
  ---+--------------------
   1 | 1.5000000000000000
   2 | 2.0000000000000000
!  3 |                   
!  4 |                   
  (4 rows)
  
  SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg         
  ---+--------------------
   1 | 1.5000000000000000
   2 | 2.0000000000000000
!  3 |                   
!  4 |                   
  (4 rows)
  
  SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg         
  ---+--------------------
   1 | 1.5000000000000000
   2 | 2.0000000000000000
!  3 |                   
!  4 |                   
  (4 rows)
  
  SELECT i,AVG(v::numeric(10,2)) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);
!  i |        avg         
  ---+--------------------
   1 | 2.0000000000000000
   2 | 2.5000000000000000
!  3 |                   
!  4 |                   
  (4 rows)
  
  SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
!  i |    avg     
  ---+------------
   1 | @ 1.5 secs
   2 | @ 2 secs
!  3 | 
!  4 | 
  (4 rows)
  
  SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum 
  ---+-----
   1 |   3
   2 |   2
!  3 |    
!  4 |    
  (4 rows)
  
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum 
  ---+-----
   1 |   3
   2 |   2
!  3 |    
!  4 |    
  (4 rows)
  
  SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum 
  ---+-----
   1 |   3
   2 |   2
!  3 |    
!  4 |    
  (4 rows)
  
  SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v);
!  i |  sum  
  ---+-------
   1 | $3.30
   2 | $2.20
!  3 |      
!  4 |      
  (4 rows)
  
  SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
!  i |   sum    
  ---+----------
   1 | @ 3 secs
   2 | @ 2 secs
!  3 | 
!  4 | 
  (4 rows)
  
  SELECT i,SUM(v::numeric(4,1)) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum 
  ---+-----
   1 | 3.3
   2 | 2.2
!  3 |    
!  4 |    
  (4 rows)
  
  SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | count 
  ---+-------
   1 |     2
   2 |     1
***************
*** 1216,1222 ****
  
  SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | count
  ---+-------
   1 |     4
   2 |     3
--- 1216,1222 ----
  
  SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | count 
  ---+-------
   1 |     4
   2 |     3
***************
*** 1227,1253 ****
  -- test that inverse transition functions work with various frame options
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum
  ---+-----
   1 |   1
   2 |   2
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum
  ---+-----
   1 |   3
   2 |   2
!  3 |
!  4 |
  (4 rows)
  
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);
!  i | sum
  ---+-----
   1 |   3
   2 |   6
--- 1227,1253 ----
  -- test that inverse transition functions work with various frame options
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum 
  ---+-----
   1 |   1
   2 |   2
!  3 |    
!  4 |    
  (4 rows)
  
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
!  i | sum 
  ---+-----
   1 |   3
   2 |   2
!  3 |    
!  4 |    
  (4 rows)
  
  SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);
!  i | sum 
  ---+-----
   1 |   3
   2 |   6
***************
*** 1261,1280 ****
  -- hard about it.
  SELECT SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
    FROM (VALUES(1,1e20),(2,1)) n(i,n);
!   sum
! --------
!  1e+020
!       1
  (2 rows)
  
  -- inverse transition function with filter
  SELECT i,SUM(v::int) FILTER (WHERE i < 4) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,4)) t(i,v);
!  i | sum
  ---+-----
   1 |   3
   2 |   2
!  3 |
!  4 |
  (4 rows)
  
--- 1261,1280 ----
  -- hard about it.
  SELECT SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
    FROM (VALUES(1,1e20),(2,1)) n(i,n);
!   sum  
! -------
!  1e+20
!      1
  (2 rows)
  
  -- inverse transition function with filter
  SELECT i,SUM(v::int) FILTER (WHERE i < 4) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM (VALUES(1,1),(2,2),(3,NULL),(4,4)) t(i,v);
!  i | sum 
  ---+-----
   1 |   3
   2 |   2
!  3 |    
!  4 |    
  (4 rows)
  

======================================================================