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

     1  # 2014 January 11
     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 WITH clause.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  source $testdir/malloc_common.tcl
    18  set ::testprefix withM
    19  
    20  ifcapable {!cte} {
    21    finish_test
    22    return
    23  }
    24  
    25  do_execsql_test 1.0 {
    26    CREATE TABLE t1(x INTEGER, y INTEGER);
    27    INSERT INTO t1 VALUES(123, 456);
    28  }
    29  
    30  do_faultsim_test withM-1.1 -prep {
    31    sqlite3 db test.db
    32  } -body {
    33    execsql { 
    34      WITH tmp AS ( SELECT * FROM t1 )
    35      SELECT * FROM tmp;
    36    }
    37  } -test {
    38    faultsim_test_result {0 {123 456}}
    39    db close
    40  }
    41  
    42  do_faultsim_test withM-1.2 -prep {
    43    sqlite3 db test.db
    44  } -body {
    45    execsql { 
    46      WITH w1 AS ( SELECT * FROM t1 ),
    47           w2 AS ( 
    48             WITH w3 AS ( SELECT * FROM w1 )
    49             SELECT * FROM w3
    50           )
    51      SELECT * FROM w2;
    52    }
    53  } -test {
    54    faultsim_test_result {0 {123 456}}
    55    db close
    56  }
    57  
    58  do_faultsim_test withM-1.3 -prep {
    59    sqlite3 db test.db
    60  } -body {
    61    execsql { 
    62      WITH w1(a,b) AS ( 
    63        SELECT 1, 1
    64        UNION ALL
    65        SELECT a+1, b + 2*a + 1 FROM w1
    66      )
    67      SELECT * FROM w1 LIMIT 5;
    68    }
    69  } -test {
    70    faultsim_test_result {0 {1 1 2 4 3 9 4 16 5 25}}
    71    db close
    72  }
    73  
    74  finish_test