SQL query question

classic Classic list List threaded Threaded
7 messages Options
Ognen Duzlevski Ognen Duzlevski
Reply | Threaded
Open this post in threaded view
|

SQL query question

Hello all,

Does every object that is being used via sql queries and has indexable fields, have to have a field named id of type UUID?

The following example doesn't work as presented; however, if I change the cache to be of type [UUID,Foo] and then enable the val id = UUID.randomUUID line that is commented out at the bottom of the file, it all works. When I say it doesn't work in the first case, I am getting the following errors:

[08:51:38] Topology snapshot [ver=1, nodes=1, CPUs=8, heap=1.3GB]
**** Configure/Create Cache ****
**** Creating Data ****
[error] (run-main-0) org.apache.ignite.cache.CachePartialUpdateException: Failed to update keys (retry update if possible).: [1092460711]
org.apache.ignite.cache.CachePartialUpdateException: Failed to update keys (retry update if possible).: [1092460711]
at org.apache.ignite.internal.processors.cache.GridCacheUtils.convertToCacheException(GridCacheUtils.java:1805)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.cacheException(IgniteCacheProxy.java:1450)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.putIfAbsent(IgniteCacheProxy.java:828)
at org.apache.ignite.scalar.pimps.ScalarCachePimp.putx$(ScalarCachePimp.scala:224)
at org.apache.ignite.scalar.pimps.ScalarCachePimp.$plus$eq(ScalarCachePimp.scala:280)
at ScalaCacheQueryExample$.main(test.scala:42)
at ScalaCacheQueryExample.main(test.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)


======== code starts here ========

import java.util._

import org.apache.ignite.cache.CacheMode._
import org.apache.ignite.cache.affinity.AffinityKey
import org.apache.ignite.configuration.CacheConfiguration
import org.apache.ignite.scalar.scalar
import org.apache.ignite.scalar.scalar._

import org.apache.ignite.configuration._
import org.apache.ignite.cache._
import org.apache.ignite._

import scala.collection.JavaConversions._

import scala.util.Random


object ScalaCacheQueryExample {

  def main(args: Array[String]) {

    println("**** Start Ignite ****")
    // Start Ignite -- can we just connect?
    Ignition.setClientMode(false)
    val ign = Ignition.start("example-ignite.xml")

    println("**** Configure/Create Cache ****")
    val fooCacheCfg = new CacheConfiguration[Int, Foo]()
    fooCacheCfg.setName("TestCache")
    fooCacheCfg.setCacheMode(CacheMode.PARTITIONED)
    fooCacheCfg.setIndexedTypes(classOf[Int], classOf[Foo])

    val fCache:IgniteCache[Int,Foo] = ign.getOrCreateCache[Int, Foo](fooCacheCfg)

    println("**** Creating Data ****")
    val r = new Random()
    // fCache.put(123, Foo(id=Math.abs(r.nextInt()), value=1000, name="bob"))
    val f1 = Foo(Math.abs(r.nextInt),1000,"bob")
    val f2 = Foo(Math.abs(r.nextInt),2000,"nancy")
     
    fCache += (f1.id -> f1)
    fCache += (f2.id -> f2)

    println("**** Starting Query ****")
    print(fCache.sql("value > 1000").getAll.map(e => e.getValue))
  }
}

private case class Foo(
    @ScalarCacheQuerySqlField
    id: Int,

    @ScalarCacheQuerySqlField
    value: Int,

    @ScalarCacheQuerySqlField
    name: String
  )
/* COMMENTED OUT */
/* {

    @ScalarCacheQuerySqlField
    val id = UUID.randomUUID
  }*/
yakov yakov
Reply | Threaded
Open this post in threaded view
|

Re: SQL query question

Ognen,

UUID is not a requirement for id fields at all. Ignite does not have such limitations. You can use any field name, any type, etc

When I run your original code I have:

