# 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.22 2008/07/29 10:26:45 danielk1977 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.

#

ifcapable conflict {

  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

} else {

  do_test misc5-2.1 {

    execsql {

      BEGIN;

      create table t2(x unique);

      create table t2_temp(x);

      insert into t2_temp values(1);

      insert into t2_temp select x*2 from t2_temp;

      insert into t2_temp select x*4 from t2_temp;

      insert into t2_temp select x*16 from t2_temp;

      insert into t2_temp select x*256 from t2_temp;

      insert into t2_temp select x*65536 from t2_temp;

      insert into t2_temp select x*2147483648 from t2_temp;

      insert into t2_temp select x-1 from t2_temp;

      insert into t2_temp select x+1 from t2_temp;

      insert into t2_temp select -x from t2_temp;

      INSERT INTO t2 SELECT DISTINCT(x) FROM t2_temp;

      DROP TABLE t2_temp;

      COMMIT;

      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.

#

ifcapable subquery {

  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) = (    

          -- This sub-query is indeterminate. Because there is no ORDER BY,

          -- it may return 'one', 'two' or 'three'. Because of this, the

	  -- outermost parent query may correctly return any of 'one', 'two' 

          -- or 'three' as well.

          SELECT DISTINCT LOWER(artist)    

          FROM (      

            -- This sub-query returns the table:

            --

            --     two      14

            --     one      10

            --     three    5

            --

            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;

    }

  } {one}

}

# 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 {