Error using query with MAX() function

I have a class that is persisting a Entity that holds remarks and a time.

Then when I query for the latest remark I get a internal error when the remark is larger than 1966 chars.

Here is a test class and entity that reproduces the problem on my system.

@Entity
@Table(name = "TestEntity", schema = "mySchema")
public class TestEntity
{
	@Id
	private int		id;
	@Basic
	@Lob
	private String	remark;
	@Basic
	@Temporal(value = TemporalType.TIMESTAMP)
	private Date	changeDate;

	public int getId()
	{
		return id;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	public String getRemark()
	{
		return remark;
	}

	public void setRemark(String remark)
	{
		this.remark = remark;
	}

	public Date getChangeDate()
	{
		return changeDate;
	}

	public void setChangeDate(Date changeDate)
	{
		this.changeDate = changeDate;
	}

}

public class Test
{
	private final EntityManagerFactory emf;
	private final EntityManager			em;

	public Test()
	{
		emf = Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
		em = emf.createEntityManager();
	}

	public static void main(String[] args)
	{
		Test t = new Test();
		t.doit();
	}

	public void doit()
	{
		for(int i=1;i<32766;i++)
		{
			em.getTransaction().begin();
			TestEntity v = em.find(TestEntity.class, i);
			if(v==null)
			{
				v = new TestEntity();
				v.setId(i);
				em.persist(v);
			}
			StringBuffer sb = new StringBuffer();
			for(int j=0;j<=i;j++)
			{
				sb.append("X");
			}
			String sbs = sb.toString();
			v.setRemark(sbs);
			v.setChangeDate(new Date(System.currentTimeMillis()));
			em.getTransaction().commit();

			em.getTransaction().begin();
			CriteriaBuilder cb = em.getCriteriaBuilder();
			CriteriaQuery<Object> q = cb.createQuery();
			Root r = q.from(TestEntity.class);

			Path c = r.get("changeDate");

			Expression me = cb.max(c);
			CriteriaQuery<Object> s = q.select(me);

			Predicate predicate = cb.isNotNull(c);
			q.where(predicate);

			TypedQuery<Object> tq = em.createQuery(s);
			Date mDate = (Date)tq.getSingleResult();
			em.getTransaction().commit();
			System.out.println("MaxDate = "+mDate+" i="+i);
		}
	}
}

The Error output is

MaxDate = Sun Apr 24 00:49:47 EDT 2011 i=1962
MaxDate = Sun Apr 24 00:49:47 EDT 2011 i=1963
MaxDate = Sun Apr 24 00:49:47 EDT 2011 i=1964
MaxDate = Sun Apr 24 00:49:47 EDT 2011 i=1965
MaxDate = Sun Apr 24 00:49:47 EDT 2011 i=1966
MaxDate = Sun Apr 24 00:49:47 EDT 2011 i=1967
Exception in thread "main" [ObjectDB 2.2.2] Unexpected exception (Error 990)
  Generated by Java HotSpot(TM) 64-Bit Server VM 1.6.0_24 (on Linux 2.6.32-71.24.1.el6.x86_64).
Please report this error on http://www.objectdb.com/database/issue/new
com.objectdb.o.InternalException: null
com.objectdb.o.InternalException
	at com.objectdb.o.BYR.s(BYR.java:113)
	at com.objectdb.o.BYR.J(BYR.java:353)
	at com.objectdb.o.VUT.k(VUT.java:545)
	at com.objectdb.o.REG.D(REG.java:339)
	at com.objectdb.o.EXR$d.c(EXR.java:131)
	at com.objectdb.o.VAR.an(VAR.java:769)
	at com.objectdb.o.VAR.am(VAR.java:712)
	at com.objectdb.o.BCN.y(BCN.java:282)
	at com.objectdb.o.BCN.i(BCN.java:251)
	at com.objectdb.o.PBI.A(PBI.java:141)
	at com.objectdb.o.PBI.p(PBI.java:111)
	at com.objectdb.o.OBI.U5(OBI.java:235)
	at com.objectdb.o.BQI.Ve(BQI.java:134)
	at com.objectdb.o.GQI.Us(GQI.java:130)
	at com.objectdb.o.PRG.aa(PRG.java:584)
	at com.objectdb.o.QRM.UR(QRM.java:256)
	at com.objectdb.o.MST.UR(MST.java:878)
	at com.objectdb.o.WRA.UR(WRA.java:286)
	at com.objectdb.o.WSM.UR(WSM.java:113)
	at com.objectdb.o.WRA.UR(WRA.java:286)
	at com.objectdb.o.WSN.UR(WSN.java:422)
	at com.objectdb.o.QRR.g(QRR.java:216)
	at com.objectdb.o.QRR.b(QRR.java:139)
	at com.objectdb.jpa.JpaQuery.getSingleResult(JpaQuery.java:607)
	at com.ibm.check.cmvc2git.Test.doit(Test.java:69)
	at com.ibm.check.cmvc2git.Test.main(Test.java:30)

This brigns up the question, what is the size limit for a persisted @Lob String in objectDB?

ObjectDB supports strings in any length that is supported by Java (i.e. up to 2GB).

Your test case demonstrates an ObjectDB bug in processing large objects in queries.

Please try build 2.2.2_02 that should fix this issue.

Thank you very much for the bug report and for the useful test case.

It works better but not completly.

When my remarks data has null remarks interspersed in the data I get the another error.

Add the following to the above test code and you can see the problem.

