Download it now!
Bug 30919 - SQL "CAST" function won't work on MySQL backend
Summary: SQL "CAST" function won't work on MySQL backend
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86 (IA32) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-10-15 23:17 UTC by Oto Magaldadze
Modified: 2011-12-23 14:02 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
bug shot (139.84 KB, image/png)
2010-11-19 12:14 UTC, Oto Magaldadze
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Oto Magaldadze 2010-10-15 23:17:09 UTC
reproduction:

1. Connect base to mysql database
2. make query with the following code:
   Select CAST("columnname" as CHAR) from "TABLE1" 

end it will yield an error msgbox.


The above code works fine on embedded (HSQLDB) database. It is persistent on linux and windows. Also tried with native connector and jdbc connector.
Comment 1 Noel Power 2010-10-26 07:21:33 UTC
how about making things easy for those non-base enabled ( like me ) :-) with some nice easy steps to reproduce this
Comment 2 Oto Magaldadze 2010-10-27 05:20:46 UTC
Unfortunately I don't know how to explain it easier :((

You should know how to connect Base to mysql it's explained here:

http://wiki.services.openoffice.org/wiki/Connect_MySQL_and_Base

Once it's done a simple query with above command of cast will show an error. But I am skeptical about the users who haven't done anything in Base or at least some other database engines before.
Comment 3 Alex Thurgood 2010-11-18 02:59:19 UTC
I can not reproduce this bug with beta3 on Mac OSX using the mysql native connector.

I have a query that uses multiple CAST instructions. I paste this query into the Query Designer window. If you click on the button "Run SQL Command Directly" (SQL with green tick mark) and then execute the query it works just fine.


If you try and use the native OOo built-in SQL parser, it baulks because it can not parse mysql specific CAST instructions to the driver, and will thus throw an error message. However, the parser has always behaved this way. The same is true within OOo at least in 3.2 this is what happens, I haven't tested in the 3.3 RC versions to see if this behaviour has been changed, but I doubt it.

So, as far as I'm concerned, I would set this to resolved worksforme, or possibly even invalid. The question will inevitably arise as to whether the in-built SQL parser should be improved to allow for better support of the drivers capabilities, but that would probably involve quite a lot of development work (I'm not a Base developer).

Unset NEEDINFO, setting RESOLVED WORKSFORME.

Alex
Comment 4 Alex Thurgood 2010-11-18 03:11:37 UTC
Just so the OP understands that I used my own SQL query to test on my mysql server :

SELECT CAST(Filing_Date1 as CHAR) as 'FD' FROM mydb

This casts the date string as a character string.

Alex
Comment 5 Oto Magaldadze 2010-11-19 12:14:51 UTC
Created attachment 40418 [details]
bug shot
Comment 6 Alex Thurgood 2010-11-22 00:17:06 UTC
Try giving your cast an alias as was indicated in my example.

SELECT CAST(ID as CHAR) as 'TXTID' from customers


Alex
Comment 7 Oto Magaldadze 2010-11-22 07:45:08 UTC
(In reply to comment #6)
> Try giving your cast an alias as was indicated in my example.
> 
> SELECT CAST(ID as CHAR) as 'TXTID' from customers
> 
> 
> Alex

That trick works, but not when Cast is used in where clause, like:

SELECT CAST(ID as CHAR) as 'TXTID' from customers where  CAST(ID as CHAR) = '556'


Oto
Comment 8 Alex Thurgood 2010-11-22 07:55:53 UTC
IMHO this is not a bug, but a lack in the current implementation of either the parser developed by Sun/Oracle, or the mysql/jdbc connector provided by Sun/Oracle. Put in for a feature request / enhancement if you like, but be prepared to be asked to help develop it ;-) Even better, put in the request with the people at Oracle, since this "feature lack" comes from the code that was developed within StarOffice initially. And yes, it annoys me too that the Base implementation isn't as "advanced" as I feel it could be, but there you go, until I develop the skills to reimplement a complete SQL parser engine for LibO in C++, it probably isn't going to happen :-))


Setting to NOTOURBUG.

Alex
Comment 9 Oto Magaldadze 2010-12-09 06:47:47 UTC
Nevertheless, a patch has been submitted on the similar issue in openoffice.org so if anyone of devs can review and include this in LO suite would be nice. You can check it out here:
http://www.openoffice.org/issues/show_bug.cgi?id=115436