Exception in thread "main" org.apache.ignite.cache.CachePartialUpdateException: Failed to update keys (retry update if possible).: [1026536079]
at org.apache.ignite.internal.processors.cache.GridCacheUtils.convertToCacheException(GridCacheUtils.java:1729)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.cacheException(IgniteCacheProxy.java:1511)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.putIfAbsent(IgniteCacheProxy.java:905)
at org.apache.ignite.scalar.pimps.ScalarCachePimp.putx$(ScalarCachePimp.scala:220)
at org.apache.ignite.scalar.pimps.ScalarCachePimp.$plus$eq(ScalarCachePimp.scala:268)
at org.apache.ignite.scalar.ScalarCacheAffinityExample$.main(ScalarCacheAffinityExample.scala:43)
at org.apache.ignite.scalar.ScalarCacheAffinityExample.main(ScalarCacheAffinityExample.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
Caused by: class org.apache.ignite.internal.processors.cache.CachePartialUpdateCheckedException: Failed to update keys (retry update if possible).: [1026536079]
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture.addFailedKeys(GridNearAtomicUpdateFuture.java:931)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture.onResult(GridNearAtomicUpdateFuture.java:364)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture$2.apply(GridNearAtomicUpdateFuture.java:822)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture$2.apply(GridNearAtomicUpdateFuture.java:817)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateAllAsyncInternal0(GridDhtAtomicCache.java:1181)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateAllAsyncInternal(GridDhtAtomicCache.java:988)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture.mapSingle(GridNearAtomicUpdateFuture.java:816)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture.map0(GridNearAtomicUpdateFuture.java:625)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture.mapOnTopology(GridNearAtomicUpdateFuture.java:473)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateFuture.map(GridNearAtomicUpdateFuture.java:307)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache$14.apply(GridDhtAtomicCache.java:772)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache$14.apply(GridDhtAtomicCache.java:770)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.asyncOp(GridDhtAtomicCache.java:580)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateAllAsync0(GridDhtAtomicCache.java:770)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.putAsync0(GridDhtAtomicCache.java:317)
at org.apache.ignite.internal.processors.cache.GridCacheAdapter.putAsync(GridCacheAdapter.java:2268)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.putIfAbsentAsync(GridDhtAtomicCache.java:364)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.putIfAbsent(GridDhtAtomicCache.java:357)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.putIfAbsent(IgniteCacheProxy.java:898)
... 9 more
Suppressed: class org.apache.ignite.IgniteCheckedException: Failed to update keys on primary node.
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridNearAtomicUpdateResponse.addFailedKey(GridNearAtomicUpdateResponse.java:334)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateSingle(GridDhtAtomicCache.java:1798)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateAllAsyncInternal0(GridDhtAtomicCache.java:1108)
... 23 more
Suppressed: class org.apache.ignite.internal.processors.cache.GridCacheIndexUpdateException: Failed to update index, incorrect key class [expCls=int, actualCls=java.lang.Integer]
at org.apache.ignite.internal.processors.cache.GridCacheMapEntry.updateIndex(GridCacheMapEntry.java:3458)
at org.apache.ignite.internal.processors.cache.GridCacheMapEntry.innerUpdate(GridCacheMapEntry.java:2040)
at org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.updateSingle(GridDhtAtomicCache.java:1667)
... 24 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to update index, incorrect key class [expCls=int, actualCls=java.lang.Integer]
at org.apache.ignite.internal.processors.query.GridQueryProcessor.store(GridQueryProcessor.java:471)
at org.apache.ignite.internal.processors.cache.query.GridCacheQueryManager.store(GridCacheQueryManager.java:353)
at org.apache.ignite.internal.processors.cache.GridCacheMapEntry.updateIndex(GridCacheMapEntry.java:3451)
... 26 more

Pay attention:  Suppressed: class org.apache.ignite.internal.processors.cache.GridCacheIndexUpdateException: Failed to update index, incorrect key class [expCls=int, actualCls=java.lang.Integer]

When I replace "Int" with Integer example starts working.

--Yakov

2015-05-28 17:00 GMT+03:00 Ognen Duzlevski <[hidden email]>:
Hello all,

Does every object that is being used via sql queries and has indexable fields, have to have a field named id of type UUID?

The following example doesn't work as presented; however, if I change the cache to be of type [UUID,Foo] and then enable the val id = UUID.randomUUID line that is commented out at the bottom of the file, it all works. When I say it doesn't work in the first case, I am getting the following errors:

[08:51:38] Topology snapshot [ver=1, nodes=1, CPUs=8, heap=1.3GB]
**** Configure/Create Cache ****
**** Creating Data ****
[error] (run-main-0) org.apache.ignite.cache.CachePartialUpdateException: Failed to update keys (retry update if possible).: [1092460711]
org.apache.ignite.cache.CachePartialUpdateException: Failed to update keys (retry update if possible).: [1092460711]
at org.apache.ignite.internal.processors.cache.GridCacheUtils.convertToCacheException(GridCacheUtils.java:1805)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.cacheException(IgniteCacheProxy.java:1450)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.putIfAbsent(IgniteCacheProxy.java:828)
at org.apache.ignite.scalar.pimps.ScalarCachePimp.putx$(ScalarCachePimp.scala:224)
at org.apache.ignite.scalar.pimps.ScalarCachePimp.$plus$eq(ScalarCachePimp.scala:280)
at ScalaCacheQueryExample$.main(test.scala:42)
at ScalaCacheQueryExample.main(test.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)


======== code starts here ========

import java.util._

import org.apache.ignite.cache.CacheMode._
import org.apache.ignite.cache.affinity.AffinityKey
import org.apache.ignite.configuration.CacheConfiguration
import org.apache.ignite.scalar.scalar
import org.apache.ignite.scalar.scalar._

import org.apache.ignite.configuration._
import org.apache.ignite.cache._
import org.apache.ignite._

import scala.collection.JavaConversions._

import scala.util.Random


object ScalaCacheQueryExample {

  def main(args: Array[String]) {

    println("**** Start Ignite ****")
    // Start Ignite -- can we just connect?
    Ignition.setClientMode(false)
    val ign = Ignition.start("example-ignite.xml")

    println("**** Configure/Create Cache ****")
    val fooCacheCfg = new CacheConfiguration[Int, Foo]()
    fooCacheCfg.setName("TestCache")
    fooCacheCfg.setCacheMode(CacheMode.PARTITIONED)
    fooCacheCfg.setIndexedTypes(classOf[Int], classOf[Foo])

    val fCache:IgniteCache[Int,Foo] = ign.getOrCreateCache[Int, Foo](fooCacheCfg)

    println("**** Creating Data ****")
    val r = new Random()
    // fCache.put(123, Foo(id=Math.abs(r.nextInt()), value=1000, name="bob"))
    val f1 = Foo(Math.abs(r.nextInt),1000,"bob")
    val f2 = Foo(Math.abs(r.nextInt),2000,"nancy")
     
    fCache += (f1.id -> f1)
    fCache += (f2.id -> f2)

    println("**** Starting Query ****")
    print(fCache.sql("value > 1000").getAll.map(e => e.getValue))
  }
}

private case class Foo(
    @ScalarCacheQuerySqlField
    id: Int,

    @ScalarCacheQuerySqlField
    value: Int,

    @ScalarCacheQuerySqlField
    name: String
  )
/* COMMENTED OUT */
/* {

    @ScalarCacheQuerySqlField
    val id = UUID.randomUUID
  }*/

Ognen Duzlevski Ognen Duzlevski
Reply | Threaded
Open this post in threaded view
|

Re: SQL query question

Yahov,

On Fri, May 29, 2015 at 7:30 AM, Yakov Zhdanov <[hidden email]> wrote:

When I replace "Int" with Integer example starts working.


Ok - well, that was unexpected - why does using Int not work and Integer does? What am I missing here? :)

