Inconsistent Query related to ANALYZE and LOWER() (or other function)

From: GHouck <hksys_at_teleport.com>
Date: 2000/05/03
Message-ID: <3910C59F.72D@teleport.com>#1/1

Query with LOWER() and ANALYZE under 8.0.5

In a previous posting, I stated that I was getting inconsistent results using a function like LOWER() on one or more of the columns. I have since determined that I can get 'correct' results if I do one or both of the following, neither of which is acceptable:

Remove the 2nd column in the GROUP BY (which is not acceptable, since it is required). In the example below, this would be the column <mgr>.
Delete the statistics for the tables/indexes.

Using; SCOTT/TIGER

The following Query WORKS under all conditions:

SELECT COUNT(*),job FROM scott.EMP WHERE job IN (
  SELECT job
  FROM scott.EMP
  GROUP BY job,mgr
  HAVING COUNT(*)>1
)
GROUP BY job

Correct Results:

2       ANALYST
3       MANAGER
4       SALESMAN


-----------------------------------------------




-----------------------------------------------

This query fails if Tables & Indexes are Analyzed (Compute Statistics):

SELECT COUNT(*),LOWER(job) FROM scott.EMP WHERE LOWER(job) IN (
  SELECT LOWER(job)
  FROM scott.EMP
  GROUP BY LOWER(job),mgr
  HAVING COUNT(*)>1
)
GROUP BY LOWER(job)

Invalid Results:

4 clerk

The only difference between the two is that the LOWER() function has been applied, and/or the Table is ANALYZED or not.

Is this known behavior? I assumed ANALYZE could give me poor (if dated) or good results, but not incorrect results. Am I missing something here?

Oracle 8.0.5, Windows NT (SP5)

Thanks,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Wed May 03 2000 - 00:00:00 CDT