Re: type checking SQL expressions

<jean.morissette666@videotron.ca> wrote:


>I want to build a simple SQL compiler and I would like to know if a
>formal specification of the type system used in SQL has been written
>somewhere?


SQL has been standardized by ANSI, but I don't know how much of the
type information has been standardized; a lot of types are product
specific; I've found numeric, char, varchar to be most generic,
allowing definitions to be used with different products; try asking in
alt.comp.databases where Joe Celko often responds.


SQL type attributes may be defined in product (and sometimes version)
specific system tables, and are in the commercial products I've used.


Semantic properties of the types are defined in product documentation,
including allowed conversions, and the names and properties of
available conversion, aggregrate, and utility functions.


Products may distinguish between types (and their formats) used for
storage in the DBMS, supported for internal use within the DBMS
software (sometimes only type name aliases, sometimes alternate
formats for efficient storage and/or computation), and externally for
interchange.


>It seems to me that the type system of SQL is incoherent.
>I let an exemple below that explain my affirmation.
>
>If the type of the request "SELECT column1, column2 FROM table1" is
>bag(tuple(type_of_column1, type_of_column2)),
>
>the type of column3 in the request "SELECT column1 FROM table2 WHERE column3
>IN (SELECT column1 FROM table1)" should be tuple(type_of_column1).
>
>But in this request:
>"SELECT column1 FROM table2 WHERE column3 > 9"
>the type of column3 is integer instead of tuple(integer)


The type of column3 is whatever it is defined as in the system tables
that store the information supplied in the DDL used to create table2.
The type of 9 is normally numeric, or whatever synonym is preferred by
the product being used.


The WHERE clause selects rows for which the predicate is true (just as
the HAVING clause selects aggregate rows, aggregated by the columns in
a GROUP BY clause, where an aggregate predicate is true).


The predicates will be evaluated depending on the types of column3 and
9, whether conversions between those types are allowed, and which
conversions are supported by a specific product.


>*Note that I'm not sure because I don't have a formal specification of the
>type system.
>[Interesting question. I've never seen a type spec beyond the elementary
>data types. -John]


I've never seen one in any product documentation. The ANSI standard is
your best (only) bet, and Joe Celko the most accessible proxy.


--
Thanks. Take care, Brian Inglis Calgary, Alberta, Canada


Brian.Inglis@CSi.com (Brian[dot]Inglis{at}SystematicSW[dot]ab[dot]ca)
        fake address use address above to reply
[The old ANSI and new ISO SQL standards are available at www.ansi.org
as downloadable PDFs for $18 per volume. That's pretty reasonable (the
ISO wants CHF 162) if you really want to know what the spec for SQL is.
-John]

Return to the comp.compilers page.
Search the comp.compilers archives again.