Documentation Source Text

Check-in [451999a899]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Continuing work on SQL language requirements.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 451999a899806ad0ae74c0e93b014a34ba8269be
User & Date: drh 2008-08-10 19:23:30
Context
2008-08-11
17:13
Ignore API tags for experimental and deprecated. check-in: 2ad6f9191f user: shaneh tags: trunk
2008-08-10
19:23
Continuing work on SQL language requirements. check-in: 451999a899 user: drh tags: trunk
2008-08-09
00:01
Continuing work on syntax requirements. check-in: ce20876314 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/syntax.in.

     1         -<title>SQLite SQL Syntax Requirements</title>
            1  +<title>SQLite SQL Language Requirements</title>
     2      2   
     3      3   <tcl>
     4      4   proc syntaxreq {id derivedfrom explaination text {bnf {}}} {
     5      5     hd_fragment $id $id
     6      6     set dlist {}
     7      7     foreach d $derivedfrom {
     8      8       append dlist <$d>
................................................................................
    24     24     global syntax_dcnt
    25     25     incr syntax_dcnt
    26     26     eval hd_fragment D$syntax_dcnt $namelist
    27     27     hd_resolve "\n<p>$text</p>\n\n"
    28     28   }
    29     29   </tcl>
    30     30   
    31         -<h1>SQLite Syntax Requirements</h1>
           31  +<h1>SQLite Language Requirements</h1>
    32     32   
    33     33   <p>These requirements make the distinction between "preparing" an
    34     34   SQL statement and "evaluating" or "invoking" an SQL statement.
    35     35   Preparation means that the SQL statement text is translated into
    36     36   an internal binary representation that is more suitable for processing.
    37     37   Evaluation or invocation means that the processing is actually carried out.
    38     38   Preparation of an SQL statement is usually accomplished by interfaces
................................................................................
   252    252   syntaxreq {H42325} {} {} {
   253    253     The preparation of a CREATE TABLE statement shall fail with an 
   254    254     error if the IF NOT EXISTS clause is omitted and the <i>objectname</i>
   255    255     is the same as the name of a table or view
   256    256     in the same database.
   257    257   }
   258    258   syntaxreq {H42328} {} {} {
   259         -  A CREATE TABLE statement shall be a silent no-op if the
          259  +  The evaluation of a CREATE TABLE statement shall be a silent no-op if the
   260    260     IF NOT EXISTS clause is present and the <i>objectname</i>
   261    261     is the same as the name of a table or view
   262    262     in the same database.
   263    263   }
   264    264   syntaxreq {H42331} {} {} {
   265         -  A CREATE TABLE statement shall fail with an error if the
          265  +  The preparation of a CREATE TABLE statement shall fail with an error if the
   266    266     the <i>objectname</i> is the same as the name of an index
   267    267     in any database attached to the same [database connection].
   268    268   }
   269    269   syntaxreq {H42334} {} {} {
   270         -  A CREATE TABLE statement shall fail with an error if the
          270  +  The preparation of a CREATE TABLE statement shall fail with an error if the
   271    271     the <i>databasename</i> references a database that is not
   272    272     attached to the same [database connection].
   273    273   }
   274    274   </tcl>
   275    275   
   276    276   <h4>2.1.1 CREATE TABLE column definitions</h4>
   277    277   
................................................................................
   377    377     conflict ::= ON CONFLICT IGNORE.
   378    378     conflict ::= ON CONFLICT REPLACE.
   379    379     conflict ::= ON CONFLICT ABORT.
   380    380     conflict ::= ON CONFLICT FAIL.
   381    381     conflict ::= ON CONFLICT ROLLBACK.
   382    382   }
   383    383   syntaxreq {H42453} {} {} {
          384  +  When a column as a DEFAULT constraint, the default value for the
          385  +  column shall be the value specified by that constraint.
          386  +}
          387  +syntaxreq {H42456} {} {} {
   384    388     If a column has no DEFAULT constraint then the default value for
   385    389     that column shall be NULL.
   386    390   }
   387         -syntaxreq {H42456} {} {} {
   388         -  Every column a table shall have a "NULL conflict resolution behavior"
   389         -  which is one of NONE, IGNORE, REPLACE, FAIL, ABORT, or ROLLBACK.
   390         -}
   391    391   syntaxreq {H42459} {} {
   392         -  The default NULL conflict resolution behavior is NONE, which means
          392  +  Every column a table has a "null-conflict resolution behavior"
          393  +  which is one of NONE, IGNORE, REPLACE, FAIL, ABORT, or ROLLBACK.</p>
          394  +
          395  +  <p>The default null-conflict resolution behavior is NONE, which means
   393    396     NULLs are allowed in the column.  This can be made explicit by
   394    397     specifying the NULL constraint.  But the use of the NULL constraint
   395         -  is merely comment for human readers.  SQLite silently ignores the
          398  +  is merely a comment for human readers.  SQLite silently ignores the
   396    399     NULL constraint and its ON CONFLICT clause.  SQLite only cares
   397    400     about NOT NULL constraints since only NOT NULL constraints make
   398    401     a behaviorial difference.
   399    402   } {
   400    403     If a column has no NOT NULL constraint then the NULL conflict
   401    404     resolution behavior for the column shall be NONE.
   402    405   }
   403    406   syntaxreq {H42462} {} {} {
   404         -  If a column has a NOT NULL constraint without an ON CONFLICT clause
   405         -  then the NULL conflict resolution behavior for the column shall
          407  +  If a column has a NOT NULL constraint and that constrait lacks
          408  +  an ON CONFLICT clause
          409  +  then the null-conflict resolution behavior for the column shall
   406    410     be ABORT.
   407    411   }
   408    412   syntaxreq {H42465} {} {} {
   409         -  If a column has a NOT NULL constraint with an ON CONFLICT clause
   410         -  then the NULL conflict resolution behavior for the column shall
          413  +  If a column has a NOT NULL constraint and that constraint
          414  +  has an ON CONFLICT clause
          415  +  then the null-conflict resolution behavior for the column shall
   411    416     be the behavior specified by the ON CONFLICT clause.
   412    417   }
   413    418   syntaxreq {H42467} {} {} {
   414    419     A column without a COLLATE constraint shall have a default
   415    420     collating sequence of BINARY.
   416    421   }
   417    422   syntaxreq {H42468} {} {} {
................................................................................
   439    444     an alias for the table rowid.
   440    445   }
   441    446   syntaxreq {H42476} {} {} {
   442    447     If a table contains no column named "_ROWID_" then "_ROWID_" shall be
   443    448     an alias for the table rowid.
   444    449   }
   445    450   syntaxreq {H42478} {} {
   446         -  The AUTOINCREMENT keyword is ignored except for on an explicit
          451  +  The AUTOINCREMENT keyword is ignored except when it appears on an explicit
   447    452     rowid column.
   448    453   } {
   449    454     A table shall have an autoincrementing rowid if and only if
   450    455     the PRIMARY KEY for the table is an alias for the rowid and
   451    456     the PRIMARY KEY declaration uses the AUTOINCR keyword.
   452    457   }
   453    458   
   454    459   syntaxreq {H42480} {} {} {
   455         -  Successful evaluation of a CREATE TABLE statement that contains
   456         -  a column UNIQUE constraint without an ON CONFLICT shall create
   457         -  unique index on the column and with a conflict resolution 
   458         -  algorithm of ABORT.
   459         -}
   460         -syntaxreq {H42483} {} {} {
   461         -  Successful evaluation of a CREATE TABLE statement that contains
   462         -  a column UNIQUE constraint with an ON CONFLICT shall create
   463         -  unique index on the column and with the specified conflict 
   464         -  resolution algorithm.
          460  +  Successful evaluation of a CREATE TABLE statement shall create
          461  +  an index for every UNIQUE constraint where the created index has
          462  +  a conflict resolution algorithm as specified by the ON CONFLICT
          463  +  clause of the UNIQUE constraint or a conflict resolution algorithm
          464  +  of ABORT if there is no ON CONFLICT clause on the constraint.
   465    465   }
   466    466   
   467    467   
   468    468   syntaxreq {H42510} {} {} {
   469    469     The SQLite parser shall accept the following syntax for the list of table
   470    470     contraints that occurs at the end of an [ordinary CREATE TABLE statement].
   471    471   } {
................................................................................
   488    488   }
   489    489   syntaxreq {H42517} {} {} {
   490    490     The preparation of a CREATE TABLE statement that contains a
   491    491     CHECK constraint that uses a parameter shall fail with an error.
   492    492   }
   493    493   syntaxreq {H42518} {} {} {
   494    494     The preparation of a CREATE TABLE statement that contains a
   495         -  CHECK constraint that references any column of any table other than
          495  +  CHECK constraint that references any column of a table other than
   496    496     the table being created shall fail with an error.
   497    497   }
   498    498   syntaxreq {H42521} {} {} {
   499    499     The preparation of a CREATE TABLE statement that contains a
   500    500     DEFAULT constraint with an non-constant expression
   501    501     shall fail with an error.
   502    502   }
   503         -syntaxreq {H42530} {} {
   504         -  Multi-column UNIQUE constraints work just like column UNIQUE
   505         -  constraints in that they generate a unique index on the column
   506         -  or columns specified.
   507         -} {
   508         -  Successful evaluation of a CREATE TABLE statement that contains
   509         -  a multi-column UNIQUE constraint without an ON CONFLICT shall create
   510         -  unique index on the specified columns and with a conflict 
   511         -  resolution algorithm of ABORT.
   512         -}
   513         -syntaxreq {H42532} {} {} {
   514         -  Successful evaluation of a CREATE TABLE statement that contains
   515         -  a multi-column UNIQUE constraint with an ON CONFLICT shall create
   516         -  unique index on the specified columns and with the specified 
   517         -  conflict resolution algorithm.
   518         -}
   519    503   syntaxreq {H42536} {} {
   520    504     Except for the special case of INTEGER PRIMARY KEY, a
   521    505     PRIMARY KEY is just an alias for UNIQUE.
   522    506   } {
   523    507     A PRIMARY KEY constraint that does not result in a rowid alias
   524    508     shall have the same effect as a UNIQUE constraint.
   525    509   }
