Re: [CDBI] Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?

> On 6 Oct 2005, at 10:45, Richard Foley wrote:

On Thursday 06 October 2005 12:10, William Ross wrote:
> On 6 Oct 2005, at 10:45, Richard Foley wrote:
> > Hi
> >
> > I think I may have found the reason for this wierd behaviour, it
> > seems to come from a 'retrieve_from_sql' statement, when I use a
> > 'UNION DISTINCT' in the clause, something like this:
> >
> >         my $sql = qq|
> >             name IN (
> >                 SELECT name FROM task where shared = 'NO' AND
> > application = '$application'
> >             )
> >             UNION DISTINCT
> >             SELECT * FROM task where shared = 'YES' AND application
> > IN (
> >                 SELECT name FROM application WHERE container =
> > '$container'
> >             )
> >
> >         |;
> >
> >         @tasks = Att::Task->retrieve_from_sql($sql);
> >
> > What seems to happen, is that the objects returned from the first
> > part of the SQL statement are expanded (or expandable), but the
> > ones which come back from the unioned (second) part of the
> > statement, are munged in a bad way.  On later inspection, they
> > appear to be a hash built from a list with an element missing or
> > added, so knocking all the key=>value pairs off by one, and messing
> > up the construction of the object, as you can see from the original
> > bug report (below).
>
> That's a nasty looking query. Are we missing a bit at the beginning?
>
Nope, that's the only way retrieve_from_sql() will accept the SQL, so I've 
found, the rest is correctly supplied by Class::DBI itself.

> and it's worth asking: do both parts of the query return rows from
> the same table, and the same set of columns in each row?
>
Yep, the stuff coming back is all correct and in order, even the primary keys 
of the found objects are in the right place in the constructed objects.  The 
problem is that all the other keys=>values are munged with one another.

I think you'll find the query is a bit of a red-herring, the critical bit, as 
far as I can tell, is the UNION DISTINCT, as described above.  I could be 
wrong of course, it's only a theory :-)

> will
>
>
> _______________________________________________
> ClassDBI mailing list
> ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
> http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi

        -- 

Ciao
Richard Foley

http://www.oreilly.com/catalog/perldebugpr/index.html
http://www.oreilly.com/catalog/rtessentials/index.html
http://www.apress.com/book/bookDisplay.html?bID=399