SQL query against a field larger than 32-bit fails with error in SQL ENGINE at 1435 data_t.cxx

A SQL query with a WHERE clause on a field larger than 32-bit fails with an error on the sort results file: Encountered internal error in SQL ENGINE at 1435 in /vobs_sql/sql/src/public/data/data_t.cxx.

Jan 20, 2021
Knowledge

Title

URL Name

Running-a-SQL-query-gives-error-Encountered-internal-error-in-SQL-ENGINE-at-1435-in-vobs-sql-sql-src-public-data-data-t-cxx

Article Number

000181233

Environment

Product: OpenEdge Release: 10.x, 11.0 to 11.7.0 inclusive OS: All Supported Operating Systems Other: OpenEdge SQL

Question/Problem Description

SQL query with a WHERE clause on a field larger than 32-bit fails with an error on the sort results file: Encountered internal error in SQL ENGINE at 1435 in /vobs_sql/sql/src/public/data/data_t.cxx

SQL SELECT statement searching for a row where the field is of varchar type but you are searching for a number and that number is larger than a 32 bit int fails

Example: Running the following SQL Query against the "Customer" table for "sports2000" database fails with an internal error on the sort results file data_t.cxx :

SELECT ROWID FROM PUB.Customer WHERE customer.address2 = 2147483649;

Steps to Reproduce

Connect to the sports2000 via SQL client and run the following query against the "Customer" table: proenv> prodb testsql sports2000 proenv> proserve testsql -S 8989 proenv> sqlexp -S 8989 -user pub -password pub -db testsql -infile ./testint.sql # testint.sql: SELECT ROWID FROM PUB.Customer WHERE customer.address2 = 2147483649; proenv> sqlexp -S 8989 -user pub -password pub -db test -infile ./testintd.sql # testintd.sql SELECT "_File-number", "_File-name", "_Prime-index", "_Index-Name", "_Field-recid", "_Field-Name", "_Index-Seq", "_dtype", "_Data-Type" FROM pub."_File" INNER JOIN pub."_Index" ON pub."_File"."_Prime-index" = pub."_Index".ROWID INNER JOIN pub."_Index-Field" ON pub."_File"."_Prime-index" = pub."_Index-Field"."_Index-recid" INNER JOIN pub."_Field" ON pub."_Index-Field"."_Field-recid" = pub."_Field".ROWID WHERE "_File"."_File-Number" > 0 AND "_Prime-index" IS NOT NULL ORDER BY "_File-Name" ASC, "_Index-Seq" ASC

Clarifying Information

As long as that number is 32bit the SQL Query does not fail
A SELECT Query of a field from table where charfield = <number> under 32-bit (INT) returns expected results

Error Message

Connecting user "pub" to URL "jdbc:datadirect:openedge://localhost:8989;databaseName=<dbname>"... (8920) rowid -------------------- === SQL Exception 1 === SQLState=HY000 ErrorCode=-211022 [DataDirect][OpenEdge JDBC Driver][OpenEdge] Encountered internal error in SQL ENGINE at 1435 in /vobs_sql/sql/src/public/data/data_t.cxx. Contact Progress Technical Support

Defect Number

Defect PSC00353571

Enhancement Number

Cause

This internal error occurs because there is no compare method between NVARCHAR and the current BIGINT internal datatype. There is a compare method for NVARCHAR and INT and for NVARCHAR and the obsolete bigint internal type in Version 9. This issue will not necessarily reproduce in test or QA environments as it depends on data stored with values above 2^32-1 and how the data are stored.

Resolution

Upgrade to OpenEdge 11.7.1 or later, where a compare method similar to the other nvarchar::int* methods has been added. While OpenEdge 11.7.1 is specifically where this issue was addressed, Progress recommends upgrading to the latest LTS release:

OpenEdge Life Cycle
https://docs.progress.com/bundle/openedge-life-cycle/page/OpenEdge-Life-Cycle.html

Other advantages of upgrading to 11.7 are in the context of this Article, performance improvements in the SQL engine and enhanced troubleshooting features should queries not work as expected:

SQL-92: How to turn SQL statement logging on and off?

Workaround

Place Quotes around the number field

Quote the queried digits at sites where the numeric characters that need to be queried exceed the 32 bit integer boundary (2^31-1):

My_ varchar = ‘2147483649’

This workaround to quote the numeric value in a char field will work, but will depend on how the data are stored:

Quoting the value will work when the data are compared purely to a string of digits with no leading zeros: 2147483649
When the value in the varchar column in the database has leading zero's for example, quoting the queried value will not return the required results: 002147483649

Alternatively, restructure the query to return the result without touching BIGINT data.

Example: Restructure the query in the second example above, instead of using: WHERE "_File"."_File-Number" > 0

SELECT "_File-Number", "_File-Name", CAST("_Field".ROWID AS INT) AS '_Field-recid', "_Field-Name", "_Data-Type", "_Extent", "_Fetch-Type", "_Width" 
    FROM PUB."_field" INNER JOIN PUB."_file" ON "_field"."_File-recid" = "_File".ROWID WHERE "_File"."_Tbl-Type" = 'T';

Notes

Progress Article:
SQL engine internal error on a query having a scalar subquery in the select list and a subquery in the from clause.

Keyword Phrase

Last Modified Date

1/20/2021 10:31 PM

SQL query against a field larger than 32-bit fails with error in SQL ENGINE at 1435 data_t.cxx

Files(0)

Was this article helpful?

Was this article helpful?

SQL query against a field larger than 32-bit fails with error in SQL ENGINE at 1435 data_t.cxx

Information

Related Articles