Setting not_null on foreign key column causing generation of bad sql

Hi,

I ran into a problem where I was gettting the following error when 
making a Manager get_objects call.
DBD::mysql::st execute failed: Unknown column 't2.id' in 'on clause' at 
/usr/local/lib/perl5/site_perl/5.8.8/Rose/DB/Object/Manager.pm line 1435

When making the call below I was getting the error above. This same call 
had worked fine in a different class that also had a foreign key to 
'object'.

$projects = Model::Manager::Project->get_projects( query => [ or => [
                                                                      
                                and => [
                                                                      
                                            'user_permissions.view' => 1,
                                                                      
                                            'users.id' => 
$r->pnotes('user_id'),
                                                                      
                                            ],
                                                                      
                                and => [
                                                                      
                            'group_permissions.view' => 1,
                                                                      
                            'groups.id' => $r->pnotes('group_ids'),
                                                                      
                            ],
                                                                      
                        ],
                                                                       
multi_many_ok => 1,
                                                                       
with_objects => [ 'object.users', 'object.groups' ],
                                                                       );

What I found was the module where this worked _didn't_ have not_null => 
1 set on the object_id column and the module where things broke had 
not_null => 1 on the object_id column, thus the different sql generated.

The sql that causes the error looks like this.
...
FROM projects t1
LEFT OUTER JOIN user_permissions t3 ON(t3.object_id = t2.id)
LEFT JOIN users t4 ON(t3.user_id = t4.id)
LEFT OUTER JOIN group_permissions t5 ON(t5.object_id = t2.id)
LEFT JOIN groups t6 ON(t5.group_id = t6.id),
objects t2                                                             
<--- this seems to be the problem
WHERE ...

My question. Is this a bug in the generated sql? Or, is this suppose to 
happen and I should just not set not_null on the foreign key column?
I can post more code if nessecary

Thanks a lot.

-Glenn







Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642