You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by kotamrajuyashasvi <ko...@gmail.com> on 2018/01/02 10:31:15 UTC
Date type field in select query is returning wrong value when Time
zones of Ignite Client and Server are different
Hi
In my sample Demo Ignite java program, Initially a Person entry is inserted
from the Ignite Client into the Ignite cache. The Person Pojo has a "dob"
field which is of java.util.Date type. Now when the Person Object is
obtained using _val in query, the getTime() on dob field of Person Object
gives same milliseconds that was used during the cache insert/put . But when
querying for dob field in select query it returns a java.sql.Timestamp
Object in QueryCursor and when getTime() on that Timestamp is called it
returns different milliseconds value. This milliseconds value obtained from
query will be used in other queries hence its crucial that correct
milliseconds value is obtained in query result. This behavior is observed
when Ignite Client and Server are in different TimeZones. The milliseconds
value in the Date Object is independent of Timezone.
Following is a sample reproducer:
****************** DemoProgram *************
package com.IgniteDemo;
import java.util.*;
import javax.cache.Cache.Entry;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteException;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.SqlQuery;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.configuration.IgniteConfiguration;
public class DemoMain {
public static void main(String[] args){
System.setProperty("user.timezone", "EST");//to change timezone of the
ignite client program
String ConfigPath = "default-config.xml";
Ignite ignite = Ignition.start(ConfigPath);
IgniteCache<PersonPK, Person> cache = ignite.cache("Person");
System.out.println("cache size "+cache.size());
long dob = 771465600000L;
Person p1 = new Person(1,"yash",22,"124345","addr",new Date(dob));
cache.put(p1.getPK(),p1);
System.out.println("cache size "+cache.size());
System.out.println("case 1: Obtaining dob from Person Object");
SqlFieldsQuery sql = new SqlFieldsQuery(
"select _val from Person where dob = ?");
try (QueryCursor<List<?>> cursor = cache.query(sql.setArgs(new
Date(7714656001000L)))) {
for (List<?> row : cursor){
dob = (((Person)row.get(0)).getDob().getTime());
System.out.println(" getTime of dob from Person Object : " + dob);
}
}
sql = new SqlFieldsQuery(
"select name from Person where dob = ?");
try (QueryCursor<List<?>> cursor = cache.query(sql.setArgs(new
Date(dob)))) {
for (List<?> row : cursor){
System.out.println(" name of person in query result : " +
row.get(0));
}
}
System.out.println("case 2: Obtaining dob from Select query field ");
sql = new SqlFieldsQuery(
"select dob from Person where dob = ?");
try (QueryCursor<List<?>> cursor = cache.query(sql.setArgs(new
Date(7714656001000L)))) {
for (List<?> row : cursor){
dob = (((Date)row.get(0)).getTime());
System.out.println(" Object Type of dob from select query result
object : " + row.get(0).getClass().getName());
System.out.println(" getTime of dob from select query result field
object : " + dob);
}
}
sql = new SqlFieldsQuery(
"select name from Person where dob = ?");
try (QueryCursor<List<?>> cursor = cache.query(sql.setArgs(new
Date(dob)))) {
for (List<?> row : cursor){
System.out.println(" name of person in query result : " +
row.get(0));
}
}
}
*** OUTPUT ***
cache size 0
cache size 1
case 1: Obtaining dob from Person Object
getTime of dob from Person Object : 771465600000
name of person in query result : yash
case 2: Obtaining dob from Select query field
Object Type of dob from select query result object : java.sql.Timestamp
getTime of dob from select query result field object : 771503400000
*********** Person.java *********************
package com.IgniteDemo;
import java.util.Date;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
public class Person {
@QuerySqlField(index = false)
private int person_no;
@QuerySqlField(index = false)
private String name;
@QuerySqlField(index = false)
private Integer age=null;
@QuerySqlField(index = false)
private String phno;
@QuerySqlField(index = false)
private String address;
@QuerySqlField(index = false)
public String tid="t1";
@QuerySqlField(index = false)
public Date dob;
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public Person(){
}
public int getPerson_no() {
return person_no;
}
public void setPerson_no(int person_no) {
this.person_no = person_no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPhno() {
return phno;
}
public void setPhno(String phno) {
this.phno = phno;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public PersonPK getPK(){
return new PersonPK(person_no,phno);
}
public Person(int person_no, String name, Integer age, String phno, String
address, Date dob) {
this.person_no = person_no;
this.name = name;
this.age = age;
this.phno = phno;
this.address = address;
this.dob = dob;
}
}
*******************************************************************************
************* PersonPK.java ***************
package com.IgniteDemo;
import java.util.Arrays;
public class PersonPK {
private String phno;
private int person_no;
public PersonPK(){
}
public PersonPK(int person_no,String phno){
this.person_no = person_no;
this.phno = phno;
}
public int getPerson_no() {
return person_no;
}
public void setPerson_no(int person_no) {
this.person_no = person_no;
}
public String getPhno() {
return phno;
}
public void setPhno(String phno) {
this.phno = phno;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + person_no;
result = prime * result + ((phno == null) ? 0 : phno.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
PersonPK other = (PersonPK) obj;
if (person_no != other.person_no)
return false;
if (phno == null) {
if (other.phno != null)
return false;
} else if (!phno.equals(other.phno))
return false;
return true;
}
@Override
public String toString() {
return "PersonPK [phno=" + phno + ", person_no=" + person_no + "]";
}
}
***********************************************************************************
**************** default-config.xml ****************************
<?xml version="1.0" encoding="UTF-8"?>
<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">
<import resource="./connection-settings.xml" />
<bean id="cache_persistence_settings"
class="org.apache.ignite.cache.store.cassandra.persistence.KeyValuePersistenceSettings">
<constructor-arg type="org.springframework.core.io.Resource"
value="file:C:\Users\persistence-settings.xml" />
</bean>
<bean id="grid.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="clientMode" value="true"/>
<property name="cacheConfiguration">
<list>
<bean
class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="Person"/>
<property name="readThrough" value="true"/>
<property name="writeThrough" value="true"/>
<property name="cacheMode" value="PARTITIONED"/>
<property name="indexedTypes">
<list>
<value>com.IgniteDemo.PersonPK</value>
<value>com.IgniteDemo.Person</value>
</list>
</property>
<property name="cacheStoreFactory">
<bean
class="org.apache.ignite.cache.store.cassandra.CassandraCacheStoreFactory">
<property name="dataSourceBean"
value="cassandraRegularDataSource"/>
<property name="persistenceSettingsBean"
value="cache_persistence_settings"/>
</bean>
</property>
<property name="queryEntities">
<list>
<bean
class="org.apache.ignite.cache.QueryEntity">
<property name="keyType"
value="com.IgniteDemo.PersonPK"/>
<property name="valueType"
value="com.IgniteDemo.Person"/>
</bean>
</list>
</property>
<property name="atomicityMode" value="TRANSACTIONAL"/>
</bean>
</list>
</property>
<property name="peerClassLoadingEnabled" value="true"/>
<property name="discoverySpi">
<bean
class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="ipFinder">
<bean
class="org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder">
<property name="addresses">
<list>
<value>127.0.0.1:47500..47509</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
</bean>
</beans>
*************************************************************************************
*********** persistence-settings.xml ***********
<persistence keyspace="testkeyspace" table="person">
<keyPersistence class="com.IgniteDemo.PersonPK" strategy="POJO" />
<valuePersistence class="com.IgniteDemo.Person" strategy="POJO" />
</persistence>
***************************************************
********** connection-settings.xml *************************************
<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="loadBalancingPolicy"
class="com.datastax.driver.core.policies.TokenAwarePolicy">
<constructor-arg
type="com.datastax.driver.core.policies.LoadBalancingPolicy">
<bean
class="com.datastax.driver.core.policies.RoundRobinPolicy"/>
</constructor-arg>
</bean>
<bean id="cassandraRegularDataSource"
class="org.apache.ignite.cache.store.cassandra.datasource.DataSource">
<property name="readConsistency" value="QUORUM"/>
<property name="writeConsistency" value="QUORUM"/>
<property name="loadBalancingPolicy" ref="loadBalancingPolicy"/>
<property name="user" value="cassandra"/>
<property name="password" value="cassandra"/>
<property name="contactPoints">
<list>
<value>10.1.2.3</value>
</list>
</property>
</bean>
</beans>
*********************************************************
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Date type field in select query is returning wrong value when
Time zones of Ignite Client and Server are different
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I have confirmed that the problem exists, raised
https://issues.apache.org/jira/browse/IGNITE-7363
Regards,
--
Ilya Kasnacheev
2018-01-09 11:09 GMT+03:00 kotamrajuyashasvi <ko...@gmail.com>:
> Hi
>
> Thanks for your response.
>
> Temporary work-around that I found was to set Ignite Servers JVM timezone
> and Ignite Client JVM Timezone to a common value using
> _JAVA_OPTIONS="-Duser.timezone=xxx"
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Date type field in select query is returning wrong value when
Time zones of Ignite Client and Server are different
Posted by kotamrajuyashasvi <ko...@gmail.com>.
Hi
Thanks for your response.
Temporary work-around that I found was to set Ignite Servers JVM timezone
and Ignite Client JVM Timezone to a common value using
_JAVA_OPTIONS="-Duser.timezone=xxx"
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Date type field in select query is returning wrong value when
Time zones of Ignite Client and Server are different
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I was able to reproduce the behaviour that you are describing. I have
suspicion that it has to do with separate handling of date types in H2,
which is used in Ignite for SQL queries. I will investigate this further,
see if there are work-arounds.
Regards,
--
Ilya Kasnacheev
2018-01-02 13:31 GMT+03:00 kotamrajuyashasvi <ko...@gmail.com>:
> Hi
>
> In my sample Demo Ignite java program, Initially a Person entry is inserted
> from the Ignite Client into the Ignite cache. The Person Pojo has a "dob"
> field which is of java.util.Date type. Now when the Person Object is
> obtained using _val in query, the getTime() on dob field of Person Object
> gives same milliseconds that was used during the cache insert/put . But
> when
> querying for dob field in select query it returns a java.sql.Timestamp
> Object in QueryCursor and when getTime() on that Timestamp is called it
> returns different milliseconds value. This milliseconds value obtained from
> query will be used in other queries hence its crucial that correct
> milliseconds value is obtained in query result. This behavior is observed
> when Ignite Client and Server are in different TimeZones. The milliseconds
> value in the Date Object is independent of Timezone.
>
> Following is a sample reproducer:
>
> ****************** DemoProgram *************
> package com.IgniteDemo;
>
> import java.util.*;
> import javax.cache.Cache.Entry;
> import org.apache.ignite.Ignite;
> import org.apache.ignite.IgniteCache;
> import org.apache.ignite.IgniteException;
> import org.apache.ignite.Ignition;
> import org.apache.ignite.cache.query.QueryCursor;
> import org.apache.ignite.cache.query.SqlFieldsQuery;
> import org.apache.ignite.cache.query.SqlQuery;
> import org.apache.ignite.configuration.CacheConfiguration;
> import org.apache.ignite.configuration.IgniteConfiguration;
>
> public class DemoMain {
>
> public static void main(String[] args){
>
>
> System.setProperty("user.timezone", "EST");//to change
> timezone of the
> ignite client program
>
> String ConfigPath = "default-config.xml";
>
> Ignite ignite = Ignition.start(ConfigPath);
>
> IgniteCache<PersonPK, Person> cache =
> ignite.cache("Person");
>
> System.out.println("cache size "+cache.size());
>
> long dob = 771465600000L;
>
> Person p1 = new Person(1,"yash",22,"124345","addr",new
> Date(dob));
> cache.put(p1.getPK(),p1);
>
> System.out.println("cache size "+cache.size());
>
> System.out.println("case 1: Obtaining dob from Person
> Object");
> SqlFieldsQuery sql = new SqlFieldsQuery(
> "select _val from Person where dob = ?");
> try (QueryCursor<List<?>> cursor =
> cache.query(sql.setArgs(new
> Date(7714656001000L)))) {
> for (List<?> row : cursor){
> dob = (((Person)row.get(0)).getDob().getTime());
> System.out.println(" getTime of dob from Person
> Object : " + dob);
> }
> }
>
> sql = new SqlFieldsQuery(
> "select name from Person where dob = ?");
> try (QueryCursor<List<?>> cursor =
> cache.query(sql.setArgs(new
> Date(dob)))) {
> for (List<?> row : cursor){
> System.out.println(" name of person in query
> result : " +
> row.get(0));
> }
> }
>
> System.out.println("case 2: Obtaining dob from Select
> query field ");
> sql = new SqlFieldsQuery(
> "select dob from Person where dob = ?");
> try (QueryCursor<List<?>> cursor =
> cache.query(sql.setArgs(new
> Date(7714656001000L)))) {
> for (List<?> row : cursor){
> dob = (((Date)row.get(0)).getTime());
> System.out.println(" Object Type of dob from select
> query result
> object : " + row.get(0).getClass().getName());
> System.out.println(" getTime of dob from select
> query result field
> object : " + dob);
> }
> }
>
>
> sql = new SqlFieldsQuery(
> "select name from Person where dob = ?");
> try (QueryCursor<List<?>> cursor =
> cache.query(sql.setArgs(new
> Date(dob)))) {
> for (List<?> row : cursor){
> System.out.println(" name of person in query result
> : " +
> row.get(0));
> }
> }
>
> }
>
> *** OUTPUT ***
> cache size 0
> cache size 1
> case 1: Obtaining dob from Person Object
> getTime of dob from Person Object : 771465600000
> name of person in query result : yash
> case 2: Obtaining dob from Select query field
> Object Type of dob from select query result object : java.sql.Timestamp
> getTime of dob from select query result field object : 771503400000
>
>
> *********** Person.java *********************
>
> package com.IgniteDemo;
>
> import java.util.Date;
>
> import org.apache.ignite.cache.query.annotations.QuerySqlField;
>
> public class Person {
> @QuerySqlField(index = false)
> private int person_no;
>
> @QuerySqlField(index = false)
> private String name;
>
> @QuerySqlField(index = false)
> private Integer age=null;
>
> @QuerySqlField(index = false)
> private String phno;
>
> @QuerySqlField(index = false)
> private String address;
>
> @QuerySqlField(index = false)
> public String tid="t1";
>
> @QuerySqlField(index = false)
> public Date dob;
>
> public Date getDob() {
> return dob;
> }
>
>
> public void setDob(Date dob) {
> this.dob = dob;
> }
>
>
> public Person(){
>
> }
>
>
> public int getPerson_no() {
> return person_no;
> }
>
>
> public void setPerson_no(int person_no) {
> this.person_no = person_no;
> }
>
>
> public String getName() {
> return name;
> }
>
>
> public void setName(String name) {
> this.name = name;
> }
>
>
> public Integer getAge() {
> return age;
> }
>
>
> public void setAge(Integer age) {
> this.age = age;
> }
>
>
> public String getPhno() {
> return phno;
> }
>
>
> public void setPhno(String phno) {
> this.phno = phno;
> }
>
>
> public String getAddress() {
> return address;
> }
>
>
> public void setAddress(String address) {
> this.address = address;
> }
>
>
> public PersonPK getPK(){
> return new PersonPK(person_no,phno);
> }
>
> public Person(int person_no, String name, Integer age, String
> phno, String
> address, Date dob) {
> this.person_no = person_no;
> this.name = name;
> this.age = age;
> this.phno = phno;
> this.address = address;
> this.dob = dob;
> }
> }
>
> ************************************************************
> *******************
>
> ************* PersonPK.java ***************
>
> package com.IgniteDemo;
>
> import java.util.Arrays;
>
> public class PersonPK {
>
> private String phno;
>
> private int person_no;
>
> public PersonPK(){
>
> }
>
> public PersonPK(int person_no,String phno){
> this.person_no = person_no;
> this.phno = phno;
> }
>
>
> public int getPerson_no() {
> return person_no;
> }
> public void setPerson_no(int person_no) {
> this.person_no = person_no;
> }
> public String getPhno() {
> return phno;
> }
> public void setPhno(String phno) {
> this.phno = phno;
> }
>
> @Override
> public int hashCode() {
> final int prime = 31;
> int result = 1;
> result = prime * result + person_no;
> result = prime * result + ((phno == null) ? 0 :
> phno.hashCode());
> return result;
> }
>
> @Override
> public boolean equals(Object obj) {
> if (this == obj)
> return true;
> if (obj == null)
> return false;
> if (getClass() != obj.getClass())
> return false;
> PersonPK other = (PersonPK) obj;
> if (person_no != other.person_no)
> return false;
> if (phno == null) {
> if (other.phno != null)
> return false;
> } else if (!phno.equals(other.phno))
> return false;
> return true;
> }
>
> @Override
> public String toString() {
> return "PersonPK [phno=" + phno + ", person_no=" +
> person_no + "]";
> }
>
> }
>
> ************************************************************
> ***********************
>
> **************** default-config.xml ****************************
>
> <?xml version="1.0" encoding="UTF-8"?>
>
> <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">
>
>
>
> <import resource="./connection-settings.xml" />
>
>
> <bean id="cache_persistence_settings"
> class="org.apache.ignite.cache.store.cassandra.persistence.
> KeyValuePersistenceSettings">
> <constructor-arg type="org.springframework.core.io.Resource"
> value="file:C:\Users\persistence-settings.xml" />
> </bean>
>
> <bean id="grid.cfg"
> class="org.apache.ignite.configuration.IgniteConfiguration">
>
>
> <property name="clientMode" value="true"/>
>
> <property name="cacheConfiguration">
> <list>
> <bean
> class="org.apache.ignite.configuration.CacheConfiguration">
> <property name="name" value="Person"/>
> <property name="readThrough" value="true"/>
> <property name="writeThrough" value="true"/>
> <property name="cacheMode"
> value="PARTITIONED"/>
>
> <property name="indexedTypes">
> <list>
> <value>com.IgniteDemo.PersonPK</value>
> <value>com.IgniteDemo.Person</value>
> </list>
> </property>
>
> <property name="cacheStoreFactory">
> <bean
> class="org.apache.ignite.cache.store.cassandra.
> CassandraCacheStoreFactory">
> <property name="dataSourceBean"
> value="cassandraRegularDataSource"/>
> <property
> name="persistenceSettingsBean"
> value="cache_persistence_settings"/>
> </bean>
> </property>
>
>
> <property name="queryEntities">
> <list>
> <bean
> class="org.apache.ignite.cache.QueryEntity">
> <property name="keyType"
> value="com.IgniteDemo.PersonPK"/>
> <property name="valueType"
> value="com.IgniteDemo.Person"/>
>
> </bean>
>
> </list>
> </property>
>
> <property name="atomicityMode"
> value="TRANSACTIONAL"/>
>
> </bean>
>
> </list>
> </property>
> <property name="peerClassLoadingEnabled" value="true"/>
> <property name="discoverySpi">
> <bean
> class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
> <property name="ipFinder">
> <bean
> class="org.apache.ignite.spi.discovery.tcp.ipfinder.vm.
> TcpDiscoveryVmIpFinder">
> <property name="addresses">
> <list>
>
> <value>127.0.0.1:47500..47509</value>
> </list>
> </property>
> </bean>
> </property>
> </bean>
> </property>
>
> </bean>
>
> </beans>
>
> ************************************************************
> *************************
>
>
> *********** persistence-settings.xml ***********
>
> <persistence keyspace="testkeyspace" table="person">
> <keyPersistence class="com.IgniteDemo.PersonPK" strategy="POJO" />
> <valuePersistence class="com.IgniteDemo.Person" strategy="POJO" />
> </persistence>
>
> ***************************************************
>
>
>
> ********** connection-settings.xml *************************************
>
> <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="loadBalancingPolicy"
> class="com.datastax.driver.core.policies.TokenAwarePolicy">
> <constructor-arg
> type="com.datastax.driver.core.policies.LoadBalancingPolicy">
> <bean
> class="com.datastax.driver.core.policies.RoundRobinPolicy"/>
> </constructor-arg>
> </bean>
>
> <bean id="cassandraRegularDataSource"
> class="org.apache.ignite.cache.store.cassandra.datasource.DataSource">
>
> <property name="readConsistency" value="QUORUM"/>
> <property name="writeConsistency" value="QUORUM"/>
> <property name="loadBalancingPolicy" ref="loadBalancingPolicy"/>
> <property name="user" value="cassandra"/>
> <property name="password" value="cassandra"/>
> <property name="contactPoints">
> <list>
> <value>10.1.2.3</value>
> </list>
> </property>
> </bean>
> </beans>
>
>
>
> *********************************************************
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>