string functions returning garbage

From: Fred Pierce <fpierce_at_avialantic.com>
Date: Fri, 23 Nov 2001 18:54:49 GMT
Message-ID: <3BFE9B72.E792C1D4@avialantic.com>

Oracle 8.1.6.3.0 on unix. Problem duplicated on 8.1.7/Windows 2k (exp/imp
tables)

One of our developers sent me the following query, saying that it resulted
in an "invalid number" error. To summarize, the string functions are sometimes returning garbage (pointer problem?). Before I hand it to tech support I thought it was interesting enough to share with the group. Also
thought someone might know of a bug or see something obviously wrong in the
query - though I would assume if the latter Oracle would have a more polite
way of expressing it.

select planet,id1,
substr(upleft,4,3),
substr(lwrrit,4,3),
substr(upleft,1,3),
substr(lwrrit,1,3)

from map where planet like '%Moon%%' and
(to_number(ltrim(substr(upleft,4,3))) >= -45 and

 to_number(ltrim(substr(lwrrit,4,3))) <= -45) and

(to_number(ltrim(substr(upleft,1,3))) <= 180 and
to_number(ltrim(substr(lwrrit,1,3))) >= 180) and id1map in (select id1map from map_inv)

I verified the invalid number result, and using Embarcadero's DBArtisan was able to get a
little more info (when the result didn't cause it to crash)

Garbage result (varies but this is typical):

Planet                      id1

Oracle8i Enterprise Edition -455552554655535500 ...zillion zeros

upleft 4 3      lwrrit 4 3      upleft 13       lwrrit 1 3
Production	PxP		PxP		PxP

After commenting out clauses from subquery to second (to_number) condition
the query gets sensible results. Once that has happened I uncomment everything and the query works:

Moon 868 -42 -45 016 007

Although I didn't see anything strange in the tables, I ran dbverify which
came up clean.

Here are the table structures:

MAP -

CENTR	Y	VARCHAR2	6
DATENT	Y	VARCHAR2	10
FRC	Y	VARCHAR2	2
ID1	Y	NUMBER	7	7	0
ID1MAP	Y	VARCHAR2	12
LASTUP	Y	VARCHAR2	10
LWRLFT	Y	VARCHAR2	6
LWRRIT	Y	VARCHAR2	6
MAPTYP	Y	VARCHAR2	4
PLANET	Y	VARCHAR2	10
PUBYR	Y	VARCHAR2	2
QUANTY	Y	NUMBER	6	6	0
SCALE	Y	VARCHAR2	4
SOURCE	Y	VARCHAR2	4
TITLE	Y	VARCHAR2	30
UPLEFT	Y	VARCHAR2	6
UPRITE	Y	VARCHAR2	6

AREA	N	VARCHAR2	13
ID1	N	NUMBER	7	7	0
ID1MAP	N	VARCHAR2	12
QUANTY	Y	NUMBER	4	4	0
SITE	N	VARCHAR2	4

I think the Planet Oracle and its connection with the Enterprise is a clue also, but just can't decide what it means...

fdp

"Toulouse was too loose; he had to lose"

Fred Pierce (DNRC)- fpierce_at_avialantic.com Mid-Atlantic Aviation on the Web - http://www.avialantic.com ** Mid Atlantic Air Museum WWII Weekend Airshow June 7-9 2002 and Planes, Trains, & Automobiles Transporation Show Sept. 21-22 2002 - www.maam.org **
*** World Airshow News - www.worldairshownews.com **

Received on Fri Nov 23 2001 - 12:54:49 CST