/ Check-in [9ca975c8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix (make test) with SQLITE_OMIT_COMPOUND_SELECT. Ticket #3235. (CVS 5530)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9ca975c8fdeaae7d5af8fe62e097d8d251362cfe
User & Date: danielk1977 2008-08-04 03:51:24
Context
2008-08-04
04:28
Add the pkgconfigdir variable to Makefile.in. Ticket #3242. (CVS 5531) check-in: 540c9d4c user: danielk1977 tags: trunk
03:51
Fix (make test) with SQLITE_OMIT_COMPOUND_SELECT. Ticket #3235. (CVS 5530) check-in: 9ca975c8 user: danielk1977 tags: trunk
01:46
removed some extraneous blank lines. made sure to use only LF line endings. (CVS 5529) check-in: 358bd660 user: shane tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2443
2444
2445
2446
2447
2448
2449

2450
2451
2452
2453
2454
2455
2456
....
2789
2790
2791
2792
2793
2794
2795

2796
2797
2798
2799
2800
2801
2802
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.462 2008/08/02 03:50:39 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**     End: ...
**
** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
** actually called using Gosub and they do not Return.  EofA and EofB loop
** until all data is exhausted then jump to the "end" labe.  AltB, AeqB,
** and AgtB jump to either L2 or to one of EofA or EofB.
*/

static int multiSelectOrderBy(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
){
  int i, j;             /* Loop counters */
  Select *pPrior;       /* Another SELECT immediately to our left */
................................................................................
  }
  p->pPrior = pPrior;

  /*** TBD:  Insert subroutine calls to close cursors on incomplete
  **** subqueries ****/
  return SQLITE_OK;
}


#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
/* Forward Declarations */
static void substExprList(sqlite3*, ExprList*, int, ExprList*);
static void substSelect(sqlite3*, Select *, int, ExprList *);

/*







|







 







>







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
....
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.463 2008/08/04 03:51:24 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**     End: ...
**
** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
** actually called using Gosub and they do not Return.  EofA and EofB loop
** until all data is exhausted then jump to the "end" labe.  AltB, AeqB,
** and AgtB jump to either L2 or to one of EofA or EofB.
*/
#ifndef SQLITE_OMIT_COMPOUND_SELECT
static int multiSelectOrderBy(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
){
  int i, j;             /* Loop counters */
  Select *pPrior;       /* Another SELECT immediately to our left */
................................................................................
  }
  p->pPrior = pPrior;

  /*** TBD:  Insert subroutine calls to close cursors on incomplete
  **** subqueries ****/
  return SQLITE_OK;
}
#endif

#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
/* Forward Declarations */
static void substExprList(sqlite3*, ExprList*, int, ExprList*);
static void substSelect(sqlite3*, Select *, int, ExprList *);