			String sbs = sb.toString();
			if( i % 20 == 0)
			{
				sbs = null;
			}
			v.setRemark(sbs);

The error output I get is

MaxDate = Sun Apr 24 15:23:41 EDT 2011 i=1975
MaxDate = Sun Apr 24 15:23:41 EDT 2011 i=1976
MaxDate = Sun Apr 24 15:23:41 EDT 2011 i=1977
MaxDate = Sun Apr 24 15:23:41 EDT 2011 i=1978
MaxDate = Sun Apr 24 15:23:41 EDT 2011 i=1979
Exception in thread "main" [ObjectDB 2.2.2_02] Unexpected exception (Error 990)
  Generated by Java HotSpot(TM) 64-Bit Server VM 1.6.0_24 (on Linux 2.6.32-71.24.1.el6.x86_64).
Please report this error on http://www.objectdb.com/database/issue/new
com.objectdb.o.InternalException: java.lang.NullPointerException: null
java.lang.NullPointerException
	at com.objectdb.o.OBI.s(OBI.java:373)
	at com.objectdb.o.OBI.q(OBI.java:263)
	at com.objectdb.o.OBI.U5(OBI.java:231)
	at com.objectdb.o.BQI.Ve(BQI.java:134)
	at com.objectdb.o.GQI.Us(GQI.java:130)
	at com.objectdb.o.PRG.aa(PRG.java:584)
	at com.objectdb.o.QRM.UR(QRM.java:256)
	at com.objectdb.o.MST.UR(MST.java:878)
	at com.objectdb.o.WRA.UR(WRA.java:286)
	at com.objectdb.o.WSM.UR(WSM.java:113)
	at com.objectdb.o.WRA.UR(WRA.java:286)
	at com.objectdb.o.WSN.UR(WSN.java:422)
	at com.objectdb.o.QRR.g(QRR.java:216)
	at com.objectdb.o.QRR.b(QRR.java:139)
	at com.objectdb.jpa.JpaQuery.getSingleResult(JpaQuery.java:607)
	at com.ibm.check.cmvc2git.Test.doit(Test.java:73)
	at com.ibm.check.cmvc2git.Test.main(Test.java:30)

Apparently the previous fix was incomplete. Please try the new build (2.2.2_03).

2.2.2_03 seems to fix the problem with my data.

Thank you for the update and thanks again for the bug report.

I'm getting an error with as stack trace again on my DB and data.

The test code above passes but my data and Entity fails.

At the spot where my code fails there are no Notes entities yet.

After the failure the database fails Doctor check

ObjectDB Doctor [version 2.2.3_03]
Copyright (c) 2011, ObjectDB Software. All rights reserved.

Scanning the database file...
.................................................. 100MB
.................................................. 200MB
............................... 262MB (total)

Analyzing database structure...
10% 20% 30% 40% 50% 60% 70% 80% 90% 100%

Global Value Errors
-------------------
[1] Unexpected total object count: 1812225 (expected 1812197)

BTree Value Errors
------------------
[1] com.ibm.check.cmvc2git.entities.Notes
  - Unexpected object count: 59999 (actual 59971)

Page Relation Errors
--------------------

...
...
...

I've downloaded the latest 2.2.3_03 with no change.

I don't know how to narrow this one down.

How do you debug the cause of internal errors other than setting log level to debug and getting the stack trace?

Here is my log output at the time of the error with log level set to debug.

[2011-05-02 21:10:36 #150 query.manager]
<queryRequest query="SELECT MAX($1.addDate) FROM Notes $1 WHERE $1.addDate IS NOT NULL" args="null" transactionId="-1" />

[2011-05-02 21:10:36 #151 query.tokenizer]
<tokenList>
    <token name="SELECT" code="132" postion="0:6" />
    <token name="MAX" code="118" postion="7:10" />
    <token name="(" code="14" postion="10:11" />
    <token name="$1" code="1" postion="11:13" />
    <token name="." code="6" postion="13:14" />
    <token name="addDate" code="1" postion="14:21" />
    <token name=")" code="15" postion="21:22" />
    <token name="FROM" code="103" postion="23:27" />
    <token name="Notes" code="1" postion="28:33" />
    <token name="$1" code="1" postion="34:36" />
    <token name="WHERE" code="150" postion="37:42" />
    <token name="$1" code="1" postion="43:45" />
    <token name="." code="6" postion="45:46" />
    <token name="addDate" code="1" postion="46:53" />
    <token name="IS NOT NULL" code="62" postion="54:65" />
</tokenList>

[2011-05-02 21:10:36 #152 query.tokenizer]
<token name="QUERY" code="67" postion="0:0">
    <token name="SELECT" code="132" postion="0:6">
        <token name="MAX" code="118" postion="7:10">
            <token name="(" code="14" postion="10:11">
                <token name="." code="6" postion="13:14">
                    <token name="$1" code="1" postion="11:13" />
                    <token name="addDate" code="1" postion="14:21" />
                </token>
                <token name=")" code="15" postion="21:22" />
            </token>
        </token>
    </token>
    <token name="FROM" code="103" postion="23:27">
        <token name="$1" code="1" postion="34:36">
            <token name="Notes" code="1" postion="28:33" />
        </token>
    </token>
    <token name="WHERE" code="150" postion="37:42">
        <token name="IS NOT NULL" code="62" postion="54:65">
            <token name="." code="6" postion="45:46">
                <token name="$1" code="1" postion="43:45" />
                <token name="addDate" code="1" postion="46:53" />
            </token>
        </token>
    </token>
</token>

[2011-05-02 21:10:36 #153 query.compiler]
<queryCompilation query="SELECT MAX($1.addDate) FROM Notes $1 WHERE $1.addDate IS NOT NULL">
    <symbolManager>
        <parameters />
        <variables>
            <$1 varId="0" type="com.ibm.check.cmvc2git.entities.Notes" subtypes="include" typeId="5" binding="null" nav="1">
                <keys />
                <navigations>
                    <addDate member="addDate" memberType="java.util.Date" id="0" />
                </navigations>
            </$1>
        </variables>
    </symbolManager>
    <queryCompData>
        <query exp="query(where(($1.addDate!=null)),select(max($1.addDate)))" var="1" directVar="0" ext="1">
            <where exp="where(($1.addDate!=null))" var="1" directVar="0" ext="1">
                <notEquals exp="($1.addDate!=null)" type="boolean" var="1" directVar="0" ext="1">
                    <member exp="$1.addDate" type="java.util.Date" var="1" directVar="0" ext="1" memberName="addDate" />
                    <literal exp="null" type="java.lang.Object" var="0" directVar="0" ext="0" />
                </notEquals>
            </where>
            <select exp="select(max($1.addDate))" var="1" directVar="0" ext="1">
                <max exp="max($1.addDate)" type="java.util.Date" var="1" directVar="0" ext="1">
                    <member exp="$1.addDate" type="java.util.Date" var="1" directVar="0" ext="1" memberName="addDate" />
                </max>
            </select>
        </query>
        <globals globalVariables="0" globalNavigations="1" />
    </queryCompData>
</queryCompilation>

[2011-05-02 21:10:36 #154 query.compiler]
<query-planning filter="($1.addDate!=null)">
    <step step="1" var="$1" filter="($1.addDate!=null)">
        <allPlans>
            <filterPlan plan="filter(extract($1,type(Notes[all])),($1.addDate!=null))" eval="8.948" var="1" prm="1" reg="1" filter="($1.addDate!=null)" />
        </allPlans>
        <selectedPlans>
            <filterPlan plan="filter(extract($1,type(Notes[all])),($1.addDate!=null))" eval="8.948" var="1" prm="1" reg="1" filter="($1.addDate!=null)" />
        </selectedPlans>
    </step>
</query-planning>

[2011-05-02 21:10:36 #155 query.compiler]
<finalPlans>
    <groupPlan plan="group(filter(extract($1,type(Notes[all])),($1.addDate!=null)))" eval="9.2033" var="1" prm="1" reg="1" group="null" eval="[max($1.addDate)]">
        <filterPlan plan="filter(extract($1,type(Notes[all])),($1.addDate!=null))" eval="8.948" var="1" prm="1" reg="1" filter="($1.addDate!=null)">
            <extractPlan plan="extract($1,type(Notes[all]))" eval="8.9268" var="1" prm="1" reg="1" variable="$1">
                <btreePlan plan="type(Notes[all])" eval="8.9268" var="1" prm="1" reg="0" variable="$1" />
            </extractPlan>
        </filterPlan>
    </groupPlan>
</finalPlans>

[2011-05-02 21:10:36 #156 query.manager]
<newQueryProgram>
    <groupPlan plan="group(filter(extract($1,type(Notes[all])),($1.addDate!=null)))" eval="9.2033" var="1" prm="1" reg="1" group="null" eval="[max($1.addDate)]">
        <filterPlan plan="filter(extract($1,type(Notes[all])),($1.addDate!=null))" eval="8.948" var="1" prm="1" reg="1" filter="($1.addDate!=null)">
            <extractPlan plan="extract($1,type(Notes[all]))" eval="8.9268" var="1" prm="1" reg="1" variable="$1">
                <btreePlan plan="type(Notes[all])" eval="8.9268" var="1" prm="1" reg="0" variable="$1" />
            </extractPlan>
        </filterPlan>
    </groupPlan>
</newQueryProgram>

[2011-05-02 21:10:36 #157 *]
[ObjectDB 2.2.3_03] Unexpected exception (Error 990)
  Generated by Java HotSpot(TM) 64-Bit Server VM 1.6.0_24 (on Linux 2.6.32-71.24.1.el6.x86_64).
Please report this error on http://www.objectdb.com/database/issue/new
com.objectdb.o.InternalException: null
com.objectdb.o.InternalException
at com.objectdb.o.BYR.s(BYR.java:113)
at com.objectdb.o.BYR.z(BYR.java:194)
at com.objectdb.o.OBI.r(OBI.java:272)
at com.objectdb.o.OBI.U5(OBI.java:232)
at com.objectdb.o.BQI.Ve(BQI.java:134)
at com.objectdb.o.GQI.Us(GQI.java:130)
at com.objectdb.o.PRG.aa(PRG.java:596)
at com.objectdb.o.QRM.UR(QRM.java:256)
at com.objectdb.o.MST.UR(MST.java:878)
at com.objectdb.o.WRA.UR(WRA.java:286)
at com.objectdb.o.WSM.UR(WSM.java:113)
at com.objectdb.o.WRA.UR(WRA.java:286)
at com.objectdb.o.WSN.UR(WSN.java:422)
at com.objectdb.o.QRR.g(QRR.java:220)
at com.objectdb.o.QRR.b(QRR.java:143)
at com.objectdb.jpa.JpaQuery.getSingleResult(JpaQuery.java:610)
at com.ibm.check.cmvc2git.database.Database.getNotesMaxaddDate(Database.java:315)
at com.ibm.check.cmvc2git.database.NotesSync.updateDatabase(NotesSync.java:51)
at com.ibm.check.cmvc2git.database.CMVCdbSync.syncDB(CMVCdbSync.java:99)
at com.ibm.check.cmvc2git.CMVCSync.syncDB(CMVCSync.java:200)
at com.ibm.check.cmvc2git.CMVCSync.runCommand(CMVCSync.java:115)
at com.ibm.check.cmvc2git.CMVCSync.main(CMVCSync.java:61)

There seems to be a link between how often I commit and the results of success or failure.

If I commit after every update it seems to work.

If I commit every 1000 updates it fails.

Are you sure that there are no Notes instances in the database yet when the exception is thrown?

Please open a support ticket to discuss options to solve this problem. If you can submit the entire Doctor report (in the support ticket) it might help in understanding the problem.

If you are using EntityManager's flush you should try build 2.2.3_04 that fixes a critical bug.

Every time I remove the database and rebuild it I get an error but each time it moves.

It's always in the same method in my code.

I have a if to reduce memory in my loop in this function

   if( (count % config.getUpdateFreq()) == 0)
   {

        em.getTransaction().commit();

        em.getTransaction().begin();

  }

After much trying to reliably recreate this problem.

I've decided to give up on the above snippet of code and replace it with the following I found in the manual.

   if( (count % config.getUpdateFreq()) == 0)
   {

            em.flush();

            em.clear();

   }

Thank you for the update. I am more worried about the Doctor errors than the query exception. Please keep running the Doctor frequently to verify that the problem was solved.

Edit

This report is similar to the report in issue #1977  that was fixed now in version 2.7.1.

The new version fixes a critical bug that in some cases (rare but possible) may cause database corruption, so it is highly recommended to use the new build.