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

isql query fails to return correct result [CORE6008] #6258

Closed
firebird-automations opened this issue Feb 21, 2019 · 18 comments
Closed

isql query fails to return correct result [CORE6008] #6258

firebird-automations opened this issue Feb 21, 2019 · 18 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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
where
adds.addr_id not in (select off.addr_id from office_defaults off)
and adds.addr_id not in (select ppl.addr_id from people ppl)
and adds.addr_id not in (select ppl_adds.addr_id from people_more_addrs ppl_adds)
and adds.addr_id not in (select Pnt.addr_id from patients Pnt);

@firebird-automations
Copy link
Collaborator Author

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
a) mine is correct actually (sorry, they are NOT ambiguous as written)
b) yours gets the same results (as expected).

Sorry, Ray

@firebird-automations
Copy link
Collaborator Author

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"?

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

select count(*) from addresses ad
where ad.addr_id not in (select pe.addr_id from people pe);
0 - WRONG, WRONG - s.b. 4 --- see below counts
select count(*) from addresses;
4625
select count(*) from addresses where addr_id in (1596, 5341, 3331, 3534);
4
select count(*) from people where addr_id in (1596, 5341, 3331, 3534);
0 and again to make sure not the in clause
select count(*) from people
where addr_id = 1596 or addr_id = 5341 or addr_id = 3331 or addr_id = 3534;
0

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Ray,

does you try to validate database (using gfix -v -full) ?

@firebird-automations
Copy link
Collaborator Author

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.
Did not see how to attach here. See it now, will do.

while addr_id is used in multiple queries, each is actually distinct as written.
IIf the queries were correlated, you need the qualifiers to get at the outside data item.

And I mean - it is broken. See above post.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

Stripped minimal DB and a query that fails. Gzipped tarfile for container.

@firebird-automations
Copy link
Collaborator Author

Modified by: Ray Holme (rholme)

Attachment: problem.tgz [ 13323 ]

@firebird-automations
Copy link
Collaborator Author

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
where ad.addr_id not in (select pe.addr_id from people pe);

Still yields 0 not 4 as it should.

Sean,
I added an attachment but I don't see it so I will try again.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

one more time we try

@firebird-automations
Copy link
Collaborator Author

Modified by: Ray Holme (rholme)

Attachment: problem.tgz [ 13324 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Ray,

you mistake is that you not count for NULL's.

Run this:

select count(*) from addresses
where addr_id not in (select addr_id from office_defaults where addr_id is not null)
and addr_id not in (select addr_id from people where addr_id is not null)
and addr_id not in (select addr_id from people_more_addrs where addr_id is not null)
and addr_id not in (select addr_id from patients where addr_id is not null);

@firebird-automations
Copy link
Collaborator Author

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.
For addresses, it is the primary key; and a required field for two tables; 99% expected for a third.
I would have thought nulls would be automatically excluded from a set in an IN clause.
and that they certainly would not skew the results like this - making the result 0 blows my math background away.

But you can close it - I guess.

The results are VERY interesting if not dismaying.

SQL> select count(*) from addresses
CON> where addr_id not in (select addr_id from office_defaults where addr_id is not null)
CON> and addr_id not in (select addr_id from people where addr_id is not null)
CON> and addr_id not in (select addr_id from people_more_addrs where addr_id is not null)
CON> and addr_id not in (select addr_id from patients where addr_id is not null);
COUNT = 194
SQL> select count(*) from addresses where addr_id is null;
COUNT = 0
SQL> select count(*) from office_defaults where addr_id is null;
COUNT = 0
SQL> select count(*) from people where addr_id is null;
COUNT = 6
SQL> select count(*) from people_more_addrs where addr_id is null;
COUNT = 0
SQL> select count(*) from addresses ad
CON> where ad.addr_id not in (select pe.addr_id from people pe where pe.addr_id is not null);
COUNT = 326

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

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)
should not REDUCE the count as the main table (addresses) has NO null keys.

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

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

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