SQLite

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.85 2002/10/27 19:35:34 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);

%left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
%left GT GE LT LE.
%left BITAND BITOR LSHIFT RSHIFT.
%left PLUS MINUS.
%left STAR SLASH REM.
%left CONCAT.
%right UMINUS UPLUS BITNOT.
%right ORACLE_OUTER_JOIN.

%type expr {Expr*}
%destructor expr {sqliteExprDelete($$);}

expr(A) ::= LP(B) expr(X) RP(E). {A = X; sqliteExprSpan(A,&B,&E); }
expr(A) ::= NULL(X).             {A = sqliteExpr(TK_NULL, 0, 0, &X);}
expr(A) ::= ID(X).               {A = sqliteExpr(TK_ID, 0, 0, &X);}
expr(A) ::= JOIN_KW(X).          {A = sqliteExpr(TK_ID, 0, 0, &X);}
expr(A) ::= nm(X) DOT nm(Y). {
  Expr *temp1 = sqliteExpr(TK_ID, 0, 0, &X);
  Expr *temp2 = sqliteExpr(TK_ID, 0, 0, &Y);
  A = sqliteExpr(TK_DOT, temp1, temp2, 0);
}
expr(A) ::= expr(B) ORACLE_OUTER_JOIN. 
                             {A = B; ExprSetProperty(A,EP_Oracle8Join);}
expr(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
expr(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
expr(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
expr(A) ::= ID(X) LP exprlist(Y) RP(E). {
  A = sqliteExprFunction(Y, &X);
  sqliteExprSpan(A,&X,&E);
}

**    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.115 2002/10/27 19:35:35 drh Exp $
*/
#include "sqliteInt.h"


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

  pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);
  dummy.z = pTab2->zName;
  dummy.n = strlen(dummy.z);
  pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);
  pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
  pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
  pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
  ExprSetProperty(pE, EP_FromJoin);
  if( *ppExpr ){
    *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
  }else{
    *ppExpr = pE;
  }
}

/*
** Set the EP_FromJoin property on all terms of the given expression.
**
** The EP_FromJoin property is used at on terms of an expression to tell
** the LEFT OUTER JOIN processing logic that this term is part of the
** join restriction specified in the ON or USING clause and not a part
** of the more general WHERE clause.  These terms are moved over to the
** WHERE clause during join processing but we need to remember that they
** originated in the ON or USING clause.
*/
static void setJoinExpr(Expr *p){
  while( p ){
    ExprSetProperty(p, EP_FromJoin);
    setJoinExpr(p->pLeft);
    p = p->pRight;
  } 
}

/*
** This routine processes the join information for a SELECT statement.

        }
        addWhereTerm(pList->a[j].zName, pTerm->pTab, pOther->pTab, &p->pWhere);
      }
    }
  }
  return 0;
}

/*
** This routine implements a minimal Oracle8 join syntax immulation.
** The precise oracle8 syntax is not implemented - it is easy enough
** to get this routine confused.  But this routine does make it possible
** to write a single SQL statement that does a left outer join in both
** oracle8 and in SQLite.
**
** This routine looks for TK_COLUMN expression nodes that are marked
** with the EP_Oracle8Join property.  Such nodes are generated by a
** column name (either "column" or "table.column") that is followed by
** the special "(+)" operator.  If the table of the column marked with
** the (+) operator is the second are subsequent table in a join, then
** that table becomes the left table in a LEFT OUTER JOIN.  The expression
** that uses that table becomes part of the ON clause for the join.
**
** It is important to enphasize that this is not exactly how oracle8
** works.  But it is close enough so that one can construct queries that
** will work correctly for both SQLite and Oracle8.
*/
static int sqliteOracle8JoinFixup(
  int base,         /* VDBE cursor number for first table in pSrc */
  SrcList *pSrc,    /* List of tables being joined */
  Expr *pWhere      /* The WHERE clause of the SELECT statement */
){
  int rc = 0;
  if( ExprHasProperty(pWhere, EP_Oracle8Join) && pWhere->op==TK_COLUMN ){
    int idx = pWhere->iTable - base;
    assert( idx>=0 && idx<pSrc->nSrc );
    if( idx>0 ){
      pSrc->a[idx-1].jointype &= ~JT_INNER;
      pSrc->a[idx-1].jointype |= JT_OUTER|JT_LEFT;
      return 1;
    }
  }
  if( pWhere->pRight ){
    rc = sqliteOracle8JoinFixup(base, pSrc, pWhere->pRight);
  }
  if( pWhere->pLeft ){
    rc |= sqliteOracle8JoinFixup(base, pSrc, pWhere->pLeft);
  }
  if( pWhere->pList ){
    int i;
    ExprList *pList = pWhere->pList;
    for(i=0; i<pList->nExpr && rc==0; i++){
      rc |= sqliteOracle8JoinFixup(base, pSrc, pList->a[i].pExpr);
    }
  }
  if( rc==1 && (pWhere->op==TK_AND || pWhere->op==TK_EQ) ){
    setJoinExpr(pWhere);
    rc = 0;
  }
  return rc;
}

