github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/tkt-38cb5df375.test (about)

     1  # 2010 October 6
     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. Specifically,
    12  # it tests that ticket [38cb5df375078d3f9711482d2a1615d09f6b3f33] has
    13  # been resolved.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  ifcapable !compound {
    20    finish_test
    21    return
    22  }
    23  
    24  do_test tkt-38cb5df375.0 {
    25    execsql {
    26      CREATE TABLE t1(a);
    27      INSERT INTO t1 VALUES(1);
    28      INSERT INTO t1 VALUES(2);
    29      INSERT INTO t1 SELECT a+2 FROM t1;
    30      INSERT INTO t1 SELECT a+4 FROM t1;
    31    }
    32  } {}
    33  
    34  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
    35    do_test tkt-38cb5df375.1.$ii {
    36      execsql {
    37        SELECT * FROM (SELECT * FROM t1 ORDER BY a)
    38        UNION ALL SELECT 9 FROM (SELECT a FROM t1)
    39        LIMIT $::ii;
    40      }
    41    } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
    42  }
    43  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
    44    do_test tkt-38cb5df375.2.$ii {
    45      execsql {
    46        SELECT 9 FROM (SELECT * FROM t1)
    47        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
    48        LIMIT $::ii;
    49      }
    50    } [lrange {9 9 9 9 9 9 9 9 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
    51  }
    52  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
    53    do_test tkt-38cb5df375.3.$ii {
    54      execsql {
    55        SELECT a FROM (SELECT * FROM t1 ORDER BY a)
    56        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
    57        LIMIT $::ii;
    58      }
    59    } [lrange {1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
    60  }
    61  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
    62    do_test tkt-38cb5df375.4.$ii {
    63      execsql {
    64        SELECT 0 FROM (SELECT * FROM t1)
    65        UNION ALL SELECT 9 FROM (SELECT a FROM t1)
    66        LIMIT $::ii;
    67      }
    68    } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
    69  }
    70  foreach ii {1 2 3 4} {
    71    do_test tkt-38cb5df375.5.$ii {
    72      execsql {
    73        SELECT 0 FROM (SELECT * FROM t1)
    74        UNION SELECT 9 FROM (SELECT a FROM t1)
    75        LIMIT $::ii;
    76      }
    77    } [lrange {0 9} 0 [expr {$ii-1}]]
    78  }
    79  
    80  foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
    81    do_test tkt-38cb5df375.11.$ii {
    82      execsql {
    83        SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
    84        UNION ALL SELECT 9 FROM (SELECT a FROM t1)
    85        LIMIT $::ii;
    86      }
    87    } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
    88  }
    89  foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
    90    do_test tkt-38cb5df375.12.$ii {
    91      execsql {
    92        SELECT 9 FROM (SELECT * FROM t1)
    93        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
    94        LIMIT $::ii;
    95      }
    96    } [lrange {9 9 9 9 9 9 9 9 1 2 3} 0 [expr {$ii-1}]]
    97  }
    98  foreach ii {1 2 3 4 5 6} {
    99    do_test tkt-38cb5df375.13.$ii {
   100      execsql {
   101        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
   102        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
   103        LIMIT $::ii;
   104      }
   105    } [lrange {1 2 3 1 2 3} 0 [expr {$ii-1}]]
   106  }
   107  foreach ii {1 2 3 4 5 6} {
   108    do_test tkt-38cb5df375.14.$ii {
   109      execsql {
   110        SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
   111        UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 3)
   112        LIMIT $::ii;
   113      }
   114    } [lrange {0 0 0 9 9 9} 0 [expr {$ii-1}]]
   115  }
   116  
   117  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
   118    do_test tkt-38cb5df375.21.$ii {
   119      execsql {
   120        SELECT * FROM (SELECT * FROM t1 ORDER BY a)
   121        UNION ALL SELECT 9 FROM (SELECT a FROM t1)
   122        ORDER BY 1
   123        LIMIT $::ii;
   124      }
   125    } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
   126  }
   127  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
   128    do_test tkt-38cb5df375.22.$ii {
   129      execsql {
   130        SELECT 9 FROM (SELECT * FROM t1)
   131        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
   132        ORDER BY 1
   133        LIMIT $::ii;
   134      }
   135    } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
   136  }
   137  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
   138    do_test tkt-38cb5df375.23.$ii {
   139      execsql {
   140        SELECT a FROM (SELECT * FROM t1 ORDER BY a)
   141        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
   142        ORDER BY 1 DESC
   143        LIMIT $::ii;
   144      }
   145    } [lrange {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1} 0 [expr {$ii-1}]]
   146  }
   147  foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
   148    do_test tkt-38cb5df375.24.$ii {
   149      execsql {
   150        SELECT 0 FROM (SELECT * FROM t1)
   151        UNION ALL SELECT 9 FROM (SELECT a FROM t1)
   152        ORDER BY 1
   153        LIMIT $::ii;
   154      }
   155    } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
   156  }
   157  
   158  foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
   159    do_test tkt-38cb5df375.31.$ii {
   160      execsql {
   161        SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
   162        UNION ALL SELECT 9 FROM (SELECT a FROM t1)
   163        ORDER BY 1
   164        LIMIT $::ii;
   165      }
   166    } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
   167  }
   168  foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
   169    do_test tkt-38cb5df375.32.$ii {
   170      execsql {
   171        SELECT 9 FROM (SELECT * FROM t1)
   172        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
   173        ORDER BY 1
   174        LIMIT $::ii;
   175      }
   176    } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
   177  }
   178  foreach ii {1 2 3 4 5 6 7} {
   179    do_test tkt-38cb5df375.33.$ii {
   180      execsql {
   181        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 4)
   182        UNION ALL SELECT 90+a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
   183        ORDER BY 1
   184        LIMIT $::ii;
   185      }
   186    } [lrange {1 2 3 4 91 92 93} 0 [expr {$ii-1}]]
   187  }
   188  foreach ii {1 2 3 4 5 6 7} {
   189    do_test tkt-38cb5df375.34.$ii {
   190      execsql {
   191        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 2)
   192        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 5)
   193        ORDER BY 1
   194        LIMIT $::ii;
   195      }
   196    } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
   197  }
   198  foreach ii {1 2 3 4 5 6 7} {
   199    do_test tkt-38cb5df375.35.$ii {
   200      execsql {
   201        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
   202        UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   203        ORDER BY 1
   204        LIMIT $::ii;
   205      }
   206    } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
   207  }
   208  foreach ii {1 2 3 4 5 6 7} {
   209    do_test tkt-38cb5df375.35b.$ii {
   210      execsql {
   211        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
   212        UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   213        ORDER BY 1
   214        LIMIT $::ii;
   215      }
   216    } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
   217  }
   218  foreach ii {1 2 3 4 5 6 7} {
   219    do_test tkt-38cb5df375.35c.$ii {
   220      execsql {
   221        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
   222        UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   223        ORDER BY 1
   224        LIMIT $::ii;
   225      }
   226    } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
   227  }
   228  foreach ii {1 2 3 4 5 6 7} {
   229    do_test tkt-38cb5df375.35d.$ii {
   230      execsql {
   231        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
   232        INTERSECT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   233        ORDER BY 1
   234        LIMIT $::ii;
   235      }
   236    } [lrange {1 2} 0 [expr {$ii-1}]]
   237  }
   238  foreach ii {1 2 3 4 5 6 7} {
   239    do_test tkt-38cb5df375.35e.$ii {
   240      execsql {
   241        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
   242        EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   243        ORDER BY 1
   244        LIMIT $::ii;
   245      }
   246    } [lrange {3 4 5} 0 [expr {$ii-1}]]
   247  }
   248  foreach ii {1 2 3 4 5 6 7} {
   249    do_test tkt-38cb5df375.36.$ii {
   250      execsql {
   251        SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
   252        UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
   253        ORDER BY 1
   254        LIMIT $::ii;
   255      }
   256    } [lrange {0 0 0 9 9 9 9} 0 [expr {$ii-1}]]
   257  }
   258  foreach ii {1 2 3 4 5 6 7} {
   259    do_test tkt-38cb5df375.37.$ii {
   260      execsql {
   261        SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
   262        UNION SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
   263        ORDER BY 1
   264        LIMIT $::ii;
   265      }
   266    } [lrange {0 9} 0 [expr {$ii-1}]]
   267  }
   268  foreach ii {1 2 3 4 5 6 7} {
   269    do_test tkt-38cb5df375.38.$ii {
   270      execsql {
   271        SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
   272        EXCEPT SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
   273        ORDER BY 1
   274        LIMIT $::ii;
   275      }
   276    } [lrange {0} 0 [expr {$ii-1}]]
   277  }
   278  
   279  foreach ii {1 2 3 4 5 6 7 8 9} {
   280    do_test tkt-38cb5df375.41.$ii {
   281      execsql {
   282        SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
   283        UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
   284        UNION ALL SELECT 88 FROM (SELECT a FROM t1 LIMIT 2)
   285        ORDER BY 1
   286        LIMIT $::ii;
   287      }
   288    } [lrange {0 0 0 9 9 9 9 88 88} 0 [expr {$ii-1}]]
   289  }
   290  foreach ii {1 2 3 4 5 6 7 8 9} {
   291    do_test tkt-38cb5df375.42.$ii {
   292      execsql {
   293        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
   294        UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
   295        UNION ALL SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   296        ORDER BY 1
   297        LIMIT $::ii;
   298      }
   299    } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
   300  }
   301  foreach ii {1 2 3 4 5 6 7 8 9} {
   302    do_test tkt-38cb5df375.43.$ii {
   303      execsql {
   304        SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
   305        UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
   306        UNION SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
   307        ORDER BY 1
   308        LIMIT $::ii;
   309      }
   310    } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
   311  }
   312  
   313  foreach ii {1 2 3 4 5 6 7} {
   314    set jj [expr {7-$ii}]
   315    do_test tkt-38cb5df375.51.$ii {
   316      execsql {
   317        SELECT a FROM (SELECT * FROM t1 ORDER BY a)
   318        EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT $::ii)
   319        ORDER BY a DESC
   320        LIMIT $::jj;
   321      }
   322    } [lrange {8 7 6 5 4 3 2 1} 0 [expr {$jj-1}]]
   323  }
   324  
   325  
   326  finish_test