Documentation Source Text

<title>SQLite SQL Language Syntax Requirements</title>

<tcl>
proc syntaxreq {id derivedfrom explaination text {bnf {}}} {
  hd_fragment $id $id
  set dlist {}
  foreach d $derivedfrom {
    append dlist <$d>

  global syntax_dcnt
  incr syntax_dcnt
  eval hd_fragment D$syntax_dcnt $namelist
  hd_resolve "\n<p>$text</p>\n\n"
}
</tcl>

<h1>SQLite Language Syntax Requirements</h1>

<p>These requirements make the distinction between "preparing" an
SQL statement and "evaluating" or "invoking" an SQL statement.
Preparation means that the SQL statement text is translated into
an internal binary representation that is more suitable for processing.
Evaluation or invocation means that the processing is actually carried out.
Preparation of an SQL statement is usually accomplished by interfaces

  Hence, SQLite considers identifiers "A" and "a" to be equivalent but
  "Å" and "å" are distinct.
} {
  SQLite shall use the built-in NOCASE collating sequence when comparing
  identifiers and datatype names within SQL statements during 
  statement preparation.
}
syntaxreq {H42008} {} {
  Many tokens are allowed to "fallback" to the ID token.  The SQL
  language has many keywords.  The fallback token feature provides
  robustness in the case where a seldom-used keyword is accidentally
  used as the name of a table or column.
} {
  A token received by the parser shall be converted into an ID token
  if the original token value would have resulted in a syntax error,
  a token value of ID will allow the parse to continue,
  and if the original token value was one of:
  ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN CASCADE CAST CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW
  TEMP TRIGGER VACUUM VIEW VIRTUAL
  EXCEPT INTERSECT UNION
  REINDEX RENAME CTIME_KW IF
}
syntaxreq {H420012} {} {
  A second level of fallback allows any token to be converted into
  the token ANY.  The ANY token is used to gather arbitrary delimited
  token sequences, such as used as the arguments to the CREATE VIRTUAL
  TABLE command.
} {
  A token received by the parser shall be converted into an ANY token
  if the original token value would have resulted in a syntax error
  and if a token value of ANY will allow the parse to continue.
}
 
</tcl>

<h2>1.0 Transaction Control</h2>

<h3>1.1 BEGIN</h3>

<tcl>

  In an ordinary CREATE TABLE statement, the <i>table_definition</i>
  consists of a list of column definitions optionally followed by a 
  list of table constraints.
} {
  The SQLite parser shall accept the following syntax for the 
  <i>table_definition</i> section of a CREATE TABLE statement:
} {
  table_definition ::= LP columnlist constraint_opt RP.
  columnlist ::= column.
  columnlist ::= columnlist COMMON column.
  constraint_opt ::= .
  constraint_opt ::= COMMA constraint_list.
}
syntaxreq {H42413} {} {
  A column definition within an ordinary CREATE TABLE statement always
  has a column name.  It might also include a datatype for the
  column and zero or more contraints on the column.  But both the

  contraints that occurs at the end of an [ordinary CREATE TABLE statement].
} {
  constraint_list ::= constraint.
  constraint_list ::= constraint_list constraint.
  constraint_list ::= constraint_list COMMA constraint.
  constraint ::= CHECK LP expr RP conflict.
  constraint ::= CONSTRAINT name.
  constraint ::= FOREIGN KEY LP columnlist RP foreign_key_constraint defer_opt.
  constraint ::= PRIMARY KEY LP indexlist autoinc RP conflict.
  constraint ::= UNIQUE LP indexlist RP conflict.
}
syntaxreq {H42513} {} {} {
  The preparation of a CREATE TABLE statement that contains more
  than one PRIMARY KEY constraint shall fail with an error.
}

  The SQLite parser shall accept CREATE TRIGGER statements
  that conform to the following syntax:
} {
  cmd ::= CREATE temp TRIGGER ifnotexists fullname trigger trigger_body.
  trigger ::= trigger_time trigger_event foreach_clause when_clause.
  trigger_body ::= BEGIN trigger_cmd_list END.
  trigger_cmd_list ::= trigger_cmd_list trigger_cmd SEMI.
  trigger_cmd_list ::= trigger_cmd_list.
  trigger_cmd ::= DELETE FROM tablename where.
  trigger_cmd ::= update_cmd tablename SET setlist where.
  trigger_cmd ::= insert_cmd INTO tablename columnlist_opt VALUES LP exprlist RP.
  trigger_cmd ::= insert_cmd INTO tablename columnlist_opt select.
  trigger_cmd ::= select.
  trigger_event ::= DELETE ON trigger_target.
  trigger_event ::= INSERT ON trigger_target.
  trigger_event ::= UPDATE OF columnlist ON trigger_target.
  trigger_event ::= UPDATE ON trigger_target.
  trigger_target ::= fullname.
  trigger_time ::= AFTER.
  trigger_time ::= BEFORE.
  trigger_time ::= INSTEAD OF.
  trigger_time ::=.
  foreach_clause ::= FOR EACH ROW.

<tcl>
syntaxreq {H43600} {} {} {
  The SQLite parser shall accept CREATE VIRTUAL TABLE statements
  that conform to the following syntax.
} {
  cmd ::= CREATE VIRTUAL TABLE fullname USING name vtab_arg_list.
  vtab_arg_list ::= .
  vtab_arg_list ::= LP vtab_arg_token RP.
  vtab_arg_token ::= ANY.
  vtab_arg_token ::= LP anylist RP.
  anylist ::= .
  anylist ::= anylist ANY.
}
</tcl>

<h3>2.10 ALTER TABLE</h3>
<h4>2.11 ALTER TABLE RENAME</h4>

<tcl>

  column_keyword ::= COLUMNKW.
}
</tcl>

