gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/contrib01.test (about)

     1  # 2013-06-05
     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.
    12  #
    13  # This file contains test cases that were contributed on the sqlite-users
    14  # mailing list on 2013-06-05 by Mi Chen at mi.chen@echostar.com.
    15  #
    16  # At the time it was contributed, this test failed on trunk, but 
    17  # worked on the NGQP.
    18  
    19  set testdir [file dirname $argv0]
    20  source $testdir/tester.tcl
    21  
    22  # Build some test data
    23  #
    24  do_test contrib01-1.0 {
    25    db eval {
    26      CREATE TABLE T1 (B INTEGER NOT NULL,
    27                       C INTEGER NOT NULL,
    28                       D INTEGER NOT NULL,
    29                       E INTEGER NOT NULL,
    30                       F INTEGER NOT NULL,
    31                       G INTEGER NOT NULL,
    32                       H INTEGER NOT NULL,
    33                       PRIMARY KEY (B, C, D));
    34      
    35      CREATE TABLE T2 (A INTEGER NOT NULL,
    36                       B INTEGER NOT NULL,
    37                       C INTEGER NOT NULL,
    38                       PRIMARY KEY (A, B, C));
    39      
    40      INSERT INTO T2(A, B, C) VALUES(702118,16183,15527);
    41      INSERT INTO T2(A, B, C) VALUES(702118,16183,15560);
    42      INSERT INTO T2(A, B, C) VALUES(702118,16183,15561);
    43      INSERT INTO T2(A, B, C) VALUES(702118,16183,15563);
    44      INSERT INTO T2(A, B, C) VALUES(702118,16183,15564);
    45      INSERT INTO T2(A, B, C) VALUES(702118,16183,15566);
    46      INSERT INTO T2(A, B, C) VALUES(702118,16183,15567);
    47      INSERT INTO T2(A, B, C) VALUES(702118,16183,15569);
    48      INSERT INTO T2(A, B, C) VALUES(702118,16183,15612);
    49      INSERT INTO T2(A, B, C) VALUES(702118,16183,15613);
    50      INSERT INTO T2(A, B, C) VALUES(702118,16183,15638);
    51      INSERT INTO T2(A, B, C) VALUES(702118,16183,15681);
    52      INSERT INTO T2(A, B, C) VALUES(702118,16183,15682);
    53      
    54      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15527,6,0,5,5,0);
    55      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15560,6,0,5,2,0);
    56      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15561,6,0,5,2,0);
    57      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15563,6,0,5,2,0);
    58      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15564,6,0,5,2,0);
    59      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15566,6,0,5,2,0);
    60      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15567,6,0,5,2,0);
    61      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15569,6,0,5,2,0);
    62      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15612,6,0,5,5,0);
    63      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15613,6,0,5,2,0);
    64      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15638,6,0,5,2,0);
    65      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15681,6,0,5,5,0);
    66      INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15682,6,0,5,2,0);
    67    }
    68  } {}
    69  do_test contrib01-1.1 {
    70    db eval {
    71      SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C), '^'
    72        FROM T1, T2
    73       WHERE T1.B = T2.B
    74         AND T1.C = T2.C
    75       GROUP BY T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H
    76       ORDER BY +max(t1.c);
    77    }
    78  } {702118 16183 6 0 5 5 0 15681 ^ 702118 16183 6 0 5 2 0 15682 ^}
    79  do_test contrib01-1.2 {
    80    db eval {
    81     SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C), '^'
    82       FROM T1, T2
    83      WHERE T1.B = T2.B
    84        AND T1.C = T2.C
    85      GROUP BY T2.A, T2.B, T1.F, T1.D, T1.E, T1.G, T1.H
    86      ORDER BY +max(t1.c);
    87    }
    88  } {702118 16183 6 0 5 5 0 15681 ^ 702118 16183 6 0 5 2 0 15682 ^}
    89  
    90  finish_test