Bug 102820 - Can't have SQL functions around property names in filters
Can't have SQL functions around property names in filters
Status: CLOSED WONTFIX
Product: Red Hat Web Application Framework
Classification: Retired
Component: persistence (Show other bugs)
nightly
All Linux
medium Severity high
: ---
: ---
Assigned To: Archit Shah
Jon Orris
:
Depends On:
Blocks: 101833 101834
  Show dependency treegraph
 
Reported: 2003-08-21 10:44 EDT by Daniel Berrange
Modified: 2007-04-18 12:57 EDT (History)
0 users

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2003-09-07 15:54:33 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Daniel Berrange 2003-08-21 10:44:44 EDT
From Bugzilla Helper:
User-Agent: Mozilla/5.0 Galeon/1.2.9 (X11; Linux i686; U;) Gecko/20030314

Description of problem:
Attempting to add the following filter causes persistence to throw an error

 Filter f = query.addFilter("trunc(launch_date) >= to_date('" + 
                            format.format(start) +
                            "','MM/DD/YYYY')");            


java.lang.IllegalArgumentException: no such path: trunc
        at com.redhat.persistence.engine.rdbms.QGen.genPath(QGen.java(Inlined
Compiled Code))
        at
com.redhat.persistence.engine.rdbms.QGen.access$500(QGen.java(Compiled Code))
        at com.redhat.persistence.engine.rdbms.QGen$4.map(QGen.java:604)
        at
com.redhat.persistence.common.SQLParser.parens(SQLParser.java(Compiled Code))
        at
com.redhat.persistence.common.SQLParser.unstructuredElementNoComma(SQLParser.java(Compiled
Code))
        at
com.redhat.persistence.common.SQLParser.unstructuredElement(SQLParser.java(Compiled
Code))
        at com.redhat.persistence.common.SQLParser.sql(SQLParser.java:239)
        at com.redhat.persistence.engine.rdbms.QGen$2.onPassthrough(QGen.java:611)
        at
com.redhat.persistence.Expression$Passthrough.dispatch(Expression.java:80)
        at com.redhat.persistence.engine.rdbms.QGen.generate(QGen.java(Inlined
Compiled Code))
        at com.redhat.persistence.engine.rdbms.QGen.generate(QGen.java(Compiled
Code))


Version-Release number of selected component (if applicable):


How reproducible:
Always

Steps to Reproduce:
1. Add the above filter to a query
2. Run the query

    

Actual Results:  An error is thrown

Expected Results:  No error is thrown

Additional info:
Comment 1 Archit Shah 2003-08-28 16:06:12 EDT
trunc(date) is an Oracle specific thing. Right now the functions that worked in
5.2 work now. I am figuring out the right thing to do for date functions and
making the error message more clear.
Comment 2 Daniel Berrange 2003-08-28 20:37:48 EDT
Yeah, that's ok - this piece of code is from the Intermedia search engine
backend, which is Oracle specific.
Comment 3 Archit Shah 2003-09-04 14:19:19 EDT
Requirements:

1) supported functions should work the same on both dbs
2) db specific supported functions should be possible, but only in db-specific
contexts
3) the list of supported functions should be configurable, so users can add
random db specific or custom functions
4) support for sql standard functions that don't use standard function call-like
syntax (e.g. trim(leading ' ' from str))

We aren't really addressing these issues now. Change 35687 makes it easy to
change _the_ list of functions (see QGen.java).

Dan: You can either not use trunc (preferred) or modify QGen.java to include
trunc as a supported function (please mark with XXX or something appropriate).
We should target a more complete resolution to this issue in October or November.
Comment 4 Daniel Berrange 2003-09-04 14:30:44 EDT
Why do we have to hard code/restrict the list of functions at all ? Its not
really feasible to produce a complete list of all allowed functions per
database, per version, not to mention user defined PL/SQL procedures.Can't we
just let the SQL parser detect the general syntax for a function & allow it
straight through without attempting to restrict them? Developers are capable of
understanding the consequences of using DB-specific functions, just as they do
using DB-specific SQL in PDL query {} blocks. 

Using the Oracle specific 'trunc' and 'to_date' functions in this context is
perfectly legitimate since this code is part of the Intermedia search which only
runs on Oracle anyhow.
Comment 5 Archit Shah 2003-09-04 16:45:56 EDT
Another option is to use a named filter (see perforce 35697). Checking of sql is
there to make it harder to accidently put dependencies for a specific DB in Java
code. That is supposed to be left to the PDL files, which have a standard
mechanism for database specificity. What we want at the Java layer is a way for
the developer to establish a database specific context allowing looser checking.
That is what I meant by 2) above.
Comment 6 Daniel Berrange 2003-09-05 04:40:04 EDT
Ah I see now. I had no idea you could do such a thing. If the persistence
documentation were still present in the developer guide I might have learnt
about this.....
Comment 7 Daniel Berrange 2003-09-07 15:54:33 EDT
Will use a named filter instead.
Comment 8 Debbie McGrath 2003-11-06 16:25:36 EST
If you are going to use a different method, then this no longer blocks
101833 and 101834, correct?
If so, please remove those dependencies.
Thanks!
--- Debbie

Note You need to log in before you can comment on or make changes to this bug.