Resolve TIMESTAMP() vs CAST()

[Zarquan]

Following on from discussions at the October 2019 IVOA interop meeting, a number of people raised comments and issues about TIMESTAMP() and CAST().

This is a placholder issue that represents an ongoing discussion on the DAL mailing list. The discussion will continue as normal on the mailing list, and this GitHub issue will be closed when the discussion reaches a conclusion.

[Zarquan]

Email from Markus Demleitner outlining the issue - ADQL 2.1: TIMESTAMP() vs CAST()

[gmantele]

I tried to sum up this issue by comparing the CAST solution and the constructor one. Here is what I got:

---

• Examples:

  CAST	Constructor
  CAST('2020-05-04T16:07:12', TIMESTAMP)	TIMESTAMP('2020-05-04T16:07:12')
  CAST(30, SMALLINT)	SMALLINT(30)

• Requirements (difficulties represented in bold font):

  CAST	Constructor
  Define a keyword for each possible data-type to cast into (e.g. TIMESTAMP, SMALLINT, ...)	Define a function for each data-type to support
  Very simple grammar => low effort	More complicated grammar: different for each function + update all possible places where each function can be used => more effort
  Description of possible data-types conversions (risk of different implementations)	No need: the grammar already describe that for each data-type
  Fixed number of parameters	Any required number of parameters
  Syntax/Function easy to remember (already existing in all DBMS)	A different signature for each constructor
  Returned type difficult to predict (need a relatively advanced parser)	Returned type already known in advance (=> simple parser)
  All numeric types can easily be supported in there without extra-change	Create functions for all numeric types

• If CAST, should we include geometries with DALI literals?

  CAST	Constructor
  CAST('23 42', POINT)	POINT(23, 42)

---

I am still quite shared between the two solutions. I would tend to have a little preference for CAST even though it means a parser a slightly more complicated because CAST could return a numeric, but also a geometry and potentially string. Like a column reference or a UDF, it would become some kind of joker expression in term of datatype.

Have I missed something?
Any new opinion?
Any preference?

[gmantele]

Considering there is not a solution better than the other one, I suggest the following:

• we go for the CAST solution
• we keep the constructors for POINT, CIRCLE, POLYGON and REGION as they already exist and they offer an alternative with more than a single argument
• no TIMESTAMP function for now ; if later we see a quite important demand for that, we will consider again to add it

That being said, here are some more details about this solution:

• Syntax: (inspired from SQL-92)

  CAST( {expression | NULL} AS TYPE)

• Example with TIMESTAMP:

  CAST('2021-01-14T11:25:00' AS TIMESTAMP)

• Minimum set of possible target types: (cross-inspiration between SQL-92 and ADQL-2.1, sec.3, "Type system")

  • Geometry:

    • POINT
    • CIRCLE
    • POLYGON

  • Character:

    • Fixed-length: CHAR[(n)]
    • Variable-length: VARCHAR[(n)]

  • Binary:

    • Fixed-length: BINARY[(n)]
    • Variable-length: VARBINARY[(n)]

  • Exact numeric:

    • BIT
    • INTEGER
    • SMALLINT
    • BIGINT

  • Approximate numeric:

    • REAL
    • DOUBLE

  • Date, time:

    • TIMESTAMP

  • LOB (Large OBject)

    • CLOB
    • BLOB

  • Types to think about including now, later or not at all:

    • REGION ?
    • INTERVAL ?
    • DATE and TIME ?
    • NUMERIC[(p[,s])] ? or/and DECIMAL[(p[,s])] ?

