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
isql query fails to return correct result [CORE6008] #6258
Comments
Commented by: Sean Leyne (seanleyne) Ray, 1- What size is the backup? 2- Your SQL is contains ambiguous references, please see if this SQL yields different results: select count(*) from addresses adds |
Commented by: Ray Holme (rholme) Seyne, I sent you a copy of the DB and the query (tar gzipped MINIMAL DB to reproduce). It is small after I cut it. As for the query, your query looks better than mine and is clearer to read, BUT Sorry, Ray |
Commented by: Sean Leyne (seanleyne) Ray, 1- How did you "send" the DB? 2- Sorry, but your query is ambiguous, "addr_id" is present in all the tables. 3- By "same results" do you mean "invalid/incorrect"? |
Commented by: Ray Holme (rholme) select count(*) from addresses ad |
Commented by: @hvlad Ray, does you try to validate database (using gfix -v -full) ? |
Commented by: Ray Holme (rholme) Sean, I sent an email response to your email - thought it was a person not a mailer - sorry my bad. while addr_id is used in multiple queries, each is actually distinct as written. And I mean - it is broken. See above post. |
Commented by: Ray Holme (rholme) Stripped minimal DB and a query that fails. Gzipped tarfile for container. |
Modified by: Ray Holme (rholme)Attachment: problem.tgz [ 13323 ] |
Commented by: Ray Holme (rholme) Vlad - just restored the stripped DB before running query so should be very clean; But for you, I ran gfix -v -full select count(*) from addresses ad Still yields 0 not 4 as it should. Sean, |
Commented by: Ray Holme (rholme) one more time we try |
Modified by: Ray Holme (rholme)Attachment: problem.tgz [ 13324 ] |
Commented by: @hvlad Ray, you mistake is that you not count for NULL's. Run this: select count(*) from addresses |
Commented by: Ray Holme (rholme) Vlad, yes I get a count there but it sure is surprising. I certainly do NOT understand why I need to exclude nulls here, but OK. But you can close it - I guess. The results are VERY interesting if not dismaying. SQL> select count(*) from addresses |
Commented by: Ray Holme (rholme) Vlad, Please explain why this null test needs to be added. Using math logic, including a null in the sub select (at most one per select - so in fact 2 - one from people, one from patients) If anything, NOT having a null in addresses should add 2 to the count making it 196, not reduce it from 194 to 0. This goes against everything I thought I knew about set theory (and hence SQL) Thanks for your prior answer and I hope you can explain this. Ray |
Commented by: @hvlad Ray, if you read docs (at link below) and still have questions - please, ask in support list: https://groups.yahoo.com/neo/groups/firebird-support/info |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Ray Holme (rholme)
Attachments:
problem.tgz
problem.tgz
This was originally a delete query, but select shows the problem well - I have a copy of both the query and a minimal backed up DB that I can provide.
The very first query returns erroneous results, the following queries prove the error;
Database: working.db, User: sysdba
SQL> select count(*) from addresses
CON> where addr_id not in (select addr_id from office_defaults)
CON> and addr_id not in (select addr_id from people)
CON> and addr_id not in (select addr_id from people_more_addrs)
CON> and addr_id not in (select addr_id from patients);
0
SQL> select count(*) from addresses where addr_id in (1596, 5341, 3331, 3534);
4
SQL> select count(*) from office_defaults where addr_id in (1596, 5341, 3331, 3534);
0
SQL> select count(*) from people where addr_id in (1596, 5341, 3331, 3534);
0
SQL> select count(*) from people_more_addrs where addr_id in (1596, 5341, 3331, 3534)
0
SQL> select count(*) from patients where addr_id in (1596, 5341, 3331, 3534);
0
The text was updated successfully, but these errors were encountered: