Bug #56967 Substr function on a 'timestamp' field returns BLOB
Submitted: 23 Sep 2010 12:26 Modified: 11 Nov 2010 15:36
Reporter: Michal Dzielinski Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:5.2.28 OS:Windows
Assigned to: CPU Architecture:Any
Tags: regression

[23 Sep 2010 12:26] Michal Dzielinski
Description:
I'm having problems whenever my query contains the 'substr' command. In the results window the cells in the respective column contain only 'BLOB' and when I export the results, they are empty.

The problem did not exist in an earlier version of Workbench I was using (5.2.16 beta). There the appropriate substrings were displayed as they should.

How to repeat:
Create a table with a string column (in my case it was 'timestamp' format) and try to retrieve part of it via the 'substr' command.
[23 Sep 2010 12:37] Valeriy Kravchuk
Please, provide complete test case, with CREATE TABLE, INSERTs to add data and problematic SELECT.
[23 Sep 2010 15:15] Michal Dzielinski
I'm sorry but I work on a database built by someone else, so I don't really know how that was done...

The statement I use is along those lines:

SELECT substr(timestamp, 1, 10), count(*)  %% timestamp is yyyy-mm-dd hh:mm:ss
FROM `database`
GROUP BY substr(timestamp, 1, 10);

and the column corresponding to substr(timestamp, 1, 10) shows only the 'BLOB' icon and after exporting it's empty. In the previous version of Workbench I used it showed the actual values of substring.
[23 Sep 2010 16:51] Valeriy Kravchuk
For the following table on current MySQL 5.1 server, with the following data, I have NO problem like you describe:

macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tts(c1 timestamp);
Query OK, 0 rows affected (0.39 sec)

mysql> insert into tts values ('2010-09-23 19:00:01'), ('2010-09-22 19:00:02');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tts;
+---------------------+
| c1                  |
+---------------------+
| 2010-09-23 19:00:01 |
| 2010-09-22 19:00:02 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select substr(c1, 1, 10), count(*) from tts GROUP BY substr(c1, 1, 10);
+-------------------+----------+
| substr(c1, 1, 10) | count(*) |
+-------------------+----------+
| 2010-09-22        |        1 |
| 2010-09-23        |        1 |
+-------------------+----------+
2 rows in set (0.01 sec)

I see the same results as above in Workbench 5.2.28. So, please, try to find out and explain how your case is different.
[27 Sep 2010 10:21] Michal Dzielinski
Sorry, I made a mistake in the first post - the format of the column is actually 'DATETIME'.

I did some more checks and the 'susbtr' command works fine for 'VARCHAR' columns but not for 'DATE' or 'TIME' columns.
[28 Sep 2010 3:33] Valeriy Kravchuk
What exact server version, x.y.z, are you working with?
[28 Sep 2010 10:29] Michal Dzielinski
The server version is 5.1.44

My problem also exists in Workbench 5.2.27, however not in 5.2.16 beta.
[11 Nov 2010 15:37] Alfredo Kojima
This is a duplicate of bug #56642