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
Comments
Commented by: Alice F. Bird (firebirds) Date: 2000-11-14 15:51 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. When we make the query=plan ourself, it says we can't use the indexes. |
Commented by: Alice F. Bird (firebirds) Date: 2000-10-24 21:04 |
Commented by: Alice F. Bird (firebirds) Date: 2000-10-13 18:09 Select id, num You can also try this syntax (I believe it should be faster): Select id, num |
Commented by: Alice F. Bird (firebirds) Date: 2000-09-10 21:30 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. |
Commented by: Alice F. Bird (firebirds) Date: 2000-09-10 14:21 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. |
Commented by: Alice F. Bird (firebirds) Date: 2000-09-08 10:08 Occured with IB6.01 super server windows 2000 professional. Downloaded the latest build from the Inprise download site. Query submitted using IBConsole |
Modified by: @pcisarWorkflow: jira [ 10477 ] => Firebird [ 14769 ] |
Modified by: @pavel-zotovQA Status: No test |
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.
The text was updated successfully, but these errors were encountered: