Xref: alice comp.databases.oracle.server:20346
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!nntp.news.xara.net!xara.net!peer.news.u-net.net!u-net!news.u-net.com!not-for-mail
From: lgmiller@elmrd.u-net.com (Graham Miller)
Newsgroups: comp.databases.oracle.server
Subject: Re: SQL Mystery : Bug in latest SQO32_73.DLL?
Date: Wed, 06 May 1998 09:36:44 GMT
Organization: a u-net customer
Lines: 69
Message-ID: <35502a44.4181275@news.u-net.com>
References: <354F8E1D.ABE319A2@superstream.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/16.451
X-No-Archive: yes

Tim Romano <tim@superstream.net> wrote (in
<354F8E1D.ABE319A2@superstream.net>)...

| Running Oracle 7.3 Workgroup for NT Server.
| 
| MyTable T has these columns:
| 
| ID	VARCHAR2(3)
| AGENT	VARCHAR2(5)
| 
| select * from MyTable T
| where T.ID = '059'
| and T.AGENT = '1111'
| 
| returns null set.  BUT
It will be worthwhile checking the data in the Table. Try the query:

select * from MyTable T
where T.ID = '059'
T.AGENT  LIKE '%1111%'

I suspect it will return rows with a leading space or zero.

Oracle does not have to do any conversion on the data and looks for an
exact match for your literal character string.

 
| select * from MyTable T
| where T.ID = '059'
| and T.AGENT = 1111
| 
| returns many rows. 
| 
| Am I missing something very simple? Both columns are
| VARCHAR2.
You have provided Oracle with a literal *number* string to test
against. Oracle is going to have to convert either:
1) The literal constant to a character string.
2) The column character data to a number. 

The safest thing Oracle can do is convert the *column* data to number,
which it does. This will provide the match if the column contains a
number string which evaluates to 1111. Some example: +1111,  ' 1111',
'01111' etc. (tested in Scott/Tiger - Oracle version 7.2)

The other point to be aware of is that the index will not be used on
the AGENT column if there is one as Oracle has applied a function to
the Agent column. Also be aware that if any column contains a
non-numeric string then a value exception will occur.

The above behaviour is a consequence of Oracle (and a lot of other
computer lanquages) of doing automatic type conversion on data.

| Query executed from SQL Plus 3.3.  Query also executed from
| TOAD.
| Query also executed from MS-Access via ODBC. All three tools
| yield identical results. 
| 
| The only thing that has changed on our system was the
| installation today of Oracle's latest ODBC driver for 32-bit
| Windows, and the existing SQO32_73.DLL file was replaced as
| part of the installation.
|
I don't see how this can affect the above behaviour.
 
| TIA,
| Tim

graham