# 2013-05-28
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is percentile.c extension.  This also tests
# the SQLITE_ENABLE_ORDERED_SET_AGGREGATES compile-time option.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Basic test of the percentile() function.
#
do_test percentile-1.0 {
  load_static_extension db percentile
  execsql {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1),(4),(6),(7),(8),(9),(11),(11),(11);
  }
  execsql {SELECT percentile(x,0) FROM t1}
} {1.0}
foreach {in out disc} {
  100    11.0  11.0
   50     8.0   8.0
   12.5   4.0   4.0
   15     4.4   4.0
   20     5.2   4.0
   80    11.0  11.0
   89    11.0  11.0
} {
  do_test percentile-1.1.$in.1 {
    execsql {SELECT percentile(x,$in) FROM t1}
  } $out
  do_test percentile-1.1.$in.2 {
    execsql {SELECT percentile_cont(x,$in*0.01) FROM t1}
  } $out
  do_test percentile-1.1.$in.3 {
    execsql {SELECT percentile_disc(x,$in*0.01) FROM t1}
  } $disc
  if {$in==50} {
    do_test percentile-1.1.$in.4 {
      execsql {SELECT median(x) FROM t1}
    } $out
  }
  ifcapable ordered_set_aggregates {
    do_test percentile-1.1.$in.5 {
      execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t1}
    } $out
    do_test percentile-1.1.$in.6 {
      execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)
                 FROM t1}
    } $out
    do_test percentile-1.1.$in.7 {
      execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)
                 FROM t1}
    } $disc
    if {$in==50} {
      do_test percentile-1.1.$in.8 {
        execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t1}
      } $out
    }
  }
}
do_execsql_test percentile-1.1.median {
  SELECT median(x) FROM t1;
} 8.0
ifcapable ordered_set_aggregates {
  do_execsql_test percentile-1.1.median {
    SELECT median() WITHIN GROUP (ORDER BY x) FROM t1;
  } 8.0
  do_execsql_test percentile-1.1.distinct.1 {
    SELECT median(DISTINCT x) FROM t1;
  } 7.0
  do_catchsql_test percentile-1.1.distinct.2 {
    SELECT percentile(DISTINCT 50) WITHIN GROUP (ORDER BY x) FROM t1;
  } {1 {DISTINCT not allowed on ordered-set aggregate percentile()}}
} else {
  do_catchsql_test percentile-1.1.median {
    SELECT median() WITHIN GROUP (ORDER BY x) FROM t1;
  } {1 {near "(": syntax error}}
}

# Add some NULL values.
#
do_test percentile-1.2 {
  execsql {INSERT INTO t1 VALUES(NULL),(NULL);}
} {}
foreach {in out disc} {
  100    11.0  11.0
   50     8.0   8.0
   12.5   4.0   4.0
   15     4.4   4.0
   20     5.2   4.0
   80    11.0  11.0
   89    11.0  11.0
} {
  do_test percentile-1.3.$in.1 {
    execsql {SELECT percentile(x,$in) FROM t1}
  } $out
  do_test percentile-1.3.$in.2 {
    execsql {SELECT percentile_cont(x,$in*0.01) FROM t1}
  } $out
  do_test percentile-1.3.$in.3 {
    execsql {SELECT percentile_disc(x,$in*0.01) FROM t1}
  } $disc
  if {$in==50} {
    do_test percentile-1.3.$in.4 {
      execsql {SELECT median(x) FROM t1}
    } $out
  }
  ifcapable ordered_set_aggregates {
    do_test percentile-1.3.$in.5 {
      execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t1}
    } $out
    do_test percentile-1.3.$in.6 {
      execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)
                 FROM t1}
    } $out
    do_test percentile-1.3.$in.7 {
      execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)
                 FROM t1}
    } $disc
    if {$in==50} {
      do_test percentile-1.3.$in.8 {
        execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t1}
      } $out
    }
  }
}

