Issue Details (XML | Word | Printable)

Key: RDBMS-45
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Jürgen Albert
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
JPOX RDBMS (ARCHIVED)

Syntax Error in JDOQL generated SQL SELECT statement caused by to early closed parenthesis

Created: 03/Nov/06 12:26 PM   Updated: 04/Mar/10 05:55 PM
Component/s: Queries
Affects Version/s: None
Fix Version/s: None

Environment: Web Shop application with j2se 1.5.0 running in a tomcat 5.5.07 (developed under windows running under linux) together with a postgres 8.0 (jdbc: postgressql-8.0-317, tried with jdbc2, jdbc2ee and jdbc3 and also with postgresql-8.1)

Datastore: PostgreSQL
Severity: Production


 Description  « Hide
I try to make a Query witch looks like:

        // Retrieve a PersistenceManager
        PersistenceManager pm = PersistenceHelper.getPersistenceManager();

        // get the current transaction
        Transaction tx = pm.currentTransaction();

        ArrayList parameterList = new ArrayList();
        parameterList.add(countryName);
        parameterList.add(regionName);

        try
        {
            String filter;
            String variables;
            String parameters;
            if (categoryName != null)
            {
                filter = "this.online == true && this.regionProducts.contains(rp) && " +
                        " rp.activeRegion == true && rp.region.name == theRegion && rp.region.country.name == theCountry && " +
                        " this.categories.contains(cat) && cat.name == theCategory";
                variables = "RegionProduct rp; Category cat";
                parameters = "String theCountry, String theRegion, String theCategory";

                parameterList.add(categoryName);
            }
            else
            {
                filter = "this.online == true && this.regionProducts.contains(rp) && " +
                        " rp.activeRegion == true && rp.region.name == theRegion && rp.region.country.name == theCountry";
                variables = "RegionProduct rp";
                parameters = "String theCountry, String theRegion";
            }

            Query q = pm.newQuery(Product.class, filter);
            q.declareVariables(variables);
            q.declareParameters(parameters);
            q.setOrdering("importance ascending");
            q.declareImports("import com.mvolution.golocal.catalog.Product; import java.lang.String;" +
            "import com.mvolution.golocal.catalog.RegionProduct; import com.mvolution.golocal.catalog.Category");
            tx.begin();
            result = (Collection) q.executeWithArray(parameterList.toArray());
            // Detach our owner objects for use elsewhere
            result = pm.detachCopyAll(result);

            tx.commit();
            }

Querys without a special categorie working fine, but if I search with a Categorie JPOX creates the following Query:

SELECT 'com.mvolution.golocal.catalog.Product'
AS JPOXMETADATA,this.product_id,this.bigimage1,this.bigimage2,this.bigimage3,this.bigimage4,this.bigimage5,this.bigimage6,this.description,this.image1,this.image2,this.image3,this.image4,this.image5,this.image6,this.link,this.link_image,this."name",this.online,this.price,this.importance AS JPOXORDER0
FROM public.product this
WHERE EXISTS
(SELECT 1 FROM public.regionproduct this_regionproducts_rp LEFT OUTER JOIN region this_regionproducts_rp_region_country ON this_regionproducts_rp.region_id_oid = this_regionproducts_rp_region_country.region_id LEFT OUTER JOIN country this_regionproducts_rp_region_country_country_name ON this_regionproducts_rp_region_country.country_id_oid = this_regionproducts_rp_region_country_country_name.country_id WHERE this_regionproducts_rp.product_id_oid = this.product_id AND this_regionproducts_rp.product_id_oid = this.product_id AND this.online AND this_regionproducts_rp.active_country AND this_regionproducts_rp_region_country_country_name."name" = 'test')
AND EXISTS
(SELECT 1 FROM product_categories this_categories_cat , public.category unbound_cat WHERE this_categories_cat.product_id_oid = this.product_id AND this_categories_cat.product_id_oid = this.product_id AND unbound_cat.category_id = this_categories_cat.category_id_eid) AND unbound_cat."name" = 'test' ORDER BY JPOXORDER0;

Obviously JPOX closes the last parenthesis to early because the query works if I shift it behind "AND unbound_cat."name" = 'test' ". If not I recieve the follwing Exception:

javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT 'com.mvolution.golocal.catalog.Product' AS JPOXMETADATA,this.product_id,this.bigimage1,this.bigimage2,this.bigimage3,this.bigimage4,this.bigimage5,this.bigimage6,this.description,this.image1,this.image2,this.image3,this.
image4,this.image5,this.image6,this.link,this.link_image,this."name",this.online,this.price,this.importance AS JPOXORDER0 FROM public.product this WHERE EXISTS (SELECT 1 FROM public.regionproduct this_regionproducts_rp LEFT OUTER JOIN region this_regionproducts_rp_region_country ON this_regionproduc
ts_rp.region_id_oid = this_regionproducts_rp_region_country.region_id LEFT OUTER JOIN country this_regionproducts_rp_region_country_country_name ON this_regionproducts_rp_region_country.country_id_oid = this_regionproducts_rp_region_country_country_name.country_id WHERE this_regionproducts_rp.produc
t_id_oid = this.product_id AND this_regionproducts_rp.product_id_oid = this.product_id AND this.online AND this_regionproducts_rp.active_country AND this_regionproducts_rp_region_country_country_name."name" = ?) AND EXISTS (SELECT 1 FROM product_categories this_categories_cat , public.category unbou
nd_cat WHERE this_categories_cat.product_id_oid = this.product_id AND this_categories_cat.product_id_oid = this.product_id AND unbound_cat.category_id = this_categories_cat.category_id_eid) AND unbound_cat."name" = ? ORDER BY JPOXORDER0" : ERROR: relation "unbound_cat" does not exist
java.sql.SQLException: ERROR: relation "unbound_cat" does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1495)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1279)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:186)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
        at org.jpox.store.rdbms.RDBMSManager.executeStatementQuery(RDBMSManager.java:640)
        at org.jpox.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:594)
        at org.jpox.store.query.Query.executeWithMap(Query.java:959)
        at org.jpox.store.query.Query.executeWithArray(Query.java:932)
        at com.mvolution.golocal.manager.CatalogMgr.getProductsForCountry(CatalogMgr.java:176)
        at com.mvolution.golocal.portal.catalog.TopProducts.getCountryProducts(TopProducts.java:81)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.myfaces.el.PropertyResolverImpl.getProperty(PropertyResolverImpl.java:400)
        at org.apache.myfaces.el.PropertyResolverImpl.getValue(PropertyResolverImpl.java:71)
        at org.apache.myfaces.el.ELParserHelper$MyPropertySuffix.evaluate(ELParserHelper.java:532)
        at org.apache.commons.el.ComplexValue.evaluate(ComplexValue.java:145)
        at org.apache.myfaces.el.ValueBindingImpl.getValue(ValueBindingImpl.java:378)
        at javax.faces.component.UIData.getValue(UIData.java:779)
        at org.apache.myfaces.component.html.ext.HtmlDataTableHack.createDataModel(HtmlDataTableHack.java:350)
        at org.apache.myfaces.component.html.ext.HtmlDataTableHack.getDataModel(HtmlDataTableHack.java:333)
        at org.apache.myfaces.component.html.ext.HtmlDataTableHack.getRowCount(HtmlDataTableHack.java:75)
        at org.apache.myfaces.custom.datalist.HtmlListRenderer.encodeChildren(HtmlListRenderer.java:107)
        at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:319)
        at org.apache.myfaces.renderkit.RendererUtils.renderChild(RendererUtils.java:444)
        at org.apache.myfaces.renderkit.RendererUtils.renderChildren(RendererUtils.java:427)
        at org.apache.myfaces.renderkit.RendererUtils.renderChild(RendererUtils.java:448)
        at org.apache.myfaces.renderkit.RendererUtils.renderChildren(RendererUtils.java:427)
        at org.apache.myfaces.renderkit.html.HtmlGroupRendererBase.encodeEnd(HtmlGroupRendererBase.java:62)
        at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:331)
        at javax.faces.webapp.UIComponentTag.encodeEnd(UIComponentTag.java:349)
        at javax.faces.webapp.UIComponentTag.doEndTag(UIComponentTag.java:253)
        at org.apache.myfaces.taglib.UIComponentBodyTagBase.doEndTag(UIComponentBodyTagBase.java:55)
        at org.apache.jsp.templates.golocal.products_jsp._jspx_meth_h_panelGroup_2(org.apache.jsp.templates.golocal.products_jsp:4723)
        at org.apache.jsp.templates.golocal.products_jsp._jspService(org.apache.jsp.templates.golocal.products_jsp:821)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
        at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:463)
        at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:398)
        at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
        at org.apache.myfaces.context.servlet.ServletExternalContextImpl.dispatch(ServletExternalContextImpl.java:415)
        at org.apache.myfaces.application.jsp.JspViewHandlerImpl.renderView(JspViewHandlerImpl.java:234)
        at com.mvolution.golocal.portal.core.SessionViewHandler.renderView(SessionViewHandler.java:45)
        at org.apache.myfaces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:352)
        at javax.faces.webapp.FacesServlet.service(FacesServlet.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:432)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
.
        at org.jpox.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:636)
        at org.jpox.store.query.Query.executeWithMap(Query.java:959)
        at org.jpox.store.query.Query.executeWithArray(Query.java:932)
        at com.mvolution.golocal.manager.CatalogMgr.getProductsForCountry(CatalogMgr.java:176)
        at com.mvolution.golocal.portal.catalog.TopProducts.getCountryProducts(TopProducts.java:81)
        ... 58 more

Sort Order: Ascending order - Click to sort in descending order
[ Permalink | « Hide ]
Erik Bengtson added a comment - 05/Nov/06 11:59 AM
try adding parenthesis e.g. " (this.categories.contains(cat) && cat.name == theCategory) "

[ Permalink | « Hide ]
Andy Jefferson added a comment - 06/Nov/06 05:57 PM
Priority downgraded since no testcase is provided so it cant be that "blocking" ...

[ Permalink | « Hide ]
Andy Jefferson added a comment - 23/Mar/09 12:30 PM
Query issues like this will not be addressed with the legacy JDOQL implementation; wait for "JDOQL2" to be complete

[ Permalink | « Hide ]
Andy Jefferson added a comment - 04/Mar/10 05:55 PM
Likely fixed in DataNucleus 2.0.2 using JDOQL2 implementation

Atlassian JIRA (v4.0#466) | Report a problem | Request a feature | Contact administrators
Powered by a free Atlassian JIRA open source license for JPOX Java Persistent Objects. Try JIRA - bug tracking software for your team.