Query on date field got exeception

[Roberto Franchini]

Hi,
my CI is broken. I didn't changed my code, so maybe something is
changed in orient:

Error Message

Error on execution of command: OSQLQuery [text=select * from Item
where date > '2011-06-29' LIMIT 20]

Stacktrace

com.orientechnologies.orient.core.exception.OCommandExecutionException:
Error on execution of command: OSQLQuery [text=select * from Item
where date > '2011-06-29' LIMIT 20]
at com.orientechnologies.orient.core.storage.OStorageEmbedded.command(OStorageEmbedded.java:70)
at com.orientechnologies.orient.core.sql.query.OSQLQuery.run(OSQLQuery.java:62)
at com.orientechnologies.orient.core.sql.query.OSQLSynchQuery.run(OSQLSynchQuery.java:73)
at com.orientechnologies.orient.core.query.OQueryAbstract.execute(OQueryAbstract.java:38)
at com.orientechnologies.orient.core.db.record.ODatabaseRecordAbstract.query(ODatabaseRecordAbstract.java:274)
at com.orientechnologies.orient.core.db.ODatabaseRecordWrapperAbstract.query(ODatabaseRecordWrapperAbstract.java:169)
[cut]
Caused by: java.lang.ClassCastException: java.util.Date cannot be cast
to java.lang.String
at java.lang.String.compareTo(String.java:109)
at com.orientechnologies.common.collection.OMVRBTree.getEntry(OMVRBTree.java:394)
at com.orientechnologies.common.collection.OMVRBTree.getHigherEntry(OMVRBTree.java:498)
at com.orientechnologies.common.collection.OMVRBTree$NavigableSubMap.absLowest(OMVRBTree.java:1361)
at com.orientechnologies.common.collection.OMVRBTree$AscendingSubMap$AscendingEntrySetView.iterator(OMVRBTree.java:1805)
at java.util.AbstractMap$2$1.<init>(AbstractMap.java:378)
at java.util.AbstractMap$2.iterator(AbstractMap.java:377)
at com.orientechnologies.orient.core.index.OIndexMVRBTreeAbstract.getValuesMajor(OIndexMVRBTreeAbstract.java:231)
at com.orientechnologies.orient.core.index.OIndexAbstractDelegate.getValuesMajor(OIndexAbstractDelegate.java:96)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchIndexedProperty(OCommandExecutorSQLSelect.java:418)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.analyzeQueryBranch(OCommandExecutorSQLSelect.java:356)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchForIndexes(OCommandExecutorSQLSelect.java:340)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchInClasses(OCommandExecutorSQLSelect.java:635)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.execute(OCommandExecutorSQLSelect.java:172)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.execute(OCommandExecutorSQLDelegate.java:51)
at com.orientechnologies.orient.core.storage.OStorageEmbedded.command(OStorageEmbedded.java:65)

cheers,
thanks in advance

FRANK
--
Roberto Franchini
L'impossibile è inevitabile.
http://www.celi.it
http://www.blogmeter.it
Tel +39.011.562.71.15
jabber:ro.fra...@gmail.com skype:ro.franchini

[Luca Garulli]

Hi,

maybe your indexes are dirty (due to previous OrientDB snapshots?). Could you rebuild the indexes?

Launch the console and connect to your database:

> rebuild index *

Lvc@

[Roberto Franchini]

On Fri, Jul 8, 2011 at 1:22 PM, Luca Garulli <l.ga...@gmail.com> wrote:
> Hi,
> maybe your indexes are dirty (due to previous OrientDB snapshots?). Could
> you rebuild the indexes?
> Launch the console and connect to your database:
>> rebuild index *

No, each test rebuilds the db from scratch, and then connect to it
"local:" mode.

Try this code, where I past some code from orient's DateTests. What's wrong?

@Test
public void shouldInsertBigAmountOfDocumentsShardingMassive() {
String dbUrl = "local:./working/storage/massive";
createDB(dbUrl);

int k = 0;
for (int j = 0; j < 10; j++) {
ODatabaseDocumentTx db = new ODatabaseDocumentTx(dbUrl);

db.open("admin", "admin");
db.declareIntent(new OIntentMassiveInsert());
for (int i = 0; i < 10; i++) {
ODocument doc = new ODocument(db, "Item");

create(k++, doc);
db.save(doc, "Item");
}
System.out.println("saved:: " + k);
db.declareIntent(null);
db.close();

}

ODatabaseDocumentTx db = new ODatabaseDocumentTx(dbUrl);

db.open("admin", "admin");

Calendar instance = Calendar.getInstance();

instance.add(Calendar.HOUR_OF_DAY, -5);

String dateAsString =
db.getStorage().getConfiguration().getDateFormatInstance().format(instance.getTime());

String query = "select * from Item where date >= " + dateAsString;

System.out.println(query);

OSQLSynchQuery<ODocument> syncQuery = new OSQLSynchQuery<ODocument>(query);

List<ODocument> documents = db.command(syncQuery).execute();

db.close();

}