Thanks!
Ognen 
dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: SQL query question



On Fri, May 29, 2015 at 6:31 AM, Ognen Duzlevski <[hidden email]> wrote:
Yahov,

On Fri, May 29, 2015 at 7:30 AM, Yakov Zhdanov <[hidden email]> wrote:

When I replace "Int" with Integer example starts working.


Ok - well, that was unexpected - why does using Int not work and Integer does? What am I missing here? :)


I also agree. I think it should not matter whether we use primitives or boxed types. Is it hard to fix? 
Alexey Kuznetsov Alexey Kuznetsov
Reply | Threaded
Open this post in threaded view
|

Re: SQL query question

In reply to this post by Ognen Duzlevski
After investigation this issue I found the following:

It seems that we have here a special case of Scala and Java compatibility.
In Scala it is possible to use "Int" to work with cache, and actually "Int" in Scala compiled into primitive "int". But in Java we can not use primitives as cache keys we could only use objects.

But also in your code you registered indexed types and Scala "Int" was taken as primitive "int".
And when you try to insert into cache boxing take place and "Integer" type used to update indexes.

So, I think we could handle this inside Ignite by translating primitive types to corresponding
 object types in CacheCfg.setIndexedTypes(...) method.

I created https://issues.apache.org/jira/browse/IGNITE-983 for this.

