Also crossposting from SO :) https://stackoverflow.com/questions/52879064/cannot-query-cache-by-affinity-key-when-custom-cache-template-is-used/52935802#52935802
Apparently, it's a bug in Ignite. Filed https://issues.apache.org/jira/browse/IGNITE-9964. Thanks for reporting! The issue only appears when you put data via withKeepBinary(). If you use SQL INSERT instead, SELECT works fine. I suggest you use INSERT instead of constructing BinaryObjects manually - it's much easier and allows to workaround the bug. If you have to use BinaryObjects then you can try added the first row via INSERT and use binary afterwards - this also worked in my tests. Stan From: adam Sent: 18 октября 2018 г. 21:39 To: user@ignite.apache.org Subject: Cannot query cache by affinity key when custom cache template is used 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/
