MongoDBBasedQueryParserService issue with IS NULL

dvk78 · **Joined:** Sun Jul 10, 2016 3:07 am **Posts:** 1

MongoDBBasedQueryParserService does not seem to be able to parse IS NULL in the JPQL properly.

Code:

public PPPoESession getUserSessionDetails(String sourceIP, Date logEntryTime) {
      PPPoESession userSession = null;
      entityManager = entityManagerFactory.createEntityManager();   

      String checkquery = "SELECT p FROM pppoe_test p where p.sourceIP=:source_ip and p.login<=:logentrytime and (p.logout>:logentrytime OR p.logout IS NULL)";
      entityManager.getTransaction().begin();
      Query q = entityManager.createQuery(checkquery, PPPoESession.class);
      q.setParameter("source_ip", sourceIP);
      q.setParameter("logentrytime", logEntryTime, TemporalType.TIMESTAMP);
      List<PPPoESession> results = q.getResultList();
      if (results != null && results.size() >= 0) {
         userSession = results.get(0);
      }
      entityManager.getTransaction().commit();
        entityManager.close();
        return userSession;

I get an error like:

Code:

line 1:146 no viable alternative at input 'NULL'
Exception in thread "main" org.hibernate.hql.ParsingException: HQL000002: The query SELECT p FROM com.cherrinet.pojo.PPPoESession p where p.sourceIP=:source_ip and p.login<=:logentrytime and (p.logout>:logentrytime OR p.logout IS NULL) is not valid; Parser error messages: [[statement, statementElement, selectStatement, queryExpression, querySpec, whereClause, logicalExpression, expression, logicalOrExpression, logicalAndExpression, negatedExpression, equalityExpression, relationalExpression, concatenation, additiveExpression, multiplyExpression, unaryExpression, atom, expressionOrVector, expression, logicalOrExpression, logicalAndExpression, negatedExpression, equalityExpression]: line 1:146 state 0 (decision=51) no viable alt; token=[@52,146:149='NULL',<75>,1:146], [statement, statementElement, selectStatement, queryExpression, querySpec, whereClause, logicalExpression, expression, logicalOrExpression, logicalAndExpression, negatedExpression, equalityExpression, relationalExpression, concatenation, additiveExpression, multiplyExpression, unaryExpression, atom]: line 1:146 mismatched token: [@52,146:149='NULL',<75>,1:146]; expecting type RIGHT_PAREN].
   at org.hibernate.hql.QueryParser.parseQuery(QueryParser.java:70)
   at org.hibernate.ogm.datastore.mongodb.query.parsing.impl.MongoDBBasedQueryParserService.parseQuery(MongoDBBasedQueryParserService.java:40)
   at org.hibernate.ogm.query.impl.OgmQueryTranslator.getQuery(OgmQueryTranslator.java:169)
   at org.hibernate.ogm.query.impl.OgmQueryTranslator.getLoader(OgmQueryTranslator.java:134)
   at org.hibernate.ogm.query.impl.OgmQueryTranslator.list(OgmQueryTranslator.java:128)
   at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
   at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)
   at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
   at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
   at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)

Gunnar · **Joined:** Sat Jan 24, 2009 12:46 pm **Posts:** 388

Hi,

IS NULL is supported, I reckon you hit a bug related to the usage of it within the (nested) boolean expression. I saw you filed issue https://hibernate.atlassian.net/browse/OGM-1118 for this; thanks! Maybe you'd be interested in helping out with fixing this one?

Cheers,

--Gunnar

DavideD · **Joined:** Fri Sep 09, 2011 3:18 am **Posts:** 295

I'll post a reply here for future reference; Guillaume Smet answered in the comments here: https://vinodkumardevi.blogspot.co.uk/2 ... 4142681275

The problem is that the query parser is key-sensitive: null, true or false must be lowercase.

There is already a PR about this.

Cheers,
Davide