For now you could use java.lang.Integer.
Or fix IGNITE-983 (I think it will be trivial).
Ognen Duzlevski Ognen Duzlevski
Reply | Threaded
Open this post in threaded view
|

Re: SQL query question

Aleksej, I would be happy to give it a whirl if we can take this off list and you can give me some pointers :)
Ognen

On Tue, Jun 2, 2015 at 10:43 AM, Alexey Kuznetsov <[hidden email]> wrote:
After investigation this issue I found the following:

It seems that we have here a special case of Scala and Java compatibility.
In Scala it is possible to use "Int" to work with cache, and actually "Int"
in Scala compiled into primitive "int". But in Java we can not use
primitives as cache keys we could only use objects.

But also in your code you registered indexed types and Scala "Int" was taken
as primitive "int".
And when you try to insert into cache boxing take place and "Integer" type
used to update indexes.

So, I think we could handle this inside Ignite by translating primitive
types to corresponding
 object types in CacheCfg.setIndexedTypes(...) method.

I created https://issues.apache.org/jira/browse/IGNITE-983 for this.

For now you could use java.lang.Integer.
Or fix IGNITE-983 (I think it will be trivial).




--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-query-question-tp426p447.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Alexey Kuznetsov Alexey Kuznetsov
Reply | Threaded
Open this post in threaded view
|

Re: SQL query question

Ognen, 

I fixed it already.
Fix is simple - translate primitive types to object types.
Pushed fix to ignite-983 branch.
If all tests passed on TC it will be merged into sprint-5 and will be available in next upcoming release.



On Wed, Jun 3, 2015 at 8:09 AM, Ognen Duzlevski <[hidden email]> wrote:
Aleksej, I would be happy to give it a whirl if we can take this off list and you can give me some pointers :)
Ognen

On Tue, Jun 2, 2015 at 10:43 AM, Alexey Kuznetsov <[hidden email]> wrote:
After investigation this issue I found the following:

It seems that we have here a special case of Scala and Java compatibility.
In Scala it is possible to use "Int" to work with cache, and actually "Int"
in Scala compiled into primitive "int". But in Java we can not use
primitives as cache keys we could only use objects.

But also in your code you registered indexed types and Scala "Int" was taken
as primitive "int".
And when you try to insert into cache boxing take place and "Integer" type
used to update indexes.

So, I think we could handle this inside Ignite by translating primitive
types to corresponding
 object types in CacheCfg.setIndexedTypes(...) method.

I created https://issues.apache.org/jira/browse/IGNITE-983 for this.

For now you could use java.lang.Integer.
Or fix IGNITE-983 (I think it will be trivial).




--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-query-question-tp426p447.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.




--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com