Created an attachment (id=59) [details]
diff against atk/db updating oracle support

use patch -p2 with the diff file in the achieve-1.0.x directory. Patch doesn't
apply cleanly. Can't figure out why. If needed I can upload all modified files,
if needed bundled into a tar.gz or/and zip file.

Almost forgot, my system is:
Apache compiled as follows: Apache/1.3.29 (Unix) PHP/4.3.4 mod_fastcgi/2.2.12
mod_perl/1.29 mod_ssl/2.8.16 OpenSSL/0.9.6c
with PHP compiled using OCI8 ($Revision: 1.183.2.5 $) against an Oracle 8.1.7
database BUT for testing LEFT JOINS Achievo is connecting to a 9i database on
another machine.

Thanks for your efforts. I'll try to apply your patches tonight. If all goes
well, we can roll this into 1.0.3.

Moving product from ATK to Achievo, I completely overlooked this bug when
searching for fixes to apply to achievo.

Status update: I'm currently integrating the patch. Some parts applied cleanly,
other parts I have to adjust manually.

I'm currently trying to get my php workstation to connect with an oracle
database to be able to test.

I committed the patches to CVS. 

I'm getting one error with 'oci8' though.. In manytoonerelations (for example
the contract dropdown in the project edit screen), I'm getting a 'not a selected
expression' statement on the order by clause. Perhaps the db driver should add
order by statements to the select list? 

Joop, if you like, can you have a look at this?

The cvs commits will be in tonights nighly build.

Received per mail from Joop: 

"I'll try to have a look this evening but this error either means that a
column is not in the groupby statement which it should or the table
aliasing is not correct.

Could you send me the offending sql statement so that I can check it?
I don't have any problem with 1.04 and didn't try any nightlies sofar
with Oracle. I did use nightlies against mysql for testing/new features."

Joop: I think it's 'order by' related, not 'group by'. I'll have a look tonight
what the query was and send you the result. It was in a new dropdown in the
project edit screen that was not in 1.0.4 yet.

This is the query that works on MySQL but fails on oracle:

dbg: [+02.81300s] atkmysqldb.query(): SELECT DISTINCT
contract.id,contract.description FROM contract ORDER BY startdate

Message from joop by mail: 

"Problem is caused by the DISTINCT clause, if you use that then you must
supply the orderby arguments to the select list. 

BTW: why use DISTINCT?, contract.id is unique!"

In this case, yes, but the query is auto-generated, and there might be
attributes in a node that cause a join, that in its turn causes the id's to turn
up twice in a resultset. hence the distinct. 

(Joop: please use the link to bugzilla to add comments, instead of replying to
the mail)

> "Problem is caused by the DISTINCT clause, if you use that then you must
> supply the orderby arguments to the select list. 
> 
> BTW: why use DISTINCT?, contract.id is unique!"
> 
> In this case, yes, but the query is auto-generated, and there might be
> attributes in a node that cause a join, that in its turn causes the id's to turn
> up twice in a resultset. hence the distinct. 
Then achievo must add the orderby elements to the select list but I still can't
think of a scenario where you would have problems with non-distinct id's. I'll
ask my DBA about it.