Documentation Source Text

Check-in [ac58d1c1e9]
Login

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

Overview
Comment:Continuing work on language syntax requirements.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ac58d1c1e958232bec1d5fe68f36f518dbcecf01
User & Date: drh 2008-08-12 00:27:52.000
Context
2008-08-12
16:12
Update the FAQ to talk about unicode case conversions and comparisons. (check-in: 668f102220 user: drh tags: trunk)
00:27
Continuing work on language syntax requirements. (check-in: ac58d1c1e9 user: drh tags: trunk)
2008-08-11
17:18
Add comment to change. (check-in: 869024cc6c user: shaneh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/syntax.in.
1
2
3
4
5
6
7
8
<title>SQLite SQL Language Requirements</title>

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







1
2
3
4
5
6
7
8
<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>
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  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 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







|







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  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
76
77
78
79
80
81
82





























83
84
85
86
87
88
89
  Hence, SQLite considers identifiers "A" and "a" to be equivalent but
  "&Aring;" and "&aring;" are distinct.
} {
  SQLite shall use the built-in NOCASE collating sequence when comparing
  identifiers and datatype names within SQL statements during 
  statement preparation.
}





























</tcl>

<h2>1.0 Transaction Control</h2>

<h3>1.1 BEGIN</h3>

<tcl>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
  Hence, SQLite considers identifiers "A" and "a" to be equivalent but
  "&Aring;" and "&aring;" 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>
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
  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 column_list constraint_opt RP.
  column_list ::= column.
  column_list ::= column_list 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







|
|
|







317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
  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
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
  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 idlist 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.
}







|







499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
  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.
}
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
  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 ::= DELETE FROM tablename where.
  trigger_cmd ::= update_cmd tablename SET setlist where.
  trigger_cmd ::= insert_cmd INTO tablename inscollist_opt VALUES LP exprlist RP.
  trigger_cmd ::= insert_cmd INTO tablename inscollist_opt select.
  trigger_cmd ::= select.
  trigger_event ::= DELETE ON trigger_target.
  trigger_event ::= INSERT ON trigger_target.
  trigger_event ::= UPDATE OF inscollist 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.







|


|
|



|







894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
  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.
970
971
972
973
974
975
976
977




978
979
980
981
982
983
984
<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_args RP.




}
</tcl>

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

<tcl>







|
>
>
>
>







999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
<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>
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
































1029













































1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056











1057
1058
1059
1060
1061
1062
1063
  column_keyword ::= COLUMNKW.
}
</tcl>

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

<h3>3.1 INSERT</h3>

<h4>3.1.1 INSERT VALUE</h4>
<tcl>
syntaxreq {H43810} {} {} {
  The SQLite parser shall accept INSERT VALUE statements that
  conform to the following syntax:
} {
  cmd ::= insert_cmd INTO fullname inscollist_opt VALUES LP exprlist RP.
  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.
  inscollist_opt ::= .
  inscollist_opt ::= LP inscollist RP.
  inscollist ::= columnname.
  inscollist ::= inscollist COMMA columnname.
}
































</tcl>













































<h4>3.1.3 INSERT DEFAULT</h4>
<tcl>
syntaxreq {H43820} {} {} {
  The SQLite parser shall accept INSERT DEFAULT statements that
  conform to the following syntax:
} {
  cmd ::= insert_cmd INTO fullname inscollist_opt DEFAULT VALUES.
}
</tcl>
<h4>3.1.3 INSERT SELECT</h4>
syntaxreq {H43830} {} {} {
  The SQLite parser shall accept INSERT SELECT statements that
  conform to the following syntax:
} {
  cmd ::= insert_cmd INTO fullname inscollist_opt select.
}
</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.











}
</tcl>

<h3>3.3 UPDATE</h3>

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







<
<


|


|







|
|
|
|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


|



|


<
<
<
<
<
<
|
<










>
>
>
>
>
>
>
>
>
>
>







1035
1036
1037
1038
1039
1040
1041


1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146






1147

1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
  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} {} {} {
1071
1072
1073
1074
1075
1076
1077



























1078
1079
1080
1081
1082
1083
1084
  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.
}



























</tcl>

<h3>3.4 SELECT</h3>

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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
  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
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129































































1130
1131
1132
1133
1134
1135
1136
  source ::= LP select RP as on using.
  as ::= .
  as ::= AS name.
  as ::= identifier.
  on ::= .
  on ::= ON expr.
  using ::= .
  using ::= USING LP idlist RP.
  joinop ::= COMMA.
  joinop ::= JOIN.
  joinop ::= JOIN_KW JOIN.
  joinop ::= JOIN_KW JOIN_KW JOIN.
  joinop ::= JOIN_KW JOIN_KW JOIN_KW JOIN.
}































































</tcl>

<h2>4.0 Other Language Elements</h2>

<h3>4.1 VACUUM</h3>

<tcl>







|






>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
  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>
1149
1150
1151
1152
1153
1154
1155






1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167







1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184







1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197

1198











1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210













1211
1212
1213
1214
1215
1216
1217
syntaxreq {H44300} {} {} {
  The SQLite parser shall accept ANALYZE statements
  that conform to the following syntax:
} {
  cmd ::= ANALYZE.
  cmd ::= ANALYZE fullname.
}






</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.
}







</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.
}







</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 key_opt.
  database_kw ::= .
  database_kw ::= DATABASE.
  key_opt ::= .

  key_opt ::= KEY expr.











}
</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.
}













</tcl>

<h3>4.7 EXPLAIN</h3>

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







>
>
>
>
>
>












>
>
>
>
>
>
>

















>
>
>
>
>
>
>





|



|


<
>
|
>
>
>
>
>
>
>
>
>
>
>












>
>
>
>
>
>
>
>
>
>
>
>
>







1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418

1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
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
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
  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 ::= REGISTER.
  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.







<







1500
1501
1502
1503
1504
1505
1506

1507
1508
1509
1510
1511
1512
1513
  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.
1313
1314
1315
1316
1317
1318
1319































































1320
1321
1322
1323
1324
1325
1326
  case_exprlist ::= case_exprlist WHEN expr THEN expr.
  case_operand ::= expr.
  case_operand ::= .
  function_name ::= ID.
  escape ::= .
  escape ::= ESCAPE expr.
}































































</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>








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
  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>