Re: Query gives incorrect results

From: Edzard <edzard_at_volcanomail.com>
Date: 4 Jun 2003 05:40:43 -0700
Message-ID: <5d75e934.0306040440.7cb23b11@posting.google.com>

Here is another case. Again it is an artificial query, written just to reproduce certain behaviour of 9.2.0.3 RDBMS.

SELECT COUNT (*)
  FROM dba_indexes i, dba_ind_columns ic, dba_tab_columns c

 WHERE i.table_owner = 'SYS'
   AND ic.index_name = i.index_name
   AND ic.table_name = i.table_name

   AND ic.table_owner = i.table_owner
   AND ic.column_position =

        (SELECT MIN (ic2.column_position)
           FROM dba_ind_columns ic2
          WHERE ic2.index_name = ic.index_name
            AND ic2.table_name = ic.table_name
            AND ic2.table_owner = ic.table_owner)
   AND c.table_name = ic.table_name
   AND c.owner = ic.table_owner

   AND c.column_name = ic.column_name
   AND c.data_type = 'VARCHAR2'
/

  COUNT(*)

---

       306

Now change COUNT (*) to * and at the bottom of the output you get:

51 rows selected. Received on Wed Jun 04 2003 - 07:40:43 CDT