Issue 87398 - Syntax error in SQL Expression when no where clause
Summary: Syntax error in SQL Expression when no where clause
Status: CLOSED NOT_AN_OOO_ISSUE
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: DEV300m4
Hardware: Mac Mac OS X, all
: P3 Trivial (vote)
Target Milestone: ---
Assignee: dbaneedsconfirm
QA Contact: issues@dba
URL:
Keywords: needmoreinfo, oooqa
Depends on:
Blocks:
 
Reported: 2008-03-25 03:19 UTC by dyrcona
Modified: 2008-09-15 12:40 UTC (History)
5 users (show)

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


Attachments
Test DB to show issue (3.08 KB, application/vnd.sun.xml.base)
2008-03-25 14:34 UTC, dyrcona
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description dyrcona 2008-03-25 03:19:54 UTC
Whenever you try to access a table with a method that has an underlying SQL
representation of "select * from tablename" you get an error dialog which says:
"The data content could not be loaded. Syntax error in SQL expression." Clicking
the "More" button in the error dialogs leads through the following:

SQL Status: HY000
Error Code: 1000
Syntax error in SQL expression

select * from "people"

syntax error, unexpected INVALIDSYMBOL,
expecting $end, Invalid symbol

NOTE: people was the name of a table I created in a database to test this.

The error occurs reproducibly under a number of circumstances. The easiest way
to cause it to happen is to open an existing database, going to the table
editor, and then double-clicking on any given table to see/edit the data.

It happens with forms or queries that load an entire table, i.e. that have no
where clause.

If you edit a query in SQL view, you can see the error with select * from
tablename. However, if you type select * from tablename where 1=1, you get the
results that you expected for the previous query.

This error occurs for me in an existing hsqldb, a brand new hsqldb created just
to test this, and a MySQL database accessed via JDBC and mysql-connector 5.0.
Comment 1 eric_openoffice 2008-03-25 08:27:23 UTC
Confirming the issue using DEV300_m4 under Mac OS X 10.5.2 on Mac Intel.
Following the given steps the issue is reproducible.  
Comment 2 Mechtilde 2008-03-25 11:24:11 UTC
Does it also occurs without quoting marks?
Comment 3 dyrcona 2008-03-25 13:37:25 UTC
Yes, it occurs without quote marks.

All you have to do is double-click on a table in the Tables sheet to view its data.
Comment 4 Mechtilde 2008-03-25 13:42:21 UTC
can you attach an example database with this problem.

so I can try to reproduse it on my machine?
Comment 5 dyrcona 2008-03-25 14:34:32 UTC
Created attachment 52285 [details]
Test DB to show issue
Comment 6 dyrcona 2008-03-25 14:37:04 UTC
I added the database that I created to test this yesterday.

However, with any database that you open with Base from DEV300_m4 on a Mac
running Leopard, you get this problem. I can't say whether or not it affects
other platforms or OS releases, as this is the only one where I've used m4.
Comment 7 Uwe Altmann 2008-03-25 18:25:04 UTC
select * from people
Works in mahos DEV300m2 (OOo_3.0.0_080314_MacOSXIntel_install_de) with your
testdb.odb (four entries, right?) and an own one HSQLDB too
Comment 8 dyrcona 2008-03-25 18:45:26 UTC
Ok. If it's working in m2 and not working in m4? What changed? sb83?
Comment 9 Frank Schönheit 2008-03-26 12:01:05 UTC
I know two issues which could cause this:

First, we once had a problem that our syntax file, when compiled with a recent
flex version, produced nonsense, but didn't when compiled with an older version.
This was fixed quite a while ago, and I doubt it re-appeared in our source
(there was no significant change between m2 and m4, not even in sb83).

Second, the flex coming with Leopard has a problem: When fed with our input
file, it produces code which is, in exactly one line, different from the code
produced by an flex downloaded from the project page. Unfortunately, this one
line makes our parser to fail.

Both of the reasons mentioned above are known to cause the problem described here.

I would assume that the m4 build we talk about was produced in a different
environment than the m2 build, the latter using a flex from Tiger, the former
using a flex from Leopard.
Comment 10 dyrcona 2008-03-26 13:34:23 UTC
Yes, in my case, the m4 build was done on my Leopard machine.

Tiger has flex version 2.5.4 and Leopard has 2.5.33. I can install 2.5.35 and
see if that makes a difference. If not, then I could manually install 2.5.4 on
Leopard and make sure it is my path before /usr/bin/flex when building.

I'll try changing flex versions and report the results.

Oh, and I diffed m2 and m4 last night, and there were no significant changes in
dbaccess, just some string resource files changed.
Comment 11 Frank Schönheit 2008-03-26 14:18:34 UTC
(the interesting project for the diff would be connectivity, not dbaccess)

2.5.4 is known to work, so I assume "downgrading" to it would solve the problem.

Note that an out-of-the-box 2.5.33 is also known to work (meanwhile, it formerly
didn't, this was the first problem I mentioned above, but it is fixed now on OOo
side). What causes the problem is the 2.5.33 shipped with Leopoard.

When you change your flex version, you need to rebuild connectivity/source/parse
and connectivity/source/dbtools. (touching sqlflex.l should suffice, but to be
on the safe side you could do a complete rebuild.)
Comment 12 dyrcona 2008-03-26 19:08:19 UTC
I did a complete rebuild from a fresh checkout with flex 2.5.35 installed from
MacPorts.

The problem has vanished. Base is working for me. I'll have to make a note about
flex versions on my Leopard Build page.

Comment 13 eric_openoffice 2008-03-26 22:57:26 UTC
Confirmed. If I use flex 2.5.35 out of MacPorts the tables are accesable. I only
rebuild connectivity and will do a clean build with the same flex once I have
the time to do so. 

@fs:
Yes. m2 was build using Tiger. So you're right that the bug is caused by
Leopards flex. Is there a chance to get this fixed for the Leopard flex?
Comment 14 Frank Schönheit 2008-03-27 07:35:10 UTC
I know this is reported to the Mac developers, though not yet in their bug
tracking system, but via other channels. Just yesterday I asked our side of the
contact to check the status of this issue.
Comment 15 christoph.lukasiak 2008-09-01 15:07:40 UTC
does this problem still occure in a current version?
Comment 16 dyrcona 2008-09-01 23:21:31 UTC
I can't say.

I am building with a more recent version of flex, 2.5.35, than comes with
Leopard, 2.5.33.

I don't have the problem any more, but I haven't build with an older flex.

Guess, I could try that later this week (time permitting) and let you know.
Comment 17 dyrcona 2008-09-03 00:22:38 UTC
I just tried DEV300_m29 with CWS cloph11 with the flex that comes with
Leopard/Xcode 3, and not a more recent one from Mac Ports, and the problem still
occurs.

When I try a select statement without a where clause, the parser tells me there
is an error in SQL statement.

The following works:

select *
from country
where 1=1


The following don't:

select *
from country
where 1

select *
from country

I am not sure that anything in OO.o DBA needs fixing, though. After all, the
problem only seems to occurs with certain versions of flex, 2.5.4 and 2.5.35
work just fine.
Comment 18 christoph.lukasiak 2008-09-15 12:40:00 UTC
=> close
Comment 19 christoph.lukasiak 2008-09-15 12:40:23 UTC
.