Issue 97811 - Base SQL parser does not accept literal string value using '+' for concatenation
Summary: Base SQL parser does not accept literal string value using '+' for concatenation
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO300m9
Hardware: PC Windows, all
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2009-01-06 18:30 UTC by eremmel
Modified: 2013-01-29 21:47 UTC (History)
2 users (show)

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

Attachments
Test database containing queries for testing (19.48 KB, application/vnd.sun.xml.base)
2009-03-15 23:29 UTC, eremmel 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
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.