Arno,

I'm reversing the order of your selections from the 03
standard - some day I get rich and get a copy, but not
any time soon at this rate. The selection on temporary
table name and content scope is quite similar to the
99 standard language, though even more baroque. The
<as subquery clause> is more interesting.

Before we go into the specifics, can someone tell me
if the <as subquery clause> is specific to temporary
table definitions, or can it apply to permanent tables
as well? From the snippets I've seen, it appears to
apply to both table types.

If the clause is general, perhaps we can separate the
discussion implementation of the <as subquery clause>
from the temporary table implementation.

> 6) If <as subquery clause> is specified, then:
> a) Let ASQ be the <subquery> immediately contained in
> <as subquery clause>. Let QT be the table specified
> by ASQ.

QT is a table, but not necessarily a base table. In Firebird
terms it would be a record selection expression and can include
joins, unions, subselects, etc. Clever of them to use a nice
simple clear word like "table" to mean both base table and
everything that can be built on it.

> b) If any two columns in QT have equivalent <column name>s,
> or if any column of QT has an implementation-dependent
> name, then <column name list> shall be specified.

OK. The names of the columns in a base table must be unique.

> c) Let D be the degree of QT.

I love it when they talk pseudo-math like that.

> d) <column name list> shall not contain two or more
> equivalent <column name>s.

Right. That seems easy.

> e) The number of <column name>s in <column name list>
> shall be D.

Good-o!

> f) For i, 1 (one) ? i ? D:
> i) Case:
> 1) If <column name list> is specified, then let
> QCNi be the i-th <column name> in that
> <column name list>.
> 2) Otherwise, let QCNi be the <column name> of
> the i-th column of QT.

OK, guys, we've got two cases. If there's a column name list,
we use that to get names for the columns and we use them in the
order presented. Otherwise, we use the names from the table QT
produced by the subquery ASQ, also in the order presented.

> ii) Let QDTi be the declared type of the i-th column of
> QT.

OK. We pick up the declared type from the subquery result set.
That certainly makes sense. And use them in the same order
as the names. Clever. Here's the old definition of "declared
type"

k) declared type: Of an expression denoting a value, the unique
data type that is common to every value that might result from
evaluation of that expression. The term is also applicable to
anything that can be referenced to denote a value, such as, for
example, a parameter, column or variable.

It appears, from usage, to include character set. How about
domain? Constraints? Default value?

> iii) If the nullability characteristic of the i-th
> column of QT is known not nullable, then let
> QNCi be NOT NULL; otherwise, let QNCi be the
> zero-length string.

Note that here we're talking about QNCi, not QCNi as mentioned
above. QCNi was the name of column i. QNCi is its null constraint.
An entirely different thing, QNCi.

> iv) Let CDi be the <column definition> QCNi QDTi QNCi

OK. Now concatenate the column name, data type, and null constraint.

> g) <as subquery clause> is effectively replaced by a
> <table element list> TEL of the form: CD1, ..., CDD

So, the <as subquery clause> works pretty much exactly as one
would expect. Does it create a dependency between the source
table and the newly defined table? For example, consider a
table like this:

create table foo as select * from bar;

What happens when I add a column to bar? When I drop bar
completely? Does that change if the table is temporary?
Now add in the <with or without data> qualifier

<as subquery clause> ::=
[ <left paren> <column name list> <right paren> ]
AS <subquery> <with or without data>

<with or without data> ::=
WITH NO DATA | WITH DATA

If you've got a permanent table, with data, you probably
don't want to have to delete that table when you change the
source. If you've got a temporary table with data, you
probably want the table definition to follow the source,
since you're going to be filling it with data when the
it's reference by a connection and it's nice to have the
query continue to work.

How's that supposed to happen?


And now for further discussion of local, global, created,
module, and declared temporary tables... for the impatient,
it appears that the proposal and standard are consistent
except that the proposal omits everything having to do with
modules. There, you've saved yourself twenty minutes.

