test/misc5.test

# 2005 Mar 16
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.5 2005/08/23 11:31:26 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#
# This test causes an assertion failure or a buffer overrun in version
# 3.1.5 and earlier.
#
for {set i 120} {$i<140} {incr i} {
  do_test misc5-1.$i {
    catchsql {DROP TABLE t1}
    set sql1 {CREATE TABLE t1}
    set sql2 {INSERT INTO t1 VALUES}
    set sep (
    for {set j 0} {$j<$i} {incr j} {
      append sql1 ${sep}a$j
      append sql2 ${sep}$j
      set sep ,
    }
    append sql1 {);}
    append sql2 {);}
    execsql $sql1$sql2
  } {}
}

# Make sure large integers are stored correctly.
#
do_test misc5-2.1 {
  execsql {
    create table t2(x unique);
    insert into t2 values(1);
    insert or ignore into t2 select x*2 from t2;
    insert or ignore into t2 select x*4 from t2;
    insert or ignore into t2 select x*16 from t2;
    insert or ignore into t2 select x*256 from t2;
    insert or ignore into t2 select x*65536 from t2;
    insert or ignore into t2 select x*2147483648 from t2;
    insert or ignore into t2 select x-1 from t2;
    insert or ignore into t2 select x+1 from t2;
    insert or ignore into t2 select -x from t2;
    select count(*) from t2;
  }
} 371
do_test misc5-2.2 {
  execsql {
    select x from t2 order by x;
  }
} \
"-4611686018427387905\
-4611686018427387904\
-4611686018427387903\
-2305843009213693953\
-2305843009213693952\
-2305843009213693951\
-1152921504606846977\
-1152921504606846976\
-1152921504606846975\
-576460752303423489\
-576460752303423488\
-576460752303423487\
-288230376151711745\
-288230376151711744\
-288230376151711743\
-144115188075855873\
-144115188075855872\
-144115188075855871\
-72057594037927937\
-72057594037927936\
-72057594037927935\
-36028797018963969\
-36028797018963968\
-36028797018963967\
-18014398509481985\
-18014398509481984\
-18014398509481983\
-9007199254740993\
-9007199254740992\
-9007199254740991\
-4503599627370497\
-4503599627370496\
-4503599627370495\
-2251799813685249\
-2251799813685248\
-2251799813685247\
-1125899906842625\
-1125899906842624\
-1125899906842623\
-562949953421313\
-562949953421312\
-562949953421311\
-281474976710657\
-281474976710656\
-281474976710655\
-140737488355329\
-140737488355328\
-140737488355327\
-70368744177665\
-70368744177664\
-70368744177663\
-35184372088833\
-35184372088832\
-35184372088831\
-17592186044417\
-17592186044416\
-17592186044415\
-8796093022209\
-8796093022208\
-8796093022207\
-4398046511105\
-4398046511104\
-4398046511103\
-2199023255553\
-2199023255552\
-2199023255551\
-1099511627777\
-1099511627776\
-1099511627775\
-549755813889\
-549755813888\
-549755813887\
-274877906945\
-274877906944\
-274877906943\
-137438953473\
-137438953472\
-137438953471\
-68719476737\
-68719476736\
-68719476735\
-34359738369\
-34359738368\
-34359738367\
-17179869185\
-17179869184\
-17179869183\
-8589934593\
-8589934592\
-8589934591\
-4294967297\
-4294967296\
-4294967295\
-2147483649\
-2147483648\
-2147483647\
-1073741825\
-1073741824\
-1073741823\
-536870913\
-536870912\
-536870911\
-268435457\
-268435456\
-268435455\
-134217729\
-134217728\
-134217727\
-67108865\
-67108864\
-67108863\
-33554433\
-33554432\
-33554431\
-16777217\
-16777216\
-16777215\
-8388609\
-8388608\
-8388607\
-4194305\
-4194304\
-4194303\
-2097153\
-2097152\
-2097151\
-1048577\
-1048576\
-1048575\
-524289\
-524288\
-524287\
-262145\
-262144\
-262143\
-131073\
-131072\
-131071\
-65537\
-65536\
-65535\
-32769\
-32768\
-32767\
-16385\
-16384\
-16383\
-8193\
-8192\
-8191\
-4097\
-4096\
-4095\
-2049\
-2048\
-2047\
-1025\
-1024\
-1023\
-513\
-512\
-511\
-257\
-256\
-255\
-129\
-128\
-127\
-65\
-64\
-63\
-33\
-32\
-31\
-17\
-16\
-15\
-9\
-8\
-7\
-5\
-4\
-3\
-2\
-1\
0\
1\
2\
3\
4\
5\
7\
8\
9\
15\
16\
17\
31\
32\
33\
63\
64\
65\
127\
128\
129\
255\
256\
257\
511\
512\
513\
1023\
1024\
1025\
2047\
2048\
2049\
4095\
4096\
4097\
8191\
8192\
8193\
16383\
16384\
16385\
32767\
32768\
32769\
65535\
65536\
65537\
131071\
131072\
131073\
262143\
262144\
262145\
524287\
524288\
524289\
1048575\
1048576\
1048577\
2097151\
2097152\
2097153\
4194303\
4194304\
4194305\
8388607\
8388608\
8388609\
16777215\
16777216\
16777217\
33554431\
33554432\
33554433\
67108863\
67108864\
67108865\
134217727\
134217728\
134217729\
268435455\
268435456\
268435457\
536870911\
536870912\
536870913\
1073741823\
1073741824\
1073741825\
2147483647\
2147483648\
2147483649\
4294967295\
4294967296\
4294967297\
8589934591\
8589934592\
8589934593\
17179869183\
17179869184\
17179869185\
34359738367\
34359738368\
34359738369\
68719476735\
68719476736\
68719476737\
137438953471\
137438953472\
137438953473\
274877906943\
274877906944\
274877906945\
549755813887\
549755813888\
549755813889\
1099511627775\
1099511627776\
1099511627777\
2199023255551\
2199023255552\
2199023255553\
4398046511103\
4398046511104\
4398046511105\
8796093022207\
8796093022208\
8796093022209\
17592186044415\
17592186044416\
17592186044417\
35184372088831\
35184372088832\
35184372088833\
70368744177663\
70368744177664\
70368744177665\
140737488355327\
140737488355328\
140737488355329\
281474976710655\
281474976710656\
281474976710657\
562949953421311\
562949953421312\
562949953421313\
1125899906842623\
1125899906842624\
1125899906842625\
2251799813685247\
2251799813685248\
2251799813685249\
4503599627370495\
4503599627370496\
4503599627370497\
9007199254740991\
9007199254740992\
9007199254740993\
18014398509481983\
18014398509481984\
18014398509481985\
36028797018963967\
36028797018963968\
36028797018963969\
72057594037927935\
72057594037927936\
72057594037927937\
144115188075855871\
144115188075855872\
144115188075855873\
288230376151711743\
288230376151711744\
288230376151711745\
576460752303423487\
576460752303423488\
576460752303423489\
1152921504606846975\
1152921504606846976\
1152921504606846977\
2305843009213693951\
2305843009213693952\
2305843009213693953\
4611686018427387903\
4611686018427387904\
4611686018427387905"

