← Back to team overview

maria-discuss team mailing list archive

Re: The future of Dynamic Columns


Hi, Roberto!

On 29.01.15 15:55, Roberto Spadim wrote:
2015-01-29 12:34 GMT-02:00 Tom Worster <fsb@xxxxxxxxxx <mailto: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
    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
     to inform the client about the datatype of the column being read
     query is executed and the server can see what datatype the column

    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

      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.

hum... no
at 3rd column, you have a function returning a value, the function have a know data type, check this: http://dev.mysql.com/doc/refman/5.1/en/udf-calling.html that's how user defined functions are implemented at mysql, think about dynamic column as many functions to handle a string and extract/include information about variables with defined data type

for example... when you create a dynamic column with:
-- MariaDB 5.3+:
INSERT  INTO  tbl  SET  dyncol_blob=COLUMN_CREATE(1  /*column id*/,  "value");
-- MariaDB 10.0.1+:
INSERT  INTO  tbl  SET  dyncol_blob=COLUMN_CREATE("column_name",  "value");
check: https://mariadb.com/kb/en/mariadb/dynamic-columns/#column_create
you have a STRING as return of function COLUMN_CREATE()

The return value is suitable for

      o storing in a table
      o further modification with other dynamic columns functions

at "value" parameter, COLUMN_CREATE know that we are using a STRING, but should be any other data type

The *|as type|* part allows one to specify the value type. In most cases, this is redundant because MariaDB will be able to deduce the type of the value. Explicit type specification may be needed when the type of the value is not apparent. For example, a literal |'2012-12-01'| has a CHAR type by default, one will need to specify|'2012-12-01' AS DATE| to have it stored as a date. See the Datatypes <https://mariadb.com/kb/en/mariadb/dynamic-columns/#Datatypes> section for further details.

You are wrong, with UDF function result type known because then goes something like:

mysql>*|CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';|*
mysql>*|CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';|*
mysql>*|CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';|*

( http://dev.mysql.com/doc/refman/5.1/en/udf-compiling.html )

Type just have to be sent to the client. Some functions required argument of certain type so ask correcpondent val*() method of Item object but top most function (or constant or field) determinate type of the column in SELECT list and the type should be known.

So previous statement about 3rd column was very precise.


Follow ups