Issue Details (XML | Word | Printable)

Key: CORE-453
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Arno Brinkman
Reporter: Alice F. Bird
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Subquery connected with 'IN' clause

Created: 08/Sep/00 12:00 AM   Updated: 14/Jun/06 09:39 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 1.5.0

Time Tracking:
Not Specified

SF_ID: 213859


 Description  « Hide
SFID: 213859#
Submitted By: nobody

Given a table test which has the colums id integer (unique) and num integer.

Insert some records (~40.000 in my case). Most of the numbers in column num are distinct, only some are two ore three times repeated.

The following query fails:

Select id, num from test where num in (select num from test group by num having count(num > 1))

What is expected: those numbers WITH ID's which occur more than one time in the database.

Note: * Each query for it's own succeeds.
         * Preparing column num in a way that select num from test group by num having count(num > 1) would return only one row and rewritting the statement as Select id, num from test where num = (select num from test group by num having count(num > 1)) succeeds too.

Tested with MS Access, MS-Sql, Oracle; same data, query finished within three seconds in any case.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-11-14 15:51
Sender: nobody

The SELECT ... WHERE ... IN (SELECT .....) statemant never uses an index with interbase. While the statemant SELECT ... WHERE ... IN (1,2,3) Uses the defined index.
I think the problem is the optimizer.

When we make the query=plan ourself, it says we can't use the indexes.

Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-10-24 21:04
Sender: nobody
Reflexive queries work much better when the context is explicitly
described with table aliases. Obviously some parser/compilers are
better than others at guessing what you mean.

Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-10-13 18:09
Sender: prenosil
I do not understand what is your problem: wrong result or performance ? After correcting typo in your original query I got correct result. Do you have index on num field ?

Select id, num
  from test
 where num in
 (select num from test group by num having count(num) > 1)

You can also try this syntax (I believe it should be faster):

Select id, num
  from test t
 where not singular (select * from test where num=t.num)

Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-09-10 21:30
Sender: nobody
Thanks for your suggestion.

First: ... count(num > 1) was a typo mistake and ... the query you suggsted now returns all the rows in the table. OK, now IB is not hangig any more but i don't get the data i want ... so this is not a real solution.
BTW: my 'IN'- way should work either ....

Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-09-10 14:21
Sender: nobody
Um... try
Select id, num from test where
exists
(
select num from test test2
where test.num=test2.num
group by num having count(num)>1
)

Why? 'cus you can't do a count on (num > 1) and exists is much faster than in if you have more than just a few records in your subselect.

Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-09-08 10:08
Sender: nobody
Ehm, query fails means the DB is not responding any more and getting very bussy - but no stressy access to the disks.

Occured with IB6.01 super server windows 2000 professional. Downloaded the latest build from the Inprise download site.

Query submitted using IBConsole