Apache OpenOffice (AOO) Bugzilla – Issue 20619
Query designer cannot handle some field types
Last modified: 2006-05-31 14:29:06 UTC
When creating a query on a MySQL DB using the graphical query designer, any queries including fields of type "tinyint" or "smallint" will refuse to save or run. The same query created as SQL can be run directly. Likewise, the SQL query can be successfully executed via ODBC using other interfaces (e.g. isql). There may be other field types that also fail. These are the only two I have been able to confirm. Cheers, Bruno
Forgot to provide the error message when running or saving a query including "tinyint" or "smallint" fields. Error message is: Syntax error in SQL expression syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE The SQL Status is HY000 (on run) or S1000 (on save) and the Error code is 1000. There is no reason why the SQL should be wrong or complex. It will fail even with something as simple as (the graphical equivalent of) "SELECT FIELD FROM TABLE;", so long as the field is one of the above types. Cheers, Bruno
correcting sub component (see http://www.openoffice.org/issues/describecomponents.cgi?component=Database%20access, please), and default owner
Hi bgprior, I can't reproduce this. I have some more questions: - What ODBC driver version do you use? - What Mysql version do you use? - What query do you use? (field name , table name, criterion, etc.) Bye Marc
MySQL: 4.0.12-1mdk (i.e. Mandrake package) MyODBC: 3.51.06 unixODBC: 2.2.6-4mdk MySQL is not running locally (it is on a server on the local network). The connection is fine in all other regards. The table I am querying has a lot of columns, so I won't include it all, but an abbreviated version would be something like: CREATE TABLE Elec_permonth ( ID int(11) NOT NULL auto_increment, Site_ID int(11) default NULL, Month tinyint(3) unsigned default NULL, Year smallint(6) default NULL, Elec_units int(11) default '0', Elec_price decimal(12,8) default NULL, Elec_Net decimal(20,4) default NULL, Modified timestamp(14) NOT NULL, PRIMARY KEY (ID), KEY Site_ID (Site_ID) ) TYPE=MyISAM; A query on this table as simple as "SELECT Month FROM Elec_permonth;" will refuse to execute, if created graphically. It works fine as direct SQL, but causes the errors if created graphically and then saved or run. I'm not the only one seeing an error like this. Have a look at: http://www.oooforum.org/forum/viewtopic.php?t=2073&highlight= Cheers, Bruno
Still get this problem, having upgraded to RC4-2mdk (Mandrake's patched version of RC4, which is supposed to be pretty similar to RC5/final). Bruno
Just noticed that the status of this is UNCONFIRMED. Is the link I gave to oooforum, where other people have discussed this problem, not enough to make this a confirmed bug? Bruno
Hi bgprior, to summarize all: - The LIMIT 5 is not supported by OOo SQL parser because it's not general SQL. (AFAIK) - To name a field month,year or any other reserved word is not good at all. So, when I don't use the keyword LIMIT 5 then all works fine. I will set this issue as RESOLVED/WONTFIX, however fell free to write an ENHANCEMENT for supporting th keyword LIMIT in mysql. Bye Marc
wontfix
What has this got to do with LIMIT? This bug is about queries failing on some field types. It has nothing to do with LIMIT, as far as I can see. Did you maybe update the wrong bug? Please reopen. This issue is unresolved. P.S. I'm not sure you're right about LIMIT being non-standard SQL, anyway.
hi bgprior, please create a simple table with the field 'myfield' and type smallint. Then create a query with this table. This works fine for me. It doesn't work if I use the LIMIT function in the SQL view or when I name my field with a reserved word ( month, year, etc). Does the simple query works? Bye Marc
change subcomponent to 'none'
Hello bgprior, please report back concerning the last comment to this issue from Marc at Mon Nov 17 02:13:08 -0700 2003. Thank you very much, Max, OOo Volunteer
I'm not currently at a location where I can connect to the MySQL DB, and won't be for a few days. I'll try to test the fieldname theory when I'm next in the office.
I have seen that the original reporter wanted to report something back in April, which has never happened. So my question is: is this issue solved? Or could the reporter of this issue close it?
I would say that if the reporter does not respond for such a long time after he definetely received the issuezilla email requesting a short comment we can close it for now. CCing myself. Bgprior, please let us know, if you can provide more information in case this defect still happens with the most recent stable build. Thank you, Max Weber
closing as WorksForMe, please reopen if needed