SQLite

** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.43 2005/01/15 01:52:32 drh Exp $
**
** NOTES:
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system.

  memset(p, 0, sizeof(*p));
  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite3StrICmp(zDate,"now")==0){
    double r;
    sqlite3OsCurrentTime(&r);
    p->rJD = r;
    p->validJD = 1;
    return 0;


  }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){
    p->rJD = sqlite3AtoF(zDate, 0);
    p->validJD = 1;
    return 0;
  }
  return 1;
}

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.178 2005/01/15 01:52:32 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**

*/
int sqlite3ExprIsInteger(Expr *p, int *pValue){
  switch( p->op ){
    case TK_INTEGER: {
      if( sqlite3GetInt32(p->token.z, pValue) ){
        return 1;
      }










      break;
    }
    case TK_UPLUS: {
      return sqlite3ExprIsInteger(p->pLeft, pValue);
    }
    case TK_UMINUS: {
      int v;

**    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.221 2005/01/15 01:52:32 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.

      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
      break;
    }



    case TK_SELECT: {
      Select *pS = pExpr->pSelect;
      zType = columnType(pParse, pS->pSrc, pS->pEList->a[0].pExpr); 
      break;
    }
    case TK_AS:
      /* The TK_AS operator can only occur in ORDER BY, GROUP BY, HAVING,
      ** and LIMIT clauses.  But pExpr originates in the result set of a
      ** SELECT.  So pExpr can never contain an AS operator.
      */
      assert( 0 );
      /* Fall thru */
    default:
      zType = 0;
  }
  
  return zType;
}

# 2003 October 31
#
# 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 date and time functions.
#
# $Id: date.test,v 1.12 2005/01/15 01:52:33 drh Exp $

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

# Skip this whole file if date and time functions are omitted
# at compile-time
#

datetest 1.19 {julianday('2000-01-01 12:00:00.1')}   2451545.00000116
datetest 1.20 {julianday('2000-01-01 12:00:00.01')}  2451545.00000012
datetest 1.21 {julianday('2000-01-01 12:00:00.001')} 2451545.00000001
datetest 1.22 {julianday('2000-01-01 12:00:00.')} NULL
datetest 1.23 julianday(12345.6) 12345.6
datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL
datetest 1.25 {julianday('2001-01-01 bogus')} NULL
datetest 1.26 {julianday('2001-01-01 12:60:00')} NULL
datetest 1.27 {julianday('2001-01-01 12:59:60')} NULL

datetest 2.1 datetime(0,'unixepoch') {1970-01-01 00:00:00}
datetest 2.2 datetime(946684800,'unixepoch') {2000-01-01 00:00:00}
datetest 2.3 {date('2003-10-22','weekday 0')} 2003-10-26
datetest 2.4 {date('2003-10-22','weekday 1')} 2003-10-27
datetest 2.5 {date('2003-10-22','weekday 2')} 2003-10-28
datetest 2.6 {date('2003-10-22','weekday 3')} 2003-10-22

set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]
datetest 4.1 {date('now')} $now

datetest 5.1 {datetime('1994-04-16 14:00:00 -05:00')} {1994-04-16 09:00:00}
datetest 5.2 {datetime('1994-04-16 14:00:00 +05:15')} {1994-04-16 19:15:00}
datetest 5.3 {datetime('1994-04-16 05:00:00 -08:30')} {1994-04-15 20:30:00}
datetest 5.4 {datetime('1994-04-16 14:00:00 +11:55')} {1994-04-17 01:55:00}
datetest 5.5 {datetime('1994-04-16 14:00:00 +11:60')} NULL

# localtime->utc and utc->localtime conversions.  These tests only work
# if the localtime is in the US Eastern Time (the time in Charlotte, NC
# and in New York.)
#
if {[clock scan [clock format 0 -format {%b %d, %Y %H:%M:%S}] -gmt 1]==-18000} {
  datetest 6.1 {datetime('2000-10-29 05:59:00','localtime')}\

   {2004-02-28 07:59:00}
datetest 11.7 {datetime('2004-02-28 20:00:00', '-11:59')} \
   {2004-02-28 08:01:00}
datetest 11.8 {datetime('2004-02-28 20:00:00', '11:59')} \
   {2004-02-29 07:59:00}
datetest 11.9 {datetime('2004-02-28 20:00:00', '12:01')} \
   {2004-02-29 08:01:00}
datetest 11.10 {datetime('2004-02-28 20:00:00', '12:60')} NULL




finish_test

#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.20 2005/01/15 01:52:33 drh Exp $

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

# Build some test data
#
execsql {

    SELECT * FROM (SELECT * FROM t6 LIMIT 3)
    UNION
    SELECT * FROM (SELECT * FROM t7 LIMIT 3)
    ORDER BY 1
    LIMIT 2
  }
} {1 2}
do_test limit-9.5 {
  catchsql {
    SELECT * FROM t6 LIMIT 3
    UNION
    SELECT * FROM t7 LIMIT 3
  }
} {1 {LIMIT clause should come after UNION not before}}

finish_test

# 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.38 2005/01/15 01:52:33 drh Exp $

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

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

  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.6 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY '8.4';
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.1 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 'xyz';
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.2 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY -8.4;
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.3 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY +8.4;
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.4 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.8 {
  execsql {
    CREATE TABLE t5(a,b);
    INSERT INTO t5 VALUES(1,10);
    INSERT INTO t5 VALUES(2,9);
    SELECT * FROM t5 ORDER BY 1;
  }
} {1 10 2 9}
do_test select1-4.9.1 {
  execsql {
    SELECT * FROM t5 ORDER BY 2;
  }
} {2 9 1 10}
do_test select1-4.9.2 {
  execsql {
    SELECT * FROM t5 ORDER BY +2;
  }
} {2 9 1 10}
do_test select1-4.10.1 {
  catchsql {
    SELECT * FROM t5 ORDER BY 3;
  }
} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}}
do_test select1-4.10.2 {
  catchsql {
    SELECT * FROM t5 ORDER BY -1;
  }
} {1 {ORDER BY column number -1 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 {