File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / tkt-38cb5df375.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

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

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>