I am crossposting this from StackOverflow.
I noticed that when I query a cache which was created with a custom cache
template and include the cache's affinity key in the WHERE clause, no
results are returned.
I am running Ignite 2.5 with the following configuration:
<beans xmlns="http://www.springframework.org/schema/beans";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd";>
<bean id="grid.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="cacheConfiguration">
<list>
<bean id="cache-template-bean" abstract="true"
class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="myCacheTemplate*"/>
<property name="cacheMode" value="PARTITIONED" />
</bean>
</list>
</property>
</bean>
</beans>
And here is my test code. The code creates 3 caches. The first one is a
"root" cache which defines colocation for the others. The other two are
caches colocated by the root's key. The first colocated cache
(colocated_default) uses the PARTITIONED template and works as expected. The
second (colocated_custom) uses the "myCacheTemplate" created in the above
configuration. I insert one cache entry into each cache, where the entries
in the colocated cache have an affinity key equal to the root cache entry's
key.
I then query the caches. I first run a query to ensure there is one entry in
each cache. Then I run a query where the affinity key is equal to value
inserted. The results for me show that I am able to select from by affinity
from both of the PARTITIONED caches, but get no results for the
"colocated_custom" cache. Here is the code:
/**
* Test which shows that creating a cache with a custom cache
configuration template doesn't allow
* for SQL queries to use an affinity key in the WHERE clause.
*/
public class App {
public static void main(String[] args) {
// Start Ignite.
Ignition.setClientMode(true);
final Ignite ignite = Ignition.start(new IgniteConfiguration());
// Create caches. Create a root entity, and two entities which are
colocated by the root's ID.
// One uses the custom cache template and one just uses the
PARTITIONED template.
final List<StringBuilder> createTableStringBuilders = new
ArrayList<>();
final StringBuilder createRoot = new StringBuilder();
createRoot.append("CREATE TABLE IF NOT EXISTS root (\n");
createRoot.append(" \"key\" VARCHAR(24) NOT NULL,\n");
createRoot.append(" \"data\" VARCHAR(100),\n");
createRoot.append(" PRIMARY KEY(\"key\"))\n");
createRoot.append(
"WITH \"template=PARTITIONED, affinity_key=key,
cache_name=root, value_type=root\";");
createTableStringBuilders.add(createRoot);
final StringBuilder createColocatedDefault = new StringBuilder();
createColocatedDefault.append("CREATE TABLE IF NOT EXISTS
colocated_default (\n");
createColocatedDefault.append(" \"root_key\" VARCHAR(24) NOT
NULL,\n");
createColocatedDefault.append(" \"key\" VARCHAR(24) NOT
NULL,\n");
createColocatedDefault.append(" \"data\" VARCHAR(100),\n");
createColocatedDefault.append(" PRIMARY KEY(\"root_key\",
\"key\"))\n");
createColocatedDefault.append(
"WITH \"template=PARTITIONED, affinity_key=root_key,
cache_name=colocated_default, key_type=colocated_default_key,
value_type=colocated_default\";");
createTableStringBuilders.add(createColocatedDefault);
final StringBuilder createColocatedCustom = new StringBuilder();
createColocatedCustom.append("CREATE TABLE IF NOT EXISTS
colocated_custom (\n");
createColocatedCustom.append(" \"root_key\" VARCHAR(24) NOT
NULL,\n");
createColocatedCustom.append(" \"key\" VARCHAR(24) NOT NULL,\n");
createColocatedCustom.append(" \"data\" VARCHAR(100),\n");
createColocatedCustom.append(" PRIMARY KEY(\"root_key\",
\"key\"))\n");
createColocatedCustom.append(
"WITH \"template=myCacheTemplate, affinity_key=root_key,
cache_name=colocated_custom, key_type=colocated_custom_key,
value_type=colocated_custom\";");
createTableStringBuilders.add(createColocatedCustom);
try (Connection connection =
DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1:10800"))
{
for (final StringBuilder createTableStringBuilder :
createTableStringBuilders) {
try (PreparedStatement createTableStatement =
connection.prepareStatement(createTableStringBuilder.toString()))
{
System.out.println(createTableStringBuilder.toString());
createTableStatement.execute();
System.out.println();
}
}
}
catch (final SQLException e) {
throw new AssertionError(e);
}
// Create the root entity.
final BinaryObject root1 = ignite.binary().builder("root")
.setField("key", "1")
.setField("data", "Data for 1")
.build();
ignite.cache("root").withKeepBinary().put("1", root1);
// Create the colocated entity which uses the PARTITIONED
template.
final BinaryObject colocatedDefault1 =
ignite.binary().builder("colocated_default")
.setField("root_key", "1")
.setField("key", "2")
.build();
final BinaryObject colocatedDefault1Key =
ignite.binary().builder("colocated_default_key")
.setField("root_key", "1")
.setField("key", "2")
.build();
ignite.cache("colocated_default").withKeepBinary().put(colocatedDefault1Key,
colocatedDefault1);
// Create the colocated entity which uses the custom template.
final BinaryObject colocatedCustom1 =
ignite.binary().builder("colocated_custom")
.setField("root_key", "1")
.setField("key", "3")
.build();
final BinaryObject colocatedCustom1Key =
ignite.binary().builder("colocated_custom_key")
.setField("root_key", "1")
.setField("key", "3")
.build();
ignite.cache("colocated_custom").withKeepBinary().put(colocatedCustom1Key,
colocatedCustom1);
// SELECT COUNT(*) on all caches to ensure data exists.
final List<String> selectAllStrings = new ArrayList<>();
selectAllStrings.add("SELECT COUNT(*) FROM root;");
selectAllStrings.add("SELECT COUNT(*) FROM colocated_default;");
selectAllStrings.add("SELECT COUNT(*) FROM colocated_custom;");
try (Connection connection =
DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1:10800"))
{
for (final String selectAllString : selectAllStrings) {
try (PreparedStatement selectAllStatement =
connection.prepareStatement(selectAllString))
{
System.out.println(selectAllString);
selectAllStatement.execute();
final ResultSet resultSet =
selectAllStatement.getResultSet();
resultSet.next();
System.out.println(resultSet.getInt(1));
System.out.println();
}
}
}
catch (final SQLException e) {
throw new AssertionError(e);
}
// SELECT COUNT(*) with affinity key in WHERE clause.
final List<String> selectWhereStrings = new ArrayList<>();
// Returns 1.
selectWhereStrings.add("SELECT COUNT(*) FROM root WHERE \"key\" =
'1';");
// Returns 1.
selectWhereStrings.add("SELECT COUNT(*) FROM colocated_default
WHERE \"root_key\" = '1';");
// Returns 0.
selectWhereStrings.add("SELECT COUNT(*) FROM colocated_custom
WHERE \"root_key\" = '1';");
try (Connection connection =
DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1:10800"))
{
for (final String selectWhereString : selectWhereStrings) {
try (PreparedStatement selectWhereStatement =
connection.prepareStatement(selectWhereString))
{
System.out.println(selectWhereString);
selectWhereStatement.execute();
final ResultSet resultSet =
selectWhereStatement.getResultSet();
resultSet.next();
System.out.println(resultSet.getInt(1));
System.out.println();
}
}
}
catch (final SQLException e) {
throw new AssertionError(e);
}
}
}
I would like to know if this is a misconfiguration on my part, or a bug in
Ignite.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