# The second argument to percentile can change some, but not much.
#
do_test percentile-1.4.1 {
  catchsql {SELECT round(percentile(x, 15+0.000001*rowid),1) FROM t1}
} {0 4.4}
do_test percentile-1.4.2 {
  catchsql {SELECT round(percentile_cont(x,(15+0.000001*rowid)*0.01),1) FROM t1}
} {0 4.4}
do_test percentile-1.4.3 {
  catchsql {SELECT percentile_disc(x, (15+0.000001*rowid)*0.01) FROM t1}
} {0 4.0}
do_test percentile-1.5.1 {
  catchsql {SELECT percentile(x, 15+0.1*rowid) FROM t1}
} {1 {the fraction argument to percentile() is not the same for all input rows}}
do_test percentile-1.5.2 {
  catchsql {SELECT percentile_cont(x, (15+0.1*rowid)*0.01) FROM t1}
} {1 {the fraction argument to percentile_cont() is not the same for all input rows}}
do_test percentile-1.5.3 {
  catchsql {SELECT percentile_disc(x, (15+0.1*rowid)*0.01) FROM t1}
} {1 {the fraction argument to percentile_disc() is not the same for all input rows}}

# Input values in a random order
#
do_test percentile-1.6 {
  execsql {
    CREATE TABLE t2(x);
    INSERT INTO t2 SELECT x+0.0 FROM t1 ORDER BY random();
  }
} {}
foreach {in out disc} {
  100    11.0  11.0
   50     8.0   8.0
   12.5   4.0   4.0
   15     4.4   4.0
   20     5.2   4.0
   80    11.0  11.0
   89    11.0  11.0
} {
  do_test percentile-1.7.$in.1 {
    execsql {SELECT percentile(x,$in) FROM t2}
  } $out
  do_test percentile-1.7.$in.2 {
    execsql {SELECT percentile_cont(x,$in*0.01) FROM t2}
  } $out
  do_test percentile-1.7.$in.3 {
    execsql {SELECT percentile_disc(x,$in*0.01) FROM t2}
  } $disc
  if {$in==50} {
    do_test percentile-1.7.$in.4 {
      execsql {SELECT median(x) FROM t2}
    } $out
  }
  ifcapable ordered_set_aggregates {
    do_test percentile-1.7.$in.5 {
      execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t2}
    } $out
    do_test percentile-1.7.$in.6 {
      execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)
                 FROM t2}
    } $out
    do_test percentile-1.7.$in.7 {
      execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)
                 FROM t2}
    } $disc
    if {$in==50} {
      do_test percentile-1.7.$in.8 {
        execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t2}
      } $out
    }
  }
}