<h2>3.0 Data Manipulation Language (DML)</h2>

<h3>3.1 INSERT</h3>


<tcl>
syntaxreq {H43810} {} {} {
  The SQLite parser shall accept INSERT statements that
  conform to the following syntax:
} {
  cmd ::= insert_cmd INTO fullname columnlist_opt insert_content.
  insert_cmd ::= INSERT.
  insert_cmd ::= REPLACE.
  insert_cmd ::= INSERT OR REPLACE.
  insert_cmd ::= INSERT OR IGNORE.
  insert_cmd ::= INSERT OR ABORT.
  insert_cmd ::= INSERT OR FAIL.
  insert_cmd ::= INSERT OR ROLLBACK.
  columnlist_opt ::= .
  columnlist_opt ::= LP columnlist RP.
  columnlist ::= columnname.
  columnlist ::= columnlist COMMA columnname.
}
syntaxreq {H43813} {} {} {
  The preparation of an INSERT statement shall fail with an error
  if the <i>instcolist</i> contains a reference to a column which
  is not a column in the table identified by <i>fullname</i> and is
  not one of the special column named "ROWID", "OID", or "_ROWID_".
}
syntaxreq {H43816} {} {} {
  The preparation of an INSERT statement shall fail with an error
  if <i>fullname</i> does not identify either a view with an
  INSTEAD OF INSERT trigger or a table.
}
syntaxreq {H43819} {} {} {
  The preparation of an INSERT statement shall fail with an error
  if the <i>objectname</i> of the <i>fullname</i> is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable schema mode.
}
syntaxreq {H43821} {} {} {
  When the form of an INSERT statement is simply "INSERT" then the
  default null- and uniqueness-conflict resolution algorithms shall
  be used.
}
syntaxreq {H43824} {} {} {
  When the form of an INSERT statement is simply "REPLACE" then the
  null- and uniqueness-conflict resolution algorithms shall all
  change to REPLACE.
}
syntaxreq {H43827} {} {} {
  When the form of an INSERT statement is "INSERT OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
}
</tcl>

<h4>3.1.1 INSERT VALUE</h4>
<tcl>
syntaxreq {H43840} {} {} {
  The SQLite parser shall accept INSERT VALUE statements that
  conform to the following syntax:
} {
  insert_content ::= VALUES LP exprlist RP.
}
syntaxreq {H43843} {} {} {
  The preparation of an INSERT VALUE statement shall fail with an
  error if the <i>columnlist</i> element exists and the number of 
  entries in the <i>instcollist</i> is different
  from the number of entries in the <i>exprlist</i>.
}
syntaxreq {H43846} {} {} {
  The preparation of an INSERT VALUE statement shall fail with an
  error if the <i>columnlist</i> element does not exists and the number of 
  entries in the <i>exprlist</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}
</tcl>

