Hi Robert,



Yes a 'UNION ALL' does provide a work-around to the problem. I've just
spoken to the programmers in question and they are happy to alter the
view definition.



However, I disagree that the query itself should be disallowed. It seems
perfectly innocuous to me.



Marty



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Robert Kibble
Sent: 24 April 2009 10:41
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] The sequential view of a union...is this
amarriagemade in hell?



Martin,



I think this query should actually be disallowed - it's a bug, similar
at least to one we know about. The query works fine if you do "union
all" instead of "union", but the union in the view adds an effective
"distinct" to the query, and using a sequence with distinctness values
causes a problem and is usually disallowed.



Would it be an acceptable workaround to use union all in this case?



Robert



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 24 April 2009 10:30
To: Ingres and related product discussion forum
Subject: [Info-Ingres] The sequential view of a union...is this a
marriagemade in hell?



Hi Everyone,



Me again, sorry to be a pest...I'm having a great week at the Office.



Try this on Ingres2006R2 or better...



1. createdb bowtest

2. sql bowtest < setup.sql

3. sql bowtest << SQL_END

\r

-- Lets see whats in the view, there should be 11 rows.

select a, b from fleabane_view;

\p\g

-- now populate the table, this should simply add the sequence counter
to

-- each row selected from the view.

insert into test_this(a,b)

select a, b from fleabane_view;

\p\g

-- Trouble is that it doesn't.

-- It seems to have repeated one of the qualifying rows from
fleabane_view

-- ad nauseum rather than use each row in the view.

-- It also got the wrong number of rows. (10 not 11)

select * from test_this;

\p\g

\q

SQL_END



Martin Bowes

Hi Karl,

God you make it sound so easy!

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: 24 April 2009 14:32
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] The sequential view of a union...is this
amarriagemade in hell?

I think something is very wrong here.

The OP150 output shows the tsort materializer CX trying to coerce the
sequence value;
the sequence should be generated and applied AFTER the sort, not
BEFORE it.

Also, I'm showing the sequence row as 19 with a length of 8, but the
sort
CX tries to materialize into row 19 and uses a length of 42. That will
overrun the row and corrupt memory.

I'm guessing that the default values for the insert are magically
stuffed into
the select statement, which is sort-of-OK, but not really. The
defaults need
to be stuffed in some manner that allows them to be compiled into the
PUT action's
ahd_current, NOT the select's get actions or the top level tsort.

I agree with Marty that the query ought to be entirely valid.

Karl


On Apr 24, 2009, at 5:41 AM, Robert Kibble wrote:


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