test/windowpushd.test

   1 # 2021 February 23
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 # This file implements regression tests for SQLite library.  The
  12 # focus of this file is testing the push-down optimization when
  13 # WHERE constraints are pushed down into a sub-query that uses
  14 # window functions.
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19 set testprefix windowpushd
  20
  21 do_execsql_test 1.0 {
  22   CREATE TABLE t1(id INTEGER PRIMARY KEY, grp_id);
  23   CREATE INDEX i1 ON t1(grp_id);
  24   CREATE VIEW lll AS SELECT
  25     row_number() OVER (PARTITION BY grp_id),
  26     grp_id, id
  27   FROM t1
  28 }
  29
  30 do_execsql_test 1.1 {
  31   INSERT INTO t1 VALUES
  32     (1, 2), (2, 3), (3, 3), (4, 1), (5, 1),
  33     (6, 1), (7, 1), (8, 1), (9, 3), (10, 3),
  34     (11, 2), (12, 3), (13, 3), (14, 2), (15, 1),
  35     (16, 2), (17, 1), (18, 2), (19, 3), (20, 2)
  36 }
  37
  38 do_execsql_test 1.2 {
  39   SELECT * FROM lll
  40 } {
  41   1 1 4 2 1 5 3 1 6 4 1 7 5 1 8 6 1 15 7 1 17
  42   1 2 1 2 2 11 3 2 14 4 2 16 5 2 18 6 2 20
  43   1 3 2 2 3 3 3 3 9 4 3 10 5 3 12 6 3 13 7 3 19
  44 }
  45
  46 do_execsql_test 1.3 {
  47   SELECT * FROM lll WHERE grp_id=2
  48 } {
  49   1 2 1 2 2 11 3 2 14 4 2 16 5 2 18 6 2 20
  50 }
  51
  52 do_eqp_test 1.4 {
  53   SELECT * FROM lll WHERE grp_id=2
  54 } {SEARCH t1 USING COVERING INDEX i1 (grp_id=?)}
  55
  56 #-------------------------------------------------------------------------
  57 reset_db
  58 do_execsql_test 2.0 {
  59   CREATE TABLE t1(a, b, c, d);
  60   INSERT INTO t1 VALUES('A', 'C', 1,  0.1);
  61   INSERT INTO t1 VALUES('A', 'D', 2,  0.2);
  62   INSERT INTO t1 VALUES('A', 'E', 3,  0.3);
  63   INSERT INTO t1 VALUES('A', 'C', 4,  0.4);
  64   INSERT INTO t1 VALUES('B', 'D', 5,  0.5);
  65   INSERT INTO t1 VALUES('B', 'E', 6,  0.6);
  66   INSERT INTO t1 VALUES('B', 'C', 7,  0.7);
  67   INSERT INTO t1 VALUES('B', 'D', 8,  0.8);
  68   INSERT INTO t1 VALUES('C', 'E', 9,  0.9);
  69   INSERT INTO t1 VALUES('C', 'C', 10, 1.0);
  70   INSERT INTO t1 VALUES('C', 'D', 11, 1.1);
  71   INSERT INTO t1 VALUES('C', 'E', 12, 1.2);
  72
  73   CREATE INDEX i1 ON t1(a);
  74   CREATE INDEX i2 ON t1(b);
  75
  76   CREATE VIEW v1 AS SELECT a, c, max(c) OVER (PARTITION BY a) FROM t1;
  77
  78   CREATE VIEW v2 AS SELECT a, c,
  79       max(c) OVER (PARTITION BY a),
  80       row_number() OVER ()
  81   FROM t1;
  82
  83   CREATE VIEW v3 AS SELECT b, d,
  84       max(d) OVER (PARTITION BY b),
  85       row_number() OVER (PARTITION BY b)
  86   FROM t1;
  87
  88   CREATE TABLE t2(x, y, z);
  89   INSERT INTO t2 VALUES('W', 3, 1);
  90   INSERT INTO t2 VALUES('W', 2, 2);
  91   INSERT INTO t2 VALUES('X', 1, 4);
  92   INSERT INTO t2 VALUES('X', 5, 7);
  93   INSERT INTO t2 VALUES('Y', 1, 9);
  94   INSERT INTO t2 VALUES('Y', 4, 2);
  95   INSERT INTO t2 VALUES('Z', 3, 3);
  96   INSERT INTO t2 VALUES('Z', 3, 4);
  97 }
  98
  99 foreach tn {0 1} {
 100   optimization_control db push-down $tn
 101
 102   do_execsql_test 2.$tn.1.1 {
 103     SELECT * FROM v1;
 104   } {
 105     A 1 4   A 2 4   A 3 4   A 4 4
 106     B 5 8   B 6 8   B 7 8   B 8 8
 107     C 9 12  C 10 12 C 11 12 C 12 12
 108   }
 109
 110   do_execsql_test 2.$tn.1.2 {
 111     SELECT * FROM v1 WHERE a IN ('A', 'B');
 112   } {
 113     A 1 4   A 2 4   A 3 4   A 4 4
 114     B 5 8   B 6 8   B 7 8   B 8 8
 115   }
 116
 117   do_execsql_test 2.$tn.1.3 {
 118     SELECT * FROM v1 WHERE a IS 'C'
 119   } {
 120     C 9 12  C 10 12 C 11 12 C 12 12
 121   }
 122
 123   if {$tn==1} {
 124     do_eqp_test 2.$tn.1.4 {
 125       SELECT * FROM v1 WHERE a IN ('A', 'B');
 126     } {USING INDEX i1 (a=?)}
 127
 128     do_eqp_test 2.$tn.1.5 {
 129       SELECT * FROM v1 WHERE a = 'c' COLLATE nocase
 130     } {USING INDEX i1}
 131   }
 132
 133   do_execsql_test 2.$tn.2.1 {
 134     SELECT * FROM v2;
 135   } {
 136     A 1 4 1    A 2 4 2     A 3 4 3      A 4 4 4
 137     B 5 8 5    B 6 8 6     B 7 8 7      B 8 8 8
 138     C 9 12 9   C 10 12 10  C 11 12 11   C 12 12 12
 139   }
 140
 141   do_execsql_test 2.$tn.2.2 {
 142     SELECT * FROM v2 WHERE a = 'C';
 143   } {
 144     C 9 12 9   C 10 12 10  C 11 12 11   C 12 12 12
 145   }
 146
 147   do_execsql_test 2.$tn.3.1 { SELECT * FROM v3; } {
 148     C 0.1 1.0 1 C 0.4 1.0 2 C 0.7 1.0 3 C 1.0 1.0 4
 149     D 0.2 1.1 1 D 0.5 1.1 2 D 0.8 1.1 3 D 1.1 1.1 4
 150     E 0.3 1.2 1 E 0.6 1.2 2 E 0.9 1.2 3 E 1.2 1.2 4
 151   }
 152
 153   do_execsql_test 2.$tn.3.2 { SELECT * FROM v3 WHERE b<'E' } {
 154     C 0.1 1.0 1 C 0.4 1.0 2 C 0.7 1.0 3 C 1.0 1.0 4
 155     D 0.2 1.1 1 D 0.5 1.1 2 D 0.8 1.1 3 D 1.1 1.1 4
 156   }
 157
 158   if {$tn==1} {
 159     do_eqp_test 2.$tn.3.3 {
 160       SELECT * FROM v3 WHERE b='E'
 161     } {SEARCH t1 USING INDEX i2 (b=?)}
 162     do_eqp_test 2.$tn.3.4 {
 163       SELECT * FROM v3 WHERE b>'C'
 164     } {SEARCH t1 USING INDEX i2 (b>?)}
 165   }
 166
 167   do_execsql_test 2.$tn.3.5 { SELECT * FROM v3 WHERE d<0.55; } {
 168     C 0.1 1.0 1 C 0.4 1.0 2
 169     D 0.2 1.1 1 D 0.5 1.1 2
 170     E 0.3 1.2 1
 171   }
 172   if {$tn==1} {
 173     do_eqp_test 2.$tn.3.6 {
 174       SELECT * FROM v3 WHERE d<0.55
 175     } {SCAN t1 USING INDEX i2}
 176   }
 177
 178   do_execsql_test 2.$tn.4.1 {
 179     SELECT * FROM (
 180       SELECT x, sum(y) AS s, max(z) AS m
 181       FROM t2 GROUP BY x
 182     )
 183   } {
 184     W 5 2
 185     X 6 7
 186     Y 5 9
 187     Z 6 4
 188   }
 189
 190   do_execsql_test 2.$tn.4.1 {
 191     SELECT * FROM (
 192       SELECT x, sum(y) AS s, max(z) AS m,
 193         max( max(z) ) OVER (PARTITION BY sum(y)
 194             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 195         )
 196       FROM t2 GROUP BY x
 197     )
 198   } {
 199     W 5 2   9
 200     Y 5 9   9
 201     X 6 7   7
 202     Z 6 4   7
 203   }
 204
 205   do_execsql_test 2.$tn.4.2 {
 206     SELECT * FROM (
 207       SELECT x, sum(y) AS s, max(z) AS m,
 208         max( max(z) ) OVER (PARTITION BY sum(y)
 209             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 210         )
 211       FROM t2 GROUP BY x
 212     ) WHERE s=6
 213   } {
 214     X 6 7   7
 215     Z 6 4   7
 216   }
 217
 218   do_execsql_test 2.$tn.4.3 {
 219     SELECT * FROM (
 220       SELECT x, sum(y) AS s, max(z) AS m,
 221         max( max(z) ) OVER (PARTITION BY sum(y)
 222             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 223         )
 224       FROM t2 GROUP BY x
 225     ) WHERE s<6
 226   } {
 227     W 5 2   9
 228     Y 5 9   9
 229   }
 230
 231 }
 232
 233
 234
 235
 236 finish_test