ISTAR BUG?
Greetings Informix Gurus,
We have a SUN sparc10/40 | and an IBM RS6000
running SunOS 4.1.3_U1 | running AIX
with OnLine:v4.10.UG1 | with OnLine:v4.10.UE3
and SQL:v4.10.UE1 | and SQL:v4.10.UE3
and ISTAR 4.10.UG1 | and ISTAR 4.10.UE3
I can do most queries joining tables among databases across the two machines,
as they are both non-ANSI and both have transaction logging turned on.
The following query works when run on the machine the table is located in,
but fails when I run the query from the remote machine:
Local query: | Remote query
---------------- | ----------------
select seq_no, | select seq_no,
max(doc_no) max_doc_no | max(doc_no) max_doc_no
from sometable | from mydb@mymach2:sometable
where po_nbr = "940100011" | where po_nbr = "940100011"
group by seq_no | group by seq_no
into temp tempx | into temp tempx
with no log; | with no log; |
select st.po_nbr, | select st.po_nbr,
st.doc_type, | st.doc_type,
st.doc_name, | st.doc_name,
st.rp_seq, | st.rp_seq,
st.xmittal_nbr | st.xmittal_nbr
from sometable st, | from mydb@mymach2:sometable st,
tempx | tempx
where st.po_nbr = "940100011" | where st.po_nbr = "940100011"
and st.rp_seq = tempx.rp_seq | and st.rp_seq = tempx.rp_seq
and st.doc_id = tempx.max_doc_id | and st.doc_id = tempx.max_doc_id
order by 1,4; | order by 1,4; | #^
drop table tempx; | # 303: Expression mixes columns
| # with aggregates.
---- | ----OK: Returns 1 record for each | No Good: Dies!
seq_no | (BUT! There are _NO_ aggregates!)
| MAX is not an aggregate.
Also, if I rewrite the remote query as a correlated subquery,
(no temp table), it takes a long time, but it WORKS!!
REMOTE, correlated subquery:
---------------------------
select st.po_nbr,
st.doc_type,
st.doc_name,
st.rp_seq,
st.xmittal_nbr
from mydb@mymach2:sometable st
where st.rp_nbr = "940100011"
and st.doc_id = (
select max(doc_id)
from mydb@mach2:sometable st_a
where st_a.po_nbr = st.po_nbr
and st_a.rp_seq = st.rp_seq
)
order by 1,4;
Putting a MAX value from a remote machine in a temp table then selecting
that value in a join is confusing ISTAR??
Any known bugs that causes this, and any releases available where it is fixed?
--
Colin McGrath Internet: colin@scdipc0.ueci.com
Raytheon Engineers & Constructors Inc. UUCP: ..!uunet!trac2000!trac3000!cmm
30 S. 17th St Voice: 215-422-3449
Philadelphia, PA, 19101 FAX: 215-422-4095
<Standard disclaimers apply>