><quote>
>A global temporary table is a named table defined by
>a <table definition> that specifies GLOBAL TEMPORARY.

OK.

>A created local temporary table is a named table defined by
>a <table definition> that specifies LOCAL TEMPORARY.

OK. Why is one a "created local temporary table" and the
other a "global temporary table" without the word created?
The opposite of "created", at least in the USA, is "evolved".
Are there "evolved local temporary tables"? Are global
tables de facto created or are they evolved? Inquiring minds
want to know....

Ah I see below that there are wheels within wheels... a
"created local temporary table" is to be contrasted with
a "declared local temporary table". Nothing to do with
evolution at all.

>Global and created local temporary tables are effectively
>materialized only when referenced in an SQL-session. Every
>SQL-client module in every SQL-session that references a
>created local temporary table causes a distinct instance of
>that created local temporary table to be materialized. That
>is, the contents of a global temporary table or a created
>local temporary table cannot be shared between SQL-sessions.

OK, that's consistent with the proposal.

>In addition, the contents of a created local temporary table
>cannot be shared between SQL-client modules of a single
>SQL-session.

And that's consistent with the not implemented LOCAL temporary
table supporting the not implemented SQL-client modules modules.

>The definition of a global temporary table or a created
>local temporary table appears in a schema. In SQL language,
>the name and the scope of the name of a global temporary
>table or a created local temporary table are indistinguishable
>from those of a persistent base table.

OK, that seems consistent.

>However, because global temporary table contents are
>distinct within SQL-sessions, and created local temporary
>tables are distinct within SQLclient modules within
>SQL-sessions, the effective <schema name> of the schema
>in which the global temporary table or the created local
>temporary table is instantiated is an implementation-
>dependent <schema name> that may be thought of as having
>been effectively derived from the <schema name> of the
>schema in which the global temporary table or created
>local temporary table is defined and the implementation-
>dependent SQLsession identifier associated with the SQL-
>session.
>
>In addition, the effective <schema name> of the schema in
>which the created local temporary table is instantiated
>may be thought of as being further qualified by a unique
>implementation-dependent name associated with the SQL-client
>module in which the created local temporary table is
>referenced.

OK, if you add our not implemented implementation-dependent
names schemas to our not implemented SQL-client modules, the
proposal follows those rules.

>A module local temporary table is a named table defined by a
><temporary table declaration> in an SQL-client module. A
>module local temporary table is effectively materialized the
>first time it is referenced in an SQLsession, and it persists
>for that SQL-session.

Ah. A module local temporary table is created with a
<temporary table declaration>, but a created local
temporary table is declared with a <table declaration>
that specifies LOCAL TEMPORARY. I see the distinction,
maybe.

>A declared local temporary table may be declared in an
>SQL-client module.
>
>A declared local temporary table is a module local temporary
>table.

OK. There are two species of local temporary tables,
"created" and "declared", and the whole topic is irrelevant
because Firebird doesn't support SQL-client modules.

>A declared local temporary table is accessible only by
>externally-invoked procedures in the SQL-client module that
>contains the <temporary table declaration> that declares the
>declared local temporary table. The effective <schema name>
>of the <schema qualified name> of the declared local temporary
>table may be thought of as the implementation-dependent
>SQLsession identifier associated with the SQL-session and a
>unique implementation-dependent name associated with the
><SQL-client module definition> that contains the
><temporary table declaration>.

OK. The name scope of declared local temporary tables is
the module containing the declaration. Interesting, but
not critically important to us at this juncture.

>All references to a declared local temporary table are
>prefixed by "MODULE."

As above.

>The materialization of a temporary table does not persist
>beyond the end of the SQL-session in which the table
>was materialized. Temporary tables are effectively empty
>at the start of an SQL-session.

OK, that seems clear and consistent with the proposal.


Cheers,


Ann