maria-discuss team mailing list archive

[Tom Worster <fsb@xxxxxxxxxx>]

I have one final technical question. It is for my understanding and does
not reflect a problem I have as a user.

The manual says:

> The answer is: SQL is a statically-typed language. The SQL interpreter
needs to know the datatypes of all expressions before the query is run
(for example, when one is using prepared statements and runs
 "select COLUMN_GET(...)", the prepared statement API requires the server
 to inform the client about the datatype of the column being read before
the
 query is executed and the server can see what datatype the column actually
 has).


This appears to say that the constraint is that the server needs to
determine the type of each column in the data sent to the client. SQL such
as:

  SELECT x, y, COLUMN_GET(stuff, 'z') FROM t

can't work because the datatype of the 3rd column in "table" returned to
the client can't be determined before reading data. I think I can follow
this explanation. But I have no use for SQL like that (the naked
COLUMN_GET, i.e. a COLUMN_GET that has no context). I use COLUMN_JSON() to
load data into memory. It's a better fit for the AR ORM I use.

I only need COLUMN_GET in the context of a larger expression, such as:

  SELECT x, y FROM t WHERE COLUMN_GET(stuff, 'z') = 5

In this select, the datatypes for the returned data are clear before
reading any data. So why do I have to explicitly cast the dyncol 'z'? Or
what if there is no returned data at all:

  DELETE FROM t WHERE COLUMN_GET(stuff, 'z') = 5

If the need to cast is to determine the type of data sent to the client,
that argument surely doesn't apply here. It really wouldn't apply to this
either:


  SELECT x, y, CONCAT('Hello. My name is ', COLUMN_GET(stuff, 'z')) FROM t

If I understand correctly (doubtful) and the cast is only necessary for
the naked COLUMN_GET, then isn't the cast in the wrong place? The cast
should be put after the COLUMN_GET function to remove the nakedness. The
server should throw an SQL error if it encounters a naked COLUMN_GET. In
other words, COLUMN_GET can be used only as _part_of_ an expression.


Tom