<h4>3.1.2 INSERT SELECT</h4>
syntaxreq {H43870} {} {} {
  The SQLite parser shall accept INSERT SELECT statements that
  conform to the following syntax:
} {
  insert_contents ::= select.
}
syntaxreq {H43873} {} {} {
  The preparation of an INSERT SELECT statement shall fail with an
  error if the <i>columnlist</i> element exists and the number of 
  entries in the <i>instcollist</i> is different
  from the number of columns in the result set of <i>select</i>.
}
syntaxreq {H43876} {} {} {
  The preparation of an INSERT VALUE statement shall fail with an
  error if the <i>columnlist</i> element does not exists and the number of 
  columns in the result set of the <i>select</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}
</tcl>
<h4>3.1.3 INSERT DEFAULT</h4>
<tcl>
syntaxreq {H43890} {} {} {
  The SQLite parser shall accept INSERT DEFAULT statements that
  conform to the following syntax:
} {
  insert_contents ::= DEFAULT VALUES.
}
</tcl>








<h3>3.2 DELETE</h3>

<tcl>
syntaxreq {H43900} {} {} {
  The SQLite parser shall accept DELETE statements that
  conform to the following syntax:
} {
  cmd ::= DELETE FROM fullname where.
  where ::= .
  where ::= WHERE expr.
}
syntaxreq {H43904} {} {} {
  The preparation of a DELETE statement shall fail with an error if
  the <i>fullname</i> element does not identify a view with an
  INSTEAD OF DELETE trigger or a table.
}
syntaxreq {H43907} {} {} {
  The preparation of a DELETE statement shall fail with an error if
  the <i>objectname</i> of the <i>fullname</i> element is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable_schema mode.
}
</tcl>

<h3>3.3 UPDATE</h3>

<tcl>
syntaxreq {H44100} {} {} {

  update_cmd ::= UPDATE OR ABORT.
  update_cmd ::= UPDATE OR FAIL.
  update_cmd ::= UPDATE OR ROLLBACK.
  setlist ::= setting.
  setlist ::= setlist COMMA setting.
  setting ::= columnname EQ expr.
}
syntaxreq {H44113} {} {} {
  The preparation of an UPDATE statement shall fail with an error
  if any <i>columnname</i>
  is not a column in the table identified by <i>fullname</i> and is
  not one of the special columns named "ROWID", "OID", or "_ROWID_".
}
syntaxreq {H44116} {} {} {
  The preparation of an UPDATE statement shall fail with an error
  if <i>fullname</i> does not identify either a view with an
  INSTEAD OF UPDATE trigger that covers all <i>columnname</i>s or a table.
}
syntaxreq {H44119} {} {} {
  The preparation of an UPDATE statement shall fail with an error
  if the <i>objectname</i> of the <i>fullname</i> is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable schema mode.
}
syntaxreq {H44121} {} {} {
  When the form of an UPDATE statement is simply "UPDATE" then the
  default null- and uniqueness-conflict resolution algorithms shall
  be used.
}
syntaxreq {H44127} {} {} {
  When the form of an UPDATE statement is "UPDATE OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
}
</tcl>

<h3>3.4 SELECT</h3>

<tcl>
syntaxreq {H45000} {} {} {
  The SQLite parser shall accept SELECT statements that

  source ::= LP select RP as on using.
  as ::= .
  as ::= AS name.
  as ::= identifier.
  on ::= .
  on ::= ON expr.
  using ::= .
  using ::= USING LP columnlist RP.
  joinop ::= COMMA.
  joinop ::= JOIN.
  joinop ::= JOIN_KW JOIN.
  joinop ::= JOIN_KW JOIN_KW JOIN.
  joinop ::= JOIN_KW JOIN_KW JOIN_KW JOIN.
}
syntaxreq {H45003} {} {
  A sequence of <i>query</i> terms connected by UNION, UNION ALL, EXCEPT,
  and/or INTERSECT operators is called a "compound query" and the individual
  <i>query</i> terms are call "terms of the compound query".
} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains
  two terms of the same compound query having a different number of columns
  in their result sets.
}
syntaxreq {H45006} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains a compound query that
  has an ORDER BY, GROUP BY, HAVING, or LIMIT clause on any term of than
  the right-most.
}
syntaxreq {H45009} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains a compound query with
  an ORDER BY or GROUP BY clause with a term that is not either
  a token-by-token duplicate of the result columns of one of the
  compound query terms, or the "AS" name of one of the compound
  query terms, or a compile-time integer between 1 and N where N is
  the number of columns in each compound query term.
}
syntaxreq {H45012} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains a join with two
  or more of the following properties:
  <ol>
  <li> The NATURAL keyword in the <i>joinop</i> </li>
  <li> An ON clause </li>
  <li> A USING clause</li>
  </ol>
}
syntaxreq {H45015} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains a <i>joinop</i>
  that uses the keywords RIGHT or FULL.
}
syntaxreq {H45018} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains a <i>joinop</i>
  that uses either of the keywords OUTER or LEFT
  together with either INNER or CROSS.
}
syntaxreq {H45021} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>select</i> contains a <i>using</i>
  that names columns that are not found in both the table to the
  immediate right of the join and in the result set of all tables
  to the left of the join.
}
syntaxreq {H45024} {} {} {
  The preparation of a statement containing a <i>select</i>
  shall fail with an error if the <i>fullname</i> of a <i>source</i>
  does not refer to an existing table or view.
}
syntaxreq {H45027} {} {} {
  The preparation of a statement containing a <i>limit</i>
  shall fail with an error if any <i>expr</i> within the <i>limit</i>
  does not evaluate to a compile-time integer.
}
</tcl>

<h2>4.0 Other Language Elements</h2>

<h3>4.1 VACUUM</h3>

<tcl>

syntaxreq {H44300} {} {} {
  The SQLite parser shall accept ANALYZE statements
  that conform to the following syntax:
} {
  cmd ::= ANALYZE.
  cmd ::= ANALYZE fullname.
}
syntaxreq {H44303} {} {} {
  The preparation of an ANALYZE statement
  shall fail with an error if
  the <i>fullname</i> is included and does not evaluate to
  either an individual table name or the name of a database.
}
</tcl>

<h3>4.3 REINDEX</h3>

<tcl>
syntaxreq {H44400} {} {} {
  The SQLite parser shall accept REINDEX statements
  that conform to the following syntax:
} {
  cmd ::= REINDEX.
  cmd ::= REINDEX fullname.
}
syntaxreq {H44403} {} {} {
  The preparation of an ANALYZE statement
  shall fail with an error if
  the <i>fullname</i> is included and does not evaluate to
  either an individual table name or the name of a database
  or the name of a collating sequence.	
}
</tcl>

<h3>4.4 PRAGMA</h3>

<tcl>
syntaxreq {H46000} {} {} {
  The SQLite parser shall accept PRAGMA statements
  that conform to the following syntax:
} {
  cmd ::= PRAGMA fullname EQ DELETE.
  cmd ::= PRAGMA fullname EQ ON.
  cmd ::= PRAGMA fullname EQ name.
  cmd ::= PRAGMA fullname EQ expr.
  cmd ::= PRAGMA fullname LP name RP.
  cmd ::= PRAGMA fullname LP expr RP.
  cmd ::= PRAGMA fullname.
}
syntaxreq {H46003} {} {
  The <i>objectname</i> of the <i>fullname</i> of a PRAGMA is called
  the PRAGMA verb.
} {
  The evaluation of a PRAGMA statement with an unknown verb shall
  be a silent no-op.
}
</tcl>

<h3>4.5 ATTACH</h3>

<tcl>
syntaxreq {H44500} {} {
  The SQLite parser shall accept ATTACH statements
  that conform to the following syntax:
} {
  cmd ::= ATTACH database_kw expr AS expr.
  database_kw ::= .
  database_kw ::= DATABASE.

}
syntaxreq {H44503} {} {} {
  The <i>expr</i> terms of an ATTACH statement that are
  identifiers shall be interpreted as string literals.
}
syntaxreq {H44506} {} {} {
  The preparation of an ATTACH statement shall fail with an error
  if either <i>expr</i> is not a constant expression.
}
syntaxreq {H44509} {} {} {
  The preparation of an ATTACH statement shall fail with an error
  if the second <i>expr</i> evaluates to the name of a database
  that is already attached to the database connection.
}
</tcl>

<h3>4.6 DETACH</h3>

