[TAP] data type for column metadata

[Patrick Dowler]

On 2009-03-25 03:16:23 Gerard wrote:
> > BINARY - not an ADQL reserved word
> > VARBINARY - not an ADQL reserved word
>
> I have no experience with these.
> (how) is varbinary different from BLOB?

Not much and usually an implementation detail. I think BLOB is always stored 
in a separate location and the row has a reference to it while varbinary may 
be stored in the row (and contributes to the size of the row and limits on 
that).

So, the query writer does not need to differentiate between BINARY, VARBINARY, 
and BLOB to select them. To use such a column in the where clause, you can 
use most comparison operators on (var)binary -- assuming you know how to 
write a binary constant in that DB (non-trivial) -- but I don't know about 
BLOB. I would assume you could only do that with special tools/operators. 

I expect the same would apply to CLOB vs (VAR)CHAR.

> > I have never found a good use for the separate TIME and DATE,
>
> dateOfBirth maybe, but could well use TIMESTAMP.
>
> > nor the arbitrary precision numeric values
>
> I have seen DECIMAL (same as NUMERIC I think) a lot in business
> applications (amounts of money).
> not since then.

So does that mean we need these for money in the IVOA? :-)

> > and rarely/never use fixed size CHAR. So my short-list would include:
>
> I think the treatment of CHAR and VARCHAR is somewhat different per
> database.
> I see CHAR usage especially when you have a list of fixed length enumerated
> values,
> especially CHAR(1). I seem to remember that oracle (or was it db2, don't
> remember) suggested
> that for length 1 strings one should use CHAR, otherwise always VARCHAR.
> I have used CHAR a few times but keep having to deal with trailing spaces.

yeah, it matters internally (and you get the padding with CHAR) but the query 
writer doesn't care if length is variable for those.

> > BOOLEAN
> > INTEGER
> > BIGINT
> > REAL
> > DOUBLE
> > TIMESTAMP
> > VARCHAR
> > VARBINARY
> > POINT
> > REGION
>
> I would suggest adding SMALLINT for sure.
> Are we sure we don't want CLOB and BLOB?
> I believe those (used to?) have different semantics from shorter (VAR)CHARs
> where ordering is concerned.
> I.e. I have seen "order by <somclob>" to be illegal in some databases.

As above, I have not used them but suspect given their intended use that you 
generally only select them and maybe use special operators (e.g. text search 
with CLOB) to search them. That would imply that we do include BLOB and CLOB 
so users will know to not do anything but select them (if desired).

> Btw, I suppose that ADQL can make no statement about the data types of
> mathematical expressions.
> For example the result of multiplying an integer by a float, or dividing
> integer by integer.
> Does SQL92 say anything about this? Is it TAP's  task to define this
> semantics?

I think the "correct behaviour" of expressions is a matter for ADQL and TAP 
should not say anything. If it turns out that ADQL does not specify it fully 
(operator precedence, implicit type conversions, etc) and there are issues 
with real-world variations in behaviour, then that could be weighed and 
possibly included in the next revision of ADQL.

-- 

Patrick Dowler
Tel/Tél: (250) 363-0044
Canadian Astronomy Data Centre
National Research Council Canada
5071 West Saanich Road
Victoria, BC V9E 2M7

Centre canadien de donnees astronomiques
Conseil national de recherches Canada
5071, chemin West Saanich
Victoria (C.-B.) V9E 2M7