................................................................................
   589    573     statement shall have base names which are the names of the columns
   590    574     in the result set of the SELECT statement
   591    575   }
   592    576   syntaxreq {H42617} {} {} {
   593    577     Each column name in a table generated by a CREATE TABLE AS
   594    578     statement shall have an arbitrary suffix appended to its basename
   595    579     if and only if such a suffix is necessary to make the name
   596         -  different from all preceding column names in the table.
          580  +  distinct from all preceding column names in the table.
   597    581   }
   598    582   syntaxreq {H42619} {} {} {
   599    583     All columns in a table generated by a CREATE TABLE AS statement
   600    584     shall have a default value of NULL.
   601    585   }
   602    586   syntaxreq {H42622} {} {} {
   603    587     All columns in a table generated by a CREATE TABLE AS statement
................................................................................
   642    626     database and discarded.
   643    627   }
   644    628   syntaxreq {H42719} {} {} {
   645    629     The successful evaluation of a DROP TABLE statement shall cause
   646    630     all indices attached to the table identified by <i>fullname</i>
   647    631     to be removed from their database and discarded.
   648    632   }
          633  +syntaxreq {H42721} {} {} {
          634  +  The successful evaluation of a DROP TABLE statement shall cause
          635  +  all triggers attached to the table identified by <i>fullname</i>
          636  +  to be removed from their database and discarded.
          637  +}
          638  +syntaxreq {H42724} {} {} {
          639  +  The preparation of a DROP TABLE statement shall fail with an
          640  +  error if <i>fullname</i> is a system table.
          641  +}
   649    642   </tcl>
   650    643   
   651    644   <h3>2.3 CREATE INDEX</h3>
   652    645   
   653    646   <tcl>
   654    647   syntaxreq {H42800} {} {} {
   655    648     The SQLite parser shall accept CREATE INDEX statements that 
................................................................................
   665    658     sortorder ::= .
   666    659     sortorder ::= ASC.
   667    660     sortorder ::= DESC.
   668    661   }
   669    662   syntaxreq {H42803} {} {} {
   670    663     The target database of a CREATE INDEX statement shall be the 
   671    664     <i>databasename</i> specified in the <i>fullname</i> term of the
   672         -  statement if that <i>databasename</i> exists.
          665  +  statement if the <i>databasename</i> exists.
   673    666   }
   674    667   syntaxreq {H42806} {} {} {
   675    668     If the <i>fullname</i> term of a CREATE INDEX statement does not
   676    669     specify a <i>databasename</i> and the <i>tablename</i> references a table 
   677    670     that is in the "temp" database, then the target database for the statement
   678    671     shall be "temp".
   679    672   }