# Wrong number of arguments
#
do_test percentile-1.8.1 {
  catchsql {SELECT percentile(x,0,1) FROM t1}
} {1 {wrong number of arguments to function percentile()}}
do_test percentile-1.8.2 {
  catchsql {SELECT percentile_cont(x,0,1) FROM t1}
} {1 {wrong number of arguments to function percentile_cont()}}
do_test percentile-1.8.3 {
  catchsql {SELECT percentile_disc(x,0,1) FROM t1}
} {1 {wrong number of arguments to function percentile_disc()}}
do_test percentile-1.8.4 {
  catchsql {SELECT median(x,0) FROM t1}
} {1 {wrong number of arguments to function median()}}
ifcapable ordered_set_aggregates {
  do_test percentile-1.8.5 {
    catchsql {SELECT percentile(0,1) WITHIN GROUP(ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function percentile()}}
  do_test percentile-1.8.2 {
    catchsql {SELECT percentile_cont(0,1)WITHIN GROUP (ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function percentile_cont()}}
  do_test percentile-1.8.3 {
    catchsql {SELECT percentile_disc(0,1)WITHIN GROUP (ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function percentile_disc()}}
  do_test percentile-1.8.4 {
    catchsql {SELECT median(x) WITHIN GROUP (ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function median()}}
}
do_test percentile-1.9.1 {
  catchsql {SELECT percentile(x) FROM t1}
} {1 {wrong number of arguments to function percentile()}}
do_test percentile-1.9.2 {
  catchsql {SELECT percentile_cont(x) FROM t1}
} {1 {wrong number of arguments to function percentile_cont()}}
do_test percentile-1.9.3 {
  catchsql {SELECT percentile_disc(x) FROM t1}
} {1 {wrong number of arguments to function percentile_disc()}}
do_test percentile-1.9.4 {
  catchsql {SELECT median() FROM t1}
} {1 {wrong number of arguments to function median()}}
ifcapable ordered_set_aggregates {
  do_test percentile-1.9.5 {
    catchsql {SELECT percentile() WITHIN GROUP(ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function percentile()}}
  do_test percentile-1.9.6 {
    catchsql {SELECT percentile_cont()WITHIN GROUP (ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function percentile_cont()}}
  do_test percentile-1.9.7 {
    catchsql {SELECT percentile_disc()WITHIN GROUP (ORDER BY x) FROM t1}
  } {1 {wrong number of arguments to function percentile_disc()}}
}

# Second argument must be numeric
#
do_test percentile-1.10 {
  catchsql {SELECT percentile(x,null) FROM t1}
} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
do_test percentile-1.11 {
  catchsql {SELECT percentile(x,'fifty') FROM t1}
} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
do_test percentile-1.12 {
  catchsql {SELECT percentile(x,x'3530') FROM t1}
} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}

# Second argument is out of range
#
do_test percentile-1.13 {
  catchsql {SELECT percentile(x,-0.0000001) FROM t1}
} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
do_test percentile-1.14 {
  catchsql {SELECT percentile(x,100.0000001) FROM t1}
} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
do_test percentile-1.14.2 {
  catchsql {SELECT percentile_cont(x,1.0000001) FROM t1}
} {1 {the fraction argument to percentile_cont() is not between 0.0 and 1.0}}
do_test percentile-1.14.3 {
  catchsql {SELECT percentile_disc(x,1.0000001) FROM t1}
} {1 {the fraction argument to percentile_disc() is not between 0.0 and 1.0}}

# First argument is not NULL and is not NUMERIC
#
do_test percentile-1.15.1 {
  catchsql {
    BEGIN;
    UPDATE t1 SET x='50' WHERE x IS NULL;
    SELECT percentile(x, 50) FROM t1;
  }
} {1 {input to percentile() is not numeric}}
do_test percentile-1.15.2 {
  catchsql {
    SELECT percentile_cont(x, 0.50) FROM t1;
  }
} {1 {input to percentile_cont() is not numeric}}
do_test percentile-1.15.3 {
  catchsql {
    SELECT percentile_disc(x, 0.50) FROM t1;
  }
} {1 {input to percentile_disc() is not numeric}}
do_test percentile-1.15.4 {
  catchsql {
    SELECT median(x) FROM t1;
  }
} {1 {input to median() is not numeric}}
do_test percentile-1.16 {
  catchsql {
    ROLLBACK;
    BEGIN;
    UPDATE t1 SET x=x'3530' WHERE x IS NULL;
    SELECT percentile(x, 50) FROM t1;
  }
} {1 {input to percentile() is not numeric}}
do_test percentile-1.17 {
  catchsql {
    ROLLBACK;
    SELECT percentile(x, 50) FROM t1;
  }
} {0 8.0}

# No non-NULL entries.
#
do_test percentile-1.18 {
  execsql {
    UPDATE t1 SET x=NULL;
    SELECT ifnull(percentile(x, 50),'NULL') FROM t1
  } 
} {NULL}

# Exactly one non-NULL entry
#
do_test percentile-1.19 {
  execsql {
    UPDATE t1 SET x=12345 WHERE rowid=5;
    SELECT percentile(x, 0), percentile(x, 50), percentile(x,100) FROM t1
  } 
} {12345.0 12345.0 12345.0}

# Infinity as an input
#
do_test percentile-1.20.1 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 SELECT x+0.0 FROM t2;
    UPDATE t1 SET x=1.0e300*1.0e300 WHERE rowid=5;
    SELECT percentile(x,50) from t1;
  }
} {1 {Inf input to percentile()}}
do_test percentile-1.20.2 {
  catchsql {
    SELECT percentile_cont(x,0.50) from t1;
  }
} {1 {Inf input to percentile_cont()}}
do_test percentile-1.20.3 {
  catchsql {
    SELECT percentile_disc(x,0.50) from t1;
  }
} {1 {Inf input to percentile_disc()}}
do_test percentile-1.20.4 {
  catchsql {
    SELECT median(x) from t1;
  }
} {1 {Inf input to median()}}
ifcapable ordered_set_aggregates {
  do_test percentile-1.20.5 {
    catchsql {
      SELECT percentile(50) WITHIN GROUP (ORDER BY x) from t1;
    }
  } {1 {Inf input to percentile()}}
  do_test percentile-1.20.6 {
    catchsql {
      SELECT percentile_cont(0.50) WITHIN GROUP (ORDER BY x) from t1;
    }
  } {1 {Inf input to percentile_cont()}}
  do_test percentile-1.20.7 {
    catchsql {
      SELECT percentile_disc(0.50) WITHIN GROUP(ORDER BY X) from t1;
    }
  } {1 {Inf input to percentile_disc()}}
  do_test percentile-1.20.8 {
    catchsql {
      SELECT median() WITHIN GROUP (ORDER BY x) from t1;
    }
  } {1 {Inf input to median()}}
}
do_test percentile-1.21 {
  catchsql {
    UPDATE t1 SET x=-1.0e300*1.0e300 WHERE rowid=5;
    SELECT percentile(x,50) from t1;
  }
} {1 {Inf input to percentile()}}

# Million-row Inputs
#
ifcapable vtab {
  do_test percentile-2.0 {
    load_static_extension db wholenumber
    execsql {
      CREATE VIRTUAL TABLE nums USING wholenumber;
      CREATE TABLE t3(x);
      INSERT INTO t3 SELECT value-1 FROM nums WHERE value BETWEEN 1 AND 500000;
      INSERT INTO t3 SELECT value*10 FROM nums
                      WHERE value BETWEEN 500000 AND 999999;
      SELECT count(*) FROM t3;
    }
  } {1000000}
  foreach {in out} {
      0          0.0
    100    9999990.0
     50    2749999.5
     10      99999.9
  } {
    do_test percentile-2.1.$in {
      execsql {
        SELECT round(percentile(x, $in),1) from t3;
      }
    } $out
  }
}

# median() as a window function.  (2024-08-31)
#
do_execsql_test percentile-3.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
  INSERT INTO t1 VALUES (1, 'A', 'one',   8.4),
                        (2, 'B', 'two',   7.1),
                        (3, 'C', 'three', 5.9),
                        (4, 'D', 'one',  11.0),
                        (5, 'E', 'two',  12.5),
                        (6, 'F', 'three', 0.0),
                        (7, 'G', 'one',   2.7);
}
foreach {id oba expr} {
  1 0 "median(d)"
  2 0 "percentile(d,50)"
  3 0 "percentile_cont(d,0.5)"
  4 1 "median() WITHIN GROUP (ORDER BY d)"
  5 1 "percentile(50) WITHIN GROUP (ORDER BY d)"
  6 1 "percentile_cont(0.5) WITHIN GROUP (ORDER BY d)"
} {
  if {$oba} {
    ifcapable !ordered_set_aggregates break
  }
  set sql "SELECT a, b, c, d, \
                  group_concat(b,'.') OVER w1 AS 'elements', \
                  $expr OVER w1 AS 'median' \
            FROM t1 \
          WINDOW w1 AS (ORDER BY c, a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)"
  do_execsql_test percentile-3.$id.1 $sql {
    1  A  one    8.4   A.D       9.7   
    4  D  one    11.0  A.D.G     8.4   
    7  G  one    2.7   D.G.C     5.9   
    3  C  three  5.9   G.C.F     2.7   
    6  F  three  0.0   C.F.B     5.9   
    2  B  two    7.1   F.B.E     7.1   
    5  E  two    12.5  B.E       9.8   
  }

  set sql "SELECT a, b, c, d, \
                  group_concat(b,'.') OVER w1 AS 'elements', \
                  $expr OVER w1 AS 'median' \
            FROM t1 \
           WINDOW w1 AS (ORDER BY c, a \
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)"
  do_execsql_test percentile-3.$id.2 $sql {
    1  A  one    8.4   A.D            9.7   
    4  D  one    11.0  A.D.G          8.4   
    7  G  one    2.7   A.D.G.C        7.15  
    3  C  three  5.9   A.D.G.C.F      5.9   
    6  F  three  0.0   A.D.G.C.F.B    6.5   
    2  B  two    7.1   A.D.G.C.F.B.E  7.1   
    5  E  two    12.5  A.D.G.C.F.B.E  7.1   
  }

  set sql "SELECT a, b, c, d, \
                  group_concat(b,'.') OVER w1 AS 'elements', \
                  $expr OVER w1 AS 'median' \
            FROM t1 \
           WINDOW w1 AS (ORDER BY c, a \
               ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)"
  do_execsql_test percentile-3.$id.3 $sql {
    1  A  one    8.4   A.D.G.C.F.B.E  7.1   
    4  D  one    11.0  A.D.G.C.F.B.E  7.1   
    7  G  one    2.7   D.G.C.F.B.E    6.5   
    3  C  three  5.9   G.C.F.B.E      5.9   
    6  F  three  0.0   C.F.B.E        6.5   
    2  B  two    7.1   F.B.E          7.1   
    5  E  two    12.5  B.E            9.8   
  }
}

# Test case adapted from examples shown at
# https://database.guide/3-functions-to-calculate-the-median-in-sql/
#
do_execsql_test percential-4.0 {
  CREATE TABLE products(
    vendorId INT,
    productId INTEGER PRIMARY KEY,
    productName REAL,
    price REAL
  );
  INSERT INTO products VALUES
    (1001, 17,  'Left-handed screwdriver', 25.99),
    (1001, 49,  'Right-handed screwdriver', 25.99),
    (1001, 216, 'Long weight (blue)', 14.75),
    (1001, 31,  'Long weight (green)', 11.99),
    (1002, 37,  'Sledge hammer', 33.49),
    (1003, 7,   'Chainsaw', 245.00),
    (1003, 8,   'Straw dog box', 55.99),
    (1003, 12,  'Hammock', 11.01),
    (1004, 113, 'Teapot', 12.45),
    (1004, 117, 'Bottomless coffee mug', 9.99);
}
do_execsql_test percentile-4.1 {
  SELECT VendorId, ProductId, /* ProductName,*/ Price,
         avg(price) OVER (PARTITION BY vendorId) AS "Average",
         median(price) OVER (PARTITION BY vendorId) AS "Median"
    FROM products
   ORDER BY vendorId, productId;
} {
  1001      17         25.99  19.68    20.37 
  1001      31         11.99  19.68    20.37 
  1001      49         25.99  19.68    20.37 
  1001      216        14.75  19.68    20.37 
  1002      37         33.49  33.49    33.49 
  1003      7          245.0  104.0    55.99 
  1003      8          55.99  104.0    55.99 
  1003      12         11.01  104.0    55.99 
  1004      113        12.45  11.22    11.22 
  1004      117        9.99   11.22    11.22 
}
do_execsql_test percentile-4.2 {
  SELECT vendorId, median(price) FROM products
   GROUP BY 1 ORDER BY 1;
} {1001 20.37 1002 33.49 1003 55.99 1004 11.22}

do_execsql_test percentile-5.0 {
  CREATE TABLE user(name TEXT, class TEXT, cost REAL);
  INSERT INTO user VALUES
    ('Alice', 'Y',  3578.27),
    ('Bob',   'X',  3399.99),
    ('Cindy', 'Z',  699.10),
    ('Dave',  'Y',  3078.27),
    ('Emma',  'Z',  2319.99),
    ('Fred',  'Y',  539.99),
    ('Gina',  'X',  2320.49),
    ('Hank',  'W',  24.99),
    ('Irma',  'W',  24.99),
    ('Jake',  'X',  2234.99),
    ('Kim',   'Y',  4319.99),
    ('Liam',  'X',  4968.59),
    ('Mia',   'W',  59.53),
    ('Nate',  'W',  23.50);
}
do_execsql_test percentile-5.1 {
  SELECT name, class, cost,
    percentile(cost,   0) OVER w1 AS 'P0',
    percentile(cost,  25) OVER w1 AS 'P1',
    percentile(cost,  50) OVER w1 AS 'P2',
    percentile(cost,  75) OVER w1 AS 'P3',
    percentile(cost, 100) OVER w1 AS 'P4'
  FROM user
  WINDOW w1 AS (PARTITION BY class)
  ORDER BY class, cost;
} {
  Nate   W  23.5     23.5     24.6175    24.99     33.625     59.53  
  Hank   W  24.99    23.5     24.6175    24.99     33.625     59.53  
  Irma   W  24.99    23.5     24.6175    24.99     33.625     59.53  
  Mia    W  59.53    23.5     24.6175    24.99     33.625     59.53  
  Jake   X  2234.99  2234.99  2299.115   2860.24   3792.14    4968.59
  Gina   X  2320.49  2234.99  2299.115   2860.24   3792.14    4968.59
  Bob    X  3399.99  2234.99  2299.115   2860.24   3792.14    4968.59
  Liam   X  4968.59  2234.99  2299.115   2860.24   3792.14    4968.59
  Fred   Y  539.99   539.99   2443.7     3328.27   3763.7     4319.99
  Dave   Y  3078.27  539.99   2443.7     3328.27   3763.7     4319.99
  Alice  Y  3578.27  539.99   2443.7     3328.27   3763.7     4319.99
  Kim    Y  4319.99  539.99   2443.7     3328.27   3763.7     4319.99
  Cindy  Z  699.1    699.1    1104.3225  1509.545  1914.7675  2319.99
  Emma   Z  2319.99  699.1    1104.3225  1509.545  1914.7675  2319.99
}

# Fuzzer find.
do_execsql_test percentile-6.0 {
  WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)
  SELECT median(iif(n%2,0.1,1.0)) FROM c;
} 0.55

finish_test