<tcl>
syntaxreq {H44600} {} {} {
  The SQLite parser shall accept DETACH statements
  that conform to the following syntax:
} {
  cmd ::= DETACH database_kw expr.
}
syntaxreq {H44603} {} {} {
  The <i>expr</i> term of an DETACH statement that is an
  identifier shall be interpreted as string literals.
}
syntaxreq {H44606} {} {} {
  The preparation of an DETACH statement shall fail with an error
  if the <i>expr</i> is not a constant expression.
}
syntaxreq {H44609} {} {} {
  The preparation of an DETACH statement shall fail with an error
  if the <i>expr</i> does not evaluate to the 
  name of an attached database other than "temp" or "main".
}
</tcl>

<h3>4.7 EXPLAIN</h3>

<tcl>
syntaxreq {H44700} {} {} {
  The SQLite parser shall accept the EXPLAIN keyword as a prefix

  expr ::= MINUS expr.
  expr ::= NOT expr.
  expr ::= PLUS expr.
  expr ::= RAISE LP IGNORE RP.
  expr ::= RAISE LP ABORT COMMA name RP.
  expr ::= RAISE LP FAIL COMMA name RP.
  expr ::= RAISE LP ROLLBACK COMMA name RP.

  expr ::= VARIABLE.
  expr ::= expr AND expr.
  expr ::= expr BITAND expr.
  expr ::= expr BITOR expr.
  expr ::= expr LSHIFT expr.
  expr ::= expr RSHIFT expr.
  expr ::= expr COLLATE ids.

  case_exprlist ::= case_exprlist WHEN expr THEN expr.
  case_operand ::= expr.
  case_operand ::= .
  function_name ::= ID.
  escape ::= .
  escape ::= ESCAPE expr.
}
syntaxreq {H47003} {} {} {
  The unary PLUS, unary MINUS, and BITNOT operators shall have
  precedence over the COLLATE operator.
}
syntaxreq {H47006} {} {} {
  The COLLATE operator shall have precedence over the CONCAT
  operator.
}
syntaxreq {H47009} {} {} {
  The CONCAT operator shall have precedence over the STAR, SLASH,
  and REM operators.
}
syntaxreq {H47012} {} {} {
  The STAR, SLASH, and REM operator shall have equal precedence.
}
syntaxreq {H47015} {} {} {
  The STAR, SLASH, and REM operators shall have precedence over the
  binary PLUS and binary MINUS operators.
}
syntaxreq {H47018} {} {} {
  The binary PLUS and binary MINUS operators shall have equal precedence.
}
syntaxreq {H47021} {} {} {
  The binary PLUS and binary MINUS operators shall have precedence
  over the BITAND, BITOR, LSHIFT, and RSHIFT operators.
}
syntaxreq {H47024} {} {} {
  The BITAND, BITOR, LSHIFT, and RSHIFT operators shall have equal
  precendence.
}
syntaxreq {H47027} {} {} {
  The BITAND, BITOR, LSHIFT, and RSHIFT operators shall have precedence
  over the ESCAPE operator.
}
syntaxreq {H47029} {} {} {
  The ESCAPE operator shall have precedence over
  the GT, LE, LT, and GE operators.
}
syntaxreq {H47033} {} {} {
  The GT, LE, LT, and GE operators shall have equal precedence.
}
syntaxreq {H47036} {} {} {
  The GT, LE, LT, and GE operators shall have precedence over
  the IS, MATCH, LIKE_KW, BETWEEN, IN, ISNULL, NOTNULL, NE, and EQ
  operators shall have equal precedence.
}
syntaxreq {H47039} {} {} {
  The IS, MATCH, LIKE_KW, BETWEEN, IN, ISNULL, NOTNULL, NE, and EQ
  operators shall have equal precedence.
}
syntaxreq {H47042} {} {} {
  The IS, MATCH, LIKE_KW, BETWEEN, IN, ISNULL, NOTNULL, NE, and EQ
  operators shall have precedence over the unary NOT operator.
}
syntaxreq {H47045} {} {} {
  The unary NOT operator shall have precedence over the AND operator.
}
syntaxreq {H47048} {} {} {
  The AND operator shall have precedence over the OR operator.
}
syntaxreq {H47051} {} {} {
  Operators of equal precedence shall group from right to left.
}
</tcl>

<h3>5.2 Functions</h3>
<h4>5.2.1 Core Scalar Functions</h4>
<h4>5.2.2 Date and Time Functions</h4>
<h4>5.2.3 Aggregate Functions</h4>