4GL select failure
I'm getting a new failure from a select in a 4GL and SQL query with a
subquery. I'm running the RDS 4GL 4.11.UC1, SQL 4.11.UC1,
with ONLINE 5.00.UD5 on SCO UNIX.
The actual query that originally failed was across 2 large databases
(each ~100,000 rows). I've narrowed down the field a bit for simplicity,
by selecting a single row on some tables.
Here is the isql output using -e.
=================================
select * from livesecs
where symbol = "JOEY"
and creation_date < (today - 90);
security_id 471372
cusip
iss_id 48516
sec_type S
symbol JOEY
exch_code C
creation_date 07/01/1993
death_date
1 row(s) retrieved.
>>>>>>>> as expected, a single row is retrieved
-- this is a view
select symbol from q:allsyms where symbol = "JOEY";
symbol
No rows found.
>>>>>>>> as expected, there are no rows that match the symbol.
>>>>>>>> note that symbol is char(11) in all 3 tables here.
select count(*) from q:allsyms;
(count(*))
150331
1 row(s) retrieved.
select *
from livesecs
where symbol = "JOEY"
and creation_date < (today - 90)
and symbol not in (select symbol from q:allsyms);
security_id 471372
cusip
iss_id 48516
sec_type S
symbol JOEY
exch_code C
creation_date 07/01/1993
death_date
1 row(s) retrieved.
>>>>>>>> so far, so good.
-- this is a table
select symbol from q:alls where symbol = "JOEY";
symbol
No rows found.
>>>>>>>> again, no symbol match.
select count(*) from q:alls;
(count(*))
84196
1 row(s) retrieved.
select *
from livesecs
where symbol = "JOEY"
and creation_date < (today - 90)
and symbol not in (select symbol from q:alls);
No rows found.
>>>>>>>>
>>>>>>>> HERE IS THE PROBLEM. WHY IS NO ROW FOUND?
>>>>>>>> the columns in the view 'allsyms' are a strict subset
>>>>>>>> of another table, without a where clause.
>>>>>>>>
DBSCHEMA output follows for the tables used in the subqueries.
Joey Duhon
duhon@netcom.com
================
DBSCHEMA Schema Utility INFORMIX-SQL Version 5.00.UD5
Copyright (C) Informix Software, Inc., 1984-1991
Software Serial Number AAA#C379030
{ TABLE "johnw".alls row size = 48 number of columns = 11 index size = 42 }
create table "johnw".alls
(
symbol char(11),
exch_code char(1),
rep_exch char(1),
bid decimal(12,4),
bidex char(1),
ask decimal(12,4),
askex char(1),
last decimal(12,4),
lastex char(1),
data_date date,
load_time datetime year to minute
);
revoke all on "johnw".alls from "public";
create unique index "johnw".symdex on "johnw".alls (symbol,exch_code,data_date);
create index "johnw".ix202_10 on "johnw".alls (data_date);
DBSCHEMA Schema Utility INFORMIX-SQL Version 5.00.UD5
Copyright (C) Informix Software, Inc., 1984-1991
Software Serial Number AAA#C379030
create view "informix".allsyms (symbol,exch_code,rep_exch,bid,bidex,ask,askex,last,lastex,data_date,load_time) as
select x0.symbol ,x0.exch ,x0.exch ,x0.bid ,x0.bidex ,x0.ask
,x0.askex ,x0.last ,x0.lastex ,x0.data_date ,x0.load_time
from "informix".allp x0 ;
DBSCHEMA Schema Utility INFORMIX-SQL Version 5.00.UD5
Copyright (C) Informix Software, Inc., 1984-1991
Software Serial Number AAA#C379030
{ TABLE "informix".allp row size = 107 number of columns = 20 index size = 0 }
create table "informix".allp
(
symbol char(11),
exch char(1),
country char(2),
sectype char(1),
stat integer,
pricetype integer,
tick char(8),
last float,
lastex char(1),
open float,
high float,
low float,
yest float,
bid float,
bidex char(1),
ask float,
askex char(1),
volume decimal(10,0),
data_date date,
load_time datetime year to minute
);
revoke all on "informix".allp from "public";