/*
** Delete the given Select structure and all of its substructures.
*/
void sqliteSelectDelete(Select *p){
  if( p==0 ) return;
  sqliteExprListDelete(p->pEList);

  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pWhere) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto select_end;
    }
    sqliteOracle8JoinFixup(base, pTabList, pWhere);
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsConstant(pE) ){
        int iCol;
        if( sqliteExprIsInteger(pE, &iCol)==0 ){

/*
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.148 2002/10/27 19:35:35 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

** be the right operand of an IN operator.  Or, if a scalar SELECT appears
** in an expression the opcode is TK_SELECT and Expr.pSelect is the only
** operand.
*/
struct Expr {
  u8 op;                 /* Operation performed by this node */
  u8 dataType;           /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */
  u16 flags;             /* Various flags.  See below */
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as function arguments
                         ** or in "<expr> IN (<expr-list)" */
  Token token;           /* An operand token */
  Token span;            /* Complete text of the expression */
  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table. */
  int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
                         ** result from the iAgg-th element of the aggregator */
  Select *pSelect;       /* When the expression is a sub-select.  Also the
                         ** right side of "<expr> IN (<select>)" */
};

/*
** The following are the meanings of bits in the Expr.flags field.
*/
#define EP_FromJoin     0x0001  /* Originated in ON or USING clause of a join */
#define EP_Oracle8Join  0x0002  /* Carries the Oracle8 "(+)" join operator */

/*
** These macros can be used to test, set, or clear bits in the 
** Expr.flags field.
*/
#define ExprHasProperty(E,P)     (((E)->flags&(P))==(P))
#define ExprHasAnyProperty(E,P)  (((E)->flags&(P))!=0)
#define ExprSetProperty(E,P)     (E)->flags|=(P)
#define ExprClearProperty(E,P)   (E)->flags&=~(P)

/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
** list of "ID = expr" items in an UPDATE.  A list of expressions can
** also be used as the argument to a function, in which case the a.zName
** field is not used.

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.51 2002/10/27 19:35:35 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*

        *tokenType = TK_COMMENT;
        return i;
      }
      *tokenType = TK_MINUS;
      return 1;
    }
    case '(': {
      if( z[1]=='+' && z[2]==')' ){
        *tokenType = TK_ORACLE_OUTER_JOIN;
        return 3;
      }else{
        *tokenType = TK_LP;
        return 1;
      }
    }
    case ')': {
      *tokenType = TK_RP;
      return 1;
    }
    case ';': {
      *tokenType = TK_SEMI;

**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.66 2002/10/27 19:35:35 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
*/
typedef struct ExprInfo ExprInfo;
struct ExprInfo {
  Expr *p;                /* Pointer to the subexpression */
  u8 indexable;           /* True if this subexprssion is usable by an index */
  u8 oracle8join;         /* -1 if left side contains "(+)".  +1 if right side
                          ** contains "(+)".  0 if neither contains "(+)" */
  short int idxLeft;      /* p->pLeft is a column in this table number. -1 if
                          ** p->pLeft is not the column of any table */
  short int idxRight;     /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not the column of any table */
  unsigned prereqLeft;    /* Bitmask of tables referenced by p->pLeft */
  unsigned prereqRight;   /* Bitmask of tables referenced by p->pRight */
  unsigned prereqAll;     /* Bitmask of tables referenced by p */

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(j=0; j<nExpr; j++){
      if( aExpr[j].p==0 ) continue;
      if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
      if( pLevel->iLeftJoin && !ExprHasProperty(aExpr[j].p,EP_FromJoin) ){
        continue;
      }
      if( haveKey ){
        haveKey = 0;
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
      }
      sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1);
      aExpr[j].p = 0;
    }

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.7 2002/10/27 19:35:35 drh Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);

    INSERT INTO usuarios VALUES(3,'c','cc',NULL);
    create index idcentro on usuarios (idcentro);
    END;
    select usuarios.id, usuarios.nombre, centros.centro from
    usuarios left outer join centros on usuarios.idcentro = centros.id;
  }
} {1 a xxx 2 b xxx 3 c {}}

# Test the goofy Oracle8 outer join syntax.
#
do_test join-6.1 {
  execsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(2,3,4);
    INSERT INTO t1 VALUES(3,4,5);
    SELECT * FROM t1;
  }  
} {1 2 3 2 3 4 3 4 5}
do_test join-6.2 {
  execsql {
    DELETE FROM t2;
    INSERT INTO t2 VALUES(1,2,3);
    INSERT INTO t2 VALUES(2,3,4);
    INSERT INTO t2 VALUES(3,4,5);
    SELECT * FROM t2;
  }  
} {1 2 3 2 3 4 3 4 5}
do_test join-6.3 {
  execsql {
    SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.a=t2.c);
  }
} {1 2 3 {} {} {} 2 3 4 1 2 3 3 4 5 2 3 4}
do_test join-6.4 {
  execsql {
    SELECT * FROM t1, t2 WHERE t1.a=t2.c(+);
  }
} {1 2 3 {} {} {} 2 3 4 1 2 3 3 4 5 2 3 4}
do_test join-6.5 {
  execsql {
    SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.a=t2.c) WHERE t1.b=2
  }
} {1 2 3 {} {} {}}
do_test join-6.6 {
  execsql {
    SELECT * FROM t1, t2 WHERE t1.a=t2.c(+) AND t1.b=2;
  }
} {1 2 3 {} {} {}}
do_test join-6.7 {
  execsql {
    SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.b=t2.b AND t1.c=t2.c)
  }
} {1 2 3 2 3 4 2 3 4 3 4 5 3 4 5 {} {} {}}
do_test join-6.8 {
  execsql {
    SELECT * FROM t1, t2 WHERE t1.b=t2.b(+) AND t1.c=t2.c(+);
  }
} {1 2 3 2 3 4 2 3 4 3 4 5 3 4 5 {} {} {}}


finish_test