COALESCE Extrage results on dates and strings

Question

First of all, I want to clarify that I have already read this question. The problem described there is quite similar to my problem but related with a bug, which has been already solved.

I have been getting strange results after querying a MySQL Server (Ver 14.14 running on Linux) through MySQL WorkBench 5.2.47 C running on Windows.

The problem is that selections on COALESCE over a list of date and string fields return BLOBS instead dates. For example

SET @dat='20130812';
SELECT COALESCE(dateA, @dat) FROM table1;        

I know the problem can be fixed by casting COALESCE into DATE type:

SELECT CAST(COALESCE(dateA,$dat) AS DATE) FROM table1;

It can also be solved just casting @dat:

SELECT COALESCE(dateA,CAST(@dat AS DATE)) FROM table1;

It seems to me that it is related to internal type conversion but, why does my first query work when the client is the mysql console client? (just as described on the question linked above).

Does the client perform some type of query preprocessing? In that case, shouldn't it be common to all clients?

I used to think that SQL servers received raw queries (text) from their clients, now I realise that isn't true at all. What level of preprocessing is performed at client side?

Answer 1

At the end, it seems I was right thinking that not hard preprocessing is done at the client side.

The difference comes at data visualization: The rules for implicit type conversion apply on COALESCE so, when some elements of its input list are dates and others are strings, these rules make all elements to be implicitly cast to BLOBs containing each date string representation.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

On the other hand, the mysql console client automatically shows this blobs as text whereas MySQL workbench uses its BLOB representation.

If opened:

Which can be read as text (that is what mysql console client does by default):

In my opinion and based on the MySQL reference text I quoted above, the best way to cope with this problem is to cast the date element to date. This way, no implicit conversions are made and COALESCE returns a date type value.