You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Floris Van Nee <fl...@Optiver.com> on 2018/08/03 13:58:23 UTC
SQL SELECT with AffinityKeyMapped - no results
Hi all,
I have defined two classes in Java - one for a key and one for a value. Suppose they look like this:
public static class Key implements Serializable {
public String a;
@QuerySqlField
@AffinityKeyMapped
public String b;
}
public static class Value implements Serializable {
@QuerySqlField
public int c;
}
I then define a distributed cache (in Java) for this key/value pair and fill it with values (in Java).
Now, I run the following in SQL:
SELECT * FROM kv_table;
I indeed see results of all the stuff that I inserted into the cache.
However, when I try to select a certain value, it returns no results:
SELECT * FROM kv_table WHERE b = 'test';
I get expected results when filtering on any column that is not defined as AffinityKeyMapped.
The only case where I get wrong results is for the AffinityKeyMapped column - I always get a wrong result (most of the time zero rows, for some values I do get one row, but I expect to see more rows). Also when I remove the AffinityKeyMapped annotation and run my test again, everything works as expected.
I run just a single server node and execute my query using the SQLLine tool that Ignite ships with.
Is it possible that there is a bug in the code that handles this affinity key mapping for SQL queries? Or am I doing something wrong here?
-Floris
Re: SQL SELECT with AffinityKeyMapped - no results
Posted by "kcheng.mvp" <kc...@gmail.com>.
I ran into the same issue. if we can not use @AffinityKeyMapped without any
workaround. then Can I use AffinityKey as the document address
Object personKey1 = new AffinityKey("myPersonId1", "myCompanyId");
Object personKey2 = new AffinityKey("myPersonId2", "myCompanyId");
Person p1 = new Person(personKey1, ...);
Person p2 = new Person(personKey2, ...);
// Both, the company and the person objects will be cached on the same node.
comCache.put("myCompanyId", new Company(..));
perCache.put(personKey1, p1);
perCache.put(personKey2, p2);
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL SELECT with AffinityKeyMapped - no results
Posted by Floris Van Nee <fl...@Optiver.com>.
I tried your suggestion but unfortunately to no effect yet. I restarted the cluster every time I tried something new.
It seems it is now the following that causes a problem:
cfg.setKeyConfiguration(new CacheKeyConfiguration(TestKey.class.getName(), "b")); // using this line, i get the incorrect behavior
cfg.setKeyConfiguration(new CacheKeyConfiguration(TestKey.class.getName(), "some_field_that_does_not_exist")); // this results in ok query behavior (but obviously not the affinity I want as the field does not exist. i think it just takes the full key as affinity here because they field does not exist
The lines that configure my cache are now:
CacheConfiguration<TestKey, TestValue> cfg = new CacheConfiguration<>(TEST);
cfg.setSqlSchema("PUBLIC");
cfg.setName("Test");
cfg.setKeyConfiguration(new CacheKeyConfiguration(TestKey.class.getName(), "b"));
cfg.setIndexedTypes(TestKey.class, TestValue.class);
-Floris
________________________________
From: Denis Mekhanikov <dm...@gmail.com>
Sent: Friday, August 3, 2018 5:35 PM
To: user@ignite.apache.org
Subject: Re: SQL SELECT with AffinityKeyMapped - no results [External]
Floris,
Binary metadata may be saved in work/binary_meta directory.
Try cleaning this directory and see if it helps. You will also need to restart the whole cluster.
Note, that it may lead to impossibility to read persisted data, if you have any.
Denis
пт, 3 авг. 2018 г. в 18:15, Floris Van Nee <fl...@optiver.com>>:
Thank you for your quick reply. That does look a lot like what I’m experiencing.
However, I did some testing but so far I did not get the workaround to work. I put the following in the XML config file:
<property name="binaryConfiguration">
<bean class="org.apache.ignite.configuration.BinaryConfiguration">
<property name="classNames">
<list>
<value>org.apache.ignite.examples.streaming.TestKey</value>
</list>
</property>
</bean>
</property>
Furthermore, when I create the cache, I pass a config with:
cfg.setKeyConfiguration(new CacheKeyConfiguration(TestKey.class.getName(), "b"));
According to the ticket this should be sufficient. Could I still be missing something here?
-Floris
From: Denis Mekhanikov [mailto:dmekhanikov@gmail.com<ma...@gmail.com>]
Sent: Friday 03 August 2018 4:05 PM
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: SQL SELECT with AffinityKeyMapped - no results [External]
Floris,
Most probably, you hit a bug, that was introduced in Ignite 2.0: https://issues.apache.org/jira/browse/IGNITE-5795<https://webvpn.optiver.com/jira/browse/,DanaInfo=.aituxixFhxjmsqM26w,SSL+IGNITE-5795>
Because of this bug @AffinityKeyMapped annotation is ignored in classes, that are used in query entity configuration.
As far as I can see, this is exactly your case.
It's going to be fixed in Ignite 2.7.
There is a workaround for this problem: you can list the problematic classes in BinaryConfiguration#classNames<https://webvpn.optiver.com/releases/latest/javadoc/org/apache/ignite/configuration/,DanaInfo=.aihplxjFhxjmsqM26w,SSL+BinaryConfiguration.html#setClassNames-java.util.Collection-> configuration property.
Binary configuration should be specified as IgniteConfiguration#binaryConfiguration<https://webvpn.optiver.com/releases/latest/javadoc/org/apache/ignite/configuration/,DanaInfo=.aihplxjFhxjmsqM26w,SSL+IgniteConfiguration.html#setBinaryConfiguration-org.apache.ignite.configuration.BinaryConfiguration->.
This configuration should be the same on all nodes.
You may also need to configure CacheConfiguration#keyConfiguration<https://webvpn.optiver.com/releases/latest/javadoc/org/apache/ignite/configuration/,DanaInfo=.aihplxjFhxjmsqM26w,SSL+CacheConfiguration.html#setKeyConfiguration-org.apache.ignite.cache.CacheKeyConfiguration...-> for your cache.
Denis
пт, 3 авг. 2018 г. в 16:58, Floris Van Nee <fl...@optiver.com>>:
Hi all,
I have defined two classes in Java – one for a key and one for a value. Suppose they look like this:
public static class Key implements Serializable {
public String a;
@QuerySqlField
@AffinityKeyMapped
public String b;
}
public static class Value implements Serializable {
@QuerySqlField
public int c;
}
I then define a distributed cache (in Java) for this key/value pair and fill it with values (in Java).
Now, I run the following in SQL:
SELECT * FROM kv_table;
I indeed see results of all the stuff that I inserted into the cache.
However, when I try to select a certain value, it returns no results:
SELECT * FROM kv_table WHERE b = ‘test’;
I get expected results when filtering on any column that is not defined as AffinityKeyMapped.
The only case where I get wrong results is for the AffinityKeyMapped column - I always get a wrong result (most of the time zero rows, for some values I do get one row, but I expect to see more rows). Also when I remove the AffinityKeyMapped annotation and run my test again, everything works as expected.
I run just a single server node and execute my query using the SQLLine tool that Ignite ships with.
Is it possible that there is a bug in the code that handles this affinity key mapping for SQL queries? Or am I doing something wrong here?
-Floris
Re: SQL SELECT with AffinityKeyMapped - no results
Posted by Denis Mekhanikov <dm...@gmail.com>.
Floris,
Binary metadata may be saved in work/binary_meta directory.
Try cleaning this directory and see if it helps. You will also need to
restart the whole cluster.
Note, that it may lead to impossibility to read persisted data, if you have
any.
Denis
пт, 3 авг. 2018 г. в 18:15, Floris Van Nee <fl...@optiver.com>:
> Thank you for your quick reply. That does look a lot like what I’m
> experiencing.
>
>
>
> However, I did some testing but so far I did not get the workaround to
> work. I put the following in the XML config file:
>
>
>
> <property name="binaryConfiguration">
>
> <bean
> class="org.apache.ignite.configuration.BinaryConfiguration">
>
> <property name="classNames">
>
> <list>
>
>
> <value>org.apache.ignite.examples.streaming.TestKey</value>
>
> </list>
>
> </property>
>
> </bean>
>
> </property>
>
>
>
> Furthermore, when I create the cache, I pass a config with:
>
> cfg.setKeyConfiguration(new CacheKeyConfiguration(TestKey.class.getName(),
> "b"));
>
>
>
> According to the ticket this should be sufficient. Could I still be
> missing something here?
>
>
>
> -Floris
>
>
>
> *From:* Denis Mekhanikov [mailto:dmekhanikov@gmail.com]
> *Sent:* Friday 03 August 2018 4:05 PM
> *To:* user@ignite.apache.org
> *Subject:* Re: SQL SELECT with AffinityKeyMapped - no results [External]
>
>
>
> Floris,
>
>
>
> Most probably, you hit a bug, that was introduced in Ignite 2.0:
> https://issues.apache.org/jira/browse/IGNITE-5795
>
> Because of this bug @AffinityKeyMapped annotation is ignored in classes,
> that are used in query entity configuration.
>
> As far as I can see, this is exactly your case.
>
> It's going to be fixed in Ignite 2.7.
>
> There is a workaround for this problem: you can list the problematic
> classes in BinaryConfiguration#classNames
> <https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/BinaryConfiguration.html#setClassNames-java.util.Collection->
> configuration property.
>
> Binary configuration should be specified as
> IgniteConfiguration#binaryConfiguration
> <https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/IgniteConfiguration.html#setBinaryConfiguration-org.apache.ignite.configuration.BinaryConfiguration->
> .
>
> This configuration should be the same on all nodes.
>
> You may also need to configure CacheConfiguration#keyConfiguration
> <https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/CacheConfiguration.html#setKeyConfiguration-org.apache.ignite.cache.CacheKeyConfiguration...->
> for your cache.
>
>
>
> Denis
>
>
>
> пт, 3 авг. 2018 г. в 16:58, Floris Van Nee <fl...@optiver.com>:
>
> Hi all,
>
>
>
> I have defined two classes in Java – one for a key and one for a value.
> Suppose they look like this:
>
>
>
> public static class Key implements Serializable {
>
> public String a;
>
> @QuerySqlField
>
> @AffinityKeyMapped
>
> public String b;
>
> }
>
> public static class Value implements Serializable {
>
> @QuerySqlField
>
> public int c;
>
> }
>
>
>
> I then define a distributed cache (in Java) for this key/value pair and
> fill it with values (in Java).
>
> Now, I run the following in SQL:
>
> SELECT * FROM kv_table;
>
> I indeed see results of all the stuff that I inserted into the cache.
>
>
>
> However, when I try to select a certain value, it returns no results:
>
> SELECT * FROM kv_table WHERE b = ‘test’;
>
> I get expected results when filtering on any column that is not defined as
> AffinityKeyMapped.
>
> The only case where I get wrong results is for the AffinityKeyMapped
> column - I always get a wrong result (most of the time zero rows, for some
> values I do get one row, but I expect to see more rows). Also when I remove
> the AffinityKeyMapped annotation and run my test again, everything works as
> expected.
>
> I run just a single server node and execute my query using the SQLLine
> tool that Ignite ships with.
>
>
>
> Is it possible that there is a bug in the code that handles this affinity
> key mapping for SQL queries? Or am I doing something wrong here?
>
>
>
> -Floris
>
>
RE: SQL SELECT with AffinityKeyMapped - no results
Posted by Floris Van Nee <fl...@Optiver.com>.
Thank you for your quick reply. That does look a lot like what I’m experiencing.
However, I did some testing but so far I did not get the workaround to work. I put the following in the XML config file:
<property name="binaryConfiguration">
<bean class="org.apache.ignite.configuration.BinaryConfiguration">
<property name="classNames">
<list>
<value>org.apache.ignite.examples.streaming.TestKey</value>
</list>
</property>
</bean>
</property>
Furthermore, when I create the cache, I pass a config with:
cfg.setKeyConfiguration(new CacheKeyConfiguration(TestKey.class.getName(), "b"));
According to the ticket this should be sufficient. Could I still be missing something here?
-Floris
From: Denis Mekhanikov [mailto:dmekhanikov@gmail.com]
Sent: Friday 03 August 2018 4:05 PM
To: user@ignite.apache.org
Subject: Re: SQL SELECT with AffinityKeyMapped - no results [External]
Floris,
Most probably, you hit a bug, that was introduced in Ignite 2.0: https://issues.apache.org/jira/browse/IGNITE-5795
Because of this bug @AffinityKeyMapped annotation is ignored in classes, that are used in query entity configuration.
As far as I can see, this is exactly your case.
It's going to be fixed in Ignite 2.7.
There is a workaround for this problem: you can list the problematic classes in BinaryConfiguration#classNames<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/BinaryConfiguration.html#setClassNames-java.util.Collection-> configuration property.
Binary configuration should be specified as IgniteConfiguration#binaryConfiguration<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/IgniteConfiguration.html#setBinaryConfiguration-org.apache.ignite.configuration.BinaryConfiguration->.
This configuration should be the same on all nodes.
You may also need to configure CacheConfiguration#keyConfiguration<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/CacheConfiguration.html#setKeyConfiguration-org.apache.ignite.cache.CacheKeyConfiguration...-> for your cache.
Denis
пт, 3 авг. 2018 г. в 16:58, Floris Van Nee <fl...@optiver.com>>:
Hi all,
I have defined two classes in Java – one for a key and one for a value. Suppose they look like this:
public static class Key implements Serializable {
public String a;
@QuerySqlField
@AffinityKeyMapped
public String b;
}
public static class Value implements Serializable {
@QuerySqlField
public int c;
}
I then define a distributed cache (in Java) for this key/value pair and fill it with values (in Java).
Now, I run the following in SQL:
SELECT * FROM kv_table;
I indeed see results of all the stuff that I inserted into the cache.
However, when I try to select a certain value, it returns no results:
SELECT * FROM kv_table WHERE b = ‘test’;
I get expected results when filtering on any column that is not defined as AffinityKeyMapped.
The only case where I get wrong results is for the AffinityKeyMapped column - I always get a wrong result (most of the time zero rows, for some values I do get one row, but I expect to see more rows). Also when I remove the AffinityKeyMapped annotation and run my test again, everything works as expected.
I run just a single server node and execute my query using the SQLLine tool that Ignite ships with.
Is it possible that there is a bug in the code that handles this affinity key mapping for SQL queries? Or am I doing something wrong here?
-Floris
Re: SQL SELECT with AffinityKeyMapped - no results
Posted by Denis Mekhanikov <dm...@gmail.com>.
Floris,
Most probably, you hit a bug, that was introduced in Ignite 2.0:
https://issues.apache.org/jira/browse/IGNITE-5795
Because of this bug @AffinityKeyMapped annotation is ignored in classes,
that are used in query entity configuration.
As far as I can see, this is exactly your case.
It's going to be fixed in Ignite 2.7.
There is a workaround for this problem: you can list the problematic
classes in BinaryConfiguration#classNames
<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/BinaryConfiguration.html#setClassNames-java.util.Collection->
configuration property.
Binary configuration should be specified as
IgniteConfiguration#binaryConfiguration
<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/IgniteConfiguration.html#setBinaryConfiguration-org.apache.ignite.configuration.BinaryConfiguration->
.
This configuration should be the same on all nodes.
You may also need to configure CacheConfiguration#keyConfiguration
<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/CacheConfiguration.html#setKeyConfiguration-org.apache.ignite.cache.CacheKeyConfiguration...->
for your cache.
Denis
пт, 3 авг. 2018 г. в 16:58, Floris Van Nee <fl...@optiver.com>:
> Hi all,
>
>
>
> I have defined two classes in Java – one for a key and one for a value.
> Suppose they look like this:
>
>
>
> public static class Key implements Serializable {
>
> public String a;
>
> @QuerySqlField
>
> @AffinityKeyMapped
>
> public String b;
>
> }
>
> public static class Value implements Serializable {
>
> @QuerySqlField
>
> public int c;
>
> }
>
>
>
> I then define a distributed cache (in Java) for this key/value pair and
> fill it with values (in Java).
>
> Now, I run the following in SQL:
>
> SELECT * FROM kv_table;
>
> I indeed see results of all the stuff that I inserted into the cache.
>
>
>
> However, when I try to select a certain value, it returns no results:
>
> SELECT * FROM kv_table WHERE b = ‘test’;
>
> I get expected results when filtering on any column that is not defined as
> AffinityKeyMapped.
>
> The only case where I get wrong results is for the AffinityKeyMapped
> column - I always get a wrong result (most of the time zero rows, for some
> values I do get one row, but I expect to see more rows). Also when I remove
> the AffinityKeyMapped annotation and run my test again, everything works as
> expected.
>
> I run just a single server node and execute my query using the SQLLine
> tool that Ignite ships with.
>
>
>
> Is it possible that there is a bug in the code that handles this affinity
> key mapping for SQL queries? Or am I doing something wrong here?
>
>
>
> -Floris
>