Hi All,



A programmer ... who will not be named in order to protect the
guilty...has just shown me the following error:

E_OP0399 consistency check

- full join does not create 2 variable sets



What the hell does that mean?



Martin Bowes

On Apr 8, 2009, at 8:28 AM, Martin Bowes wrote:

It means there's a bug.

I just barely sort of understand it, but full join builds bit maps that
separate the two sides of the join. The message is saying that the end
result isn't what was expected, but it's an internal problem, not a
query
problem.

I assume that the programmer (ahem) actually did write a full join?

Karl

Hi Karl,

Yes a full join between two views. The programmer just rewrote it using
session temp tables and the query now works.

I've just found the most interesting note on tech support. It reads:
E_OP0399 CONSISTENCY CHECK - USING FULL OUTER JOINS IN VIEWS

Summary:
E_OP0399 CONSISTENCY CHECK - USING FULL OUTER JOINS IN VIEWS

Problem:
OI 1.2/01 (rmx.us5/00) - p5207, client on SINIX-N 5.42 c3002 A select on
views based on views..... using FULL outer joins fails with: E_OP0399
consistency check - full join does not create 2 variable sets Problem
appears to be generic on II, 2.0/9712 and 1.2. Tested and seen on So
alris and NT CLOSED AS SUPPORT TERMINATED FOR THIS RELEASE

I'm trying to create a simple test case at the moment.

We are running:
II 9.1.1 (a64.lnx/103)NPTL + 13140

Marty
-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Karl
& Betty Schendel
Sent: 08 April 2009 13:41
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Weird error message


On Apr 8, 2009, at 8:28 AM, Martin Bowes wrote:

It means there's a bug.

I just barely sort of understand it, but full join builds bit maps that
separate the two sides of the join. The message is saying that the end
result isn't what was expected, but it's an internal problem, not a
query
problem.

I assume that the programmer (ahem) actually did write a full join?

Karl


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Karl & Betty Schendel wrote:
At the risk of going completely OT, I can't help but wonder: Why at age 35
is SQL still a cranky adolescent? Who would have thought in 1974 that
whole careers would be based on re-writing queries such that the
"optimizer" would understand them?

Do you have any insight into this? Is query optimization known to be in
the class of insoluable problems, or have the vendors just decided there's
no payoff in making it better? (It's certainly not an Ingres-specific
problem, industry-wide is more like it.)

The trends I see run in exactly the wrong direction: ORMs,
"post-relational" blather. As far as I can tell, it's not a subject of
academic investigation, or R&D, or the focus of any (let us call them)
RDBMS project, proprietary or free. Like so much software, it's "good
enough" and the great minds have gone elsewhere in search of new
frontiers.

I understand of course how hard it is to benchmark a database engine. But
I also have to believe that if one could establish a reputation for a
great query optimizer, one that *never* requires recasting a query -- or,
even, shudder to think, user-specified indices -- it would become the tool
of choice. Even in a market as dysfunctional as this one.

Just asking....

--jkl

James K. Lowden wrote:

I hear you. I have asked the same question here and elsewhere myself.

Interestingly though, I find there is a lot of programmers who actually
revel in having that kind of arcane knowledge, and resist being told
they can give up on it when they can. Superstitions always persist.

Query optimization is hugely complex to be sure. But in spite of SQL
setting out to be a non-procedural language (that is, one
where the query execution plan needs to be generated from the result
specification), it seems that the "designers" of SQL did nothing to
facilitate optimization. There are just way too many ways to write
equivalent specifications that cannot be determined to be equivalent.

It's an SQL problem.

Oh it's much worse than that. There have been plausible claims that
taking an interest in relational theory has actually been a
career-limiting move in academia for more than a decade. Some of the
sneering from senior academics working in what they call "database
theory" is shockingly ill- informed (and indeed anti-intellectual). Go
over to comp.databases.theory and you will find the shell-shocked
survivors huddling for mutual support. (Well, actually, mostly it
seems like they are taking pot-shots at each other, but you can tell it
is for mutual support! :-)

You just go on believing that chum. To quote an amusing bank commercial
from the UK, "It doesn't work like that."

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.