Issue 115436 - mysql Cast(col1 as CHAR) yields error
Summary: mysql Cast(col1 as CHAR) yields error
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.3 RC3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: OOo 3.3
Assignee: marc.neumann
QA Contact: issues@dba
Keywords: oooqa, regression
Depends on:
Blocks: 111112
  Show dependency tree
Reported: 2010-11-05 17:19 UTC by ottoshmidt
Modified: 2010-12-18 08:47 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description ottoshmidt 2010-11-05 17:19:12 UTC

1. Connect Base to mysql database (assuming there is a table created)
2. Query with SQL:
"Select CAST(Col1 as CHAR) as Col1 From Table1"

error occurs

As far as my observations yielded, it's a connector independent bug.
Comment 1 r4zoli 2010-11-06 10:33:00 UTC
I can confirm it, the fiedd data type is VARCHAR.
I tested on win7 with MySQL 5.1.44 with native (1.0.1), JDBC and ODBC driver.
Same results, error: 
The data content could not be loaded.

SQL Status: 42000
Error code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'CHAR ) AS "field_name"
FROM "table1"' at line 1

If I use query on DATE data type field, 
Select CAST(Col1 as DATE) as Col1 From Table1
I get error:
The data content could not be loaded.
SQL Status: 42000
Error code: 1584

Incorrect parameters in the call to stored function 'CAST'

Both query runs in MySQL Workbench without error.

I tested in OOo 3.2.1, I get same results with ODBC and JDBC drivers.
Surprisingly the native driver 1.0.0 let the queries run, both works correctly. 

Other CAST Queries run without error:
SELECT CAST('2000-01-01' AS DATE);
select cast('2007-12-25' as DATETIME) 

Assign developer.
Comment 2 sos 2010-12-08 16:23:04 UTC
sinds OOO330m17 

cast(timestamp as date ) works no longer  also
Comment 3 ocke.janssen 2010-12-09 07:27:05 UTC
I tested a 5.04 with OOo 3.2, 3.2.1, 3.3, DEV300m94 and I got the same error
when connecting through
- native connector

And I tested the ODBC driver with the odbcte32.exe from MS with the same error.
So OOo is out of the line. The convert(dd,DATE) function does the job for me.

On the other side I also tested the mySQL workbench and the CAST function
worked. I don't know what they use.

This a bug in the connectors which I can' fix.
Comment 4 ocke.janssen 2010-12-09 09:56:45 UTC
I found the reason.
Comment 5 ocke.janssen 2010-12-09 09:58:17 UTC
The reason is that for MySQL the statement

SELECT CAST('2001-01-01' AS DATE) as `MyDate` FROM `testdb`.`i115436`; 
works but
SELECT CAST ('2001-01-01' AS DATE) as `MyDate` FROM `testdb`.`i115436`;
doesn't. You see the space between the CAST and the bracket.
Comment 6 ocke.janssen 2010-12-09 12:00:06 UTC
Fixed in cws dba33m
Comment 7 ocke.janssen 2010-12-09 13:09:22 UTC
Please verify. Thanks.
Comment 8 Oliver Specht 2010-12-09 13:48:05 UTC
patch reviewed
Comment 9 marc.neumann 2010-12-09 15:09:46 UTC
I have checked that the following SQL String works now 
SELECT CAST ('2010-12-09' AS DATE) FROM "test"."tt_test_create-table"
with mysql and the mysql native connector
Comment 10 r4zoli 2010-12-10 09:03:25 UTC
I tested cws dba33m on ubuntu 10.10 32bit linux, and queries:
SELECT CAST(`datum` AS CHAR) as `MyDate` FROM `test`.`Tabla1`; 
SELECT CAST(`datum` AS DATE) as `MyDate` FROM `test`.`Tabla1`; 
SELECT CAST ('2010-12-09' AS DATE) FROM `test`.`Tabla1`; 

Runs without problem with JDBC and native connection. 
Comment 11 r4zoli 2010-12-18 08:47:58 UTC
I checked in OOo 3.3RC8 (m18), and no error occurs. Closing.