ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexei Scherbakov <alexey.scherbak...@gmail.com>
Subject Re: Using SQL to query Object field stored in Cache ?
Date Wed, 18 May 2016 12:47:15 GMT
Hi,

Currently Ignite SQL engine does not support Object type in query
conditions.
It doesn't know how to compare any type(which Object can hold) with
Integer, on example.

Possible workarounds for that would be:
1) Use different fields for different types like:
stringValue, intVaue, etc.
This allows you to use conditions like:
AttributeCache.propertyName = " + "\'" + "height" + "\' "
+ "and AttributeCache.intFalue > 182

2) Use user defined SQL functions like:

public class ParamsComparator {
    @QuerySqlFunction
    public static boolean compareLongParam(Object param, Long arg) {
        return param instanceof Long && ((Long)param).equals(arg);
    }

    @QuerySqlFunction
    public static boolean compareStringParam(Object param, String arg) {
        return param instanceof String && ((String)param).equals(arg);
    }
}


See fully working example in the attachment. Note the function code
must present on all cluster nodes for this to work.


I recommend using first approach, because it's more elegant and allows
to use indexes on *value fields.


Did this help?




2016-05-17 20:56 GMT+03:00 David Robinson <drobin1437@gmail.com>:

> With Ignite 1.5.0:
>
> I have two caches.
>
> Cache 1 stores a Person object like this:
>
> personCache.put(id, PersonObj1);
>
> The Person class has only a single field in it declared like this:
>
> @QuerySqlField(index = true)
> private int personId;
>
> Cache 2 stores a Person Attribute object like this:
> AttributeCache.put(id, PersonAttributeObj1);
>
> The Attribute class has 3 fields in it:
>
> @QuerySqlField(index = true)
> private int personId;
>
> @QuerySqlField(index = false)
> private String attributeName;
>
> @QuerySqlField(index = false)
> private Object attributeValue;
>
> A PersonAttribute value can be any object type - for example, if attributeName is "height",
then
>
> attributeValue could be a Float: 182.88
>
> If attributeName is "haircolor", then attributeValue could be a String: "brown".
>
> I need to be able to write a SQL join query between the Person and Attribute caches and
find all
>
> of the people with height > 182.
>
> When I try to use a SQL join query...something like below (it doesn't matter if the 182
is set
>
> as a attribute or hard coded in the query)
>
>
> SqlFieldsQuery sql = new SqlFieldsQuery(
>        "select PersonCache.personId  "
>                + "\"" + personCacheName + "\"" + "from PersonCache, "
>                + "\"" + attributeCacheName + "\"" +  ".AttributeCache where "
>                + "PersonCache.personId = AttributeCache.personId "
>                + "and AttributeCache.propertyName = " + "\'" + "height" + "\' "
>                + "and AttributeCache.value > 182");
>
> I received the following exception from the Ignite Server:
>
> Caused by: class org.apache.ignite.binary.BinaryObjectException: Invalid flag value:
-128
> 	at org.apache.ignite.internal.binary.BinaryReaderExImpl.deserialize(BinaryReaderExImpl.java:1632)
> 	at org.apache.ignite.internal.binary.GridBinaryMarshaller.deserialize(GridBinaryMarshaller.java:292)
> 	at org.apache.ignite.internal.binary.BinaryMarshaller.unmarshal(BinaryMarshaller.java:112)
> 	at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$5.deserialize(IgniteH2Indexing.java:1491)
> 	at org.h2.util.Utils.deserialize(Utils.java:392)
>
> If value > 182 is taken out of the query, it runs fine.
>
> Ignite does not appear to know how to deserialize an "Object" field correctly to perform
a comparison in SQL.
> What is the recommended Ignite way to store Object types like this and be able to compare/query
them
> in Ignite SQL ? I do not know ahead of time if something will be a Long or Integer or
String, etc.
>
> Thank you,
>
>


-- 

Best regards,
Alexei Scherbakov

Mime
View raw message