Consultor Eletrônico

Status: Verified

SYMPTOM(s):

Oracle DataServer client terminates abnormally when certain 4GL query is not parsed properly

Oracle DataServer GPF when certain 4GL query is not parsed properly

The following error is found in the dataserv.lg file:

error text: ORA-01036: illegal variable name
umber ----

ORA-01036: illegal variable name
umber

FACT(s) (Environment):

Here is the 4GL query:

FIND FIRST table1 where
table1.field1 = "my" and
table1.field2 = "var" and
table1.StartDate <= 1/1/05 and
(table1.EndDate = ? or table1.EndDate >= 2/1/05 )
NO-LOCK NO-ERROR.

FIND LAST table2 where
table2.field1 = table1.field1 and
table2.StartDate = table1.StartDate and
(table2.EndDate <= table1.EndDate or table1.EndDate = ? ) and
table2.field2 = table1.field2 and
table2.field2 = "var"
NO-LOCK NO-ERROR.
The SQL parsed by the DataServer for the second query is:

SELECT /*+ INDEX_DESC(T0 table2##field2) */ PROGRESS_RECID FROM <OWNER NAME>.table2 T0 WHERE ((upper(field1) IS NULL AND STARTDATE IS NULL AND
(1 = 1 OR1 = 1) order by upper(PRODUCTID) DESC, upper(DISTRIBZONEID) DESC, upper(DISTRICTID) DESC, STARTDATE DESC, ENDDATE DESC
In the above parsed query there is a space missing between OR and 1 in (1 = 1 OR1 = 1)
Reproducible with Oracle DataServer OpenEdge Release 10.0B02 and OE 10.0B3
Oracle DataServer
OpenEdge 10.0B
OpenEdge 10.1A
OpenEdge 10.1A02 Service Pack
All Supported Operating Systems

CAUSE:

Bug# OE00120502

FIX:

Upgrade to OpenEdge 10.1B.

or,

If upgrade is not possible use the two workarounds described below:

Use the fields from the same table in the where clause when querying only one table.

Example:

Replace *table1.EndDate = ?* with *table2.EndDate = ?* in the second query.

OR,

Check for the record returned from the first query using IF AVAILABLE logic. If the record is not available change the second query to match the expectation of the query result.