← Back to team overview

maria-discuss team mailing list archive

Data type conversion (Was: The future of Dynamic Columns)

 

Hi!

On 30.01.15 00:03, Roberto Spadim wrote:
Hi Oleksandr!! :)

    Hi, Roberto!

         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:


hum nice,
but COLUMN_CREATE should return *always* string data type right? and the second parameter of function, in this case "value", is a parameter with a value="value" and data type=string , right?

i'm not a internal expert, i'm trying to understand some doubts, check i'm my affirmations are right: 1) UPDATE/DELETE/SELECT, at WHERE "part", data type is a problem to operators for example "=", cause it need a cast before compare... for example 3="03", it must know if we will convert interger to string, or string to integer, that's why a CAST at COLUMN_GET is important
Yes, there are some rules of type casting if they are not the same for expression like this and even for SELECT UNION.
2) at SELECT "part" data type is a problem to mysql protocol, cause we must send to client what type each column will receive, at this part i don't know how COLUMN_GET should work... internally it return the data type before receiving data? or it will read some data and after inform the data type being used? or each row it return a data type, and internally mysql buffer the SELECT result, and identify after SELECT what data type should be used?
Internally on preparing it ask what type the function (or field or constant) returns "naturally"

For example all string manipulation functions return string, but they can return number if it will be able to convert string.

3) at UPDATE/INSERT/REPLACE data type is important to storage don't truncate,overflow or any other possible data loss
It depend on SQL_MODE, so it can be warning or error if data can't be converted or data lost



    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';|*


hum, if we have a function that could return INT or STRING or REAL, we must have 3 functions, right? i'm trying to search at mariadb source code how COLUMN_GET is implemented, should a internall function return >1 datatypes and UDF not? (i'm begginer to internall functions)
All function internally can return any internal type (integer (boolean), double, decimal, string (date/time)), but sometimes it cause an error or require conversion.



    ( 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.

nice, in this case
CONCAT('abc',any_function())
the any_function could return any data type, but client/server must know what datatype CONCAT return, right?
Yes, and it return string. Even more, on preparation it will try to guess maximum possible length of the string. but concat() can return numeric types also, the problem is that data loss or errors possible in this case (depends on SQL_MODE as I told)

    So previous statement about 3rd column was very precise.


your comments make things easier to understand each day :) many thanks

you are welcome



References