Re: SQL Mystery : Bug in latest SQO32_73.DLL?

Tim Romano <tim_at_superstream.net> wrote (in <354F8E1D.ABE319A2_at_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 Received on Wed May 06 1998 - 04:36:44 CDT