SQLite

**    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 *);

/*

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

# 2001 September 15
#
# 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.  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;
  }

# 2001 September 15
#
# 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.  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.
#

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

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

# 2001 September 15
#
# 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.  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');

#    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

# 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');

# 2008 June 24
#
# 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. 
#
# $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 }

# 2008 July 14
#
# 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.
#
#***********************************************************************
#
# 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.
#

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

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

#***********************************************************************
# 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,

# 2007 Sep 12
#
# 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 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.
# So if one of the SELECT statements within a compound made
# use of a table that occurs later in a join, the query
# 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);

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

# 2008 Feb 6
#
# 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 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);

        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

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

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 {