[Info-Ingres] Ambiguous replace?

[Paul Mason]

On 11 Apr 2007 23:38:01 -0700, simonl at unwired.com.au <simonl at unwired.com.au>
wrote:
>
> On Apr 11, 7:30 pm, "Tim Ellis" <tell... at csc.com> wrote:
> > On Apr 6, 5:33 pm, iinu... at yahoo.com wrote:
> >
> >
> >
> > > On Apr 6, 6:17 am, "Tim Ellis" <tell... at csc.com> wrote:
> >
> > > > If I run the following bit of SQL
> > > >     Select * from View_A a, View_B b
> > > >     where a.Column_2 = b.Column_2
> > > >          and a.column_3 = b.column_3
> > > >          and a.column_2 like 'Q%'
> > > >          and b.column_2 like 'Q%'
> >
> > > > then I get back two rows - They have the same value for column_1 but
> > > > different values for
> > > > column_3 and  View_B.Column_1
> > > > However if I run
> >
> > > >     Update VIew_A a from view B b
> > > >     Set Column_1 = b.Column_1
> > > >     where a.Column_2 = b.Column_2
> > > >          and a.column_3 = b.column_3
> > > >          and a.column_2 like 'Q%'
> > > >          and b.column_2 like 'Q%'
> >
> > > > I get:-
> > > > E_US125D Ambiguous replace: you have specified several
> > > > replacements for the same row.
> >
> > > > Surely if I had several replacements for the same row I would see
> this
> > > > in the original select.
> >
> > > This is just straightforward SQL.
> >
> > > According to your statements, the original select gives you two rows
> > > with same View_A.Column_1, and differing Column_3 and
> > > View_B.Column_1.  So, when you go ahead and try to do an Update table
> > > A, Ingres finds that there are two matching rows and does not know
> > > which one to use as the new value to update in View_A.
> >
> > Either I didn't clearly explain, or you didn't follow - The values of
> > Column_3 are distinct
> >
> > Table_A
> > Column_1   Column_2   Column_3
> > "   "             "Q1234"     13590
> > "   "             "Q1234"     66000
> >
> > Table_B
> > Column_1   Column_2   Column_3
> > "69804"      "Q1234"     13590
> > "86592"      "Q1234"     66000
> >
> >     Select * from View_A a, View_B b
> >     where a.Column_2 = b.Column_2
> >          and a.column_3 = b.column_3
> >          and a.column_2 like 'Q%'
> >          and b.column_2 like 'Q%'
> >
> > gives 2 rows
> >
> >     Update VIew_A a from view B b
> >     Set Column_1 = b.Column_1
> >     where a.Column_2 = b.Column_2
> >          and a.column_3 = b.column_3
> >          and a.column_2 like 'Q%'
> >          and b.column_2 like 'Q%'
> >
> > E_US125D Ambiguous replace: you have specified several replacements
> > for  the same row.- Hide quoted text -
> >
> > - Show quoted text -
>
> You can find the errant rows with:
> select a.column_2, a.column_3, count(*)
> from view_a a, view_b b
> where a.Column_2 = b.Column_2
> and a.column_3 = b.column_3
> and a.column_2 like 'Q%'
> and b.column_2 like 'Q%'
> group by a.column_2, a.column_3
> having count(*) > 1
>
> If it is truly working as you describe then that would be a bug!  Log
> a support issue.  They will want a test case (i.e. copydb output &
> data) to support your accusation.  Or you could try and work around it
> by removing the views from the SQL.
>

I meant to reply to this yesterday. I ran the Tim's SQL on Ingres 2006 and
it updated two rows correctly.I agree that failure to do so is a bug. From
memory there are a couple of bugs where an ambiguous replace is wrongly
reported. If not then it's a fairly simple testcase, provided we get the
same version and patch that Tim is running.

cheers
-- 
Paul Mason
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20070412/fdb4d607/attachment.html