Hi,
I need to use both SQL and non-SQL APIs (key-value) on a single cache. I follow
the document in:
https://apacheignite-sql.readme.io/docs/create-table
I use "CREATE TABLE" to create the table and its underlying cache. I can use
both SQL "INSERT" and put to add data to the cache. However, when I run a
SqlFieldsQuery, only the row added by SQL "INSERT" can be seen. The Ignite
version is 2.4.0.
You can reproduce the bug using the following code:
CacheConfiguration<Integer, Integer> dummyCfg = new
CacheConfiguration<>("DUMMY");
dummyCfg.setSqlSchema("PUBLIC");
try(IgniteCache<Integer,
Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
String
createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR,
lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", KEY_TYPE=String,
VALUE_TYPE=BinaryObject\"";
dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
SqlFieldsQuery
firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id, orgId, firstName,
lastname, resume, salary) VALUES (?,?,?,?,?,?)");
firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
dummyCache.query(firstInsert).getAll();
try(IgniteCache<String, Person> personCache = ignite.cache(PERSON_CACHE_NAME)){
Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
personCache.put("Hello", p2);
IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long,
BinaryObject>withKeepBinary();
System.out.println("Size of the cache is: " +
binaryCache.size(CachePeekMode.ALL));
binaryCache.query(new ScanQuery<>(null)).forEach(entry ->
System.out.println(entry.getKey()));
System.out.println("Select results: ");
SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
QueryCursor<List<?>> answers = personCache.query(qry);
List<List<?>> personList = answers.getAll();
for(List<?> row : personList) {
String fn = (String)row.get(2);
System.out.println(fn);
}
}
}
The output is:
Size of the cache is: 2
Hello
String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
Select results:
John
The bug is that the SqlFieldsQuery cannot see the data added by "put".
