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.368 2007/12/13 07:58:51 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.

  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }
  if( p->pPrior==0 ){
    if( processOrderGroupBy(pParse, p, p->pOrderBy, 1, &sNC.hasAgg) ){
      return SQLITE_ERROR;
    }
  }
  if( processOrderGroupBy(pParse, p, pGroupBy, 0, &sNC.hasAgg) ){
    return SQLITE_ERROR;
  }

#    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.3 2007/12/13 07:58:51 danielk1977 Exp $

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

ifcapable !subquery {
  finish_test
  return

} {0}
do_test insert5-2.8 {
  uses_temp_table { 
    INSERT INTO b 
    SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
  }
} {1}

# UPDATE: Using a column from the outer query (main.id) in the GROUP BY
# or ORDER BY of a sub-query is no longer supported.
#
# do_test insert5-2.9 {
#   uses_temp_table { 
#     INSERT INTO b 
#     SELECT * FROM main 
#     WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
#   }
# } {}
do_test insert5-2.9 {
  catchsql { 
    INSERT INTO b 
    SELECT * FROM main 
    WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
  }
} {1 {no such column: main.id}}

finish_test

#
#***********************************************************************
# 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.19 2007/12/13 07:58:51 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.
#

    }
  } {1 {no such table: blah}}
  do_test misc5-6.2 {
    execsql {
      CREATE TABLE logs(msg TEXT, timestamp INTEGER, dbtime TEXT);
    }
    catchsql {
      SELECT * FROM logs WHERE logs.oid >= (SELECT head FROM logs_base) 
      UNION ALL 
      SELECT * FROM logs 
      LIMIT (SELECT lmt FROM logs_base) ;
    }
  } {1 {no such table: logs_base}}
}

# Overflow the lemon parser stack by providing an overly complex
# expression.  Make sure that the overflow is detected and reported.
#
do_test misc5-7.1 {
  execsql {CREATE TABLE t1(x)}

  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }
  } {{} 0 1}
  do_test null-6.5 {
    catchsql {
      select b from t1 union select c from t1 order by t1.a;
    }
  } {1 {1st ORDER BY term does not match any column in the result set}}
  do_test null-6.6 {
    catchsql {
      select b from t1 union select c from t1 order by main.t1.a;
    }
  } {1 {1st ORDER BY term does not match any column in the result set}}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {
    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 the SELECT statement.
#
# $Id: select1.test,v 1.57 2007/12/13 07:58:51 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {

do_test select1-4.3 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {misuse of aggregate: min(f1)}}

# The restriction not allowing constants in the ORDER BY clause
# has been removed.  See ticket #1768
#do_test select1-4.5 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY 8.4;
#  }

    SELECT * FROM t5 ORDER BY +2;
  }
} {2 9 1 10}
do_test select1-4.10.1 {
  catchsql {
    SELECT * FROM t5 ORDER BY 3;
  }
} {1 {1st ORDER BY term out of range - should be between 1 and 2}}
do_test select1-4.10.2 {
  catchsql {
    SELECT * FROM t5 ORDER BY -1;
  }
} {1 {1st ORDER BY term out of range - should be between 1 and 2}}
do_test select1-4.11 {
  execsql {
    INSERT INTO t5 VALUES(3,10);
    SELECT * FROM t5 ORDER BY 2, 1 DESC;
  }
} {2 9 3 10 1 10}
do_test select1-4.12 {

} {0 {f1 11 f1 22 f1 33 f1 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    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');

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.22 2007/12/13 07:58:51 danielk1977 Exp $

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

# Build some test data
#
do_test select3-1.0 {

#    SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
#  }
#} {1 {GROUP BY terms must not be non-integer constants}}
do_test select3-2.10 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
  }
} {1 {1st GROUP BY term out of range - should be between 1 and 2}}
do_test select3-2.11 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
  }
} {1 {1st GROUP BY term out of range - should be between 1 and 2}}
do_test select3-2.12 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
  }
} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}

