Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
[prev] [thread] [next] [Date index for 2005/10/06]
From: Richard Foley
Subject: Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
Date: 09:45 on 06 Oct 2005
Subject: Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
Date: 09:45 on 06 Oct 2005
This is a multipart message in MIME format.
--=_alternative 0035A65DC1257092_=
Content-Type: text/plain; charset="us-ascii"
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).
In the example below, it's not just that enabled=>YES and shared=>ENABLED
is wrong, created_by=>1 should be created_by=>$adatestring, and so on.
If you follow me?
In summary, I think this is distinctly related to the UNION DISTINCT in
the SQL, and retrieve_from_sql() not handling it correctly.
Richard.
From: "Richard.Foley@xxxxxxxx.xx" <Richard.Foley on 29-09-2005 16:29
ZE2
Please respond to Richard.Foley@xxx.xxx
To:
classdbi@xxxxx.xxxxxxxxxxxxxxxx.xxx
cc:
rfoley@xxxxxxx.xxx
Subject:
Class::DBI internal column swapping wierdness ?
I have some very wierd behaviour going on using Class::DBI. Columns
appear to be being swapped internally, which is seriously screwing our
application up. Now I may be missing something obvious here, of course,
but here is the basic scenario:
Here's the stripped down package:
package Att::Task;
use strict;
use base qw(Att::Dbi);
__PACKAGE__->table ('task');
__PACKAGE__->columns(Primary => qw(name));
__PACKAGE__->columns(Essential => qw(
created created_by modified modified_by
title application stage seq enabled shared freetext
));
__PACKAGE__->has_a(application => qw(Att::Application));
__PACKAGE__->has_a(stage => qw(Att::Stage));
__PACKAGE__->has_a(enabled => qw(Att::EnabledDisabled));
__PACKAGE__->has_a(shared => qw(Att::YesNo));
1
The relevant lines are shared (YesNo) and enabled(EnabledDisabled). We
can take a look in the database to see what's in the relevant tables:
mysql> SELECT name FROM enableddisabled;
+----------+
| name |
+----------+
| DISABLED |
| ENABLED |
+----------+
mysql> SELECT name FROM yesno;
+------+
| name |
+------+
| NO |
| YES |
+------+
Just to be pedantic here's the database constraint definitions for the
relevant columns:
CONSTRAINT `task_ibfk_2` FOREIGN KEY (`ENABLED`) REFERENCES
`enableddisabled` (`NAME`),
CONSTRAINT `task_ibfk_5` FOREIGN KEY (`SHARED`) REFERENCES `yesno`
(`NAME`)
Pretty simple stuff so far. Now let's take a look at a record from the
database.
mysql> SELECT name, enabled, shared FROM task WHERE name LIKE
'thenew%';
+--------------------------+---------+--------+
| name | enabled | shared |
+--------------------------+---------+--------+
| thenewdeliverychec_02397 | ENABLED | YES |
+--------------------------+---------+--------+
As expected, enabled=ENABLED and shared=YES. Now, take a look at what
Class::DBI returns:
Att::Task thenewdeliverychec_02397 $VAR1 = bless( {
'stage' => bless( {
'name' => '2005-09-13 14:08:13'
}, 'Att::Stage' ),
'name' => 'thenewdeliverychec_02397',
'application' => bless( {
'name' => 'att'
}, 'Att::Application' ),
'freetext' => 'Check what was delivered changed to analysis - ppt
pptcomp',
'modified' => '0000000000analysis_00504',
'created' => 'The new delivery check',
'shared' => bless( {
'name' => 'ENABLED'
}, 'Att::YesNo' ),
'modified_by' => '1',
'title' => '2005-09-13 14:08:13',
'seq' => bless( {
'id' => 'att'
}, 'Att::Sequence' ),
'created_by' => 'pptcomp_25143',
'enabled' => bless( {
'name' => 'YES'
}, 'Att::EnabledDisabled' )
}, 'Att::Task' );
All of a sudden, enabled=YES and shared=ENABLED!!!
How the hell did that happen...?! If anyone has any bright ideas here,
before I pull my remaining hair out, I'd appreciate it. TIA.
--
Ciao
Richard Foley
Ciao - shorter than aufwiedersehen
--=_alternative 0035A65DC1257092_=
Content-Type: text/html; charset="us-ascii"
<br><font size=2 face="sans-serif">Hi</font>
<br>
<br><font size=2 face="sans-serif">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:</font>
<br>
<br><font size=2 face="sans-serif"> my $sql = qq|</font>
<br><font size=2 face="sans-serif"> name IN (</font>
<br><font size=2 face="sans-serif"> SELECT name FROM task where shared = 'NO' AND application = '$application'</font>
<br><font size=2 face="sans-serif"> )</font>
<br><font size=2 face="sans-serif"> UNION DISTINCT</font>
<br><font size=2 face="sans-serif"> SELECT * FROM task where shared = 'YES' AND application IN (</font>
<br><font size=2 face="sans-serif"> SELECT name FROM application WHERE container = '$container'</font>
<br><font size=2 face="sans-serif"> )</font>
<br><font size=2 face="sans-serif"> |; </font>
<br><font size=2 face="sans-serif"> @tasks = Att::Task->retrieve_from_sql($sql);</font>
<br>
<br><font size=2 face="sans-serif">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).</font>
<br>
<br><font size=2 face="sans-serif">In the example below, it's not just that enabled=>YES and shared=>ENABLED is wrong, created_by=>1 should be created_by=>$adatestring, and so on.</font>
<br>
<br><font size=2 face="sans-serif">If you follow me?</font>
<br>
<br><font size=2 face="sans-serif">In summary, I think this is distinctly related to the UNION DISTINCT in the SQL, and retrieve_from_sql() not handling it correctly.</font>
<br>
<br><font size=2 face="sans-serif">Richard.</font>
<br>
<br>
<br>
<p><font size=1 color=#800080 face="sans-serif">From: "Richard.Foley@xxxxxxxx.xx" <Richard.Foley on 29-09-2005 16:29 ZE2</font>
<p><font size=1 color=#800080 face="sans-serif">Please respond to Richard.Foley@xxx.xxx</font>
<p>
<table width=100%>
<tr valign=top>
<td width=9%><font size=1 color=#800080 face="sans-serif">To:</font>
<td width=48%><font size=1 face="sans-serif">classdbi@xxxxx.xxxxxxxxxxxxxxxx.xxx</font>
<br>
<td width=42%>
<br>
<tr valign=top>
<td><font size=1 color=#800080 face="sans-serif">cc:</font>
<td><font size=1 face="sans-serif">rfoley@xxxxxxx.xxx</font>
<br>
<td>
<br>
<tr valign=top>
<td>
<td>
<td>
<tr valign=top>
<td>
<td>
<td>
<tr valign=top>
<td><font size=1 color=#800080 face="sans-serif">Subject:</font>
<td colspan=2><font size=1 face="sans-serif">Class::DBI internal column swapping wierdness ?</font>
<tr valign=top>
<td>
<td>
<td></table>
<br>
<br>
<br><font size=2 face="Courier New">I have some very wierd behaviour going on using Class::DBI. Columns<br>
appear to be being swapped internally, which is seriously screwing our<br>
application up. Now I may be missing something obvious here, of course,<br>
but here is the basic scenario:<br>
<br>
Here's the stripped down package:<br>
<br>
package Att::Task;<br>
use strict;<br>
use base qw(Att::Dbi);<br>
<br>
__PACKAGE__->table ('task');<br>
__PACKAGE__->columns(Primary => qw(name));<br>
__PACKAGE__->columns(Essential => qw(<br>
created created_by modified modified_by <br>
title application stage seq enabled shared freetext<br>
));<br>
<br>
__PACKAGE__->has_a(application => qw(Att::Application));<br>
__PACKAGE__->has_a(stage => qw(Att::Stage));<br>
__PACKAGE__->has_a(enabled => qw(Att::EnabledDisabled));<br>
__PACKAGE__->has_a(shared => qw(Att::YesNo));<br>
<br>
1<br>
<br>
The relevant lines are shared (YesNo) and enabled(EnabledDisabled). We<br>
can take a look in the database to see what's in the relevant tables:<br>
<br>
<br>
mysql> SELECT name FROM enableddisabled;<br>
+----------+<br>
| name |<br>
+----------+<br>
| DISABLED |<br>
| ENABLED |<br>
+----------+<br>
<br>
mysql> SELECT name FROM yesno;<br>
+------+<br>
| name |<br>
+------+<br>
| NO |<br>
| YES |<br>
+------+<br>
<br>
<br>
Just to be pedantic here's the database constraint definitions for the<br>
relevant columns:<br>
<br>
CONSTRAINT `task_ibfk_2` FOREIGN KEY (`ENABLED`) REFERENCES<br>
`enableddisabled` (`NAME`),<br>
CONSTRAINT `task_ibfk_5` FOREIGN KEY (`SHARED`) REFERENCES `yesno`<br>
(`NAME`)<br>
<br>
Pretty simple stuff so far. Now let's take a look at a record from the<br>
database.<br>
<br>
<br>
mysql> SELECT name, enabled, shared FROM task WHERE name LIKE<br>
'thenew%';<br>
+--------------------------+---------+--------+<br>
| name | enabled | shared |<br>
+--------------------------+---------+--------+<br>
| thenewdeliverychec_02397 | ENABLED | YES |<br>
+--------------------------+---------+--------+<br>
<br>
As expected, enabled=ENABLED and shared=YES. Now, take a look at what<br>
Class::DBI returns:<br>
<br>
Att::Task thenewdeliverychec_02397 $VAR1 = bless( {<br>
'stage' => bless( {<br>
'name' => '2005-09-13 14:08:13'<br>
}, 'Att::Stage' ),<br>
'name' => 'thenewdeliverychec_02397',<br>
'application' => bless( {<br>
'name' => 'att'<br>
}, 'Att::Application' ),<br>
'freetext' => 'Check what was delivered changed to analysis - ppt<br>
pptcomp',<br>
'modified' => '0000000000analysis_00504',<br>
'created' => 'The new delivery check',<br>
'shared' => bless( {<br>
'name' => 'ENABLED'<br>
}, 'Att::YesNo' ),<br>
'modified_by' => '1',<br>
'title' => '2005-09-13 14:08:13',<br>
'seq' => bless( {<br>
'id' => 'att'<br>
}, 'Att::Sequence' ),<br>
'created_by' => 'pptcomp_25143',<br>
'enabled' => bless( {<br>
'name' => 'YES'<br>
}, 'Att::EnabledDisabled' )<br>
}, 'Att::Task' );<br>
<br>
All of a sudden, enabled=YES and shared=ENABLED!!!<br>
<br>
How the hell did that happen...?! If anyone has any bright ideas here,<br>
before I pull my remaining hair out, I'd appreciate it. TIA.<br>
<br>
-- <br>
Ciao<br>
Richard Foley<br>
Ciao - shorter than aufwiedersehen<br>
<br>
<br>
<br>
<br>
</font>
<br>
<br>
<p>
<p>
--=_alternative 0035A65DC1257092_=--
 |
Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
|
|
 |
[CDBI] Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
|
|
|
|
Re: [CDBI] Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
|
|
|
|
|
[CDBI] Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?
|