Question about a weird Oracle bug...

bonkey

I'm hoping someone can offer some insight into this one....something that cropped up in our Oracle migration....

While its not terribly good practice to rely on implicit conversions with dates, the following statement executes correctly:

(For reference, med_per_datum is defined as being of type DATE.)

SELECT *
FROM t_ema411
WHERE med_per_datum >= '01-Nov-2003'

So, if the above is valid, the following should be valid (if not immediately sensible):

SELECT * 
FROM ( SELECT *
		 FROM t_ema411
		 WHERE med_per_datum >= '01-Nov-2003'
	 ) t
WHERE med_per_datum = '30-Apr-2004'

Except it isn't. At least, not on our newly-installed 9i (9.2.0.6.0) installation. I've found this because its a simple example of a more complex situation that was causing some reports to fail post-import. The more complex situation isn't really the issue....just that this bug.

Now, I did say above that implicit conversions aren't terribly good practice. The good news is that editing either of the date conversions to use to_date will make everything ok again (although I'm at a loss to explain how a to_date with no date-format is different to an implicit conversion).

This isn't a massively difficult problem to work around (i.e. I just recode where necessary to have the explicit conversions that should have been there in the first place) ....but I'd like to know why its happening if possible.

I'm also tempted to say its possibly either a configuration issue or a very-version-specific, because I'm running an Oracle 9.2.0.1.0 instance on my lappy which doesn't exhibit this problem.

Anyone have any thoughts, or (as an alternative) a different version that they can check this against?

jc

ressem

Wild guess, don't have oracle available currently, but

"
Oracle automatically converts character values
that are in the default date format into date values when they are
used in date expressions. The default date format is specified by
the initialization parameter NLS_DATE_FORMAT and is a string such as
'DD-MON-YY'
"
Perhaps something is fussy about YY versus YYYY? Or the default NLS_DATE_FORMAT / NLS_LANG changed or mis-set in the new install.

The other obvious step is whether the query works on the real world data without the central select.
SELECT *
FROM t_ema411
WHERE med_per_datum = '30-Apr-2004'

to see whether the equivalence operation is being a stickler over the implicit time element in '30-Apr-2004' in a particular version. Unlikely though.

BC

Is the second query not returning what you want? Can you give an example?

You say your column is in the date format, that means there could be data in there stored with the date and time. For example 24-MAY-2004 18:30.

If you do a straighforward select on this column you will just see 24-MAY-2004. However, for comparison purposed if the time has actually been stored and you are doing a comparison using '=' it won't work.

Not sure if that makes sense so i'll try to explain with an example.

Say you have a table with col1 of type date.

You insert sysdate into this column.

Select * from the table will produce:
COl1

---

09-JUN-2005

however select to_char(col1, 'DD-MON-RRRR HH24:MI:SS') will produce:
Col1

---

09-JUN-2005 13:46:32

So if you try to do a select where col1='09-JUN-2005' you will get no rows selected.

Truncating the column however will work...select where trunc(col1) = '09-JUN-2005'

Hope that makes sense and it what you're after!

bonkey

Ta for the responses guys.

The second query fails. It returns an error, rather than an unexpected set of results. The error (which I notice I forgot to mention) is ORA-03113 - apparently a "something has gone wrong somewhere" catch-all error number for Oracle. The official text is "end-of-file on communication channel", and yes...it does actually break the database connection, so its not lying about that!

Because the date-conversion works without nesting, and/or within a to_date with no format specified, I can rule out the NLS_DATE_FORMAT as being the problem.


As I said...I know how to work around the occurrences that I've found where this causes a problem - by changing the implicit conversion to explicit to_date ones). When I do this, it works fine, and returns exactly what I want. I'd prefer to understand whats happening (and why) as well, though.


jc

bonkey

A little more investigation has revealed the following query will also cause the error to occur:

SELECT * FROM
  t_EMA411
  WHERE MED_PER_DATUM >= '01-Nov-03'
    AND med_per_datum = '30-Apr-04'

It occurred to me that the optimiser was probably folding the two queries (or the view and the query) together, so this would probably be the "base" form of the problem.

So the problem seems to boil down to multiple implicit conversions against the same field in a query generating an ora-03113.

ressem

Are you sure that this is not a symptom of the new setup? That the explicit conversion of one or other parameter might save resources demanded by an implicit conversion.

http://openacs.org/forums/message-view?message_id=78350
"
I'd assume you are on Linux... Usually, getting ORA-03113 after an installation on Linux means that the kernel parameters aren't adequate for Oracle: look in the installation guides and in the various sources of information on the net for informations about shared memory sizes and semaphores. Depending on your Linux distribution, you may or may not have to recompile your kernel to make it use the new parameters.
"
AFAIK you should only need to restart the server after setting values but I'm just a dabbler when it comes to oracle dba.

bonkey

ressem wrote:

Are you sure that this is not a symptom of the new setup?

I'm sure it is a symptom of the new setup, but with no other 9.2.0.6 installations to check against at the mo, I can't tell if its a version-specific or configuration-specific issue. Thats why I'm trying to find out more about it

<edit>
I should point out that nothing else in testing has produced this error-number, so its not like we've a massively unstable setup or anything...which is what the posts no that link seemed to be hinting at.

The problem with 03113, from what I can tell, is that its basically like the catch-all "Somethnig is broken, and none of the more specific errors occurred, so here you go" type.

jc

bonkey

bonkey wrote:

A little more investigation has revealed the following query will also cause the error to occur:

SELECT * FROM
t_EMA411
WHERE MED_PER_DATUM >= '01-Nov-03'
AND med_per_datum = '30-Apr-04'

I shoulda mentioned above that this is fine:

SELECT * FROM
t_EMA411
WHERE MED_PER_DATUM >= '01-Nov-03'
AND med_per_datum <= '30-Apr-04'

as is

SELECT * FROM
t_EMA411
WHERE MED_PER_DATUM = '01-Nov-03'
OR med_per_datum = '30-Apr-04'

(I swapped the AND to an OR here because ANDing equality with two different values isn't ever gonna return anything....but it does run fine)

So its an inequality and an equality comparison, in the same query, both implicitly converting.

jc