................................................................................
   697    690     A successful evaluation of a CREATE INDEX statement shall create a 
   698    691     new index called <i>objectname</i>
   699    692     in the database of the statement and attached to the
   700    693     table identified by <i>tablename</i> in that same database.
   701    694   }
   702    695   syntaxreq {H42821} {} {} {
   703    696     An index generated by a CREATE INDEX statement that omits the
   704         -  UNIQUE keyword shall have a uniqueness conflict resolution behavior
          697  +  UNIQUE keyword shall have a conflict resolution behavior
   705    698     of NONE.
   706    699   }
   707    700   syntaxreq {H42824} {} {} {
   708    701     An index generated by a CREATE INDEX statement that includes the
   709         -  UNIQUE keyword shall have a uniqueness conflict resolution behavior
          702  +  UNIQUE keyword shall have a conflict resolution behavior
   710    703     of ABORT.
   711    704   }
   712    705   syntaxreq {H42830} {} {} {
   713    706     The preparation of a CREATE INDEX statement shall fail with an error if any
   714    707     <i>columnname</i> value within the <i>indexlist</i> is not the
   715    708     name of one of the columns of the <i>tablename</i> table.
   716    709   }
   717    710   syntaxreq {H42833} {} {
   718    711     The following rules regarding default collating sequences and
   719    712     sort order for indices applies both to indices created by CREATE INDEX
          713  +  statements
   720    714     and also by UNIQUE or PRIMARY KEY constraints on the table definition.
   721    715   } {
   722    716     The collating sequence for each column of an index shall be the
   723    717     collating sequence specified in the <i>indexlist</i>.
   724    718   }
   725    719   syntaxreq {H42836} {} {} {
   726    720     If an index column does not specify a collating sequence then
................................................................................
   728    722     the default collating sequence of the corresponding table column.
   729    723   }
   730    724   syntaxreq {H42839} {} {} {
   731    725     The sort order for an index column shall be descending if and only
   732    726     if the DESC keyword is used in the <i>indexlist</i> entry for that
   733    727     term.
   734    728   }
   735         -
          729  +syntaxreq {H42842} {} {} {
          730  +  The preparation of a CREATE INDEX statement shall fail with an error
          731  +  if the <i>tablename</i> refers to a system table.
          732  +}
   736    733   
   737    734   </tcl>
   738    735   
   739    736   <h3>2.4 DROP INDEX</h3>
   740    737   
   741    738   <tcl>
   742    739   syntaxreq {H42900} {} {} {
   743    740     The SQLite parser shall accept DROP INDEX statements 
   744    741     that conform to the following syntax:
   745    742   } {
   746    743     cmd ::= DROP INDEX ifexists fullname.
   747    744   }
          745  +syntaxreq {H42910} {} {} {
          746  +  The preparation of a DROP INDEX statement shall fail with an
          747  +  error if the statement lacks an IF EXISTS clause and the
          748  +  <i>fullname</i> does not reference a existing index.
          749  +}
          750  +syntaxreq {H42913} {} {} {
          751  +  The evaluation of a DROP INDEX statement shall be a silent no-op
          752  +  if the the statement has an IF EXISTS clause and the
          753  +  <i>fullname</i> does not reference a existing index.
          754  +}
          755  +syntaxreq {H42916} {} {} {
          756  +  The successful evaluation of a DROP INDEX statement shall cause
          757  +  the index identified by <i>fullname</i> to be removed from its
          758  +  database and discarded.
          759  +}
   748    760   </tcl>
   749    761   
   750    762   <h3>2.5 CREATE VIEW</h3>
   751    763   
   752    764   <tcl>
   753    765   syntaxreq {H43100} {} {} {
   754    766     The SQLite parser shall accept CREATE VIEW statements 
   755    767     that conform to the following syntax:
   756    768   } {
   757    769     cmd ::= CREATE temp VIEW ifnotexists fullname AS select.
   758    770   }
          771  +syntaxreq {H43113} {} {} {
          772  +  When the TEMP keyword appears in a CREATE VIEW statement and the
          773  +  <i>databasename</i> exists and is something other than "temp", then
          774  +  the preparation of the CREATE VIEW statement shall fail with an error.
          775  +}
          776  +syntaxreq {H43116} {} {} {
          777  +  When the TEMP keyword appears in a CREATE VIEW statement the behavior
          778  +  shall be as if the <i>databasename</i> where "temp".
          779  +}
          780  +syntaxreq {H43119} {} {} {
          781  +  The successful evaluation of a CREATE VIEW statement shall cause
          782  +  a new view whose name is given by the <i>objectname</i> and is located
          783  +  in the schema of the database whose name is given by the
          784  +  <i>databasename</i>.
          785  +}
          786  +syntaxreq {H43122} {} {} {
          787  +  If a CREATE VIEW statement specifies no <i>databasename</i> and omits
          788  +  the TEMP keyword then the behavior shall be as if a <i>databasename</i>
          789  +  of "main" where used.
          790  +}
          791  +syntaxreq {H43125} {} {} {
          792  +  The preparation of a CREATE VIEW statement shall fail with an 
          793  +  error if the IF NOT EXISTS clause is omitted and the <i>objectname</i>
          794  +  is the same as the name of a table or view
          795  +  in the same database.
          796  +}
          797  +syntaxreq {H43128} {} {} {
          798  +  The evaluation of a CREATE VIEW statement shall be a silent no-op if the
          799  +  IF NOT EXISTS clause is present and the <i>objectname</i>
          800  +  is the same as the name of a table or view
          801  +  in the same database.
          802  +}
          803  +syntaxreq {H43131} {} {} {
          804  +  The preparation of a CREATE VIEW statement shall fail with an error if the
          805  +  the <i>objectname</i> is the same as the name of an index
          806  +  in any database attached to the same [database connection].
          807  +}
          808  +syntaxreq {H43234} {} {} {
          809  +  The preparation of a CREATE VIEW statement shall fail with an error if the
          810  +  the <i>databasename</i> references a database that is not
          811  +  attached to the same [database connection].
          812  +}
          813  +syntaxreq {H43237} {} {} {
          814  +  The view generated by a CREATE VIEW statement shall have the
          815  +  same number of columns as the result set of the SELECT.
          816  +}
          817  +syntaxreq {H43241} {} {} {
          818  +  The names of the columns in a view generated by a CREATE VIEW
          819  +  statement shall have base names which are the names of the columns
          820  +  in the result set of the SELECT statement
          821  +}
          822  +syntaxreq {H43244} {} {} {
          823  +  Each column name in a table generated by a CREATE VIEW
          824  +  statement shall have an arbitrary suffix appended to its basename
          825  +  if and only if such a suffix is necessary to make the name
          826  +  distinct from all preceding column names in the view.
          827  +}
   759    828   </tcl>
   760    829   
   761    830   <h3>2.6 DROP VIEW</h3>
   762    831   
   763    832   <tcl>
   764    833   syntaxreq {H43200} {} {} {
   765    834     The SQLite parser shall accept DROP VIEW statements
   766    835     that conform to the following syntax:
   767    836   } {
   768    837     cmd ::= DROP VIEW ifexists fullname.
   769    838   }
          839  +syntaxreq {H43204} {} {} {
          840  +  The preparation of a DROP VIEW statement shall fail with an
          841  +  error if the statement lacks an IF EXISTS clause and the
          842  +  <i>fullname</i> does not reference a existing view.
          843  +}
          844  +syntaxreq {H43207} {} {} {
          845  +  The evaluation of a DROP VIEW statement shall be a silent no-op
          846  +  if the the statement has an IF EXISTS clause and the
          847  +  <i>fullname</i> does not reference a existing view.
          848  +}
          849  +syntaxreq {H43211} {} {} {
          850  +  The successful evaluation of a DROP VIEW statement shall cause
          851  +  the view identified by <i>fullname</i> to be removed from its
          852  +  database and discarded.
          853  +}
          854  +syntaxreq {H43214} {} {} {
          855  +  The successful evaluation of a DROP VIEW statement shall cause
          856  +  all triggers attached to the view identified by <i>fullname</i>
          857  +  to be removed from their database and discarded.
          858  +}
   770    859   </tcl>
   771    860   
   772    861   <h3>2.7 CREATE TRIGGER</h3>
   773    862   
   774    863   <tcl>
   775    864   syntaxreq {H43300} {} {} {
   776    865     The SQLite parser shall accept CREATE TRIGGER statements
   777    866     that conform to the following syntax:
   778    867   } {
   779    868     cmd ::= CREATE temp TRIGGER ifnotexists fullname trigger trigger_body.
   780         -  trigger ::= trigger_time trigger_event ON fullname foreach_clause when_clause.
          869  +  trigger ::= trigger_time trigger_event foreach_clause when_clause.
   781    870     trigger_body ::= BEGIN trigger_cmd_list END.
   782    871     trigger_cmd_list ::= trigger_cmd_list trigger_cmd SEMI.
   783    872     trigger_cmd_list ::=.
   784         -  trigger_cmd ::= DELETE FROM name where_opt.
   785         -  trigger_cmd ::= UPDATE orconf name SET setlist where_opt.
   786         -  trigger_cmd ::= insert_cmd INTO name inscollist_opt VALUES LP itemlist RP.
   787         -  trigger_cmd ::= insert_cmd INTO name inscollist_opt select.
          873  +  trigger_cmd ::= DELETE FROM tablename where.
          874  +  trigger_cmd ::= update_cmd tablename SET setlist where.
          875  +  trigger_cmd ::= insert_cmd INTO tablename inscollist_opt VALUES LP exprlist RP.
          876  +  trigger_cmd ::= insert_cmd INTO tablename inscollist_opt select.
   788    877     trigger_cmd ::= select.
   789         -  trigger_event ::= DELETE.
   790         -  trigger_event ::= INSERT.
   791         -  trigger_event ::= UPDATE OF inscollist.
   792         -  trigger_event ::= UPDATE.
          878  +  trigger_event ::= DELETE ON trigger_target.
          879  +  trigger_event ::= INSERT ON trigger_target.
          880  +  trigger_event ::= UPDATE OF inscollist ON trigger_target.
          881  +  trigger_event ::= UPDATE ON trigger_target.
          882  +  trigger_target ::= fullname.
   793    883     trigger_time ::= AFTER.
   794    884     trigger_time ::= BEFORE.
   795    885     trigger_time ::= INSTEAD OF.
   796    886     trigger_time ::=.
   797    887     foreach_clause ::= FOR EACH ROW.
   798    888     foreach_clause ::=.
   799    889     when_clause ::= WHEN expr.
   800    890     when_clause ::=.
   801    891   }
          892  +syntaxreq {H43303} {} {} {
          893  +  When the TEMP keyword appears in a CREATE TRIGGER statement and the
          894  +  <i>databasename</i> of the <i>fullname</i> exists
          895  +  then the preparation of the statement
          896  +  shall fail with an error.
          897  +}
          898  +syntaxreq {H43306} {} {} {
          899  +  When the TEMP keyword appears in a CREATE TRIGGER statement 
          900  +  the target database of the trigger shall be "temp".
          901  +}
          902  +syntaxreq {H43309} {} {} {
          903  +  When the TEMP keyword is omitted in a CREATE TRIGGER statement and the
          904  +  <i>databasename</i> of the <i>fullname</i> is
          905  +  omitted then the target database of the trigger shall be "main".
          906  +}
          907  +syntaxreq {H43312} {} {} {
          908  +  When the <i>databasename</i> of <i>fullname</i> in a CREATE TRIGGER
          909  +  statement exists, then the target database shall be the database
          910  +  named by <i>databasename</i>.
          911  +}
          912  +syntaxreq {H43315} {} {} {
          913  +  If a CREATE TRIGGER does not specify a <i>trigger_time</i> then
          914  +  the <i>trigger_time</i> shall be BEFORE.
          915  +}
          916  +syntaxreq {H43318} {} {
          917  +  An INSTEAD OF trigger may be used only on views.  Other kinds of triggers
          918  +  may be used on tables only.
          919  +} {
          920  +  The preparation of a CREATE TRIGGER statement shall fail with an error
          921  +  the <i>trigger_time</i> is INSTEAD OF and <i>trigger_target</i> is not
          922  +  the name of view in the target database.
          923  +}
          924  +syntaxreq {H43321} {} {} {
          925  +  The preparation of a CREATE TRIGGER statement shall fail with an error
          926  +  the <i>trigger_time</i> is not INSTEAD OF and <i>trigger_target</i> is not
          927  +  the name of an ordinary table in the target database.
          928  +}
          929  +syntaxreq {H43324} {} {} {
          930  +  The preparation of a CREATE TRIGGER statement shall fail with an error
          931  +  if the <i>trigger_target</i> is a system table.
          932  +}
          933  +
   802    934   </tcl>
          935  +
   803    936   
   804    937   <h3>2.8 DROP TRIGGER</h3>
   805    938   
   806    939   <tcl>
   807    940   syntaxreq {H43500} {} {} {
   808    941     The SQLite parser shall accept DROP TRIGGER statements
   809    942     that conform to the following syntax:
   810    943   } {
   811    944     cmd ::= DROP TRIGGER ifexists fullname.
   812    945   }
          946  +syntaxreq {H43504} {} {} {
          947  +  The preparation of a DROP TRIGGER statement shall fail with an
          948  +  error if the statement lacks an IF EXISTS clause and the
          949  +  <i>fullname</i> does not reference a existing trigger.
          950  +}
          951  +syntaxreq {H43507} {} {} {
          952  +  The evaluation of a DROP TRIGGER statement shall be a silent no-op
          953  +  if the the statement has an IF EXISTS clause and the
          954  +  <i>fullname</i> does not reference a existing trigger.
          955  +}
          956  +syntaxreq {H43511} {} {} {
          957  +  The successful evaluation of a DROP TRIGGER statement shall cause
          958  +  the trigger identified by <i>fullname</i> to be removed from its
          959  +  database and discarded.
          960  +}
          961  +syntaxreq {H43514} {} {} {
          962  +  The successful evaluation of a DROP TRIGGER statement shall cause
          963  +  all triggers attached to the trigger identified by <i>fullname</i>
          964  +  to be removed from their database and discarded.
          965  +}
   813    966   </tcl>
   814    967   
   815    968   <h3>2.9 CREATE VIRTUAL TABLE</h3>
   816    969   
   817    970   <tcl>
   818    971   syntaxreq {H43600} {} {} {
   819    972     The SQLite parser shall accept CREATE VIRTUAL TABLE statements
................................................................................
   856   1009   
   857   1010   <h4>3.1.1 INSERT VALUE</h4>
   858   1011   <tcl>
   859   1012   syntaxreq {H43810} {} {} {
   860   1013     The SQLite parser shall accept INSERT VALUE statements that
   861   1014     conform to the following syntax:
   862   1015   } {
   863         -  cmd ::= insert_cmd INTO fullname inscollist_opt VALUES LP itemlist RP.
   864         -  insert_cmd ::= INSERT orconf.
         1016  +  cmd ::= insert_cmd INTO fullname inscollist_opt VALUES LP exprlist RP.
         1017  +  insert_cmd ::= INSERT.
   865   1018     insert_cmd ::= REPLACE.
         1019  +  insert_cmd ::= INSERT OR REPLACE.
         1020  +  insert_cmd ::= INSERT OR IGNORE.
         1021  +  insert_cmd ::= INSERT OR ABORT.
         1022  +  insert_cmd ::= INSERT OR FAIL.
         1023  +  insert_cmd ::= INSERT OR ROLLBACK.
         1024  +  inscollist_opt ::= .
         1025  +  inscollist_opt ::= LP inscollist RP.
         1026  +  inscollist ::= columnname.
         1027  +  inscollist ::= inscollist COMMA columnname.
   866   1028   }
   867   1029   </tcl>
   868   1030   <h4>3.1.3 INSERT DEFAULT</h4>
   869   1031   <tcl>
   870   1032   syntaxreq {H43820} {} {} {
   871   1033     The SQLite parser shall accept INSERT DEFAULT statements that
   872   1034     conform to the following syntax:
................................................................................
   875   1037   }
   876   1038   </tcl>
   877   1039   <h4>3.1.3 INSERT SELECT</h4>
   878   1040   syntaxreq {H43830} {} {} {
   879   1041     The SQLite parser shall accept INSERT SELECT statements that
   880   1042     conform to the following syntax:
   881   1043   } {
   882         -  cmd ::= insert_cmd INTO fullname inscollist_opt DEFAULT VALUES.
         1044  +  cmd ::= insert_cmd INTO fullname inscollist_opt select.
   883   1045   }
   884   1046   </tcl>
   885   1047   <h3>3.2 DELETE</h3>
   886   1048   
   887   1049   <tcl>
   888   1050   syntaxreq {H43900} {} {} {
   889   1051     The SQLite parser shall accept DELETE statements that
   890   1052     conform to the following syntax:
   891   1053   } {
   892         -  cmd ::= DELETE FROM fullname where_opt.
         1054  +  cmd ::= DELETE FROM fullname where.
         1055  +  where ::= .
         1056  +  where ::= WHERE expr.
   893   1057   }
   894   1058   </tcl>
   895   1059   
   896   1060   <h3>3.3 UPDATE</h3>
   897   1061   
   898   1062   <tcl>
   899   1063   syntaxreq {H44100} {} {} {
   900   1064     The SQLite parser shall accept UPDATE statements that
   901   1065     conform to the following syntax:
   902   1066   } {
   903         -  cmd ::= UPDATE orconf fullname SET setlist where_opt.
         1067  +  cmd ::= update_cmd fullname SET setlist where.
         1068  +  update_cmd ::= UPDATE.
         1069  +  update_cmd ::= UPDATE OR IGNORE.
         1070  +  update_cmd ::= UPDATE OR REPLACE.
         1071  +  update_cmd ::= UPDATE OR ABORT.
         1072  +  update_cmd ::= UPDATE OR FAIL.
         1073  +  update_cmd ::= UPDATE OR ROLLBACK.
         1074  +  setlist ::= setting.
         1075  +  setlist ::= setlist COMMA setting.
         1076  +  setting ::= columnname EQ expr.
   904   1077   }
   905   1078   </tcl>
   906   1079   
   907   1080   <h3>3.4 SELECT</h3>
   908   1081   
   909   1082   <tcl>
   910   1083   syntaxreq {H45000} {} {} {
................................................................................
   914   1087     cmd ::= select.
   915   1088     select ::= query.
   916   1089     select ::= select UNION query.
   917   1090     select ::= select UNION ALL query.
   918   1091     select ::= select EXCEPT query.
   919   1092     select ::= select INTERSECT query.
   920   1093     query ::= SELECT distinct resultset from where groupby having orderby limit.
   921         -  where ::= .
   922         -  where ::= WHERE expr.
         1094  +  distinct ::= .
         1095  +  distinct ::= DISTINCT.
   923   1096     groupby ::= .
   924   1097     groupby ::= GROUP BY exprlist.
   925   1098     having ::= .
   926   1099     having ::= HAVING expr.
   927   1100     orderby ::= .
   928   1101     orderby ::= ORDER BY exprlist.
   929   1102     limit ::=.
   930   1103     limit ::= LIMIT expr.
   931   1104     limit ::= LIMIT expr COMMA expr.
   932   1105     limit ::= LIMIT expr OFFSET expr.
   933   1106     resultset ::= result.
   934   1107     resultset ::= resultset COMMA result.
   935   1108     result ::= STAR.
         1109  +  result ::= tablename DOT STAR.
   936   1110     result ::= expr as.
   937         -  result ::= name DOT STAR.
   938   1111     from ::= .
   939   1112     from ::= FROM sourceset.
   940   1113     sourceset ::= source.
   941   1114     sourceset ::= sourceset joinop source.
   942   1115     source ::= fullname as on using.
   943   1116     source ::= LP select RP as on using.
   944   1117     as ::= .
................................................................................
   947   1120     on ::= .
   948   1121     on ::= ON expr.
   949   1122     using ::= .
   950   1123     using ::= USING LP idlist RP.
   951   1124     joinop ::= COMMA.
   952   1125     joinop ::= JOIN.
   953   1126     joinop ::= JOIN_KW JOIN.
   954         -  joinop ::= JOIN_KW name JOIN.
   955         -  joinop ::= JOIN_KW name name JOIN.
         1127  +  joinop ::= JOIN_KW JOIN_KW JOIN.
         1128  +  joinop ::= JOIN_KW JOIN_KW JOIN_KW JOIN.
   956   1129   }
   957   1130   </tcl>
   958   1131   
   959   1132   <h2>4.0 Other Language Elements</h2>
   960   1133   
   961   1134   <h3>4.1 VACUUM</h3>
   962   1135   
................................................................................
   999   1172   <tcl>
  1000   1173   syntaxreq {H46000} {} {} {
  1001   1174     The SQLite parser shall accept PRAGMA statements
  1002   1175     that conform to the following syntax:
  1003   1176   } {
  1004   1177     cmd ::= PRAGMA fullname EQ DELETE.
  1005   1178     cmd ::= PRAGMA fullname EQ ON.
  1006         -  cmd ::= PRAGMA fullname EQ minus_num.
  1007         -  cmd ::= PRAGMA fullname EQ nmnum.
  1008         -  cmd ::= PRAGMA fullname LP nmnum RP.
         1179  +  cmd ::= PRAGMA fullname EQ name.
         1180  +  cmd ::= PRAGMA fullname EQ expr.
         1181  +  cmd ::= PRAGMA fullname LP name RP.
         1182  +  cmd ::= PRAGMA fullname LP expr RP.
  1009   1183     cmd ::= PRAGMA fullname.
  1010   1184   }
  1011   1185   </tcl>
  1012   1186   
  1013   1187   <h3>4.5 ATTACH</h3>
  1014   1188   
  1015   1189   <tcl>
  1016   1190   syntaxreq {H44500} {} {} {
  1017   1191     The SQLite parser shall accept ATTACH statements
  1018   1192     that conform to the following syntax:
  1019   1193   } {
  1020         -  cmd ::= ATTACH database_kw_opt expr AS expr key_opt.
         1194  +  cmd ::= ATTACH database_kw expr AS expr key_opt.
         1195  +  database_kw ::= .
         1196  +  database_kw ::= DATABASE.
         1197  +  key_opt ::= .
         1198  +  key_opt ::= KEY expr.
  1021   1199   }
  1022   1200   </tcl>
  1023   1201   
  1024   1202   <h3>4.6 DETACH</h3>
  1025   1203   
  1026   1204   <tcl>
  1027   1205   syntaxreq {H44600} {} {} {
  1028   1206     The SQLite parser shall accept DETACH statements
  1029   1207     that conform to the following syntax:
  1030   1208   } {
  1031         -  cmd ::= DETACH database_kw_opt expr.
         1209  +  cmd ::= DETACH database_kw expr.
  1032   1210   }
  1033   1211   </tcl>
  1034   1212   
  1035   1213   <h3>4.7 EXPLAIN</h3>
  1036   1214   
  1037   1215   <tcl>
  1038   1216   syntaxreq {H44700} {} {} {
................................................................................
  1073   1251     expr ::= JOIN_KW.
  1074   1252     expr ::= LP expr RP.
  1075   1253     expr ::= LP select RP.
  1076   1254     expr ::= MINUS expr.
  1077   1255     expr ::= NOT expr.
  1078   1256     expr ::= PLUS expr.
  1079   1257     expr ::= RAISE LP IGNORE RP.
  1080         -  expr ::= RAISE LP raisetype COMMA name RP.
         1258  +  expr ::= RAISE LP ABORT COMMA name RP.
         1259  +  expr ::= RAISE LP FAIL COMMA name RP.
         1260  +  expr ::= RAISE LP ROLLBACK COMMA name RP.
  1081   1261     expr ::= REGISTER.
  1082   1262     expr ::= VARIABLE.
  1083   1263     expr ::= expr AND expr.
  1084   1264     expr ::= expr BITAND expr.
  1085   1265     expr ::= expr BITOR expr.
  1086   1266     expr ::= expr LSHIFT expr.
  1087   1267     expr ::= expr RSHIFT expr.
  1088   1268     expr ::= expr COLLATE ids.
  1089   1269     expr ::= expr CONCAT expr.
  1090   1270     expr ::= expr EQ expr.
  1091   1271     expr ::= expr NE expr.
  1092   1272     expr ::= expr IS NOT NULL.
  1093   1273     expr ::= expr IS NULL.
  1094         -  expr ::= expr ISNULL|NOTNULL.
         1274  +  expr ::= expr ISNULL
         1275  +  expr ::= expr NOTNULL.
  1095   1276     expr ::= expr LT expr.
  1096   1277     expr ::= expr GT expr.
  1097   1278     expr ::= expr GE expr.
  1098   1279     expr ::= expr LE expr.
  1099   1280     expr ::= expr NOT NULL.
  1100   1281     expr ::= expr OR expr.
  1101   1282     expr ::= expr PLUS expr.
................................................................................
  1111   1292     expr ::= expr NOT IN LP exprlist RP.
  1112   1293     expr ::= expr NOT IN LP select RP.
  1113   1294     expr ::= expr NOT IN fullname.
  1114   1295     expr ::= expr LIKE_KW expr escape.
  1115   1296     expr ::= expr MATCH expr escape.
  1116   1297     expr ::= expr NOT LIKE_KW expr escape.
  1117   1298     expr ::= expr NOT MATCH expr escape.
  1118         -  expr ::= name DOT name DOT name.
  1119         -  expr ::= name DOT name.
         1299  +  expr ::= databasename DOT tablename DOT columnname.
         1300  +  expr ::= tablename DOT columnname.
  1120   1301     expr ::= term.
  1121   1302     term ::= CTIME_KW.
  1122   1303     term ::= INTEGER.
  1123   1304     term ::= FLOAT
  1124   1305     term ::= BLOB.
  1125   1306     term ::= NULL.
  1126   1307     term ::= STRING.
................................................................................
  1129   1310     case_else ::= ELSE expr.
  1130   1311     case_else ::= .
  1131   1312     case_exprlist ::= WHEN expr THEN expr.
  1132   1313     case_exprlist ::= case_exprlist WHEN expr THEN expr.
  1133   1314     case_operand ::= expr.
  1134   1315     case_operand ::= .
  1135   1316     function_name ::= ID.
         1317  +  escape ::= .
         1318  +  escape ::= ESCAPE expr.
  1136   1319   }
  1137   1320   </tcl>
  1138   1321   
  1139   1322   <h3>5.2 Functions</h3>
  1140   1323   <h4>5.2.1 Core Scalar Functions</h4>
  1141   1324   <h4>5.2.2 Date and Time Functions</h4>
  1142   1325   <h4>5.2.3 Aggregate Functions</h4>