• Possible conversions: (inspired from SQL-92)
  Y = yes,
  N = no,
  Y* = yes, but output may be different from one implementation to another
  Y? = to define

  Input/Output	Exact Numeric	Approximate Numeric	Variable-length Character	Fixed-length Character	Variable-length Bit String	Fixed-length Bit String	Timestamp	POINT	CIRCLE	POLYGON
  Exact Numeric	Y	Y	Y	Y*	N	N	N	N	N	N
  Approximate Numeric	Y	Y	Y	Y*	N	N	N	N	N	N
  Character	Y	Y*	Y	Y*	Y?	Y?	Y	Y*	Y*	Y*
  Bit String	N	N	Y	Y	Y?	Y?	N	N	N	N
  Binary	N	N	Y	Y	Y?	Y?	N	N	N	N
  Timestamp	N	N	Y	Y	N	N	Y	N	N	N
  POINT	N	N	Y	Y	N	N	N	Y	N	N
  CIRCLE	N	N	Y	Y	N	N	N	N	Y	N
  POLYGON	N	N	Y	Y	N	N	N	N	N	Y

  A such matrix was provided in SQL-92. So, I thought it could be interesting to have an updated version of it in ADQL-2.1. Bad idea ?

  The idea of this matrix is not to be exhaustive but to explicit what is the expected behaviour. I think it is especially important to point out in which cases there might be something different in implementations. The idea is also to be as generic as possible and to not explicitly cite database solutions ; hence the Y* just highlighting a potential difference between implementations.

[gmantele]

I am currently trying to implement this entire suggestion with 3 DBMS: Postgres (+PgSphere), MySQL and MS-SQLServer. That's how I already came with the above matrix (that I will adjust with further experimentations if needed).

Note that the CAST function offered by MySQL is really poor. Much less data types are supported. For instance, it is not possible to cast into a SMALLINT or BIGINT, and so on for all variants of other numerics, characters and binary.

[gmantele]

I'd also like to add a Non-Normative Appendix to the ADQL-2.1 document about auto-casting. An ADQL implementation could automatically cast a type into another in function of the context. The perfect example is numerics. Most of the time, DBMS are performing such auto-cast from, let's say, a REAL into an DOUBLE. It is also the case with TIMESTAMP when manipulating/comparing them with time related functions/columns.

Based on that it would not be forbidden that an ADQL implementation choose to allow auto-casting for types like numerics, TIMESTAMP or even geometrical types (e.g. WHERE 1=CONTAINS(POINT(ra,dec), '12.3 45.6 0.1')). But of course, that is not normative. If ADQL implementations want to support that (which is already partly the case for some simple datatypes in existing implementations), why not. But that is not part of the ADQL standard (mainly due to the fact that it would require some important grammar work).

What do you think about this idea? A Non-Normative Appendix or just a comment in the CAST function description?

[msdemlei]

On Tue, Jan 26, 2021 at 02:45:27AM -0800, Grégory Mantelet wrote: Considering there is not a solution better than the other one, I suggest the following: - we go for the `CAST` solution - we keep the constructors for `POINT`, `CIRCLE`, `POLYGON` and `REGION` as they already exist and they offer an alternative with more than a single argument - no `TIMESTAMP` function for now ; if later we see a quite important demand for that, we will consider again to add it

+1 on all of these.

That being said, here are some more details about this solution: - **Syntax:** _(inspired from SQL-92)_ ```sql CAST( {expression | NULL} AS TYPE) ``` - **Example with TIMESTAMP:** ```sql CAST('2021-01-14T11:25:00' AS TIMESTAMP) ```

Yes, that's about what I've implemented.

- **Minimum set of possible target types:** _(cross-inspiration between SQL-92 and ADQL-2.1, sec.3, "Type system")_ - Geometry: - `POINT` - `CIRCLE` - `POLYGON`

I could live without those, in particular since we'd have to define semantics for them as they don't exist in SQL. Is this supposed to work with DALI strings? With arrays put in in some way? While I give you it'd be nice to have them for symmetry, I'm not sure I'd put a lot of work into this.

- Character: - Fixed-length: `CHAR[(n)]` - Variable-length: `VARCHAR[(n)]` - Binary: - Fixed-length: `BINARY[(n)]` - Variable-length: `VARBINARY[(n)]` - Exact numeric: - `BIT`

Since we don't have BINARY or BIT or function or operators for them anywhere else in ADQL (or do we?), I'd leave those out. Me, for one, wouldn't know what to do with such a cast (and so I'd blindly hand it through to postgres -- is that useful?).

