[comp.databases] Informix bug - more information

ahl@technix.oz.au (Tony Landells) (03/05/91)

A week or so ago, I posted about a problem I was having with a select
statement under Informix.  It appears now that the problem has
something to do with the index I'm using--the following program
exhibits the bug.  It's extremely bad code, but I wanted the shortest
possible program I could get, so I just kept hacking bits out until I
figured there wasn't much else that could go...

If anyone could explain to me WHY the order of the subparts of an
index should be important, I would appreciate it...

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

PS - This is with Informix RDS and SQL from the .03 release.

main
	create database test

	create table dept (code char(3), deptKey serial)
	insert into table dept values ("d1", 0)

	create table person (code char(3), deptKey integer)
	insert into table person values ("x", 1)	# fudge--deptKey = 1

	call getFirst("without index")		# this will work okay

	create index person on person(deptKey, code)
	call getFirst("with index(deptKey, code)")	# still okay
	drop index person

	create index person on person(code, deptKey)
	call getFirst("with index(code, deptKey)")	# this will fail

	close database
	drop database test
end main

function getFirst(msg)
	define msg char(200)

	# deptKey is fudged because we know it's one
	# in reality, it's selected from dept
	select * from person
		where @code =
		(select min(code) from person
			where @deptKey = 1)
		and @deptKey = 1

	case (status)
	when notfound
		display "No person found in dept 1 searching ", msg clipped
	when 0
		display "Search succeeded ", msg clipped
	otherwise
		display "Searching ", msg clipped, " caused error ", status
	end case
end function