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: | |
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
[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