[daisy] [JIRA] Created: (DSY-525) Testcases for date functions in LocalQuery2Test fail with PostgreSQL as database

[Andreas Deininger (JIRA)]

Testcases for date functions in LocalQuery2Test fail with PostgreSQL as database
--------------------------------------------------------------------------------

         Key: DSY-525
         URL: http://issues.cocoondev.org//browse/DSY-525
     Project: Daisy
        Type: Bug
  Components: Repository, Repository - database support, Repository - querying and indexing  
    Versions: 2.1    
 Environment: SVN 4286
 Reporter: Andreas Deininger
    Priority: Minor


Seems like many (or all?) of the testcases for the date functions inside LocalQuery2Test fail if PostgreSQL is used as SQL database:

e.g. offending test statement: select Year(CurrentDate()) where Year(CurrentDate()) = " + currentYear + " and id = '" + document

Testcase: testQuery(org.outerj.daisy.repository.test.LocalQuery2Test):	Caused an ERROR
Error performing query.
org.outerj.daisy.repository.query.QueryException: Error performing query.
	at org.outerj.daisy.repository.serverimpl.query.LocalQueryManager.performQueryReturnDocuments(LocalQueryManager.java:561)
	at org.outerj.daisy.repository.serverimpl.query.LocalQueryManager.performQuery(LocalQueryManager.java:107)
	at org.outerj.daisy.repository.serverimpl.query.LocalQueryManager.performQuery(LocalQueryManager.java:87)
	at org.outerj.daisy.repository.serverimpl.query.LocalQueryManager.performQuery(LocalQueryManager.java:75)
	at org.outerj.daisy.repository.test.AbstractQuery2Test.testQuery(AbstractQuery2Test.java:603)
Caused by: org.postgresql.util.PSQLException: FEHLER: Funktion pg_catalog.date_part("unknown", "unknown") ist nicht eindeutig

	at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
	at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
	at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
	at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
	at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
	at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:233)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:91)
	at org.outerj.daisy.repository.serverimpl.query.LocalQueryManager.performQueryReturnDocuments(LocalQueryManager.java:446)
	... 19 more

Seems like the implementation of the CurrentDate() function is causing trouble.
Generated SQL: select distinct documents1.id as id, namespaces2.name_ as ns_name, document_variants.branch_id as branch_id, document_variants.lang_id as lang_id from document_variants  left outer join documents documents1 on (document_variants.doc_id = documents1.id and document_variants.ns_id = documents1.ns_id) left outer join daisy_namespaces namespaces2 on (document_variants.ns_id = namespaces2.id) where ( ( ( EXTRACT(YEAR FROM  ? )  =  ? ) AND  (documents1.id_search =  ? ))) and document_variants.retired = ? and document_variants.liveversion_id != -1

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.cocoondev.org//secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira