Issue 122286 - postgres DB query using ilike '%something%' result in syntax error
Summary: postgres DB query using ilike '%something%' result in syntax error
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: 3.4.1
Hardware: PC Windows XP
: P3 Major (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2013-05-10 21:09 UTC by woodz
Modified: 2013-05-10 21:09 UTC (History)
1 user (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 woodz 2013-05-10 21:09:43 UTC
Be precise:
- client OS-version: Windows XP 5.1 (Build 2600.xpsp_sp3_gdr.130307-0422 : Service Pack 3) 32bit
- DB-Version: PostgreSQL 9.0.12 running on localhost:5432
- server OS-version: openSUSE 11.4 (i586), VERSION = 11.4. CODENAME = Celadon
- JDBC-Version: postgresql-9.2-1002.jdbc4.jar

In AOO query design, if using the ilike comparator in a where clause of a query to a postgres DB-table connected through a registerd JDBC databese file, a message box appears on saving the query saying: "Syntax error in SQL expression"

Steps to Reproduce:
- having a running postgres DB service
- connect to DB via JDBC, registered database file *.odb
- establishing the proper tables to do the following query:
select name, surname from names
where name ilike '%myname%' and surname not ilike '%Miller%'
order by surname,name;

Actual Results:
message box: base "Syntax error in SQL expression"
hit 'More' button:
- lists 3 errors
1. SQL Status: HY000, Error code: 1000, Syntax error in SQL expression
2. SQL Status: HY000 Error code: 1000
3. SQL Status: HY000, Error code: 1000, syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

unable to store my query

Expected Results:
same as the query would be (replacing any "ilike" by "like" as a rotten compromise):
select name, surname from names
where name like '%myname%' and surname not like '%Miller%'
order by surname,name;

able to store my query without errors

Build Date & Platform: Date and platform of the build in which you first encountered the bug.

Build: AOO 3.4.1, AOO341m1(Build9593) - Rev.1372282, OS: Windows XP

Additional Builds and Platforms: none

Additional Information: see 'Be precise'

Regression info: none