private ODocument create(int id, ODocument doc) {
String itemKey = Integer.valueOf(id).toString();

doc.setClassName("item");
doc.field("collection", "prova");
doc.field("itemKey", itemKey);
doc.field("article_articleID", itemKey);
doc.field("article_key", itemKey);
String contents =
"OrientDB is a deeply scalable Document-Graph DBMS with the
flexibility of the Document databases and the power to manage links of
the Graph databases. It can work in schema-less mode, schema-full or a
mix of both. Supports advanced features such as ACID Transactions,
Fast Indexes, Native and SQL queries. It imports and exports documents
in JSON. Graphs of hundreads of linked documents can be retrieved all
in memory in few milliseconds without executing costly JOIN such as
the Relational DBMSs do. OrientDB uses a new indexing algorithm called
MVRB-Tree, derived from the Red-Black Tree and from the B+Tree with
benefits of both: fast insertion and ultra fast lookup. The
transactional engine can run in distributed systems supporting up to
9.223.372.036 Billions of records for the maximum capacity of
19.807.040.628.566.084 Terabytes of data distributed on multiple disks
in multiple nodes. OrientDB is FREE for any use. Open Source License
Apache 2.0. ";
doc.field("contents", contents);
doc.field("article_title", "orientDB");
doc.field("uri", "file:/path/to/original/prova-" + itemKey + ".txt");
doc.field("permalink", "http://www.host.it/pagina/di/prova/#" + itemKey);
doc.field("host", "www.host.it");
Calendar instance = Calendar.getInstance();

instance.add(Calendar.HOUR_OF_DAY, -id);
doc.field("date", instance.getTime(), OType.DATETIME);

return doc;

}

private void createDB(String dbUrl) {
ODatabaseDocumentTx db = new ODatabaseDocumentTx(dbUrl);
if (db.exists()) db.delete();
db.create();
OSchema schema = db.getMetadata().getSchema();
OClass itemClass = schema.createClass("Item");
OClass annotation = schema.createClass("Annotation");
annotation.createProperty("type",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);

OClass analysis = schema.createClass("Analysis");
analysis.createProperty("analyzedText", OType.STRING);
analysis.createProperty("annotations", OType.LINKLIST, annotation);
analysis.createProperty("date", OType.DATE).createIndex(INDEX_TYPE.NOTUNIQUE);

itemClass.createProperty("itemKey",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("collection",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("date",
OType.DATE).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("decade",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("type",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("article_key",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("article_articleID",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("issue_key",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("page_key",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("bobina",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("publication_code",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("issue_edition",
OType.STRING).createIndex(INDEX_TYPE.NOTUNIQUE);
itemClass.createProperty("issue_edition_int",
OType.INTEGER).createIndex(INDEX_TYPE.NOTUNIQUE);

itemClass.createProperty("analysis", OType.LINKLIST, analysis);

analysis.createProperty("item", OType.LINK, itemClass);
// itemClass.setOverSize(2);

schema.save();

schema.reload();

db.close();

[Luca Garulli]

Ciao Roberto,

this is the result of latest additions to the SQL engine made by some contributors to use index in better way. Before now your code didn't use the index with that query.

Now works but there was a problem on conversion: you're using a String against Date types. I've just committed the fix to execute the conversion also when the index is used on query.

SVN r3368.

Lvc@

[Roberto Franchini]

On Fri, Jul 8, 2011 at 4:23 PM, Luca Garulli <l.ga...@gmail.com> wrote:
> Ciao Roberto,
> this is the result of latest additions to the SQL engine made by some
> contributors to use index in better way. Before now your code didn't use the
> index with that query.
> Now works but there was a problem on conversion: you're using a String
> against Date types. I've just committed the fix to execute the conversion
> also when the index is used on query.
> SVN r3368.
> Lvc@
>

Ok, now it works, thx!
FRANK

[Roberto Franchini]

On Fri, Jul 8, 2011 at 5:24 PM, Roberto Franchini <fran...@celi.it> wrote:
> On Fri, Jul 8, 2011 at 4:23 PM, Luca Garulli <l.ga...@gmail.com> wrote:
>> Ciao Roberto,

[cut]

>>
>
> Ok, now it works, thx!
> FRANK

I was wrong. Query with range (pagination) and date clause aren't
workink anymore.

Add thi line to the test method i send before:
....
// date query and range
instance = Calendar.getInstance();
instance.add(Calendar.HOUR_OF_DAY, -20);
dateAsString =
db.getStorage().getConfiguration().getDateFormatInstance().format(instance.getTime());
query = "select * from Item where date > '" + dateAsString + "' LIMIT 20";

List<ODocument> resultset = db.query(new OSQLSynchQuery<ODocument>(query));
while (!resultset.isEmpty()) {
ORID last = resultset.get(resultset.size() - 1).getIdentity();
System.out.println("last:: " + last);

query = "select * from Item where date >= '" + dateAsString + "'
range " + last.next() + " LIMIT 20";
resultset = db.query(new OSQLSynchQuery<ODocument>(query));
}

....

I've got a loop.
cheers,

[Luca Garulli]

Hi,

good catch! Range was ignored when indexes are used. Fixed as SVN r3370.

Lvc@