/ Check-in [a704b65b]
Login

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

Overview
Comment:Sync with version 3.8.3.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: a704b65b9476d60c88b5bc82f2743faa2bce5ac2
User & Date: drh 2014-02-03 13:58:42
Context
2014-02-11
04:30
Sync the latest trunk changes, and in particular the STAT4 IS NOT NULL fix. check-in: b0067926 user: drh tags: sessions
2014-02-03
13:58
Sync with version 3.8.3. check-in: a704b65b user: drh tags: sessions
13:52
Version 3.8.3 check-in: e816dd92 user: drh tags: trunk, release, version-3.8.3
2014-01-29
14:21
Merge latest fixes from the trunk. check-in: 6b6dcd4c user: dan tags: sessions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Makefile.msc.

267
268
269
270
271
272
273











274
275
276
277
278
279
280
...
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
....
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
TCC = $(TCC) -I$(TOP)\ext\fts3
RCC = $(RCC) -I$(TOP)\ext\fts3
TCC = $(TCC) -I$(TOP)\ext\rtree
RCC = $(RCC) -I$(TOP)\ext\rtree
TCC = $(TCC) -I$(TOP)\ext\session
RCC = $(RCC) -I$(TOP)\ext\session
!ENDIF












# Define -DNDEBUG to compile without debugging (i.e., for production usage)
# Omitting the define will cause extra debugging code to be inserted and
# includes extra comments when "EXPLAIN stmt" is used.
#
!IF $(DEBUG)==0
TCC = $(TCC) -DNDEBUG
................................................................................
	for %i in ($(SRC)) do copy /Y %i tsrc
	del /Q tsrc\sqlite.h.in tsrc\parse.y
	$(TCLSH_CMD) $(TOP)\tool\vdbe-compress.tcl $(OPTS) < tsrc\vdbe.c > vdbe.new
	move vdbe.new tsrc\vdbe.c
	echo > .target_source

sqlite3.c:	.target_source $(TOP)\tool\mksqlite3c.tcl
	$(TCLSH_CMD) $(TOP)\tool\mksqlite3c.tcl
	copy tsrc\shell.c .
	copy tsrc\sqlite3ext.h .
	copy $(TOP)\ext\session\sqlite3session.h .

sqlite3-all.c:	sqlite3.c $(TOP)\tool\split-sqlite3c.tcl
	$(TCLSH_CMD) $(TOP)\tool\split-sqlite3c.tcl

................................................................................
	$(NAWK) -f $(TOP)\tool\tostr.awk $(TOP)\tool\spaceanal.tcl >> $@
	echo ; return zMainloop; } >> $@

sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
	$(LTLINK) -DBUILD_sqlite -DTCLSH=2 -I$(TCLINCDIR) sqlite3_analyzer.c \
		/link $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)

showdb.exe:	$(TOP)\tool\showdb.c sqlite3.c
	$(LTLINK) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o $@ \
		$(TOP)\tool\showdb.c sqlite3.c

wordcount.exe:	$(TOP)\test\wordcount.c sqlite3.c
	$(LTLINK) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o $@ \
		$(TOP)\test\wordcount.c sqlite3.c

speedtest1.exe:	$(TOP)\test\speedtest1.c sqlite3.c
	$(LTLINK) -DSQLITE_OMIT_LOAD_EXTENSION -o $@ \
		$(TOP)\test\speedtest1.c sqlite3.c

clean:
	del /Q *.lo *.ilk *.lib *.obj *.pdb sqlite3.exe libsqlite3.lib
	del /Q *.cod *.da *.bb *.bbg gmon.out
	del /Q sqlite3.h opcodes.c opcodes.h
	del /Q lemon.exe lempar.c parse.*
	del /Q mkkeywordhash.exe keywordhash.h







>
>
>
>
>
>
>
>
>
>
>







 







|







 







|
|
|

|
|
|

|
|
|







267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
...
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
....
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
TCC = $(TCC) -I$(TOP)\ext\fts3
RCC = $(RCC) -I$(TOP)\ext\fts3
TCC = $(TCC) -I$(TOP)\ext\rtree
RCC = $(RCC) -I$(TOP)\ext\rtree
TCC = $(TCC) -I$(TOP)\ext\session
RCC = $(RCC) -I$(TOP)\ext\session
!ENDIF

# The mksqlite3c.tcl script accepts some options on the command
# line.  When compiling with debugging enabled, some of these
# options are necessary in order to allow debugging symbols to
# work correctly with Visual Studio when using the amalgamation.
#
!IF $(DEBUG)>0
MKSQLITE3C_ARGS = --linemacros
!ELSE
MKSQLITE3C_ARGS =
!ENDIF

# Define -DNDEBUG to compile without debugging (i.e., for production usage)
# Omitting the define will cause extra debugging code to be inserted and
# includes extra comments when "EXPLAIN stmt" is used.
#
!IF $(DEBUG)==0
TCC = $(TCC) -DNDEBUG
................................................................................
	for %i in ($(SRC)) do copy /Y %i tsrc
	del /Q tsrc\sqlite.h.in tsrc\parse.y
	$(TCLSH_CMD) $(TOP)\tool\vdbe-compress.tcl $(OPTS) < tsrc\vdbe.c > vdbe.new
	move vdbe.new tsrc\vdbe.c
	echo > .target_source

sqlite3.c:	.target_source $(TOP)\tool\mksqlite3c.tcl
	$(TCLSH_CMD) $(TOP)\tool\mksqlite3c.tcl $(MKSQLITE3C_ARGS)
	copy tsrc\shell.c .
	copy tsrc\sqlite3ext.h .
	copy $(TOP)\ext\session\sqlite3session.h .

sqlite3-all.c:	sqlite3.c $(TOP)\tool\split-sqlite3c.tcl
	$(TCLSH_CMD) $(TOP)\tool\split-sqlite3c.tcl

................................................................................
	$(NAWK) -f $(TOP)\tool\tostr.awk $(TOP)\tool\spaceanal.tcl >> $@
	echo ; return zMainloop; } >> $@

sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
	$(LTLINK) -DBUILD_sqlite -DTCLSH=2 -I$(TCLINCDIR) sqlite3_analyzer.c \
		/link $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)

showdb.exe:	$(TOP)\tool\showdb.c $(SQLITE3C)
	$(LTLINK) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -Fe$@ \
		$(TOP)\tool\showdb.c $(SQLITE3C)

wordcount.exe:	$(TOP)\test\wordcount.c $(SQLITE3C)
	$(LTLINK) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -Fe$@ \
		$(TOP)\test\wordcount.c $(SQLITE3C)

speedtest1.exe:	$(TOP)\test\speedtest1.c $(SQLITE3C)
	$(LTLINK) -DSQLITE_OMIT_LOAD_EXTENSION -Fe$@ \
		$(TOP)\test\speedtest1.c $(SQLITE3C)

clean:
	del /Q *.lo *.ilk *.lib *.obj *.pdb sqlite3.exe libsqlite3.lib
	del /Q *.cod *.da *.bb *.bbg gmon.out
	del /Q sqlite3.h opcodes.c opcodes.h
	del /Q lemon.exe lempar.c parse.*
	del /Q mkkeywordhash.exe keywordhash.h

Changes to autoconf/tea/doc/sqlite3.n.

7
8
9
10
11
12
13
14
15
\fBsqlite3\fI command_name ?filename?\fR
.br
.SH DESCRIPTION
SQLite3 is a self-contains, zero-configuration, transactional SQL database
engine.  This extension provides an easy to use interface for accessing
SQLite database files from Tcl.
.PP
For full documentation see http://www.sqlite.org/ and
in particular http://www.sqlite.org/tclsqlite.html.







|
|
7
8
9
10
11
12
13
14
15
\fBsqlite3\fI command_name ?filename?\fR
.br
.SH DESCRIPTION
SQLite3 is a self-contains, zero-configuration, transactional SQL database
engine.  This extension provides an easy to use interface for accessing
SQLite database files from Tcl.
.PP
For full documentation see \fIhttp://www.sqlite.org/\fR and
in particular \fIhttp://www.sqlite.org/tclsqlite.html\fR.

Changes to sqlite3.1.

1
2
3
4
5
6
7
8
9
10
11
12
..
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
...
104
105
106
107
108
109
110


111
112


