Hi All,



I've been shown some code which demonstrates how the usage of aggregate
functions on tables prepared by inline selects may produce E_OP03A6.



Interestingly in the example I've got, if the inline view is turned into
an explicit view and then that view used in the query the problem
persists. If the min(difference) is removed then the query is OK.



Do this...

sql bowtest < load_stuff.sql | \

egrep -e'\<E_[A-Z]{2}[0-9A-F]{4}' | grep -v -e'E_US0AC1'



sql bowtest << SQL_END

SELECT studyid,

MIN(difference)

FROM

(SELECT

a.studyid,

SUM(day(a.candidate_date)) difference

FROM all_dates a

INNER JOIN all_dates a2 ON a.studyid = a2.studyid

INNER JOIN extra_data ON a.studyid = extra_data.studyid

GROUP BY a.studyid, a.candidate_date) differences

GROUP BY studyid;

\p\g

\q

SQL_END

Found in source code
(...\ingres2006-9.2.0-134-gpl-src\src\common\hdr\hdr\eropf.msg)

E_OP03A6_OUTER_JOIN:SS50000_MISC_ERRORS
"consistency check
\n - internal processing error related to outer joins "
/*%
** Parameters:
** None.
**
** Explanation:
** An inconsistent internal system state was detected related
** to outer join processing
**
** System Status:
** The query will be terminated; ongoing transactions are not aborted.
**
** Recommendation:
** Please report this error to technical support.
*/

Hope this help,
--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/


2009/4/21 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Hi Jean-Piere,

** Recommendation:
** Please report this error to technical support.
*/



I've raised the issue with IngresCorp.



Marty

I'm not at work today but I'm pretty sure this is a known bug. I think I
dealt with a similar query recently.

Paul

Martin Bowes wrote: