RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache

[Cong Guo]

Hi,

How should I use BinaryObject in the CREATE TABLE statement? I try using 
BinaryObject.class.getName() as the value_type, but get the following exception:

class org.apache.ignite.IgniteCheckedException: Failed to initialize property 
'ORGID' of type 'java.lang.Long' for key class 'class java.lang.Long' and value 
class 'interface org.apache.ignite.binary.BinaryObject'. Make sure that one of 
these classes contains respective getter method or field.

I want to use BinaryObject here for some flexibility.

Thanks,
Cong

From: Вячеслав Коптилин [mailto:slava.kopti...@gmail.com]
Sent: 2018年6月18日 18:10
To: user@ignite.apache.org
Subject: Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache

Hello,

It seems that the root cause of the issue is wrong values of 'KEY_TYPE' and 
'VALUE_TYPE' parameters.
In your case, there is no need to specify 'KEY_TYPE' at all, and 'VALUE_TYPE' 
should Person.class.getName() I think.

Please try the following:
        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 +
            ", VALUE_TYPE=" + Person.class.getName() + "\"";

Best regards,
Slava.

пн, 18 июн. 2018 г. в 21:50, Cong Guo 
<cong.g...@huawei.com<mailto:cong.g...@huawei.com>>:
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”.