modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/minmax4.test (about)

     1  # 2012 February 02
     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  #
    12  # Test for queries of the form:  
    13  #
    14  #    SELECT p, max(q) FROM t1;
    15  #
    16  # Demonstration that the value returned for p is on the same row as 
    17  # the maximum q.
    18  #
    19  
    20  set testdir [file dirname $argv0]
    21  source $testdir/tester.tcl
    22  
    23  ifcapable !compound {
    24    finish_test
    25    return
    26  }
    27  
    28  do_test minmax4-1.1 {
    29    db eval {
    30      CREATE TABLE t1(p,q);
    31      SELECT p, max(q) FROM t1;
    32    }
    33  } {{} {}}
    34  do_test minmax4-1.2 {
    35    db eval {
    36      SELECT p, min(q) FROM t1;
    37    }
    38  } {{} {}}
    39  do_test minmax4-1.3 {
    40    db eval {
    41      INSERT INTO t1 VALUES(1,2);
    42      SELECT p, max(q) FROM t1;
    43    }
    44  } {1 2}
    45  do_test minmax4-1.4 {
    46    db eval {
    47      SELECT p, min(q) FROM t1;
    48    }
    49  } {1 2}
    50  do_test minmax4-1.5 {
    51    db eval {
    52      INSERT INTO t1 VALUES(3,4);
    53      SELECT p, max(q) FROM t1;
    54    }
    55  } {3 4}
    56  do_test minmax4-1.6 {
    57    db eval {
    58      SELECT p, min(q) FROM t1;
    59      SELECT p FROM (SELECT p, min(q) FROM t1);
    60    }
    61  } {1 2 1}
    62  do_test minmax4-1.7 {
    63    db eval {
    64      INSERT INTO t1 VALUES(5,0);
    65      SELECT p, max(q) FROM t1;
    66      SELECT p FROM (SELECT max(q), p FROM t1);
    67    }
    68  } {3 4 3}
    69  do_test minmax4-1.8 {
    70    db eval {
    71      SELECT p, min(q) FROM t1;
    72    }
    73  } {5 0}
    74  do_test minmax4-1.9 {
    75    db eval {
    76      INSERT INTO t1 VALUES(6,1);
    77      SELECT p, max(q) FROM t1;
    78      SELECT p FROM (SELECT max(q), p FROM t1);
    79    }
    80  } {3 4 3}
    81  do_test minmax4-1.10 {
    82    db eval {
    83      SELECT p, min(q) FROM t1;
    84    }
    85  } {5 0}
    86  do_test minmax4-1.11 {
    87    db eval {
    88      INSERT INTO t1 VALUES(7,NULL);
    89      SELECT p, max(q) FROM t1;
    90    }
    91  } {3 4}
    92  do_test minmax4-1.12 {
    93    db eval {
    94      SELECT p, min(q) FROM t1;
    95    }
    96  } {5 0}
    97  do_test minmax4-1.13 {
    98    db eval {
    99      DELETE FROM t1 WHERE q IS NOT NULL;
   100      SELECT p, max(q) FROM t1;
   101    }
   102  } {7 {}}
   103  do_test minmax4-1.14 {
   104    db eval {
   105      SELECT p, min(q) FROM t1;
   106    }
   107  } {7 {}}
   108  
   109  do_test minmax4-2.1 {
   110    db eval {
   111      CREATE TABLE t2(a,b,c);
   112      INSERT INTO t2 VALUES
   113           (1,null,2),
   114           (1,2,3),
   115           (1,1,4),
   116           (2,3,5);
   117      SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a;
   118    }
   119  } {1 2 3 2 3 5}
   120  do_test minmax4-2.2 {
   121    db eval {
   122      SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a;
   123    }
   124  } {1 1 4 2 3 5}
   125  do_test minmax4-2.3 {
   126    db eval {
   127      SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC;
   128    }
   129  } {2 3 3.0 1 5 1 1 1.5 2 4}
   130  do_test minmax4-2.4 {
   131    db eval {
   132      SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a;
   133    }
   134  } {1 1 2 3 2 3 3 5}
   135  do_test minmax4-2.5 {
   136    db eval {
   137      SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a;
   138    }
   139  } {1 2 1 4 2 3 3 5}
   140  do_test minmax4-2.6 {
   141    db eval {
   142      SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a;
   143    }
   144  } {1 2 1 4 4 2 3 3 5 5}
   145  do_test minmax4-2.7 {
   146    db eval {
   147      SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
   148    }
   149  } {1 1 {} 2 2 2 3 3 5 5}
   150  
   151  
   152  
   153  finish_test