/ Check-in [28ce42f7]
Login
Overview
Comment:Make the FROM clause on a SELECT optional. If omitted, the result of the SELECT is a single row consisting of the values in the expression list. (CVS 520)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:28ce42f7872e2660faa22e66b508db9b1f046af0
User & Date: drh 2002-04-06 13:57:43
Context
2002-04-06
14:10
Added the last_insert_rowid() SQL function. (CVS 521) check-in: 6aca3f86 user: drh tags: trunk
13:57
Make the FROM clause on a SELECT optional. If omitted, the result of the SELECT is a single row consisting of the values in the expression list. (CVS 520) check-in: 28ce42f7 user: drh tags: trunk
2002-04-04
15:10
Add an fflush() call to shell.c to insure that all output has been written before we prompt for a new line of input. (CVS 519) check-in: 93227418 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
267
268
269
270
271
272
273

274
275
276
277
278
279
280
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.60 2002/04/04 02:10:57 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
%type seltablist {IdList*}
%destructor seltablist {sqliteIdListDelete($$);}
%type stl_prefix {IdList*}
%destructor stl_prefix {sqliteIdListDelete($$);}
%type from {IdList*}
%destructor from {sqliteIdListDelete($$);}


from(A) ::= FROM seltablist(X).               {A = X;}
stl_prefix(A) ::= seltablist(X) COMMA.        {A = X;}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) ids(Y).       {A = sqliteIdListAppend(X,&Y);}
seltablist(A) ::= stl_prefix(X) ids(Y) as ids(Z). {
  A = sqliteIdListAppend(X,&Y);
  sqliteIdListAddAlias(A,&Z);







|







 







>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.61 2002/04/06 13:57:43 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
%type seltablist {IdList*}
%destructor seltablist {sqliteIdListDelete($$);}
%type stl_prefix {IdList*}
%destructor stl_prefix {sqliteIdListDelete($$);}
%type from {IdList*}
%destructor from {sqliteIdListDelete($$);}

from(A) ::= .                                 {A = sqliteMalloc(sizeof(*A));}
from(A) ::= FROM seltablist(X).               {A = X;}
stl_prefix(A) ::= seltablist(X) COMMA.        {A = X;}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) ids(Y).       {A = sqliteIdListAppend(X,&Y);}
seltablist(A) ::= stl_prefix(X) ids(Y) as ids(Z). {
  A = sqliteIdListAppend(X,&Y);
  sqliteIdListAddAlias(A,&Z);

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
621
622
623
624
625
626
627
628








































629
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.23 2002/04/04 02:10:57 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
  }
} {t3.a 1 t3.b 2 max(a) 3 max(b) 4}
do_test select1-11.15 {
  execsql2 {
    SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
  }
} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2}









































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.24 2002/04/06 13:57:43 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
  }
} {t3.a 1 t3.b 2 max(a) 3 max(b) 4}
do_test select1-11.15 {
  execsql2 {
    SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
  }
} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2}

# Tests of SELECT statements without a FROM clause.
#
do_test select1-12.1 {
  execsql2 {
    SELECT 1+2+3
  }
} {1+2+3 6}
do_test select1-12.2 {
  execsql2 {
    SELECT 1,'hello',2
  }
} {1 1 'hello' hello 2 2}
do_test select1-12.3 {
  execsql2 {
    SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
  }
} {a 1 b hello c 2}
do_test select1-12.4 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
  }
} {1 2 3 4}
do_test select1-12.5 {
  execsql {
    SELECT 3, 4 UNION SELECT * FROM t3;
  }
} {1 2 3 4}
do_test select1-12.6 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 1);
  }
} {1 2}
do_test select1-12.7 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 2);
  }
} {}

finish_test

Changes to www/changes.tcl.

24
25
26
27
28
29
30

31
32
33
34
35
36
37
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Apr 03 (2.4.7)} {
<li>Add the ability to put TABLE.* in the column list of a
    SELECT statement.</li>

}

chng {2002 Apr 02 (2.4.6)} {
<li>Bug fix: Correctly handle terms in the WHERE clause of a join that
    do not contain a comparison operator.</li>
}








>







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Apr 03 (2.4.7)} {
<li>Add the ability to put TABLE.* in the column list of a
    SELECT statement.</li>
<li>Permit SELECT statements without a FROM clause.</li>
}

chng {2002 Apr 02 (2.4.6)} {
<li>Bug fix: Correctly handle terms in the WHERE clause of a join that
    do not contain a comparison operator.</li>
}

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
...
989
990
991
992
993
994
995


996
997
998
999
1000
1001
1002
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.29 2002/03/28 14:20:08 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<a href="#insert">INSERT command</a> documentation for additional
information.</p>  
}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list>
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
[LIMIT <integer> [OFFSET <integer>]]
} {result} {
................................................................................
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.</p>

<p>The query is executed again one or more tables specified after
the FROM keyword.  If more than one table is specified, then the
query is against the (inner) join of the various tables.  A sub-query
in parentheses may be substituted for any table name in the FROM clause.


</p>

<p>The WHERE clause can be used to limit the number of rows over
which the query operates.  In the current implementation,
indices will only be used to
optimize the query if WHERE expression contains equality comparisons
connected by the AND operator.</p>



|







 







|







 







>
>







1
2
3
4
5
6
7
8
9
10
11
...
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
...
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.30 2002/04/06 13:57:44 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<a href="#insert">INSERT command</a> documentation for additional
information.</p>  
}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> [FROM <table-list>]
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
[LIMIT <integer> [OFFSET <integer>]]
} {result} {
................................................................................
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.</p>

<p>The query is executed again one or more tables specified after
the FROM keyword.  If more than one table is specified, then the
query is against the (inner) join of the various tables.  A sub-query
in parentheses may be substituted for any table name in the FROM clause.
The entire FROM clause may be omitted, in which case the result is a
single row consisting of the values of the expression list.
</p>

<p>The WHERE clause can be used to limit the number of rows over
which the query operates.  In the current implementation,
indices will only be used to
optimize the query if WHERE expression contains equality comparisons
connected by the AND operator.</p>