113
114
115

116
117
118
119




120
121
122
123
124
125
126
127
128
129

130
131

132
133
134

135


136
137
138




139


140

141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

157
158
159

160
161
162
163
164
165






















166
167
168



169
170
171
172
173
174
175
176
177
178
179


180
181
182
183
184






185
186
187
188
189


190
191
192
193
194
195
196
...
218
219
220
221
222
223
224
225
226
227
228
229

.\"                                      Hey, EMACS: -*- nroff -*-
.\" First parameter, NAME, should be all caps
.\" Second parameter, SECTION, should be 1-8, maybe w/ subsection
.\" other parameters are allowed: see man(7), man(1)
.TH SQLITE3 1 "Mon Apr 15 23:49:17 2002"
.\" Please adjust this date whenever revising the manpage.
.\"
.\" Some roff macros, for reference:
.\" .nh        disable hyphenation
.\" .hy        enable hyphenation
.\" .ad l      left justify
.\" .ad b      justify to both left and right margins
................................................................................

For example, to create a new database file named "mydata.db", create
a table named "memos" and insert a couple of records into that table:
.sp
$ 
.B sqlite3 mydata.db
.br
SQLite version 3.1.3
.br
Enter ".help" for instructions
.br
sqlite>
.B create table memos(text, priority INTEGER);
.br
sqlite>
................................................................................
A list of available meta-commands can be viewed at any time by issuing
the '.help' command.  For example:
.sp
sqlite>
.B .help
.nf
.cc |


.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format


.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.

.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE




.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values

.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen

.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME

.schema ?TABLE?        Show the CREATE statements


.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern




.timeout MS            Try opening locked tables for MS milliseconds


.width NUM NUM ...     Set column widths for "column" mode

sqlite>
|cc .
.sp
.fi

.SH OPTIONS
.B sqlite3
has the following options:
.TP
.BI \-init\ file
Read and execute commands from
.I file
, which can contain a mix of SQL statements and meta-commands.
.TP
.B \-echo
Print commands before execution.

.TP
.B \-[no]header
Turn headers on or off.

.TP
.B \-column
Query results will be displayed in a table like form, using
whitespace characters to separate the columns and align the
output.
.TP






















.B \-html
Query results will be output as simple HTML tables.
.TP



.B \-line
Query results will be displayed with one value per line, rows
separated by a blank line.  Designed to be easily parsed by
scripts or other programs
.TP
.B \-list
Query results will be displayed with the separator (|, by default)
character between each field value.  The default.
.TP
.BI \-separator\  separator
Set output field separator.  Default is '|'.


.TP
.BI \-nullvalue\  string
Set string used to represent NULL values.  Default is ''
(empty string).
.TP






.B \-version
Show SQLite version.
.TP
.B \-help
Show help on options and exit.




.SH INIT FILE
.B sqlite3
reads an initialization file to set the configuration of the
interactive environment.  Throughout initialization, any previously
specified setting can be overridden.  The sequence of initialization is
................................................................................
o If the -init option is present, the specified file is processed.

o All other command line options are processed.

.SH SEE ALSO
http://www.sqlite.org/
.br
The sqlite-doc package
.SH AUTHOR
This manual page was originally written by Andreas Rottmann
<rotty@debian.org>, for the Debian GNU/Linux system (but may be used
by others).   It was subsequently revised by Bill Bumgarner <bbum@mac.com>.





|







 







|







 







>
>


>
>


|
>



|
>
>
>
>









|
>


>



>

>
>


<
>
>
>
>

>
>
|
>




<




<
<
<
<
<
|
<
>

|
<
>






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



>
>
>









|
|
>
>





>
>
>
>
>
>



|
|
>
>







 







|



|
>
1
2
3
4
5
6
7
8
9
10
11
12
..
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

152
153
154
155
156
157
158
159
160
161
162
163
164

165
166
167
168





169

170
171
172