/*

Changes to test/cse.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
90
91
92
93
94
95
96

97
98
99
100
101
102
103
104
105
106
107
108
109
110

111
112
113
114
115
116
117
#
#***********************************************************************
#
# Test cases designed to exercise and verify the logic for
# factoring constant expressions out of loops and for
# common subexpression eliminations.
#
# $Id: cse.test,v 1.5 2008/04/01 18:04:11 drh Exp $
#

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

do_test cse-1.1 {
  execsql {
................................................................................
  }
} {0 -12 1 11 0 -22 1 21}
do_test cse-1.10 {
  execsql {
    SELECT CAST(b AS integer), typeof(b), CAST(b AS text), typeof(b) FROM t1
  }
} {11 integer 11 integer 21 integer 21 integer}

do_test cse-1.11 { 
  execsql {
    SELECT *,* FROM t1 WHERE a=2
    UNION ALL
    SELECT *,* FROM t1 WHERE a=1
  }
} {2 21 22 23 24 25 2 21 22 23 24 25 1 11 12 13 14 15 1 11 12 13 14 15}
do_test cse-1.12 { 
  execsql {
    SELECT coalesce(b,c,d,e), a, b, c, d, e FROM t1 WHERE a=2
    UNION ALL
    SELECT coalesce(e,d,c,b), e, d, c, b, a FROM t1 WHERE a=1
  }
} {21 2 21 22 23 24 14 14 13 12 11 1}

do_test cse-1.13 {
  execsql {
     SELECT upper(b), typeof(b), b FROM t1
  }
} {11 integer 11 21 integer 21}
do_test cse-1.14 {
  execsql {







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
#
#***********************************************************************
#
# Test cases designed to exercise and verify the logic for
# factoring constant expressions out of loops and for
# common subexpression eliminations.
#
# $Id: cse.test,v 1.6 2008/08/04 03:51:24 danielk1977 Exp $
#

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

do_test cse-1.1 {
  execsql {
................................................................................
  }
} {0 -12 1 11 0 -22 1 21}
do_test cse-1.10 {
  execsql {
    SELECT CAST(b AS integer), typeof(b), CAST(b AS text), typeof(b) FROM t1
  }
} {11 integer 11 integer 21 integer 21 integer}
ifcapable compound {
  do_test cse-1.11 { 
    execsql {
      SELECT *,* FROM t1 WHERE a=2
      UNION ALL
      SELECT *,* FROM t1 WHERE a=1
    }
  } {2 21 22 23 24 25 2 21 22 23 24 25 1 11 12 13 14 15 1 11 12 13 14 15}
  do_test cse-1.12 { 
    execsql {
      SELECT coalesce(b,c,d,e), a, b, c, d, e FROM t1 WHERE a=2
      UNION ALL
      SELECT coalesce(e,d,c,b), e, d, c, b, a FROM t1 WHERE a=1
    }
  } {21 2 21 22 23 24 14 14 13 12 11 1}
}
do_test cse-1.13 {
  execsql {
     SELECT upper(b), typeof(b), b FROM t1
  }
} {11 integer 11 21 integer 21}
do_test cse-1.14 {
  execsql {

Changes to test/func.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
286
287
288
289
290
291
292

293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316

317
318
319
320
321
322
323
#    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.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.85 2008/07/31 01:47:11 shane Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
................................................................................
  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

do_test func-8.5 {
  execsql {
    SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
                        UNION ALL SELECT -9223372036854775807)
  }
} {0}
do_test func-8.6 {
  execsql {
    SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
                        UNION ALL SELECT -9223372036854775807)
  }
} {integer}
do_test func-8.7 {
  execsql {
    SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
                        UNION ALL SELECT -9223372036854775807)
  }
} {real}
do_test func-8.8 {
  execsql {
    SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
                        UNION ALL SELECT -9223372036850000000)
  }
} {1}


# How do you test the random() function in a meaningful, deterministic way?
#
do_test func-9.1 {
  execsql {
    SELECT random() is not null;
  }







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
#    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.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.86 2008/08/04 03:51:24 danielk1977 Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
................................................................................
  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
ifcapable compound {
  do_test func-8.5 {
    execsql {
      SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
                          UNION ALL SELECT -9223372036854775807)
    }
  } {0}
  do_test func-8.6 {
    execsql {
      SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
                          UNION ALL SELECT -9223372036854775807)
    }
  } {integer}
  do_test func-8.7 {
    execsql {
      SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
                          UNION ALL SELECT -9223372036854775807)
    }
  } {real}
  do_test func-8.8 {
    execsql {
      SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
                          UNION ALL SELECT -9223372036850000000)
    }
  } {1}
}

# How do you test the random() function in a meaningful, deterministic way?
#
do_test func-9.1 {
  execsql {
    SELECT random() is not null;
  }

Changes to test/in.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
362
363
364
365
366
367
368

369
370
371
372
373
374
375
...
424
425
426
427
428
429
430

431
432
433
434
435
436
437
#    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.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.21 2008/06/26 18:04:03 danielk1977 Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
  execsql {
    SELECT * FROM t6 WHERE +a IN ('2');
  }
} {}

# Test error conditions with expressions of the form IN(<compound select>).
#

do_test in-12.1 {
  execsql {
    CREATE TABLE t2(a, b, c);
    CREATE TABLE t3(a, b, c);
  }
} {}
do_test in-12.2 {
................................................................................
do_test in-12.9 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}



#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#







|







 







>







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
...
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
#    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.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.22 2008/08/04 03:51:24 danielk1977 Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
  execsql {
    SELECT * FROM t6 WHERE +a IN ('2');
  }
} {}

# Test error conditions with expressions of the form IN(<compound select>).
#
ifcapable compound {
do_test in-12.1 {
  execsql {
    CREATE TABLE t2(a, b, c);
    CREATE TABLE t3(a, b, c);
  }
} {}
do_test in-12.2 {
................................................................................
do_test in-12.9 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
}


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#

Changes to test/in3.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
99
100
101
102
103
104
105

106
107
108
109
110
111
112

113
114
115
116
117
118
119
#
#***********************************************************************
# This file tests the optimisations made in November 2007 of expressions 
# of the following form:
#
#     <value> IN (SELECT <column> FROM <table>)
#
# $Id: in3.test,v 1.4 2008/03/12 10:39:00 danielk1977 Exp $

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

ifcapable !subquery {
  finish_test
  return
................................................................................
  exec_neph {
    SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 ORDER BY a LIMIT 1 OFFSET 1)
  }
} {1 3}

# Has to use a temp-table because of the compound sub-select.
#

do_test in3-1.13 {
  exec_neph {
    SELECT a FROM t1 WHERE a IN (
      SELECT a FROM t1 UNION ALL SELECT a FROM t1
    )
  }
} {1 1 3 5}


# The first of these queries has to use the temp-table, because the 
# collation sequence used for the index on "t1.a" does not match the
# collation sequence used by the "IN" comparison. The second does not
# require a temp-table, because the collation sequences match.
#
do_test in3-1.14 {







|







 







>
|
|
|
|
|
|
|
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
#
#***********************************************************************
# This file tests the optimisations made in November 2007 of expressions 
# of the following form:
#
#     <value> IN (SELECT <column> FROM <table>)
#
# $Id: in3.test,v 1.5 2008/08/04 03:51:24 danielk1977 Exp $

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

ifcapable !subquery {
  finish_test
  return
................................................................................
  exec_neph {
    SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 ORDER BY a LIMIT 1 OFFSET 1)
  }
} {1 3}

# Has to use a temp-table because of the compound sub-select.
#
ifcapable compound {
  do_test in3-1.13 {
    exec_neph {
      SELECT a FROM t1 WHERE a IN (
        SELECT a FROM t1 UNION ALL SELECT a FROM t1
      )
    }
  } {1 1 3 5}
}

# The first of these queries has to use the temp-table, because the 
# collation sequence used for the index on "t1.a" does not match the
# collation sequence used by the "IN" comparison. The second does not
# require a temp-table, because the collation sequences match.
#
do_test in3-1.14 {

Changes to test/insert5.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
43
44
45
46
47
48
49

50
51
52
53
54
55
56
57










58
59
60
61
62
63
64
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that a temporary table is used
# when required by an "INSERT INTO ... SELECT ..." statement.
#
# $Id: insert5.test,v 1.4 2008/01/06 00:25:22 drh Exp $

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

ifcapable !subquery {
  finish_test
  return
................................................................................
    INSERT INTO MAIN(Id,Id1) VALUES(2,3); 
    INSERT INTO B(Id,Id1) VALUES(2,3); 
  }
} {}

# Run the query.
#

do_test insert5-1.1 {
  execsql {
    INSERT INTO B 
      SELECT * FROM B UNION ALL 
      SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
      SELECT * FROM B;
  }
} {2 3 2 3 2 3}










do_test insert5-2.1 {
  uses_temp_table { INSERT INTO b SELECT * FROM main }
} {0}
do_test insert5-2.2 {
  uses_temp_table { INSERT INTO b SELECT * FROM b }
} {1}
do_test insert5-2.3 {







|







 







>
|
|
|
|
|

|
|
>
>
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that a temporary table is used
# when required by an "INSERT INTO ... SELECT ..." statement.
#
# $Id: insert5.test,v 1.5 2008/08/04 03:51:24 danielk1977 Exp $

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

ifcapable !subquery {
  finish_test
  return
................................................................................
    INSERT INTO MAIN(Id,Id1) VALUES(2,3); 
    INSERT INTO B(Id,Id1) VALUES(2,3); 
  }
} {}

# Run the query.
#
ifcapable compound {
  do_test insert5-1.1 {
    execsql {
      INSERT INTO B 
        SELECT * FROM B UNION ALL 
        SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
      SELECT * FROM B;
    }
  } {2 3 2 3 2 3}
} else {
  do_test insert5-1.1 {
    execsql {
      INSERT INTO B SELECT * FROM B;
      INSERT INTO B
        SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
      SELECT * FROM B;
    }
  } {2 3 2 3 2 3}
}
do_test insert5-2.1 {
  uses_temp_table { INSERT INTO b SELECT * FROM main }
} {0}
do_test insert5-2.2 {
  uses_temp_table { INSERT INTO b SELECT * FROM b }
} {1}
do_test insert5-2.3 {

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
#    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.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.64 2008/07/15 00:27:35 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+101;
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}

# Ticket #2296
ifcapable subquery {
do_test select1-6.20 {
   execsql {
     CREATE TABLE t6(a TEXT, b TEXT);
     INSERT INTO t6 VALUES('a','0');
     INSERT INTO t6 VALUES('b','1');
     INSERT INTO t6 VALUES('c','2');
     INSERT INTO t6 VALUES('d','3');







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
#    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.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.65 2008/08/04 03:51:24 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+101;
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}

# Ticket #2296
ifcapable subquery&&compound {
do_test select1-6.20 {
   execsql {
     CREATE TABLE t6(a TEXT, b TEXT);
     INSERT INTO t6 VALUES('a','0');
     INSERT INTO t6 VALUES('b','1');
     INSERT INTO t6 VALUES('c','2');
     INSERT INTO t6 VALUES('d','3');

Changes to test/select4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
...
788
789
790
791
792
793
794
795


796
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.28 2008/06/25 08:02:44 mihailim Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
  execsql2 {
    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
     WHERE b>0
  }
} {a 1 b 2 a 3 b 4}
} ;# ifcapable subquery

} ;# ifcapable compound

# Try combining DISTINCT, LIMIT, and OFFSET.  Make sure they all work
# together.
#
do_test select4-10.1 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log
  }
................................................................................
    SELECT x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}



finish_test







|







 







<
<







 








>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
612
613
614
615
616
617
618


619
620
621
622
623
624
625
...
786
787
788
789
790
791
792
793
794
795
796
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.29 2008/08/04 03:51:24 danielk1977 Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
  execsql2 {
    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
     WHERE b>0
  }
} {a 1 b 2 a 3 b 4}
} ;# ifcapable subquery



# Try combining DISTINCT, LIMIT, and OFFSET.  Make sure they all work
# together.
#
do_test select4-10.1 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log
  }
................................................................................
    SELECT x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

} ;# ifcapable compound

finish_test

Changes to test/selectA.test.

13
14
15
16
17
18
19
20
21
22
23





24
25
26
27
28
29
30
# The focus of this file is testing the compound-SELECT merge
# optimization.  Or, in other words, making sure that all
# possible combinations of UNION, UNION ALL, EXCEPT, and
# INTERSECT work together with an ORDER BY clause (with or w/o
# explicit sort order and explicit collating secquites) and
# with and without optional LIMIT and OFFSET clauses.
#
# $Id: selectA.test,v 1.4 2008/07/15 00:27:35 drh Exp $

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






do_test selectA-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c COLLATE NOCASE);
    INSERT INTO t1 VALUES(1,'a','a');
    INSERT INTO t1 VALUES(9.9, 'b', 'B');
    INSERT INTO t1 VALUES(NULL, 'C', 'c');







|



>
>
>
>
>







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# The focus of this file is testing the compound-SELECT merge
# optimization.  Or, in other words, making sure that all
# possible combinations of UNION, UNION ALL, EXCEPT, and
# INTERSECT work together with an ORDER BY clause (with or w/o
# explicit sort order and explicit collating secquites) and
# with and without optional LIMIT and OFFSET clauses.
#
# $Id: selectA.test,v 1.5 2008/08/04 03:51:24 danielk1977 Exp $

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

ifcapable !compound {
  finish_test
  return
}

do_test selectA-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c COLLATE NOCASE);
    INSERT INTO t1 VALUES(1,'a','a');
    INSERT INTO t1 VALUES(9.9, 'b', 'B');
    INSERT INTO t1 VALUES(NULL, 'C', 'c');

Changes to test/selectB.test.

6
7
8
9
10
11
12
13
14
15
16





17
18
19
20
21
22
23
#    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. 
#
# $Id: selectB.test,v 1.8 2008/07/12 14:52:20 drh Exp $

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






proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
  db eval "explain $sql2" { lappend ::vdbe2 $opcode }








|



>
>
>
>
>







6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#    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. 
#
# $Id: selectB.test,v 1.9 2008/08/04 03:51:24 danielk1977 Exp $

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

ifcapable !compound {
  finish_test
  return
}

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
  db eval "explain $sql2" { lappend ::vdbe2 $opcode }

Changes to test/shared4.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test the btree mutex protocol for shared cache mode.
#
# $Id: shared4.test,v 1.1 2008/07/14 19:39:17 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
db close
puts hello

# This script is only valid if we are running shared-cache mode in a
# threadsafe-capable database engine.
#
ifcapable !shared_cache {
  finish_test
  return
}
set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

# Prepare multiple databases in shared cache mode.
#







|









|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test the btree mutex protocol for shared cache mode.
#
# $Id: shared4.test,v 1.2 2008/08/04 03:51:24 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
db close
puts hello

# This script is only valid if we are running shared-cache mode in a
# threadsafe-capable database engine.
#
ifcapable !shared_cache||!compound {
  finish_test
  return
}
set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

# Prepare multiple databases in shared cache mode.
#

Changes to test/sidedelete.test.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
43
44
45
46
47
48
49

50
51
52
53
54
55
56
57
#
# This file contains test cases for stressing database
# changes that involve side effects that delete rows from
# the table being changed.  Ticket #2832 shows that in
# older versions of SQLite that behavior was implemented
# incorrectly and resulted in corrupt database files.
#
# $Id: sidedelete.test,v 1.1 2007/12/12 22:24:13 drh Exp $
#

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

# The sequence table is created to store a sequence of integers
# starting with 1.  This is used to reinitialize other tables
................................................................................
# correlated subquery.  This would cause database corruption
# prior to the fix for ticket #2832.
#
do_test sidedelete-2.0 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE chng(a PRIMARY KEY, b);

    SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM chng;
  }
} {0 0}
for {set i 2} {$i<=100} {incr i} {
  set n [expr {($i+2)/2}]
  do_test sidedelete-2.$i.1 {
    execsql {
      DELETE FROM t1;







|







 







>
|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
#
# This file contains test cases for stressing database
# changes that involve side effects that delete rows from
# the table being changed.  Ticket #2832 shows that in
# older versions of SQLite that behavior was implemented
# incorrectly and resulted in corrupt database files.
#
# $Id: sidedelete.test,v 1.2 2008/08/04 03:51:24 danielk1977 Exp $
#

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

# The sequence table is created to store a sequence of integers
# starting with 1.  This is used to reinitialize other tables
................................................................................
# correlated subquery.  This would cause database corruption
# prior to the fix for ticket #2832.
#
do_test sidedelete-2.0 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE chng(a PRIMARY KEY, b);
    SELECT count(*) FROM t1;
    SELECT count(*) FROM chng;
  }
} {0 0}
for {set i 2} {$i<=100} {incr i} {
  set n [expr {($i+2)/2}]
  do_test sidedelete-2.$i.1 {
    execsql {
      DELETE FROM t1;

Changes to test/subselect.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
52
53
54
55
56
57
58

59
60
61
62
63
64

65
66
67
68
69
70
71
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.15 2008/07/15 00:27:35 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
................................................................................
} {4}
do_test subselect-1.3c {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
} {6}
do_test subselect-1.3d {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
} {}

do_test subselect-1.3e {
  execsql {
    SELECT b FROM t1
     WHERE a = (SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY 1);
  }
} {2}


# What if the subselect doesn't return any value.  We should get
# NULL as the result.  Check it out.
#
do_test subselect-1.4 {
  execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
} {2}







|







 







>
|
|
|
|
|
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.16 2008/08/04 03:51:24 danielk1977 Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
................................................................................
} {4}
do_test subselect-1.3c {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
} {6}
do_test subselect-1.3d {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
} {}
ifcapable compound {
  do_test subselect-1.3e {
    execsql {
      SELECT b FROM t1
       WHERE a = (SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY 1);
    }
  } {2}
}

# What if the subselect doesn't return any value.  We should get
# NULL as the result.  Check it out.
#
do_test subselect-1.4 {
  execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
} {2}

Changes to test/tkt2192.test.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #2192 has been
# fixed.  
#
#
# $Id: tkt2192.test,v 1.2 2007/09/12 17:01:45 danielk1977 Exp $

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

ifcapable !datetime {
  finish_test
  return
}

do_test tkt2191-1.1 {
  execsql {
    -- Raw data (RBS) --------
    
    create table records (
      date          real,
      type          text,
      description   text,







|




|




|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #2192 has been
# fixed.  
#
#
# $Id: tkt2192.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $

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

ifcapable !datetime||!compound {
  finish_test
  return
}

do_test tkt2192-1.1 {
  execsql {
    -- Raw data (RBS) --------
    
    create table records (
      date          real,
      type          text,
      description   text,

Changes to test/tkt2640.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file is to test that ticket #2640 has been fixed.
#
# $Id: tkt2640.test,v 1.2 2007/12/10 05:03:48 danielk1977 Exp $
#

# The problem in ticket #2640 was that the query optimizer was 
# not recognizing all uses of tables within subqueries in the
# WHERE clause.  If the subquery contained a compound SELECT,
# then tables that were used by terms of the compound other than
# the last term would not be recognized as dependencies.
................................................................................
# optimizer would not recognize this and would try to evaluate
# the subquery too early, before that tables value had been
# established.

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

ifcapable !subquery {
  finish_test
  return
}

do_test tkt2640-1.1 {
  execsql {
    CREATE TABLE persons(person_id, name);







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file is to test that ticket #2640 has been fixed.
#
# $Id: tkt2640.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $
#

# The problem in ticket #2640 was that the query optimizer was 
# not recognizing all uses of tables within subqueries in the
# WHERE clause.  If the subquery contained a compound SELECT,
# then tables that were used by terms of the compound other than
# the last term would not be recognized as dependencies.
................................................................................
# optimizer would not recognize this and would try to evaluate
# the subquery too early, before that tables value had been
# established.

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

ifcapable !subquery||!compound {
  finish_test
  return
}

do_test tkt2640-1.1 {
  execsql {
    CREATE TABLE persons(person_id, name);

Changes to test/tkt2822.test.

9
10
11
12
13
14
15
16
17
18
19
20





21
22
23
24
25
26
27
#
#***********************************************************************
#
# This file is to test that the issues surrounding expressions in
# ORDER BY clauses on compound SELECT statements raised by ticket
# #2822 have been dealt with.
#
# $Id: tkt2822.test,v 1.4 2007/12/13 17:50:23 drh Exp $
#

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






# The ORDER BY matching algorithm is three steps:
# 
#   (1)  If the ORDER BY term is an integer constant i, then
#        sort by the i-th column of the result set.
# 
#   (2)  If the ORDER BY term is an identifier (not x.y or x.y.z







|




>
>
>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#
#***********************************************************************
#
# This file is to test that the issues surrounding expressions in
# ORDER BY clauses on compound SELECT statements raised by ticket
# #2822 have been dealt with.
#
# $Id: tkt2822.test,v 1.5 2008/08/04 03:51:24 danielk1977 Exp $
#

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

ifcapable !compound {
  finish_test
  return
}

# The ORDER BY matching algorithm is three steps:
# 
#   (1)  If the ORDER BY term is an integer constant i, then
#        sort by the i-th column of the result set.
# 
#   (2)  If the ORDER BY term is an identifier (not x.y or x.y.z

Changes to test/tkt2927.test.

7
8
9
10
11
12
13
14
15
16
17
18





19
20
21
22
23
24
25
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file is to test that ticket #2927 is fixed.
#
# $Id: tkt2927.test,v 1.3 2008/07/12 14:52:21 drh Exp $
#

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






# Create a database.
#
do_test tkt2927-1.1 {
  db eval {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1,11);







|




>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file is to test that ticket #2927 is fixed.
#
# $Id: tkt2927.test,v 1.4 2008/08/04 03:51:24 danielk1977 Exp $
#

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

ifcapable !compound {
  finish_test
  return
}

# Create a database.
#
do_test tkt2927-1.1 {
  db eval {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1,11);

Changes to test/trigger9.test.

185
186
187
188
189
190
191


192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219

220
        INSERT INTO t2 VALUES(old.a);
      END;
      UPDATE v1 SET b = 'hello';
      SELECT * FROM t2;
    ROLLBACK;
  }
} {1 2 3 3}


do_test trigger9-3.5 {
  execsql {
    BEGIN;
      INSERT INTO t3 VALUES(1, 'uno');
      CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one';
      CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
        INSERT INTO t2 VALUES(old.a);
      END;
      UPDATE v1 SET b = 'hello';
      SELECT * FROM t2;
    ROLLBACK;
  }
} {1 2 3}
do_test trigger9-3.6 {
  execsql {
    BEGIN;
      INSERT INTO t3 VALUES(1, 'zero');
      CREATE VIEW v1 AS 
        SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';
      CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
        INSERT INTO t2 VALUES(old.a);
      END;
      UPDATE v1 SET b = 'hello';
      SELECT * FROM t2;
    ROLLBACK;
  }
} {2}


finish_test







>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>

185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
        INSERT INTO t2 VALUES(old.a);
      END;
      UPDATE v1 SET b = 'hello';
      SELECT * FROM t2;
    ROLLBACK;
  }
} {1 2 3 3}

ifcapable compound {
  do_test trigger9-3.5 {
    execsql {
      BEGIN;
        INSERT INTO t3 VALUES(1, 'uno');
        CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one';
        CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
          INSERT INTO t2 VALUES(old.a);
        END;
        UPDATE v1 SET b = 'hello';
        SELECT * FROM t2;
      ROLLBACK;
    }
  } {1 2 3}
  do_test trigger9-3.6 {
    execsql {
      BEGIN;
        INSERT INTO t3 VALUES(1, 'zero');
        CREATE VIEW v1 AS 
          SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';
        CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
          INSERT INTO t2 VALUES(old.a);
        END;
        UPDATE v1 SET b = 'hello';
        SELECT * FROM t2;
      ROLLBACK;
    }
  } {2}
}

finish_test

Changes to test/triggerA.test.

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# into the query that manifests the view?
#
# Ticket #2938
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

# Create two table containing some sample data
#
do_test triggerA-1.1 {







|







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# into the query that manifests the view?
#
# Ticket #2938
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !trigger||!compound {
  finish_test
  return
}

# Create two table containing some sample data
#
do_test triggerA-1.1 {

Changes to tool/omittest.tcl.

1
2
3
4
5
6
7
8
9
..
58
59
60
61
62
63
64

65
66

67
68
69
70
71
72
73

set rcsid {$Id: omittest.tcl,v 1.5 2008/07/31 02:43:35 shane Exp $}

# Documentation for this script. This may be output to stderr
# if the script is invoked incorrectly.
set ::USAGE_MESSAGE {
This Tcl script is used to test the various compile time options 
available for omitting code (the SQLITE_OMIT_xxx options). It
should be invoked as follows:
................................................................................
  }

  # Create the directory and do the build. If an error occurs return
  # early without attempting to run the test suite.
  file mkdir $dir
  puts -nonewline "Building $dir..."
  flush stdout

  file copy -force ./config.h $dir
  file copy -force ./libtool $dir

  set rc [catch {
    exec make -C $dir -f $::MAKEFILE testfixture OPTS=$opts >& $dir/build.log
  }]
  if {$rc} {
    puts "No good. See $dir/build.log."
    return
  } else {

|







 







>


>







1
2
3
4
5
6
7
8
9
..
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

set rcsid {$Id: omittest.tcl,v 1.6 2008/08/04 03:51:24 danielk1977 Exp $}

# Documentation for this script. This may be output to stderr
# if the script is invoked incorrectly.
set ::USAGE_MESSAGE {
This Tcl script is used to test the various compile time options 
available for omitting code (the SQLITE_OMIT_xxx options). It
should be invoked as follows:
................................................................................
  }

  # Create the directory and do the build. If an error occurs return
  # early without attempting to run the test suite.
  file mkdir $dir
  puts -nonewline "Building $dir..."
  flush stdout
catch {
  file copy -force ./config.h $dir
  file copy -force ./libtool $dir
}
  set rc [catch {
    exec make -C $dir -f $::MAKEFILE testfixture OPTS=$opts >& $dir/build.log
  }]
  if {$rc} {
    puts "No good. See $dir/build.log."
    return
  } else {