# Cannot have an empty GROUP BY

#    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.24 2007/12/13 07:58:51 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.
#

  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}
do_test select4-5.2d {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}
do_test select4-5.2e {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY n;
  }} msg]

do_test select4-5.2h {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 2;
  }
} {1 {1st ORDER BY term out of range - should be between 1 and 1}}
do_test select4-5.2i {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 2, 1;
  }

      SELECT 4 AS a, 5 AS b
    ) ORDER BY x LIMIT 1;
  }
} {x 0 y 1}
} ;# ifcapable subquery

do_test select4-9.8 {
  execsql {
    SELECT 0 AS x, 1 AS y
    UNION
    SELECT 2 AS y, -3 AS x
    ORDER BY x LIMIT 1;
  }
} {0 1}

do_test select4-9.9.1 {
  execsql2 {
    SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
  }
} {a 1 b 2 a 3 b 4}

#
#***********************************************************************
#
# 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.3 2007/12/13 07:58:51 danielk1977 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
#        but simply x) then look for a column alias with the same
#        name.  If found, then sort by that column.
# 
#   (3)  Evaluate the term as an expression and sort by the
#        value of the expression.
# 
# For a compound SELECT the rules are modified slightly.
# In the third rule, the expression must exactly match one
# of the result columns.  The sequences of three rules is
# attempted first on the left-most SELECT.  If that doesn't
# work, we move to the right, one by one. This is not standard
# SQL, it is an SQLite extension.
#


# Test plan:
#
#   tkt2822-1.* - Simple identifier as ORDER BY expression.
#   tkt2822-2.* - More complex ORDER BY expressions.

do_test tkt2822-0.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(a, b, c);

    INSERT INTO t1 VALUES(1, 3, 9);
    INSERT INTO t1 VALUES(3, 9, 27);
    INSERT INTO t1 VALUES(5, 15, 45);

    INSERT INTO t2 VALUES(2, 6, 18);
    INSERT INTO t2 VALUES(4, 12, 36);
    INSERT INTO t2 VALUES(6, 18, 54);
  }
} {}

# Test the "ORDER BY <integer>" syntax.
#
do_test tkt2822-1.1 {
  execsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 1;
  }






} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}



do_test tkt2822-1.2 {
  execsql {
    SELECT a, CAST (b AS TEXT), c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 


      ORDER BY 2;



  }

} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}







# Test the "ORDER BY <identifier>" syntax.
#
do_test tkt2822-2.1 {
  execsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

do_test tkt2822-2.2 {
  execsql {
    SELECT a, CAST (b AS TEXT) AS x, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY x;
  }

} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
do_test tkt2822-2.3 {
  execsql {
    SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY a;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

# Test the "ORDER BY <expression>" syntax.
#
do_test tkt2822-3.1 {
  execsql {
    SELECT a, CAST (b AS TEXT) AS x, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY CAST (b AS TEXT);
  }
} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
do_test tkt2822-3.2 {
  execsql {
    SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY t1.a;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

# Test that if a match cannot be found in the leftmost SELECT, an
# attempt is made to find a match in subsequent SELECT statements.
#
do_test tkt2822-3.1 {
  execsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
do_test tkt2822-3.2 {
  # But the leftmost SELECT takes precedence.
  execsql {
    SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY a;
  }
} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
do_test tkt2822-3.3 {
  execsql {
    SELECT a, b, c FROM t2 
      UNION ALL 
    SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 
      ORDER BY a;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

# Test some error conditions (ORDER BY clauses that match no column).
#
do_test tkt2822-4.1 {
  catchsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x
  }
} {1 {1st ORDER BY term does not match any column in the result set}}
do_test tkt2822-4.2 {
  catchsql {
    SELECT a, CAST (b AS TEXT) AS x, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY CAST (b AS INTEGER);
  }
} {1 {1st ORDER BY term does not match any column in the result set}}

finish_test