# Ticket #1210.  Do proper reference counting of Table structures
# so that deeply nested SELECT statements can be flattened correctly.
#
do_test misc5-3.1 {
  execsql {
    CREATE TABLE songs(songid, artist, timesplayed);
    INSERT INTO songs VALUES(1,'one',1);
    INSERT INTO songs VALUES(2,'one',2);
    INSERT INTO songs VALUES(3,'two',3);
    INSERT INTO songs VALUES(4,'three',5);
    INSERT INTO songs VALUES(5,'one',7);
    INSERT INTO songs VALUES(6,'two',11);
    SELECT DISTINCT artist 
    FROM (    
     SELECT DISTINCT artist    
     FROM songs      
     WHERE songid IN (    
      SELECT songid    
      FROM songs    
      WHERE LOWER(artist) = (    
        SELECT DISTINCT LOWER(artist)    
        FROM (      
          SELECT DISTINCT artist,sum(timesplayed) AS total      
          FROM songs      
          GROUP BY LOWER(artist)      
          ORDER BY total DESC      
          LIMIT 10    
        )    
        WHERE artist <> '' 
      )  
     )       
    )  
    ORDER BY LOWER(artist) ASC;
  }
} {two}

# Ticket #1370.  Do not overwrite small files (less than 1024 bytes)
# when trying to open them as a database.
#
do_test misc5-4.1 {
  db close
  file delete -force test.db
  set fd [open test.db w]
  puts $fd "This is not really a database"
  close $fd
  sqlite3 db test.db
  catchsql {
    CREATE TABLE t1(a,b,c);
  }
} {1 {file is encrypted or is not a database}}

# Ticket #1371.  Allow floating point numbers of the form .N  or N.
#
do_test misc5-5.1 {
  execsql {SELECT .1 }
} 0.1
do_test misc5-5.2 {
  execsql {SELECT 2. }
} 2.0
do_test misc5-5.3 {
  execsql {SELECT 3.e0 }
} 3.0
do_test misc5-5.4 {
  execsql {SELECT .4e+1}
} 4.0


finish_test