173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
...
267
268
269
270
271
272
273
274
275
276
277
278
279
.\"                                      Hey, EMACS: -*- nroff -*-
.\" First parameter, NAME, should be all caps
.\" Second parameter, SECTION, should be 1-8, maybe w/ subsection
.\" other parameters are allowed: see man(7), man(1)
.TH SQLITE3 1 "Mon Jan 31 11:14:00 2014"
.\" Please adjust this date whenever revising the manpage.
.\"
.\" Some roff macros, for reference:
.\" .nh        disable hyphenation
.\" .hy        enable hyphenation
.\" .ad l      left justify
.\" .ad b      justify to both left and right margins
................................................................................

For example, to create a new database file named "mydata.db", create
a table named "memos" and insert a couple of records into that table:
.sp
$ 
.B sqlite3 mydata.db
.br
SQLite version 3.8.3
.br
Enter ".help" for instructions
.br
sqlite>
.B create table memos(text, priority INTEGER);
.br
sqlite>
................................................................................
A list of available meta-commands can be viewed at any time by issuing
the '.help' command.  For example:
.sp
sqlite>
.B .help
.nf
.cc |
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.open ?FILENAME?       Close existing database and reopen FILENAME
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings

.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off
sqlite>
|cc .
.sp
.fi

.SH OPTIONS
.B sqlite3
has the following options:
.TP





.B \-bail

Stop after hitting an error.
.TP
.B \-batch

Force batch I/O.
.TP
.B \-column
Query results will be displayed in a table like form, using
whitespace characters to separate the columns and align the
output.
.TP
.BI \-cmd\  command
run
.I command
before reading stdin
.TP
.B \-csv
Set output mode to CSV (comma separated values).
.TP
.B \-echo
Print commands before execution.
.TP
.BI \-init\  file
Read and execute commands from
.I file
, which can contain a mix of SQL statements and meta-commands.
.TP
.B \-[no]header
Turn headers on or off.
.TP
.B \-help
Show help on options and exit.
.TP
.B \-html
Query results will be output as simple HTML tables.
.TP
.B \-interactive
Force interactive I/O.
.TP
.B \-line
Query results will be displayed with one value per line, rows
separated by a blank line.  Designed to be easily parsed by
scripts or other programs
.TP
.B \-list
Query results will be displayed with the separator (|, by default)
character between each field value.  The default.
.TP
.BI \-mmap\  N
Set default mmap size to
.I N
\.
.TP
.BI \-nullvalue\  string
Set string used to represent NULL values.  Default is ''
(empty string).
.TP
.BI \-separator\  separator
Set output field separator.  Default is '|'.
.TP
.B \-stats
Print memory stats before each finalize.
.TP
.B \-version
Show SQLite version.
.TP
.BI \-vfs\  name
Use
.I name
as the default VFS.


.SH INIT FILE
.B sqlite3
reads an initialization file to set the configuration of the
interactive environment.  Throughout initialization, any previously
specified setting can be overridden.  The sequence of initialization is
................................................................................
o If the -init option is present, the specified file is processed.

o All other command line options are processed.

.SH SEE ALSO
http://www.sqlite.org/
.br
The sqlite3-doc package.
.SH AUTHOR
This manual page was originally written by Andreas Rottmann
<rotty@debian.org>, for the Debian GNU/Linux system (but may be used
by others). It was subsequently revised by Bill Bumgarner <bbum@mac.com> and
further updated by Laszlo Boszormenyi <gcs@debian.hu> .

Changes to test/e_select.test.

460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
...
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
...
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
....
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
....
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
....
1401
1402
1403
1404
1405
1406
1407








1408
1409
1410
1411
1412
1413
1414
        -58 {} 1.21       alerting {} -93.79 {}                  \
        -58 {} 1.21       coldest -96 dramatists 82.3            \
        -58 {} 1.21       conducting -87.24 37.56 {}             \
        -58 {} 1.21       presenting 51 reformation dignified    \
]
# TODO: Come back and add a few more like the above.

# EVIDENCE-OF: R-20659-43267 In other words, if the left-hand dataset
# consists of Nlhs rows of Mlhs columns, and the right-hand dataset of
# Nrhs rows of Mrhs columns, then the cartesian product is a dataset of
# Nlhs.Nrhs rows, each containing Mlhs+Mrhs columns.
#
# x1, x2    (Nlhs=3, Nrhs=2)   (Mlhs=2, Mrhs=3)
do_join_test e_select-1.4.3.1 { 
  SELECT count(*) FROM x1 %JOIN% x2 
} [expr 3*2]
do_test e_select-1.4.3.2 { 
  expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6}
