Date   

Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

mukul.gupta@...
 

Problem Description and Reproduction

We were able to reproduce the problem in a small standalone program. We took the samples available from
https://github.com/datanucleus/samples-jdo

We picked the "tutorial" sample application and made changes in Main.java and then ran it with DB2 as backend store (version 5.2.3 of datanucleus)
Please find attached modified Main.java

Following is the summary of changes we made and description of what we are attempting:

1. While querying the data, we removed the transaction opening and closing statements
Following lines are commented





2. Following properties are set


loadResultsAtCommit is set to false as we are querying and processing a large amount of data. So, we do not wish to load all the data in memory at one go.
resultSizeMethod is set to "count" for efficiency reason (As stated in data nucleus user guide).

When we are running this sample application with above mentioned changes, we are encountering the following error (while executing hasNext method)

Executing Query for Products with price below 150.00
Exception performing queries : datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently
datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently
org.datanucleus.exceptions.NucleusUserException: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently
at org.datanucleus.store.query.AbstractQueryResult.getSizeUsingMethod(AbstractQueryResult.java:404)
at org.datanucleus.store.rdbms.query.ScrollableQueryResult.getSizeUsingMethod(ScrollableQueryResult.java:567)
at org.datanucleus.store.query.AbstractQueryResult.size(AbstractQueryResult.java:256)
at org.datanucleus.store.rdbms.query.ScrollableQueryResult$QueryResultIterator.hasNext(ScrollableQueryResult.java:380)
at org.datanucleus.samples.jdo.tutorial.Main.main(Main.java:125)

Other changes we tried

1. Keeping it as transactional query
          Result: It works
2. Keeping "loadResultsAtCommit" as default, while retaining other 3 properties.
          Result: hasNext() returns true while next() returns null
3. Keeping "loadResultsAtCommit" and "resultSizeMethod" as default, while retaining other 2 properties
          Result: It works

Our analysis

Since, we are migrating from an old version of data nucleus (2.0.4) to version 5.2.3, we looked into data nucleus code to see what changed.
In case of data nucleus 2.0.4, when query is executed (performExecute in JDOQLQuery.java), then connection is released. Since, reference count to this connection becomes zero, it is closed. However, just before a connection is closed, registered callback (look for managedConnectionPreClose in performExecute in JDOQLQuery.java) is invoked.

This callback is empty in old version, while in new version it contains "qr1.disconnect()", which is causing the query object inside query result to become null).
When hasNext is called and resultSizeMethod is set to "count", data nucleus attempt to get the size by constructing a new query but finds existing query object needed for same to be null. Hence, the error.

Summary
Since, we are migrating a legacy application, this error got us confused as this combination of query properties use to work correctly with old version of data nucleus.

Also, We are executing a query without transaction and processing a large number of records which may take a while. Processing of records do also involve opening of several transactions. So, we are not sure of putting this query inside a transaction (Request for a general guidance in this regard).
Also, it seems if we decide to remove loadResultsAtCommit and resultSizeMethod, that may impact performance.

A overall guidance in terms of how either how we can adapt according to this changed behavior of data nucleus or if we could some apply some patch on data nucleus is highly appreciated.


Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

Andy
 

https://www.datanucleus.org/documentation/problem_reporting.html


Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

mayank.chawla@...
 

Hello,

Query results are accessible, once the result is returned, we cast it into Iterable. When we call hasNext() on Iterable method, flow goes into Datanucleus layer as hasNext() method is overriden in ScrollableQueryResult.java. 

From there it goes into size method of AbstractQueryResult.java and finally into getSizeUsingMethod method of AbstractQueryResult.java, where the query object is null hence we get below exception. During debugging I found query object of AbstractQueryResult had a value set into it but during connection release/close at the time of returning the result it was set to null and as I explained above, we casted this result into iterable and called hasNext on it, at that time it require query object.

datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently
org.datanucleus.exceptions.NucleusUserException: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently
at org.datanucleus.store.query.AbstractQueryResult.getSizeUsingMethod(AbstractQueryResult.java:404)
at org.datanucleus.store.rdbms.query.ScrollableQueryResult.getSizeUsingMethod(ScrollableQueryResult.java:567)
at org.datanucleus.store.query.AbstractQueryResult.size(AbstractQueryResult.java:256)
at org.datanucleus.store.rdbms.query.ScrollableQueryResult$QueryResultIterator.hasNext(ScrollableQueryResult.java:380)
at com.ibm.cdb.topomgr.util.extension.iterable.IteratorX.empty(IteratorX.java:79)
Please find below stack trace where we get result and query object is set and later result is returned on which we call hasNext() in our code,
[java.lang.Thread.getStackTrace(Thread.java:1164)]
org.datanucleus.store.query.AbstractQueryResult.<init>(AbstractQueryResult.java:80)]
org.datanucleus.store.rdbms.query.AbstractRDBMSQueryResult.<init>(AbstractRDBMSQueryResult.java:80)]
org.datanucleus.store.rdbms.query.ScrollableQueryResult.<init>(ScrollableQueryResult.java:82)]
org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getQueryResultForQuery(RDBMSQueryUtils.java:107)]
org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:683)]
org.datanucleus.store.query.Query.executeQuery(Query.java:1973)]
org.datanucleus.store.query.Query.executeWithArray(Query.java:1862)]
org.datanucleus.store.query.Query.execute(Query.java:1844)]
org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:439)]
org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:263)]
OpenQuery.execute(OpenQuery.java:46)]

Also, in order to reproduce it, should I provide dump of query object? or if you could tell what all info is required to reproduce this issue.


Re: How to use datanucleus.CurrentUserProvider?

Andy
 

The "persistence properties" are amply defined in the docs. "datanucleus.CurrentUserProvider" is one of them.
As said, you are using
PersistenceManagerFactory pmf = new JDOPersistenceManagerFactory(globalPumd, null);

and you need to provide any ADDITIONAL persistence properties (such as the one you wanted to specify) via the second argument in that constructor.


Re: How to use datanucleus.CurrentUserProvider?

1919wang@...
 

I got your point on PersistenceUnitMetaData.
May I ask what is the "persistence properties" if it doesn't mean JDOPersistenceManagerFactory.setPersistenceProperties()? I am new to datanucleus, your guide would be appreciated~

 

You create an INSTANCE of your CurrentUserProvider, and specify that as the value of "datanucleus.CurrentUserProvider" to the persistence properties.


Re: How to use datanucleus.CurrentUserProvider?

Andy
 

I didnt mention JDOPersistenceManagerFactory.setPersistenceProperties(), no.

PersistenceUnitMetaData is to replicate the file "persistence.xml" content. You cannot specify an Object in a text-based persistence.xml file. Hence PersistenceUnitMetaData doesnt support Object values.


Re: How to use datanucleus.CurrentUserProvider?

1919wang@...
 
Edited

