Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Subquery connected with 'IN' clause [CORE453] #448

Closed
firebird-automations opened this issue Sep 8, 2000 · 8 comments
Closed

Subquery connected with 'IN' clause [CORE453] #448

firebird-automations opened this issue Sep 8, 2000 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alice F. Bird (firebirds)

Assigned to: @ArnoBrinkman

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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)

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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 ....

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10477 ] => Firebird [ 14769 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant