Why would a query work on one machine but not another?
A user running IDS 11.50 on two Linux boxes found the same duplicate-row DELETE (DELETE ... WHERE rowid IN (SELECT ... FROM same table twice)) worked on one machine but returned error -360 "Cannot modify table or view used in subquery" on the other. After others ruled out schema/data differences, Jonathan Leffler suggested checking the exact build via DBINFO('version','full'); production was 11.50.UC1E (fails) and development 11.50.UC2E (works). So the difference was a fix/behaviour change between the two fixpacks, documented in the xC2 release notes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have an install of 11.5 in production and one in development. The
installs should be the same, except one machine is fedora and the
other is debian. On one box I my query works on the other I get a
-360 error "Cannot modify table or view used in subquery." I am
deleting duplicate rows with:
DELETE FROM table
WHERE rowid IN(
SELECT fs1.rowid
FROM table fs1, table fs2
WHERE fs1.serial_num = fs2.serial_num
AND fs1.rowid > fs2.rowid)
I know the other tricks to do the job, but I just am curious why this
works on one machine and fail on the other?
I could imagine that the query would succeed if the sub query matched no
rows - do both systems have the same data in the tables?
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of JaxenT
Sent: Thursday, 20 November 2008 1:13 p.m.
To: informix-list@iiug.org
Subject: Why would a query work on one machine but not another?
I have an install of 11.5 in production and one in development. The
installs should be the same, except one machine is fedora and the other
is debian. On one box I my query works on the other I get a -360 error
"Cannot modify table or view used in subquery." I am deleting duplicate
rows with:
DELETE FROM table
WHERE rowid IN(
SELECT fs1.rowid
FROM table fs1, table fs2
WHERE fs1.serial_num = fs2.serial_num
AND fs1.rowid > fs2.rowid)
I know the other tricks to do the job, but I just am curious why this
works on one machine and fail on the other?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
DISCLAIMER:
This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
same schema? same data?
J.
2008/11/19 JaxenT <jaxent@gmail.com>
> I have an install of 11.5 in production and one in development. The
> installs should be the same, except one machine is fedora and the
> other is debian. On one box I my query works on the other I get a
> -360 error "Cannot modify table or view used in subquery." I am
> deleting duplicate rows with:
>
> DELETE FROM table
> WHERE rowid IN(
> SELECT fs1.rowid
> FROM table fs1, table fs2
> WHERE fs1.serial_num = fs2.serial_num
> AND fs1.rowid > fs2.rowid)>
>
> I know the other tricks to do the job, but I just am curious why this
> works on one machine and fail on the other?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Freshly imported, exactly the same. There are ~2300 rows returned from the subquery. Glad to see that others find this strange too. On Nov 20, 7:43 am, "Jean Sagi" <jeansagi....@gmail.com> wrote: > same schema? same data? > J. >
JaxenT wrote:
> I have an install of 11.5 in production and one in development. The
> installs should be the same, except one machine is fedora and the
> other is debian. On one box I my query works on the other I get a
> -360 error "Cannot modify table or view used in subquery." I am
> deleting duplicate rows with:
>
> DELETE FROM table
> WHERE rowid IN(
> SELECT fs1.rowid
> FROM table fs1, table fs2
> WHERE fs1.serial_num = fs2.serial_num
> AND fs1.rowid > fs2.rowid)>
>
> I know the other tricks to do the job, but I just am curious why this
> works on one machine and fail on the other?
Interesting ...
So, same schema and data makes one wonder.
dbschema -ss of the two tables from the Prod and the Dev environment.
Are you using PDQ? Multiple temporary dbspaces? Fragmentation?
What about the sqexplain??
rowids are also a bit of a "vague area", and not really sure for what purpose the above query would be serving as rowids are not
really involved in a relationship model.
On Wed, Nov 19, 2008 at 4:12 PM, JaxenT <jaxent@gmail.com> wrote:
> I have an install of 11.5 in production and one in development. The
> installs should be the same, except one machine is fedora and the
> other is debian. On one box I my query works on the other I get a
> -360 error "Cannot modify table or view used in subquery." I am
> deleting duplicate rows with:
>
> DELETE FROM table
> WHERE rowid IN(
> SELECT fs1.rowid
> FROM table fs1, table fs2
> WHERE fs1.serial_num = fs2.serial_num
> AND fs1.rowid > fs2.rowid)>
>
> I know the other tricks to do the job, but I just am curious why this
> works on one machine and fail on the other?
I suspect that you have different sub-versions of IDS 11.50 on the two
machines. Probably, one is running 11.50.xC1 and the other is running
xC2 or xC3, or something similar like that.
If they are the same version - down to the last letter or digit - then
I don't have a good explanation.
SELECT DBINFO('version','full') FROM "informix".systables WHERE tabid = 1;
Run that on both systems - compare the output.
[Resend to list too.]
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
JaxenT wrote: > Freshly imported, exactly the same. There are ~2300 rows returned from > the subquery. Glad to see that others find this strange too. > > On Nov 20, 7:43 am, "Jean Sagi" <jeansagi....@gmail.com> wrote: >> same schema? same data? >> J. >> I'm struggling to believe it. How about demonstrating with a count and an inner join that this is the case? That there are the same number of matching rows in both. Please post the result. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)
You may be on to something.
IBM Informix Dynamic Server Version 11.50.UC1E - production - throws
the error
IBM Informix Dynamic Server Version 11.50.UC2E - development - query
works
Pretty major difference for is dot release! What are the rules for a
360 error. The error description implies that you can never use the
table in the subquery, but I have many queries that I use it in, I
just don't have it in the from clause of the subquery it is only in
the from of the main query. Is seeing the table in the from clause
the trigger?
On Nov 20, 1:23 pm, "Jonathan Leffler" <jleffler.i...@gmail.com>
wrote:
> > DELETE FROM table
> > WHERE rowid IN(
> > SELECT fs1.rowid
> > FROM table fs1, table fs2
> > WHERE fs1.serial_num = fs2.serial_num
> > AND fs1.rowid > fs2.rowid)>
> > I know the other tricks to do the job, but I just am curious why this
> > works on one machine and fail on the other?
>
> I suspect that you have different sub-versions of IDS 11.50 on the two
> machines. Probably, one is running 11.50.xC1 and the other is running
> xC2 or xC3, or something similar like that.
>
> If they are the same version - down to the last letter or digit - then
> I don't have a good explanation.
>
> SELECT DBINFO('version','full') FROM "informix".systables WHERE tabid = 1;
>
> Run that on both systems - compare the output.
>
> [Resend to list too.]
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informix v2008.0513 --http://dbi.perl.org/
> "Blessed are we who can laugh at ourselves, for we shall never cease
> to be amused."
> NB: Please do not use this email for correspondence.
> I don't necessarily read it every week, even.
On Nov 20, 1:24 pm, DA Morgan <damor...@psoug.org> wrote: > JaxenT wrote: > > Freshly imported, exactly the same. There are ~2300 rows returned from > > the subquery. Glad to see that others find this strange too. > > > On Nov 20, 7:43 am, "Jean Sagi" <jeansagi....@gmail.com> wrote: > >> same schema? same data? > >> J. > > I'm struggling to believe it. How about demonstrating with a count and > an inner join that this is the case? That there are the same number of > matching rows in both. Please post the result. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu (replace x with u to respond) Sorry, I already cleared the rows using a temp table.
----- Original Message ----- From: "JaxenT" <jaxent@gmail.com> Newsgroups: comp.databases.informix Sent: Thursday, November 20, 2008 8:44 PM Subject: Re: Why would a query work on one machine but not another? You may be on to something. IBM Informix Dynamic Server Version 11.50.UC1E - production - throws the error IBM Informix Dynamic Server Version 11.50.UC2E - development - query works >> Pretty major difference for is dot release! Well, these "dot" releases are typically spaced out by 3-6 months, so the differences between the two could be quite major, especially just after the first release, which is the first one to be experienced by the wider community, and therefore presumably attacts the most bug reports ...
On Thu, Nov 20, 2008 at 12:44 PM, JaxenT <jaxent@gmail.com> wrote:
> You may be on to something.
>
> IBM Informix Dynamic Server Version 11.50.UC1E - production - throws
> the error
>
> IBM Informix Dynamic Server Version 11.50.UC2E - development - query
> works
>
> Pretty major difference for is dot release! What are the rules for a
> 360 error. The error description implies that you can never use the
> table in the subquery, but I have many queries that I use it in, I
> just don't have it in the from clause of the subquery it is only in
> the from of the main query. Is seeing the table in the from clause
> the trigger?
Try looking in the release notes...forx xC2:
http://publibfp.boulder.ibm.com/epubs/html/i1190811.html#wq23
> On Nov 20, 1:23 pm, "Jonathan Leffler" <jleffler.i...@gmail.com>
> wrote:
>
>> > DELETE FROM table
>> > WHERE rowid IN(
>> > SELECT fs1.rowid
>> > FROM table fs1, table fs2
>> > WHERE fs1.serial_num = fs2.serial_num
>> > AND fs1.rowid > fs2.rowid)>>
>> > I know the other tricks to do the job, but I just am curious why this
>> > works on one machine and fail on the other?
>>
>> I suspect that you have different sub-versions of IDS 11.50 on the two
>> machines. Probably, one is running 11.50.xC1 and the other is running
>> xC2 or xC3, or something similar like that.
>>
>> If they are the same version - down to the last letter or digit - then
>> I don't have a good explanation.
>>
>> SELECT DBINFO('version','full') FROM "informix".systables WHERE tabid = 1;
>>
>> Run that on both systems - compare the output.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.