Thanks Andy. 
I have several more questions:
  • The 1st option you mentioned is `JDOPersistenceManagerFactory.setPersistenceProperties()`, right? The underlying logic of those 2 options seems identical - saving the properties via `Configuration.setPersistenceProperties`, please correct me if I misunderstood.
  • One thing confusing is that PersistenceUnitMetaData only support String value properties, while overrideProps supports Object value; And when constructing PMF, those 2 properties are actually merged into one Map. So one question is - Is it better to make PersistenceUnitMetaData support Object value?
    public JDOPersistenceManagerFactory(PersistenceUnitMetaData pumd, Map overrideProps)
    {
    // Build up map of all properties to apply (from persistence-unit + overridden + defaulted)
    Map props = new HashMap();
    if (pumd != null && pumd.getProperties() != null)
    {
    props.putAll(pumd.getProperties());
    }
    if (overrideProps != null)
    {
    props.putAll(overrideProps);
    }


Re: How to use datanucleus.CurrentUserProvider?

Andy
 
Edited

You create an INSTANCE of your CurrentUserProvider, and specify that as the value of "datanucleus.CurrentUserProvider" to the persistence properties.
If you insist on creating a PMF in that way you use the second argument to input OVERRIDING PROPERTIES.


How to use datanucleus.CurrentUserProvider?

1919wang@...
 

According to JDO Mapping Guide, to support auditing @CreateUser, we could define an implementation of the DataNucleus interface CurrentUserProvider, and specify it on PMF creation using the property datanucleus.CurrentUserProvider. 

I have a com.alpha.MyUserProvider implements CurrentUserProvider, and tried to set the property like below:

PersistenceUnitMetaData globalPumd = new PersistenceUnitMetaData(jdoName, transactionType, null);
Map<String, String> jdoProperties =
new HashMap<>();
...
jdoProperties.put("datanucleus.CurrentUserProvider", "com.alpha.MyUserProvider");

for (Map.Entry<String, String> entry: jdoProperties.entrySet()) {
globalPumd.addProperty(entry.getKey()
, entry.getValue()); // Here can only add String property value
}


PersistenceManagerFactory pmf
= new JDOPersistenceManagerFactory(globalPumd, null);
...


The property value can only be set as String; However, in org.datanucleus.PersistenceNucleusContextImpl gets the CurrentUserProvider by calling Configuration.getProperty, which is a String, then got a ClassCastException

(CurrentUserProvider)config.getProperty(PropertyNames.PROPERTY_MAPPING_CURRENT_USER_PROVIDER); 


Seems it requires put an MyUserProvider instance  as property value, but PersistenceUnitMetaData only accept String value.


Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

Andy
 
Edited

I have zero problem accessing a query result after transaction commit, with scrollable results. You still haven't posted anything resembling a stack trace with current versions, nor a way of reproducing it.

Copy the query results into a standard Java List before closing the results if that is a problem to you


Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

mayank.chawla@...
 

Hello,

After further debugging we found -  our application goes into Datanucleus layer twice in this flow. First time it creates query, execute it and return result. While returning the result, finally block of JQOQLQuery.java of performExecute() executes mconn.release(), which closes the connection and set query object to null in AbstractQueryResult.java(as ec.getTransaction().isActive() is false, query gets disconnected).
Please find below the code snippet,

JDODQQuery.java
 finally
        {
            mconn.release();
        }
 

public void managedConnectionPreClose()
                                {
                                    if (!ec.getTransaction().isActive())
                                    {
                                        // Non-Tx : disconnect query from ManagedConnection (read in unread rows etc)
                                        qr1.disconnect();
                                    }
                                }
So, in second flow when we go into Datanucleus layer by calling hasNext on returned object(it takes us to hasNext implementation of ScrollableQueryResult), there size() method takes us to AbstractQueryResult, where we get query object as null and exception is thrown.

ScrollableQueryResult.java
        public boolean hasNext()
        {
            synchronized (ScrollableQueryResult.this)
            {
                if (!isOpen())
                {
                    // Spec 14.6.7 Calling hasNext() on closed Query will return false
                    return false;
                }
 
                int theSize = size();
                if (applyRangeChecks)
                {
                    if (theSize < query.getRangeToExcl()-query.getRangeFromIncl())
                    {
                        // Size reached before upper limit of range
                        return iterRowNum <= (query.getRangeFromIncl() + theSize - 1);
                    }
 
                    if (iterRowNum == query.getRangeToExcl()-1)
                    {
                        endIndex = iterRowNum;
                    }
                    // When we are at "query.getRangeToExcl()-1" we have 1 more element
                    return (iterRowNum <= (query.getRangeToExcl()-1));
                }
 
                // When we are at at "size()-1" we have one more element
                return (iterRowNum <= (theSize - 1));
            }
        }
,
I verified ec.getTransaction().isActive() was set to false in previous version(2.0.4) as well. Now, could you please let me know which property I need to set to have the behaviour where query is not set to null as we need to access it later.

I understand I am comparing with very old version, but I have to take reference to make it behave in similar manner. Thank you.


Re: DyadicExpression "OR" compiles into nested SQL expressions which exhausts the stack of the underlying DB

yunus@...
 

Thanks Andy, "contains" has worked. It uses the "IN" statement.

I have been confused about the behavior of the "OR" statements. I have expected something like  (x OR y OR z) instead it is (((X) OR Y) OR Z). I've thought that I miss a flag/parameter that causes this. But I understand that it is the expected behavior.


Re: DyadicExpression "OR" compiles into nested SQL expressions which exhausts the stack of the underlying DB

Andy
 
Edited

What SQL do you want to generate??
The JDOQL you provided has been mapped directly as you requested it.

You could try having a single parameter of type Collection that has multiple String values, and then do
:param.contains(partition.partitionName)
which will probably compile to an IN expression. Clearly you then may have a limit in your RDBMS about the number of values specifiable in an IN expression. Can't see what you would expect an ORM to do about that, you could split up your query to have fewer values in a query and aggregate multiple queries


DyadicExpression "OR" compiles into nested SQL expressions which exhausts the stack of the underlying DB

yunus@...
 

Hi everyone,

I use JDOQL with Datanucleus 4 and Spanner as backend. I use Spanner as a backend for some time and it works mostly fine. But recently I hit a problem when there are too many OR statements (~2K in my case).
The OR statements are merged by nesting. Each of them is separated via parentheses inside another one. As a result, we have a deeply nested statement and database raises an exception that the query exhausts the nesting limits. 

How can I configure Datanucleus to get rid of these heavily nested queries? See below for more explanation.

A query like: 
SELECT FROM PartitionPrivilege WHERE partition.table.tableName == t1 && partition.table.database.name == t2 && (partition.partitionName == p0 || partition.partitionName == p1 || partition.partitionName == p2 || ............ partition.partitionName == p1823 )
Compiled as many nested DyadicExpressions:
[filter:DyadicExpression{DyadicExpression{DyadicExpression{PrimaryExpression{partition.table.tableName}  =  ParameterExpression{t1}}  AND  DyadicExpression{PrimaryExpression{partition.table.database.name}  =  ParameterExpression{t2}}}  AND  DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{DyadicExpression{ ................................................ DyadicExpression{PrimaryExpression{partition.partitionName}  =  ParameterExpression{p0}}  OR  DyadicExpression{PrimaryExpression{partition.partitionName}  =  ParameterExpression{p1}}}  OR  DyadicExpression{PrimaryExpression{partition.partitionName}  =  ParameterExpression{p2}}}  OR  DyadicExpression{PrimaryExpression{partition.partitionName}  =  ParameterExpression{p3}}}  OR  DyadicExpression{PrimaryExpression{partition.partitionName}  =  ParameterExpression{p4}}}  OR .....................
Finally, compiled query is:
Query compiled to datastore query "SELECT DISTINCT 'PartitionPrivilege' AS NUCLEUS_TYPE,A0.CREATE_TIME,A0.GRANT_OPTION,A0.GRANTOR,A0.GRANTOR_TYPE,A0.PRINCIPAL_NAME,A0.PRINCIPAL_TYPE,A0.PART_PRIV,A0.PART_GRANT_ID FROM PART_PRIVS A0 LEFT OUTER JOIN PARTITIONS B0 ON A0.PART_ID = B0.PART_ID LEFT OUTER JOIN TBLS C0 ON B0.TBL_ID = C0.TBL_ID LEFT OUTER JOIN DBS D0 ON C0.DB_ID = D0.DB_ID WHERE C0.TBL_NAME = ? AND D0.`NAME` = ? AND ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((B0.PART_NAME = ?) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?)) OR (B0.PART_NAME = ?))

Thanks in advance
yunus


Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

Andy
 

Only you can see it, hence only you can debug it. Check you're not multithreading things. Reproduce it in a testcase. That's all there is.


Re: datanucleus.query.resultSizeMethod of "COUNT" is only valid for use with JDOQL or JPQL currently

mayank.chawla@...
 
Edited

Hello,

After debugging the code I found it is going in the below flow, I ran the queries that were formed, copied them through logs and ran them at DB2, all queries executed correctly but returned no result because data was not present matching that condition. Now, when the flow returns back to our application, We cast result in "Iterable" object, when we call hasNext() it again goes into datanucleus layer and in AbstractQueryResult, query object is found to be null(Hence it throws exception, as per second stacktrace), which is strange as it was set in below stack. Maybe it got garbage collected or some other issue is there as queries were formed properly. Could you please assist or let me know if any further info needs to be furnished.

JDOQLQuery.performExecute(Map) line: 803
JDOQLQuery(Query<T>).executeQuery(Map) line: 1973
JDOQLQuery(Query<T>).executeWithMap(Map) line: 1880
ScrollableQueryResult<E>(AbstractQueryResult<E>).getSizeUsingMethod() line: 350
ScrollableQueryResult<E>.getSizeUsingMethod() line: 567
ScrollableQueryResult<E>(AbstractQueryResult<E>).size() line: 258
ScrollableQueryResult$QueryResultIterator.hasNext() line: 380
ScrollableQueryResult<E>(AbstractCollection<E>).toString() line: 466
String.valueOf(Object) line: 3450
StringBuilder.append(Object) line: 537
JDOQLQuery.performExecute(Map) line: 743
JDOQLQuery(Query<T>).executeQuery(Map) line: 1973
Query<T>.executeWithArray(Object[]) line: 1862
Query<T>.execute() line: 1844
JDOQuery<T>.executeInternal() line: 439
JDOQuery<T>.execute() line: 263
Second datanucleus touchpoint, where exceptions is thrown
ScrollableQueryResult<E>(AbstractQueryResult<E>).getSizeUsingMethod: 325
ScrollableQueryResult<E>(AbstractQueryResult<E>).size() line: 256
ScrollableQueryResult$QueryResultIterator.hasNext() line: 380
 

We set below extensions,
            query.addExtension( "datanucleus.rdbms.query.resultSetType", "scroll-insensitive");
            query.addExtension( "datanucleus.query.resultCacheType", cache);
            query.addExtension( "datanucleus.query.resultSizeMethod", "count");
            query.addExtension( "datanucleus.query.loadResultsAtCommit", "false");

Query: SELECT FROM MQClusterReceiverChannelJdo WHERE version_ == 0 && deleted_ == false
FetchGroup:
FetchGroup<MQClusterJdo> : 55 members=[pk_, name_], modifiable=true, postLoad=false, listeners.size=1
FetchGroup<MQClusterReceiverChannelJdo> : 56 members=[pk_, clusters_], modifiable=true, postLoad=false, listeners.size=1

Thanks.


Re: Converter not working with datanucleus-mongodb

Andy
 

Use of JDO converters to convert a String to a Long is not supported, and is minority interest. Get the code if you require such things


Re: Converter not working with datanucleus-mongodb

qquantum@...
 

Here's a bug report: https://github.com/datanucleus/datanucleus-mongodb/issues/55


Re: Converter not working with datanucleus-mongodb

qquantum@...
 

Adding 

```
@PersistenceCapable
@EmbeddedOnly
public class Availability {
  private String day;
  @Convert(LocalTimeConverter.class)
  private String opens;
  @Convert(LocalTimeConverter.class)
  private String closes;
}
```
Solved one part of the problem that is it now stored as a JSON document (embedded) but the converter still fails to be called. 


Re: Converter not working with datanucleus-mongodb

Andy
 

Looks totally normal to me since you selected to have Availability stored as its own type, hence it has a reference to the object "id" that it relates to.
Obviously you could store them as embedded if you wanted (see the docs).
Or if you expected to see a "DBRef" stored then you can contribute to https://github.com/datanucleus/datanucleus-mongodb/issues/6

241 - 260 of 476