Issue 97811

Summary:

Base SQL parser does not accept literal string value using '+' for concatenation

Product:

Base

Reporter:

eremmel <jhf.remmelzwaal>

Component:

code

Assignee:

AOO issues mailing list <issues>

Status:

CONFIRMED ---

QA Contact:

Severity:

Trivial

Priority:

CC:

drewjensen.inbox, issues

Version:

OOO300m9

Keywords:

oooqa

Target Milestone:

---

Hardware:

OS:

Windows, all

Issue Type:

DEFECT

Latest Confirmation in:

---

Developer Difficulty:

---

Attachments:

Description	Flags
Test database containing queries for testing	none

Description eremmel 2009-01-06 18:30:49 UTC

Not native executed queries in Base against MS access database that contain a
concatenations based on plus symbol, break when a string literal value is
involved. The reported error is: 
  SQL Status HY000, 
  Error code:1000 
  "Syntax error in SQL expression", 
  "syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"


The following example goes wrong:
   SELECT f1 + 'value' FROM table1;
whereas this is OK:
   SELECT f1 + f2 FROM table1;

A workaround is:
   SELECT X.f1 + X.v FROM ( SELECT f1, 'value' AS "v" FROM table1 ) X

Comment 1 Mechtilde 2009-01-06 20:22:36 UTC

change OS to windows because
it is only possible under windows to connect to MS Access tables

Comment 2 eremmel 2009-03-15 23:27:38 UTC

I played with the internal database of Base and found the same issue as I
reported for Base with MS Access. The internal database supports two
concatenation operators '||' and '+'. Base is not able to parse queries that use
the '+' operator for concatenation in combination with a literal value. I
attached a test database "97811.ods" that contains the following queries:

"q_concat_col_plus_col with Base SQL: ok"
This query proves that concatenation with '+' symbol is supported with internal
database.

"q_concat_col_plus_literal with Direct SQL:ok"
This query proves that concatenation with '+' symbol is supported with internal
database in direct SQL mode. Disable Direct SQL mode makes the query fail.

"q_concat_col_plus_literal via derived table with Base SQL: ok"
This proves the workaround by using a derived table.

Note the other 'workaround' for internal Base database is to use the more common
'||' operator, but that is no available for MS access.

I assume that both databases suffer from the fact that Base SQL parser can not
parse literal string values in combination with '+' symbol as string concatenation.

Comment 3 eremmel 2009-03-15 23:29:13 UTC

Created attachment 60939 [details]
Test database containing queries for testing

Comment 4 drewjensen.inbox 2009-05-31 12:05:32 UTC

Confirmed w/ OOo 3.1

Updated summary line.