Wrong result set by 'except all' / 'distinct'

[Former Member]

Hello all,

I get a wrong result set when executing a query of the form:

select col1 from test_except
except all
select distinct col1 from test_except

The table contains the value 'M' twice, what means that this value 'M' is present in two rows. So the result set should contain 'M' once because of 'except all' and 'distinct'. Usually this works fine, but not in one of my tables.

Unfortunately I could not reproduce that behavior by a new example.

create table test_except (col1 char(8))

insert into test_except values('M')
insert into test_except values('M')

select col1 from test_except
except all
select distinct col1 from test_except

works perfectly.

I tried to create another example that fits the table definition we use:

create domain tablename char(36)

CREATE TABLE test_except2 (TABLENAME TABLENAME key, INSERT_USERID
 fixed(3), INSERT_TIME TIMESTAMP, UPDATE_USERID fixed(3), UPDATE_TIME
 TIMESTAMP, SHORT_ID char(8))
 
insert into test_except2 (TABLENAME,SHORT_ID) values('T1','M')
insert into test_except2 (TABLENAME,SHORT_ID) values('T2','M')

create table test_except2_r1 (tablename tablename key, x2 char(2) key)

alter table TEST_EXCEPT2_R1 add foreign key f_test_except2 (tablename) references test_except2 (tablename)

insert into test_except2_r1 values ('T1', 'A')
insert into test_except2_r1 values ('T1', 'B')
insert into test_except2_r1 values ('T2', 'A')
insert into test_except2_r1 values ('T2', 'B')


select short_id from test_except2
except all
select distinct short_id from test_except2

"Unforunately" also this exaple works perfectly and can not demonstrate the thing which seems to be a bug.

As I remember it's not the first time that I have this problem. It seems to depend on the context but I can't tell on what exaclty it depends.

I found that when I add the condition 'where short_id is not null', the correct result set is shown. (The table we use contains the 'null'-value multiple times, too. But: trying to reproduce the bug/feature by adding null-values fails: After 'insert into test_except values(null)', the result set is still correct).

(We use MaxDB Version 7.6.03.15)

Thanks for any comment.

Gabriel

Edited by: Gabriel Matter on Jul 24, 2008 12:39 AM

[Former Member]

Hi,

you did not tell us, in which case your result was wrong.

Did you receive too many or too few rows?

Did you check if your assumption that at that point in time exactly two records with the same short_id were in your table, was correct?

Depending on the work done in parallel and your isolation level in use, you may have seen more/less rows.

Does your example where the problem is not reproducable differs perhaps in context of indexes and your real select has some qualification in where indexes could be used?

Did you check the explain for the real select and compared it to the example-select?

BTW: it is hard for me to catch what you want to do with this select.

Would you like to explain why do you take n equal short_ids and just throw one out of this list, resulting in (n-1) equal short_ids for each different short_id?

No other column is in the select-list, no qualification known to us, nothing what could explain what this select is for.

Perhaps a different select would do much better?

Elke

[Former Member]

Hello Elke

Thank you & sorry for my late reply.

Regarding this matter I was still not able to reproduce the behavior. I can imagine that the difference in context (indexes as an example) between my productive context and the example is the reason why that wrong behavior does not occur in my example but did in the productive context.

> you did not tell us, in which case your result was wrong.

> Did you receive too many or too few rows?

OK, the following was the case:

"select ... from ... except all select distinct ... from ..." should result in one row containing the value 'M' once. The effective result was, that NO ROW was returned.

> Did you check if your assumption that at that point in time exactly two records with the same short_id > were in your table, was correct?

> Depending on the work done in parallel and your isolation level in use, you may have seen more/less rows.

I'm sure that my assumption was correct, yes. This wrong behavior could be reproduced by a single select statement, so no work in parallel or isolation level was concerned.

> BTW: it is hard for me to catch what you want to do with this select.

> Would you like to explain why do you take n equal short_ids and just

> throw one out of this list, resulting in (n-1) equal short_ids for each different short_id?

> No other column is in the select-list, no qualification known to us, nothing what

> could explain what this select is for.

> Perhaps a different select would do much better?

This select statement was used to determine if a value ('m') was contained several times in a column or only once. I did often make use of 'select ... from ... except all select distinct ... from ...' to do so and usually this works perfectly.

I'll now mark this question as answered because I'm sure it will not be possible to create a reproducible example. It seems to be a bug that occurs only in very specific circumstances, probably depending also on which way tables were created and modified.

Thank you and best regards,

Gabriel