1 # 2003 December 17

     2 #

     3 # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

     4 #

     5 # The author disclaims copyright to this source code.  In place of

     6 # a legal notice, here is a blessing:

     7 #

     8 #    May you do good and not evil.

     9 #    May you find forgiveness for yourself and forgive others.

    10 #    May you share freely, never taking more than you give.

    11 #

    12 #***********************************************************************

    13 # This file implements regression tests for SQLite library.

    14 #

    15 # This file implements tests for miscellanous features that were

    16 # left out of other test files.

    17 #

    18 # $Id: misc3.test,v 1.19 2008/06/25 02:47:57 drh Exp $

    19 

    20 set testdir [file dirname $argv0]

    21 source $testdir/tester.tcl

    22 

    23 ifcapable {integrityck} {

    24   # Ticket #529.  Make sure an ABORT does not damage the in-memory cache

    25   # that will be used by subsequent statements in the same transaction.

    26   #

    27   do_test misc3-1.1 {

    28     execsql {

    29       CREATE TABLE t1(a UNIQUE,b);

    30       INSERT INTO t1

    31         VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');

    32       UPDATE t1 SET b=b||b;

    33       UPDATE t1 SET b=b||b;

    34       UPDATE t1 SET b=b||b;

    35       UPDATE t1 SET b=b||b;

    36       UPDATE t1 SET b=b||b;

    37       INSERT INTO t1 VALUES(2,'x');

    38       UPDATE t1 SET b=substr(b,1,500);

    39       BEGIN;

    40     }

    41     catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}

    42     execsql {

    43       CREATE TABLE t2(x,y);

    44       COMMIT;

    45       PRAGMA integrity_check;

    46     }

    47   } ok

    48 }

    49 ifcapable {integrityck} {

    50   do_test misc3-1.2 {

    51     execsql {

    52       DROP TABLE t1;

    53       DROP TABLE t2;

    54     }

    55     ifcapable {vacuum} {execsql VACUUM}

    56     execsql {

    57       CREATE TABLE t1(a UNIQUE,b);

    58       INSERT INTO t1

    59       VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');

    60       INSERT INTO t1 SELECT a+1, b||b FROM t1;

    61       INSERT INTO t1 SELECT a+2, b||b FROM t1;

    62       INSERT INTO t1 SELECT a+4, b FROM t1;

    63       INSERT INTO t1 SELECT a+8, b FROM t1;

    64       INSERT INTO t1 SELECT a+16, b FROM t1;

    65       INSERT INTO t1 SELECT a+32, b FROM t1;

    66       INSERT INTO t1 SELECT a+64, b FROM t1;

    67       BEGIN;

    68     }

    69     catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}

    70     execsql {

    71       INSERT INTO t1 VALUES(200,'hello out there');

    72       COMMIT;

    73       PRAGMA integrity_check;

    74     }

    75   } ok

    76 }

    77 

    78 # Tests of the sqliteAtoF() function in util.c

    79 #

    80 do_test misc3-2.1 {

    81   execsql {SELECT 2e-25*0.5e25}

    82 } 1.0

    83 do_test misc3-2.2 {

    84   execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}

    85 } 1.0

    86 do_test misc3-2.3 {

    87   execsql {SELECT 000000000002e-0000000025*0.5e25}

    88 } 1.0

    89 do_test misc3-2.4 {

    90   execsql {SELECT 2e-25*0.5e250}

    91 } 1e+225

    92 do_test misc3-2.5 {

    93   execsql {SELECT 2.0e-250*0.5e25}

    94 } 1e-225

    95 #

    96 # Symbian OS: the next test fails due to problems in sprintf/printf formatting. 

    97 if {$::tcl_platform(platform)!="symbian"} {

    98   do_test misc3-2.6 {

    99     execsql {SELECT '-2.0e-127' * '-0.5e27'}

   100   } 1e-100

   101   do_test misc3-2.7 {

   102     execsql {SELECT '+2.0e-127' * '-0.5e27'}

   103   } -1e-100

   104 }  

   105 do_test misc3-2.8 {

   106   execsql {SELECT 2.0e-27 * '+0.5e+127'}

   107 } 1e+100

   108 do_test misc3-2.9 {

   109   execsql {SELECT 2.0e-27 * '+0.000005e+132'}

   110 } 1e+100

   111 

   112 # Ticket #522.  Make sure integer overflow is handled properly in

   113 # indices.

   114 #

   115 integrity_check misc3-3.1

   116 do_test misc3-3.2 {

   117   execsql {

   118     CREATE TABLE t2(a INT UNIQUE);

   119   }

   120 } {}

   121 integrity_check misc3-3.2.1

   122 do_test misc3-3.3 {

   123   execsql {

   124     INSERT INTO t2 VALUES(2147483648);

   125   }

   126 } {}

   127 integrity_check misc3-3.3.1

   128 do_test misc3-3.4 {

   129   execsql {

   130     INSERT INTO t2 VALUES(-2147483649);

   131   }

   132 } {}

   133 integrity_check misc3-3.4.1

   134 do_test misc3-3.5 {

   135   execsql {

   136     INSERT INTO t2 VALUES(+2147483649);

   137   }

   138 } {}

   139 integrity_check misc3-3.5.1

   140 do_test misc3-3.6 {

   141   execsql {

   142     INSERT INTO t2 VALUES(+2147483647);

   143     INSERT INTO t2 VALUES(-2147483648);

   144     INSERT INTO t2 VALUES(-2147483647);

   145     INSERT INTO t2 VALUES(2147483646);

   146     SELECT * FROM t2 ORDER BY a;

   147   }

   148 } {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}

   149 do_test misc3-3.7 {

   150   execsql {

   151     SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a;

   152   }

   153 } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}

   154 do_test misc3-3.8 {

   155   execsql {

   156     SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a;

   157   }

   158 } {-2147483647 2147483646 2147483647 2147483648 2147483649}

   159 do_test misc3-3.9 {

   160   execsql {

   161     SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a;

   162   }

   163 } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}

   164 do_test misc3-3.10 {

   165   execsql {

   166     SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC;

   167   }

   168 } {2147483648 2147483647 2147483646}

   169 do_test misc3-3.11 {

   170   execsql {

   171     SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC;

   172   }

   173 } {2147483648 2147483647 2147483646}

   174 do_test misc3-3.12 {

   175   execsql {

   176     SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC;

   177   }

   178 } {2147483647 2147483646}

   179 do_test misc3-3.13 {

   180   execsql {

   181     SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC;

   182   }

   183 } {2147483647 2147483646}

   184 do_test misc3-3.14 {

   185   execsql {

   186     SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;

   187   }

   188 } {2147483646}

   189 

   190 # Ticket #565.  A stack overflow is occurring when the subquery to the

   191 # right of an IN operator contains many NULLs

   192 #

   193 do_test misc3-4.1 {

   194   execsql {

   195     CREATE TABLE t3(a INTEGER PRIMARY KEY, b);

   196     INSERT INTO t3(b) VALUES('abc');

   197     INSERT INTO t3(b) VALUES('xyz');

   198     INSERT INTO t3(b) VALUES(NULL);

   199     INSERT INTO t3(b) VALUES(NULL);

   200     INSERT INTO t3(b) SELECT b||'d' FROM t3;

   201     INSERT INTO t3(b) SELECT b||'e' FROM t3;

   202     INSERT INTO t3(b) SELECT b||'f' FROM t3;

   203     INSERT INTO t3(b) SELECT b||'g' FROM t3;

   204     INSERT INTO t3(b) SELECT b||'h' FROM t3;

   205     SELECT count(a), count(b) FROM t3;

   206   }

   207 } {128 64}

   208 ifcapable subquery {

   209 do_test misc3-4.2 {

   210     execsql {

   211       SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);

   212     }

   213   } {64}

   214   do_test misc3-4.3 {

   215     execsql {

   216       SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);

   217     }

   218   } {64}

   219 }

   220 

   221 # Ticket #601:  Putting a left join inside "SELECT * FROM (<join-here>)"

   222 # gives different results that if the outer "SELECT * FROM ..." is omitted.

   223 #

   224 ifcapable subquery {

   225   do_test misc3-5.1 {

   226     execsql {

   227       CREATE TABLE x1 (b, c);

   228       INSERT INTO x1 VALUES('dog',3);

   229       INSERT INTO x1 VALUES('cat',1);

   230       INSERT INTO x1 VALUES('dog',4);

   231       CREATE TABLE x2 (c, e);

   232       INSERT INTO x2 VALUES(1,'one');

   233       INSERT INTO x2 VALUES(2,'two');

   234       INSERT INTO x2 VALUES(3,'three');

   235       INSERT INTO x2 VALUES(4,'four');

   236       SELECT x2.c AS c, e, b FROM x2 LEFT JOIN

   237          (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)

   238          USING(c);

   239     }

   240   } {1 one cat 2 two {} 3 three {} 4 four dog}

   241   do_test misc3-5.2 {

   242     execsql {

   243       SELECT * FROM (

   244         SELECT x2.c AS c, e, b FROM x2 LEFT JOIN

   245            (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)

   246            USING(c)

   247       );

   248     }

   249   } {1 one cat 2 two {} 3 three {} 4 four dog}

   250 }

   251 

   252 ifcapable {explain} {

   253   # Ticket #626:  make sure EXPLAIN prevents BEGIN and COMMIT from working.

   254   #

   255   do_test misc3-6.1 {

   256     execsql {EXPLAIN BEGIN}

   257     catchsql {BEGIN}

   258   } {0 {}}

   259   do_test misc3-6.2 {

   260     execsql {EXPLAIN COMMIT}

   261     catchsql {COMMIT}

   262   } {0 {}}

   263   do_test misc3-6.3 {

   264     execsql {BEGIN; EXPLAIN ROLLBACK}

   265     catchsql {ROLLBACK}

   266   } {0 {}}

   267 

   268   # Do some additional EXPLAIN operations to exercise the displayP4 logic.

   269   do_test misc3-6.10 {

   270     set x [execsql {

   271       CREATE TABLE ex1(

   272         a INTEGER DEFAULT 54321,

   273         b TEXT DEFAULT "hello",

   274         c REAL DEFAULT 3.1415926

   275       );

   276       CREATE UNIQUE INDEX ex1i1 ON ex1(a);

   277       EXPLAIN REINDEX;

   278     }]

   279     regexp { IsUnique \d+ \d+ \d+ \d+ } $x

   280   } {1}

   281   do_test misc3-6.11 {

   282     set x [execsql {

   283       EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC

   284     }]

   285     set y [regexp { 123456789012 } $x]

   286     lappend y [regexp { 4.5678 } $x]

   287     lappend y [regexp { hello } $x]

   288     lappend y [regexp {,-BINARY} $x]

   289   } {1 1 1 1}

   290 

   291 }

   292 

   293 ifcapable {trigger} {

   294 # Ticket #640:  vdbe stack overflow with a LIMIT clause on a SELECT inside

   295 # of a trigger.

   296 #

   297 do_test misc3-7.1 {

   298   execsql {

   299     BEGIN;

   300     CREATE TABLE y1(a);

   301     CREATE TABLE y2(b);

   302     CREATE TABLE y3(c);

   303     CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN

   304       INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;

   305     END;

   306     INSERT INTO y1 VALUES(1);

   307     INSERT INTO y1 VALUES(2);

   308     INSERT INTO y1 SELECT a+2 FROM y1;

   309     INSERT INTO y1 SELECT a+4 FROM y1;

   310     INSERT INTO y1 SELECT a+8 FROM y1;

   311     INSERT INTO y1 SELECT a+16 FROM y1;

   312     INSERT INTO y2 SELECT a FROM y1;

   313     COMMIT;

   314     SELECT count(*) FROM y1;

   315   }

   316 } 32

   317 do_test misc3-7.2 {

   318   execsql {

   319     DELETE FROM y1;

   320     SELECT count(*) FROM y1;

   321   }

   322 } 0

   323 do_test misc3-7.3 {

   324   execsql {

   325     SELECT count(*) FROM y3;

   326   }

   327 } 32

   328 } ;# endif trigger

   329 

   330 # Ticket #668:  VDBE stack overflow occurs when the left-hand side

   331 # of an IN expression is NULL and the result is used as an integer, not

   332 # as a jump.

   333 #

   334 ifcapable subquery {

   335   do_test misc-8.1 {

   336     execsql {

   337       SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3

   338     }

   339   } {2}

   340   do_test misc-8.2 {

   341     execsql {

   342       SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2

   343     }

   344   } {2}

   345 }

   346 

   347 finish_test