Informix, Boolean complement of a pair of OR clause

Jacob Salomo
#1 / 7

Boolean complement of a pair of OR clause

WOW! That sounds like an esoteric topic!

I have data in a temp table ready to be inserted into a main table.
However, the main table has two columns, each of which must remain
unique. (So why don't my users allow me to create uniqueness
constraints? That's another story. Grrr!) So before I do the
INSERT INTO main-table SELECT * FROM temp-table
I run a query to see if there are any rows in the temp table that, if
inserted to the main table, would violate the above conventions.

As it happens, the temp table has 422 rows. When I seek potential
violations, I get 20 rows. Therefore, if I seek non-violators, I should
get 402 rows. In fact, I should be able to just do:
INSERT INTO main-table
SELECT * FROM temp-table
WHERE no violations would occur.

Instead, I am getting 0 rows inserted. When I do a select count for
non-violators, I get 0.

Now, if you have held on this long, I can get specific. I have a small
example of what I want. It works in the small example, however.

create table t1
( number integer, -- Intended to be unique
name char(2) -- Intended to be unique
);
create table t2
( number integer,
name char(2)
);
insert into t1 values (1, "A");
insert into t1 values (2, "B");
insert into t1 values (3, "C");
insert into t1 values (4, "D");
insert into t1 values (5, "E");
insert into t1 values (6, "F");
insert into t1 values (7, "G");
insert into t1 values (8, "H");
insert into t1 values (9, "I");

insert into t2 values (11, "R");
insert into t2 values (12, "S");
insert into t2 values (13, "T");
insert into t2 values (14, "U");
insert into t2 values (15, "V");
insert into t2 values (16, "W");
insert into t2 values (17, "X");
insert into t2 values (18, "Y");
insert into t2 values (19, "Z");

insert into t2 values (2, "J"); -- Violates uniquness of number
insert into t2 values (10, "B"); -- Violates uniquness of name
insert into t2 values (3, "D"); -- Violates both uniqunesses

Suppose I want to merge the contents of table t2 into t1. Looking out
for potential violators, I run the following query:

select * from t2
where number in (select number from t1)
or name in (select name from t1)

Sure enough, I get the expected results, the ones I commented about as
violators.

number name

2 J
10 B
3 D

Now suppose I want to see the rows in t2 that I *can* insert into t1 in
compliance with the uniqueness "convention". Then my WHERE clause needs
to be the logical complement of the above WHERE clause. Recalling my
boolean algebra, not (A or B) is the same as (not A) and (not B). Hence,
the WHERE clause to seek out valid rows is:

select * from t2
where number not in (select number from t1)
and name not in (select name from t1)

And, sure enough, I get:
number name

11 R
12 S
13 T
14 U
15 V
16 W
17 X
18 Y
19 Z

So what's the problem? To my chagrin, when I do this pair of WHERE
clauses against a much larger table (a customer table with thousands of
rows) the "valid row" query returns 0 rows.

THIS IS NUTS! And it's turning me into squirrel bait myself!

Does someone have a large table thaey can test this kind of logic
against?

Thanks for at least *reading* this far! I have been on the phone with
Informix tech support and, since they see the small example working on
their end (as well as in my small example) I'm getting little sympathy.
--
-- Jake (In pursuit of undomesticated semi-aquatic avians)
+---------------------------------------------------------------+
|Insofar as manifestations of functional deficiencies are agreed|
|by any and all concerned parties to be imperceivable, and are |
|so stipulated, it is incumbent upon said heretofore mentioned |
|parties to exercise the deferment of otherwise pertinent |
|maintenance procedures. |
+--------------------------------------- (www.hardyharhar.com) -+

Sat, 18 Mar 2000 03:00:00 GMT

Bryan Tonn
#2 / 7

Boolean complement of a pair of OR clause

Quote:

>WOW! That sounds like an esoteric topic!
>select * from t2
> where number not in (select number from t1)
> and name not in (select name from t1)
>So what's the problem? To my chagrin, when I do this pair of WHERE
>clauses against a much larger table (a customer table with thousands of
>rows) the "valid row" query returns 0 rows.

Without testing, I can't check it, but it sounds like the sort of wierdo
result you get when nulls stick their grubby little hands into the pot.

I don't know. Does 'number not in (<select which has some nulls>)' return
true or false. I'll have to run one when I get a chance.

Bryan Tonnet

--
Bryan Tonnet

Sun, 19 Mar 2000 03:00:00 GMT

Mark Fishe
#3 / 7

Boolean complement of a pair of OR clause

Quote:

> So what's the problem? To my chagrin, when I do this pair of WHERE
> clauses against a much larger table (a customer table with thousands of
> rows) the "valid row" query returns 0 rows.

> THIS IS NUTS! And it's turning me into squirrel bait myself!

As Bryan says, this is a nulls problem.
Try the following:

insert into t1 values (11, NULL);
insert into t1 values (NULL, "S");

-- returns 0 rows as you found out.
select * from t2
where number not in (select number from t1)
and name not in (select name from t1);

-- returns result set you want
select * from t2
where ( number not in (select number from t1 where number is not null ))
and ( name not in (select name from t1 where name is not null ))

i.e. number name
13 T
14 U
15 V
16 W
17 X
18 Y
19 Z

Mark Fisher
Britannic Assurance

--
------------------------------------------
The views expressed here are mine and not
those of my employer, Britannic Assurance
------------------------------------------

Sun, 19 Mar 2000 03:00:00 GMT

Art S. Kage
#4 / 7

Boolean complement of a pair of OR clause

Quote:

> WOW! That sounds like an esoteric topic!

> I have data in a temp table ready to be inserted into a main table.
> However, the main table has two columns, each of which must remain
> unique. (So why don't my users allow me to create uniqueness
[SNIP]

> Does someone have a large table thaey can test this kind of logic
> against?

I'll look into it. In the meantime remember Kagel's First Law of SQL?
Try deleting from t2 all duplicates in two separate operations then you
can insert the survivors:

delete from t2 where number in (select number from t1);
delete from t2 where name in (select name from t1);
insert into t1 select * from t2;

Art S. Kagel

Kagel's First Law of SQL:
There are at least three ways to do ANYTHING in SQL.

First Correllary to Kagel's First Law of SQL:
If you have not found all three you may not be doing it the
best way.

Sun, 19 Mar 2000 03:00:00 GMT

Jacob Salomo
#5 / 7

Boolean complement of a pair of OR clause

... SNIP ...

Quote:

> Without testing, I can't check it, but it sounds like the sort of
> wierdo result you get when nulls stick their grubby little hands into
> the pot.

> I don't know. Does 'number not in (<select which has some nulls>)'
> return true or false. I'll have to run one when I get a chance.

Kazowwee!!! Kawabunga! (Does this mean anything?)

I added a row to t1 with a null column and ran the same "valid row"
query again. As with my live problem, it suddenly came up with 0 rows.
When I added in the "where name/number is not null" clauses to the
subqueries, it got me the rows I needed.

Thanks, Bryan. That was it! I have just implemented that in the live
query and it got the results I wanted.

Of course, I could not do the insert and select in the same statement
because SQL correctly will not let me insert into a table I am
subquerying from. A select into temp followed by insert-select from the
temp took care of that.
--
-- Jake (In pursuit of undomesticated semi-aquatic avians)
+---------------------------------------------------------------+
|Insofar as manifestations of functional deficiencies are agreed|
|by any and all concerned parties to be imperceivable, and are |
|so stipulated, it is incumbent upon said heretofore mentioned |
|parties to exercise the deferment of otherwise pertinent |
|maintenance procedures. |
+--------------------------------------- (www.hardyharhar.com) -+

Sun, 19 Mar 2000 03:00:00 GMT

Scott Bla
#6 / 7

Boolean complement of a pair of OR clause

This was done real quick, but I think I see your problem. I inserted
the following into t1 and t2:

insert into t1 values (25, null);
insert into t1 values (null, "#");

insert into t2 values (53, null);
insert into t2 values (null, "*");

Afterwards I got no rows from the second select statement.

Bitten by the null again :-).

try instead:

select * from t2
where number not in (select number from t1 where number is not null)
and name not in (select name from t1 where name is not null)

Or constrain t1 to not null.

Quote:

} -----Original Message-----

} Sent: Tuesday, September 30, 1997 6:32 PM

} Subject: Boolean complement of a pair of OR clause
}
} WOW! That sounds like an esoteric topic!
}
} I have data in a temp table ready to be inserted into a main table.
} However, the main table has two columns, each of which must remain
} unique. (So why don't my users allow me to create uniqueness
} constraints? That's another story. Grrr!) So before I do the
} INSERT INTO main-table SELECT * FROM temp-table
} I run a query to see if there are any rows in the temp table that, if
} inserted to the main table, would violate the above conventions.
}
} As it happens, the temp table has 422 rows. When I seek potential
} violations, I get 20 rows. Therefore, if I seek non-violators, I
} should
} get 402 rows. In fact, I should be able to just do:
} INSERT INTO main-table
} SELECT * FROM temp-table
} WHERE no violations would occur.
}
} Instead, I am getting 0 rows inserted. When I do a select count for
} non-violators, I get 0.
}
} Now, if you have held on this long, I can get specific. I have a
} small
} example of what I want. It works in the small example, however.
}
} create table t1
} ( number integer, -- Intended to be unique
} name char(2) -- Intended to be unique
} );
} create table t2
} ( number integer,
} name char(2)
} );
} insert into t1 values (1, "A");
} insert into t1 values (2, "B");
} insert into t1 values (3, "C");
} insert into t1 values (4, "D");
} insert into t1 values (5, "E");
} insert into t1 values (6, "F");
} insert into t1 values (7, "G");
} insert into t1 values (8, "H");
} insert into t1 values (9, "I");
}
} insert into t2 values (11, "R");
} insert into t2 values (12, "S");
} insert into t2 values (13, "T");
} insert into t2 values (14, "U");
} insert into t2 values (15, "V");
} insert into t2 values (16, "W");
} insert into t2 values (17, "X");
} insert into t2 values (18, "Y");
} insert into t2 values (19, "Z");
}
} insert into t2 values (2, "J"); -- Violates uniquness of
} number
} insert into t2 values (10, "B"); -- Violates uniquness of name
} insert into t2 values (3, "D"); -- Violates both uniqunesses
}
} Suppose I want to merge the contents of table t2 into t1. Looking out
} for potential violators, I run the following query:
}
} select * from t2
} where number in (select number from t1)
} or name in (select name from t1)
}
} Sure enough, I get the expected results, the ones I commented about as
} violators.
}
} number name
}
} 2 J
} 10 B
} 3 D
}
} Now suppose I want to see the rows in t2 that I *can* insert into t1
} in
} compliance with the uniqueness "convention". Then my WHERE clause
} needs
} to be the logical complement of the above WHERE clause. Recalling my
} boolean algebra, not (A or B) is the same as (not A) and (not B).
} Hence,
} the WHERE clause to seek out valid rows is:
}
} And, sure enough, I get:
} number name
}
} 11 R
} 12 S
} 13 T
} 14 U
} 15 V
} 16 W
} 17 X
} 18 Y
} 19 Z
}
} So what's the problem? To my chagrin, when I do this pair of WHERE
} clauses against a much larger table (a customer table with thousands
} of
} rows) the "valid row" query returns 0 rows.
}
} THIS IS NUTS! And it's turning me into squirrel bait myself!
}
} Does someone have a large table thaey can test this kind of logic
} against?
}
} Thanks for at least *reading* this far! I have been on the phone with
} Informix tech support and, since they see the small example working on
} their end (as well as in my small example) I'm getting little
} sympathy.
} --
} -- Jake (In pursuit of undomesticated semi-aquatic avians)
} +---------------------------------------------------------------+
} |Insofar as manifestations of functional deficiencies are agreed|
} |by any and all concerned parties to be imperceivable, and are |
} |so stipulated, it is incumbent upon said heretofore mentioned |
} |parties to exercise the deferment of otherwise pertinent |
} |maintenance procedures. |
} +--------------------------------------- (www.hardyharhar.com) -+

Sun, 19 Mar 2000 03:00:00 GMT

Peter Lancashir
#7 / 7

Boolean complement of a pair of OR clause

Quote:

> WOW! That sounds like an esoteric topic!

> I have data in a temp table ready to be inserted into a main table.
> However, the main table has two columns, each of which must remain
> unique. (So why don't my users allow me to create uniqueness
> constraints? That's another story. Grrr!) So before I do the
> INSERT INTO main-table SELECT * FROM temp-table
> I run a query to see if there are any rows in the temp table that, if
> inserted to the main table, would violate the above conventions.

..SNIP..

Although I read from other contributors that this is a NULL problem, the
version 7 SET VIOLATIONS statement is wonderfully useful in sorting out
this sort of problem. You can get it to ignore constraint violations so
only the unique rows are loaded.

Peter
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258, Fax: +44-1635-562281

My Internet plumbing does not allow me to mail and post news together.
Sorry.
All opinions are my own and not those of Bayer plc.

Fri, 24 Mar 2000 03:00:00 GMT