Path: utzoo!censor!geac!torsqnt!lethe!yunexus!ists!helios.physics.utoronto.ca!news-server.csri.toronto.edu!cs.utexas.edu!uunet!munnari.oz.au!mel.dit.csiro.au!yarra!technix.oz.au!technix.oz.au!ahl
From: ahl@technix.oz.au (Tony Landells)
Newsgroups: comp.databases
Subject: informix bug
Message-ID: <AHL.91Feb27190822@saussure.technix.oz.au>
Date: 27 Feb 91 08:08:22 GMT
Sender: ahl@technix.oz.au (Tony Landells)
Distribution: comp
Organization: TechNIX Consulting Services, Melbourne, Australia
Lines: 72

I've tried the following with the Informix RDS 1.10.03K and Informix
SQL 2.10.03K, and both seem to be somewhat confused.

I have two tables:

# valid departments
# code is unique, but reference is through the departmentKey
# so that code changes need not be propogated
create table department
	(
	code		char(3),
	description	char(20),
	departmentKey	serial
	)

# staff members
# code is only unique within a department
# staffKey is always unique, and avoids having to propogate changes
create table staff
	(
	code		char(3),
	description	char(20),
	departmentKey	integer,
	staffKey	serial
	)

For one of my screens, I want to select the first person in the first
department (for which people have been entered).  To select the
department, therefore, I use:

select * into p_department.*
	from department
	where @code =
		(select min(code) from department
			where @departmentKey in
				(select departmentKey from staff)
		)

Then, to get the first person I use:

select * into p_staff
	from staff
	where @code =
		(select min(code) from staff
			where @departmentKey = p_department.departmentKey
		)
	and @departmentKey = p_department.departmentKey

For some reason, this won't work!  Even though the department has been
chosen to be one with staff in it, the second select returns status
100.  Apparently, the criterion at fault is the part after the "and";
changing it to something as banal as

	... and @departmentKey =
		(select departmentKey from department
			where @code = p_department.code)

is sufficient to make it work okay.  It doesn't matter which order I
have the two conditions; it is the departmentKey restriction which
causes the problem.  I even tried using constants (having determined
that the correct departmentKey was 1), still no luck.  Obviously, I
can't drop the restriction, or I might get back multiple rows (since
the code can be repeated in different departments).

Can anyone tell me what's going on here?  It's annoying that it
happens, but if I can predict when it will happen, I can at least
avoid it without spending vast amounts of time testing every single
query as I write it to ensure I won't get bitten during system
debugging, when it would be much harder to find.

Thanks,
Tony Landells <ahl@technix.oz.au>