................................................................................
  CREATE TABLE x2(k, x, y2);
  INSERT INTO x2 VALUES(1, 50, X'B82838');
  INSERT INTO x2 VALUES(5, 84.79, 65.88);
  INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
  INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
} {}

# EVIDENCE-OF: R-06999-14330 If a WHERE clause is specified, the WHERE
# expression is evaluated for each row in the input data as a boolean
# expression. All rows for which the WHERE clause expression evaluates
# to false are excluded from the dataset before continuing.
#
do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
................................................................................
     51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  }
  10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
     51.65 -59.58 belfries 51.65 -59.58 belfries
  }
}

# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
# expression in any context other than than a result expression list.
#
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
# "alias.*" expression in a simple SELECT query that does not have a
# FROM clause.
#
foreach {tn select err} {
  1.1  "SELECT a, b, c FROM z1 WHERE *"    {near "*": syntax error}
................................................................................
  CREATE TABLE b3(a COLLATE nocase, b COLLATE binary);
  INSERT INTO b3 VALUES('abc', 'abc');
  INSERT INTO b3 VALUES('aBC', 'aBC');
  INSERT INTO b3 VALUES('Def', 'Def');
  INSERT INTO b3 VALUES('dEF', 'dEF');
} {}

# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
# query with a GROUP BY clause, then each of the expressions specified
# as part of the GROUP BY clause is evaluated for each row of the
# dataset. Each row is then assigned to a "group" based on the results;
# rows for which the results of evaluating the GROUP BY expressions are
# the same are assigned to the same group.
#
#   These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
................................................................................
  24  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 
      LIMIT {INTERSECT}
} {
  set err "$op1 clause should come after $op2 not before"
  do_catchsql_test e_select-7.2.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur
# at the end of the entire compound SELECT.

#
foreach {tn select} {
  1   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a"
  2   "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1"
  3   "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3"
  4   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 
  5   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  6   "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 

  7   "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a"
  8   "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1"

  9   "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3"
  10  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 
  11  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  12  "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 

  13  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a"
  14  "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1"
................................................................................
  21  "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3"
  22  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 
  23  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  24  "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
} {
  do_test e_select-7.3.$tn { catch {execsql $select} msg } 0
}









# EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL
# operator returns all the rows from the SELECT to the left of the UNION
# ALL operator, and all the rows from the SELECT to the right of it.
#
drop_all_tables
do_execsql_test e_select-7.4.0 {







|
|
|
|







 







|

|
|







 







|
|







 







|




|







 







|
|
>











>







 







>
>
>
>
>
>
>
>







460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
...
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
...
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
....
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
....
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
....
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
        -58 {} 1.21       alerting {} -93.79 {}                  \
        -58 {} 1.21       coldest -96 dramatists 82.3            \
        -58 {} 1.21       conducting -87.24 37.56 {}             \
        -58 {} 1.21       presenting 51 reformation dignified    \
]
# TODO: Come back and add a few more like the above.

# EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset
# consists of Nleft rows of Mleft columns, and the right-hand dataset of
# Nright rows of Mright columns, then the cartesian product is a dataset
# of Nleft&times;Nright rows, each containing Mleft+Mright columns.
#
# x1, x2    (Nlhs=3, Nrhs=2)   (Mlhs=2, Mrhs=3)
do_join_test e_select-1.4.3.1 { 
  SELECT count(*) FROM x1 %JOIN% x2 
} [expr 3*2]
do_test e_select-1.4.3.2 { 
  expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6}
................................................................................
  CREATE TABLE x2(k, x, y2);
  INSERT INTO x2 VALUES(1, 50, X'B82838');
  INSERT INTO x2 VALUES(5, 84.79, 65.88);
  INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
  INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
} {}

# EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE
# expression is evaluated for each row in the input data as a boolean
# expression. Only rows for which the WHERE clause expression evaluates
# to true are included from the dataset before continuing.
#
do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
................................................................................
     51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  }
  10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
     51.65 -59.58 belfries 51.65 -59.58 belfries
  }
}

# EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*"
# expression in any context other than a result expression list.
#
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
# "alias.*" expression in a simple SELECT query that does not have a
# FROM clause.
#
foreach {tn select err} {
  1.1  "SELECT a, b, c FROM z1 WHERE *"    {near "*": syntax error}
................................................................................
  CREATE TABLE b3(a COLLATE nocase, b COLLATE binary);
  INSERT INTO b3 VALUES('abc', 'abc');
  INSERT INTO b3 VALUES('aBC', 'aBC');
  INSERT INTO b3 VALUES('Def', 'Def');
  INSERT INTO b3 VALUES('dEF', 'dEF');
} {}

# EVIDENCE-OF: R-07284-35990 If the SELECT statement is an aggregate
# query with a GROUP BY clause, then each of the expressions specified
# as part of the GROUP BY clause is evaluated for each row of the
# dataset. Each row is then assigned to a "group" based on the results;
# rows for which the results of evaluating the GROUP BY expressions are
# the same get assigned to the same group.
#
#   These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
................................................................................
  24  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 
      LIMIT {INTERSECT}
} {
  set err "$op1 clause should come after $op2 not before"
  do_catchsql_test e_select-7.2.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur
# at the end of the entire compound SELECT, and then only if the final
# element of the compound is not a VALUES clause.
#
foreach {tn select} {
  1   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a"
  2   "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1"
  3   "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3"
  4   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 
  5   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  6   "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 

  7   "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a"
  8   "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1"
  8b  "VALUES('8b') UNION SELECT max(e) FROM j2 ORDER BY 1"
  9   "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3"
  10  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 
  11  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  12  "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 

  13  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a"
  14  "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1"
................................................................................
  21  "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3"
  22  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 
  23  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  24  "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
} {
  do_test e_select-7.3.$tn { catch {execsql $select} msg } 0
}
foreach {tn select} {
  50   "SELECT * FROM j1 ORDER BY 1 UNION ALL SELECT * FROM j2,j3"
  51   "SELECT * FROM j1 LIMIT 1 UNION ALL SELECT * FROM j2,j3"
  52   "SELECT count(*) FROM j1 UNION ALL VALUES(11) ORDER BY 1"
  53   "SELECT count(*) FROM j1 UNION ALL VALUES(11) LIMIT 1"
} {
  do_test e_select-7.3.$tn { catch {execsql $select} msg } 1
}

# EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL
# operator returns all the rows from the SELECT to the left of the UNION
# ALL operator, and all the rows from the SELECT to the right of it.
#
drop_all_tables
do_execsql_test e_select-7.4.0 {

Changes to test/permutations.test.

641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
  pragma journal_mode = 'memory'
} -files [test_set $::allquicktests -exclude {
  # Exclude all tests that simulate IO errors.
  autovacuum_ioerr2.test incrvacuum_ioerr.test ioerr.test
  ioerr.test ioerr2.test ioerr3.test ioerr4.test ioerr5.test
  vacuum3.test incrblob_err.test diskfull.test backup_ioerr.test
  e_fts3.test fts3cov.test fts3malloc.test fts3rnd.test
  fts3snippet.test

  # Exclude test scripts that use tcl IO to access journal files or count
  # the number of fsync() calls.
  pager.test exclusive.test jrnlmode.test sync.test misc1.test 
  journal1.test conflict.test crash8.test tkt3457.test io.test
  journal3.test 8_3_names.test








|







641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
  pragma journal_mode = 'memory'
} -files [test_set $::allquicktests -exclude {
  # Exclude all tests that simulate IO errors.
  autovacuum_ioerr2.test incrvacuum_ioerr.test ioerr.test
  ioerr.test ioerr2.test ioerr3.test ioerr4.test ioerr5.test
  vacuum3.test incrblob_err.test diskfull.test backup_ioerr.test
  e_fts3.test fts3cov.test fts3malloc.test fts3rnd.test
  fts3snippet.test mmapfault.test

  # Exclude test scripts that use tcl IO to access journal files or count
  # the number of fsync() calls.
  pager.test exclusive.test jrnlmode.test sync.test misc1.test 
  journal1.test conflict.test crash8.test tkt3457.test io.test
  journal3.test 8_3_names.test