- `INTEGER` - `SMALLINT` - `BIGINT` - Approximate numeric: - `REAL` - `DOUBLE`

I *think* we need to allow "DOUBLE PRECISION" here, as that is (or used to be?) the official SQL92 name for the type.

- Date, time: - `TIMESTAMP` - LOB (Large OBject) - `CLOB` - `BLOB`

Again, I could live without those: What would they do?

- Types to think about including now, later or not at all: - _`REGION` ?_ - _`INTERVAL` ?_ - _`DATE` and `TIME` ?_ - _`NUMERIC[(p[,s])]` ?_ or/and _`DECIMAL[(p[,s])]` ?_

Yeah, let's defer those until people miss them. As to a cast matrix: I can see why we might want to define a minimal compliant behaviour, in particular for what we define in addition to sql92. But then... I'd say it's enough if, for our extra types, we simply say what must be castable how in words.

[gmantele]

• Minimum set of possible target types: (cross-inspiration between SQL-92 and ADQL-2.1, sec.3, "Type system") - Geometry: - POINT - CIRCLE - POLYGON

I could live without those, in particular since we'd have to define semantics for them as they don't exist in SQL. Is this supposed to work with DALI strings? With arrays put in in some way?

For me, yes, it is supposed to work with DALI strings. For instance:

CAST('12.3 45.6' AS POINT)
CAST('12.3 45.6 1.0' AS CIRCLE)
CAST('1.0 0.1 2.0 0.2 3.0 0.3' AS POLYGON)

I did not think to arrays...particularly because they are not really integrated in ADQL. So, for the moment, I'd say, only CAST(string AS POINT/CIRCLE/POLYGON). We'll see later for arrays, once we'll work on their correct integration in the ADQL language.

• Character: - Fixed-length: CHAR[(n)] - Variable-length: VARCHAR[(n)] - Binary: - Fixed-length: BINARY[(n)] - Variable-length: VARBINARY[(n)] - Exact numeric: - BIT

Since we don't have BINARY or BIT or function or operators for them anywhere else in ADQL (or do we?), I'd leave those out. Me, for one, wouldn't know what to do with such a cast (and so I'd blindly hand it through to postgres -- is that useful?).

I have exactly the same feeling about those. However, they are part of DALI or VOTable and described in the section 3. of ADQL-2.1. I personally have to usage of these datatypes, but some TAP implementations may have...if this is the case, it would good that they speak now so that we can figure out what to do with these datatypes. If not, I would not be against deferring them.

In the meantime, let's defer them. If any implementation needs them, it is not forbidden to support them....it is just they won't be part of the standard yet.

• DOUBLE

I think we need to allow "DOUBLE PRECISION" here, as that is (or used to be?) the official SQL92 name for the type.

In my first draft, it was also DOUBLE PRECISION. But I realise it was written as DOUBLE in the section 3.. So, if we go for DOUBLE PRECISION, we'll have to fix this in the section 3. as well.

• LOB (Large OBject) - CLOB - BLOB

Again, I could live without those: What would they do?

Again, I get them from the section 3. of ADQL-2.1. They are datatypes existing in most DBMS. Whether or not they are useful in ADQL, I have no idea. I personally have nothing against putting them aside until there is a need for them in astronomy.

• Types to think about including now, later or not at all: - REGION ? - INTERVAL ? - DATE and TIME ? - NUMERIC[(p[,s])] ? or/and DECIMAL[(p[,s])] ?

Yeah, let's defer those until people miss them.

OK.

As to a cast matrix: I can see why we might want to define a minimal compliant behaviour, in particular for what we define in addition to sql92. But then... I'd say it's enough if, for our extra types, we simply say what must be castable how in words.

I think it is more synthetic and in some way, you (as an unexperimented ADQL user) immediately get the answer to your question (without the ambiguity that words can have sometime): "is this conversion allowed?". For the conversions marked with a Y* I however intend to write few explanations below the matrix.

[gmantele]

I think I am going to write a PullRequest in the next days (at least after the potential chat tomorrow during the DAL running meeting) to make a proposition matching all these ideas. Then, we will see how it turns out from that.