You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Igor Sapego <is...@gridgain.com> on 2016/02/10 19:09:35 UTC
'Date' and 'Timestamp' types in SQL queries
Hello,
Recently I've been working on implementation of the Date and Timestamp
types support for C++ client [1] and I today have faced an issue when I was
running tests with Date and SqlFieldQuery.
Here is the issue. I have class that have field of type 'Date'. I was able
to create
instances of that class and put them in a cache, but when I tried to
retrieve
these fields with SQL query I've got an exception. After the short debug
session
I have found that the values that SQL queries return are of type
'Timestamp'.
So now I'm wonder, is it an expected behavior? Because to me it looks like
something that may be very confusing for a user. User knows that object's
field
is of type 'Date' but when they try to call GetNext<Date> on query row they
get an
exception.
I have also tested simple caches with Date where Date is a value and these
tests
work just fine with 'Date' returned as a result of Cache#Get() requests.
[1] - IGNITE-2222: CPP: Implement Date and Timestamp data types support for
binary protocol. <https://issues.apache.org/jira/browse/IGNITE-2222>
Best Regards,
Igor
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Vladimir Ozerov <vo...@gridgain.com>.
I think Igor's suggestion is fine. Essentially it mean that we will operate
on the most "precise" data and convert it to less precise only if user
explicitly requested it by choosing less precise data type.
On Fri, Feb 12, 2016 at 8:09 PM, Dmitriy Setrakyan <ds...@apache.org>
wrote:
> Igor,
>
> I will wait for Vova to comment on your last suggestion. Just wanted to add
> that we should be careful not to loose any precision during the conversion,
> as we got hit by it in the past.
>
> D.
>
> On Fri, Feb 12, 2016 at 1:36 AM, Igor Sapego <is...@gridgain.com> wrote:
>
> > Ok, then I propose following solution: when user of the C++ client tries
> > to read 'Date' value when there is an 'Timestamp' value in a stream
> > implicit cast from 'Timestamp' to 'Date' happens and user gets his
> > value.
> >
> > What do you think?
> >
> > Best Regards,
> > Igor
> >
> > On Thu, Feb 11, 2016 at 11:25 PM, Vladimir Ozerov <vo...@gridgain.com>
> > wrote:
> >
> > > I do not think we are going to change BinaryMarshaller that way.
> > > java.util.Date is widely used and accepted data type. To the contrast,
> > > java.sql.Date is very specific data type usually used somewhere near
> JDBC
> > > layer.
> > >
> > > On Thu, Feb 11, 2016 at 11:06 PM, Igor Sapego <is...@gridgain.com>
> > > wrote:
> > >
> > > > I guess we should switch to java.sql.Date in BinaryMarshaller then.
> > > >
> > > > Best Regards,
> > > > Igor
> > > >
> > > > On Thu, Feb 11, 2016 at 7:20 PM, Sergi Vladykin <
> > > sergi.vladykin@gmail.com>
> > > > wrote:
> > > >
> > > > > This is because there is no java.util.Date in SQL, we have to
> either
> > > > treat
> > > > > it as BLOB or as native SQL type Timestamp. We've chosen the latter
> > > > > approach.
> > > > >
> > > > > Sergi
> > > > >
> > > > > 2016-02-11 18:24 GMT+03:00 Igor Sapego <is...@gridgain.com>:
> > > > >
> > > > > > Sorry, I meant In our Binary marshaler we use *java.util.Date.*
> > > > > >
> > > > > > Best Regards,
> > > > > > Igor
> > > > > >
> > > > > > On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <
> isapego@gridgain.com
> > >
> > > > > wrote:
> > > > > >
> > > > > > > Ok, It seems like I have found what was causing the issue.
> > > > > > >
> > > > > > > In our
> > > > > > >
> > > > >
> > >
> apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
> > > > > > >
> > > > > > > /**
> > > > > > > * Initialize map of DB types.
> > > > > > > */
> > > > > > > static {
> > > > > > > map.put(int.class, INT);
> > > > > > > map.put(Integer.class, INT);
> > > > > > > map.put(boolean.class, BOOL);
> > > > > > > map.put(Boolean.class, BOOL);
> > > > > > > map.put(byte.class, TINYINT);
> > > > > > > map.put(Byte.class, TINYINT);
> > > > > > > map.put(short.class, SMALLINT);
> > > > > > > map.put(Short.class, SMALLINT);
> > > > > > > map.put(long.class, BIGINT);
> > > > > > > map.put(Long.class, BIGINT);
> > > > > > > map.put(BigDecimal.class, DECIMAL);
> > > > > > > map.put(double.class, DOUBLE);
> > > > > > > map.put(Double.class, DOUBLE);
> > > > > > > map.put(float.class, REAL);
> > > > > > > map.put(Float.class, REAL);
> > > > > > > map.put(Time.class, TIME);
> > > > > > > map.put(Timestamp.class, TIMESTAMP);
> > > > > > > map.put(java.util.Date.class, TIMESTAMP);
> > > > > > > map.put(java.sql.Date.class, DATE);
> > > > > > > map.put(String.class, VARCHAR);
> > > > > > > map.put(UUID.class, UUID);
> > > > > > > map.put(byte[].class, BINARY);
> > > > > > > }
> > > > > > >
> > > > > > > As I was using java.util.Date and not the java.sql.Date it was
> > > > > translated
> > > > > > > as TIMESTAMP
> > > > > > > and not as DATE. Are there any particular reason for
> > java.util.Date
> > > > > being
> > > > > > > treated as a
> > > > > > > TIMESTAMP?
> > > > > > >
> > > > > > > In our Binary marshaler we use java.sql.Date and when I try to
> > > change
> > > > > > > configuration and
> > > > > > > make the Date field to be of the type java.sql.Date I've got an
> > > > error,
> > > > > > > because this field value
> > > > > > > deserialized as java.sql.Date:
> > > > > > >
> > > > > > > lass org.apache.ignite.IgniteCheckedException: Failed to
> execute
> > > SQL
> > > > > > query.
> > > > > > > at
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> > > > > > > [...]
> > > > > > > at
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> > > > > > > Caused by: org.h2.jdbc.JdbcSQLException:
> > > > "java.lang.ClassCastException:
> > > > > > > java.util.Date cannot be cast to java.sql.Date"
> > > > > > >
> > > > > > >
> > > > > > > Best Regards,
> > > > > > > Igor
> > > > > > >
> > > > > > > On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <
> > > > > vozerov@gridgain.com>
> > > > > > > wrote:
> > > > > > >
> > > > > > >> There was some changes in how .NET interoperate w/ Java on
> > binary
> > > > > level.
> > > > > > >> No
> > > > > > >> changes were made to cache or query logic.
> > > > > > >> I performed a smoke test in Java and observed that Date field
> > was
> > > > > > >> correctly
> > > > > > >> mapped to H2 date and then vice versa.
> > > > > > >>
> > > > > > >> Probably this is a kind of configuration problem.
> > > > > > >>
> > > > > > >> Vladimir.
> > > > > > >>
> > > > > > >> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
> > > > > > >> dsetrakyan@apache.org>
> > > > > > >> wrote:
> > > > > > >>
> > > > > > >> > I remember seeing some work done for the .NET support to
> > provide
> > > > > > better
> > > > > > >> > precision for time data values. Could it be that SQL now
> > > converts
> > > > > > >> > everything to Timestamp because of that?
> > > > > > >> >
> > > > > > >> > D.
> > > > > > >> >
> > > > > > >> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <
> > > > isapego@gridgain.com
> > > > > >
> > > > > > >> > wrote:
> > > > > > >> >
> > > > > > >> > > Hello,
> > > > > > >> > >
> > > > > > >> > > Recently I've been working on implementation of the Date
> and
> > > > > > Timestamp
> > > > > > >> > > types support for C++ client [1] and I today have faced an
> > > issue
> > > > > > when
> > > > > > >> I
> > > > > > >> > was
> > > > > > >> > > running tests with Date and SqlFieldQuery.
> > > > > > >> > >
> > > > > > >> > > Here is the issue. I have class that have field of type
> > > 'Date'.
> > > > I
> > > > > > was
> > > > > > >> > able
> > > > > > >> > > to create
> > > > > > >> > > instances of that class and put them in a cache, but when
> I
> > > > tried
> > > > > to
> > > > > > >> > > retrieve
> > > > > > >> > > these fields with SQL query I've got an exception. After
> the
> > > > short
> > > > > > >> debug
> > > > > > >> > > session
> > > > > > >> > > I have found that the values that SQL queries return are
> of
> > > type
> > > > > > >> > > 'Timestamp'.
> > > > > > >> > >
> > > > > > >> > > So now I'm wonder, is it an expected behavior? Because to
> me
> > > it
> > > > > > looks
> > > > > > >> > like
> > > > > > >> > > something that may be very confusing for a user. User
> knows
> > > that
> > > > > > >> object's
> > > > > > >> > > field
> > > > > > >> > > is of type 'Date' but when they try to call GetNext<Date>
> on
> > > > query
> > > > > > row
> > > > > > >> > they
> > > > > > >> > > get an
> > > > > > >> > > exception.
> > > > > > >> > >
> > > > > > >> > > I have also tested simple caches with Date where Date is a
> > > value
> > > > > and
> > > > > > >> > these
> > > > > > >> > > tests
> > > > > > >> > > work just fine with 'Date' returned as a result of
> > Cache#Get()
> > > > > > >> requests.
> > > > > > >> > >
> > > > > > >> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data
> > > types
> > > > > > >> support
> > > > > > >> > for
> > > > > > >> > > binary protocol. <
> > > > > https://issues.apache.org/jira/browse/IGNITE-2222
> > > > > > >
> > > > > > >> > > Best Regards,
> > > > > > >> > > Igor
> > > > > > >> > >
> > > > > > >> >
> > > > > > >>
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Igor,
I will wait for Vova to comment on your last suggestion. Just wanted to add
that we should be careful not to loose any precision during the conversion,
as we got hit by it in the past.
D.
On Fri, Feb 12, 2016 at 1:36 AM, Igor Sapego <is...@gridgain.com> wrote:
> Ok, then I propose following solution: when user of the C++ client tries
> to read 'Date' value when there is an 'Timestamp' value in a stream
> implicit cast from 'Timestamp' to 'Date' happens and user gets his
> value.
>
> What do you think?
>
> Best Regards,
> Igor
>
> On Thu, Feb 11, 2016 at 11:25 PM, Vladimir Ozerov <vo...@gridgain.com>
> wrote:
>
> > I do not think we are going to change BinaryMarshaller that way.
> > java.util.Date is widely used and accepted data type. To the contrast,
> > java.sql.Date is very specific data type usually used somewhere near JDBC
> > layer.
> >
> > On Thu, Feb 11, 2016 at 11:06 PM, Igor Sapego <is...@gridgain.com>
> > wrote:
> >
> > > I guess we should switch to java.sql.Date in BinaryMarshaller then.
> > >
> > > Best Regards,
> > > Igor
> > >
> > > On Thu, Feb 11, 2016 at 7:20 PM, Sergi Vladykin <
> > sergi.vladykin@gmail.com>
> > > wrote:
> > >
> > > > This is because there is no java.util.Date in SQL, we have to either
> > > treat
> > > > it as BLOB or as native SQL type Timestamp. We've chosen the latter
> > > > approach.
> > > >
> > > > Sergi
> > > >
> > > > 2016-02-11 18:24 GMT+03:00 Igor Sapego <is...@gridgain.com>:
> > > >
> > > > > Sorry, I meant In our Binary marshaler we use *java.util.Date.*
> > > > >
> > > > > Best Regards,
> > > > > Igor
> > > > >
> > > > > On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <isapego@gridgain.com
> >
> > > > wrote:
> > > > >
> > > > > > Ok, It seems like I have found what was causing the issue.
> > > > > >
> > > > > > In our
> > > > > >
> > > >
> > apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
> > > > > >
> > > > > > /**
> > > > > > * Initialize map of DB types.
> > > > > > */
> > > > > > static {
> > > > > > map.put(int.class, INT);
> > > > > > map.put(Integer.class, INT);
> > > > > > map.put(boolean.class, BOOL);
> > > > > > map.put(Boolean.class, BOOL);
> > > > > > map.put(byte.class, TINYINT);
> > > > > > map.put(Byte.class, TINYINT);
> > > > > > map.put(short.class, SMALLINT);
> > > > > > map.put(Short.class, SMALLINT);
> > > > > > map.put(long.class, BIGINT);
> > > > > > map.put(Long.class, BIGINT);
> > > > > > map.put(BigDecimal.class, DECIMAL);
> > > > > > map.put(double.class, DOUBLE);
> > > > > > map.put(Double.class, DOUBLE);
> > > > > > map.put(float.class, REAL);
> > > > > > map.put(Float.class, REAL);
> > > > > > map.put(Time.class, TIME);
> > > > > > map.put(Timestamp.class, TIMESTAMP);
> > > > > > map.put(java.util.Date.class, TIMESTAMP);
> > > > > > map.put(java.sql.Date.class, DATE);
> > > > > > map.put(String.class, VARCHAR);
> > > > > > map.put(UUID.class, UUID);
> > > > > > map.put(byte[].class, BINARY);
> > > > > > }
> > > > > >
> > > > > > As I was using java.util.Date and not the java.sql.Date it was
> > > > translated
> > > > > > as TIMESTAMP
> > > > > > and not as DATE. Are there any particular reason for
> java.util.Date
> > > > being
> > > > > > treated as a
> > > > > > TIMESTAMP?
> > > > > >
> > > > > > In our Binary marshaler we use java.sql.Date and when I try to
> > change
> > > > > > configuration and
> > > > > > make the Date field to be of the type java.sql.Date I've got an
> > > error,
> > > > > > because this field value
> > > > > > deserialized as java.sql.Date:
> > > > > >
> > > > > > lass org.apache.ignite.IgniteCheckedException: Failed to execute
> > SQL
> > > > > query.
> > > > > > at
> > > > > >
> > > > >
> > > >
> > >
> >
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> > > > > > [...]
> > > > > > at
> > > > > >
> > > > >
> > > >
> > >
> >
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> > > > > > Caused by: org.h2.jdbc.JdbcSQLException:
> > > "java.lang.ClassCastException:
> > > > > > java.util.Date cannot be cast to java.sql.Date"
> > > > > >
> > > > > >
> > > > > > Best Regards,
> > > > > > Igor
> > > > > >
> > > > > > On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <
> > > > vozerov@gridgain.com>
> > > > > > wrote:
> > > > > >
> > > > > >> There was some changes in how .NET interoperate w/ Java on
> binary
> > > > level.
> > > > > >> No
> > > > > >> changes were made to cache or query logic.
> > > > > >> I performed a smoke test in Java and observed that Date field
> was
> > > > > >> correctly
> > > > > >> mapped to H2 date and then vice versa.
> > > > > >>
> > > > > >> Probably this is a kind of configuration problem.
> > > > > >>
> > > > > >> Vladimir.
> > > > > >>
> > > > > >> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
> > > > > >> dsetrakyan@apache.org>
> > > > > >> wrote:
> > > > > >>
> > > > > >> > I remember seeing some work done for the .NET support to
> provide
> > > > > better
> > > > > >> > precision for time data values. Could it be that SQL now
> > converts
> > > > > >> > everything to Timestamp because of that?
> > > > > >> >
> > > > > >> > D.
> > > > > >> >
> > > > > >> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <
> > > isapego@gridgain.com
> > > > >
> > > > > >> > wrote:
> > > > > >> >
> > > > > >> > > Hello,
> > > > > >> > >
> > > > > >> > > Recently I've been working on implementation of the Date and
> > > > > Timestamp
> > > > > >> > > types support for C++ client [1] and I today have faced an
> > issue
> > > > > when
> > > > > >> I
> > > > > >> > was
> > > > > >> > > running tests with Date and SqlFieldQuery.
> > > > > >> > >
> > > > > >> > > Here is the issue. I have class that have field of type
> > 'Date'.
> > > I
> > > > > was
> > > > > >> > able
> > > > > >> > > to create
> > > > > >> > > instances of that class and put them in a cache, but when I
> > > tried
> > > > to
> > > > > >> > > retrieve
> > > > > >> > > these fields with SQL query I've got an exception. After the
> > > short
> > > > > >> debug
> > > > > >> > > session
> > > > > >> > > I have found that the values that SQL queries return are of
> > type
> > > > > >> > > 'Timestamp'.
> > > > > >> > >
> > > > > >> > > So now I'm wonder, is it an expected behavior? Because to me
> > it
> > > > > looks
> > > > > >> > like
> > > > > >> > > something that may be very confusing for a user. User knows
> > that
> > > > > >> object's
> > > > > >> > > field
> > > > > >> > > is of type 'Date' but when they try to call GetNext<Date> on
> > > query
> > > > > row
> > > > > >> > they
> > > > > >> > > get an
> > > > > >> > > exception.
> > > > > >> > >
> > > > > >> > > I have also tested simple caches with Date where Date is a
> > value
> > > > and
> > > > > >> > these
> > > > > >> > > tests
> > > > > >> > > work just fine with 'Date' returned as a result of
> Cache#Get()
> > > > > >> requests.
> > > > > >> > >
> > > > > >> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data
> > types
> > > > > >> support
> > > > > >> > for
> > > > > >> > > binary protocol. <
> > > > https://issues.apache.org/jira/browse/IGNITE-2222
> > > > > >
> > > > > >> > > Best Regards,
> > > > > >> > > Igor
> > > > > >> > >
> > > > > >> >
> > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Igor Sapego <is...@gridgain.com>.
Ok, then I propose following solution: when user of the C++ client tries
to read 'Date' value when there is an 'Timestamp' value in a stream
implicit cast from 'Timestamp' to 'Date' happens and user gets his
value.
What do you think?
Best Regards,
Igor
On Thu, Feb 11, 2016 at 11:25 PM, Vladimir Ozerov <vo...@gridgain.com>
wrote:
> I do not think we are going to change BinaryMarshaller that way.
> java.util.Date is widely used and accepted data type. To the contrast,
> java.sql.Date is very specific data type usually used somewhere near JDBC
> layer.
>
> On Thu, Feb 11, 2016 at 11:06 PM, Igor Sapego <is...@gridgain.com>
> wrote:
>
> > I guess we should switch to java.sql.Date in BinaryMarshaller then.
> >
> > Best Regards,
> > Igor
> >
> > On Thu, Feb 11, 2016 at 7:20 PM, Sergi Vladykin <
> sergi.vladykin@gmail.com>
> > wrote:
> >
> > > This is because there is no java.util.Date in SQL, we have to either
> > treat
> > > it as BLOB or as native SQL type Timestamp. We've chosen the latter
> > > approach.
> > >
> > > Sergi
> > >
> > > 2016-02-11 18:24 GMT+03:00 Igor Sapego <is...@gridgain.com>:
> > >
> > > > Sorry, I meant In our Binary marshaler we use *java.util.Date.*
> > > >
> > > > Best Regards,
> > > > Igor
> > > >
> > > > On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <is...@gridgain.com>
> > > wrote:
> > > >
> > > > > Ok, It seems like I have found what was causing the issue.
> > > > >
> > > > > In our
> > > > >
> > >
> apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
> > > > >
> > > > > /**
> > > > > * Initialize map of DB types.
> > > > > */
> > > > > static {
> > > > > map.put(int.class, INT);
> > > > > map.put(Integer.class, INT);
> > > > > map.put(boolean.class, BOOL);
> > > > > map.put(Boolean.class, BOOL);
> > > > > map.put(byte.class, TINYINT);
> > > > > map.put(Byte.class, TINYINT);
> > > > > map.put(short.class, SMALLINT);
> > > > > map.put(Short.class, SMALLINT);
> > > > > map.put(long.class, BIGINT);
> > > > > map.put(Long.class, BIGINT);
> > > > > map.put(BigDecimal.class, DECIMAL);
> > > > > map.put(double.class, DOUBLE);
> > > > > map.put(Double.class, DOUBLE);
> > > > > map.put(float.class, REAL);
> > > > > map.put(Float.class, REAL);
> > > > > map.put(Time.class, TIME);
> > > > > map.put(Timestamp.class, TIMESTAMP);
> > > > > map.put(java.util.Date.class, TIMESTAMP);
> > > > > map.put(java.sql.Date.class, DATE);
> > > > > map.put(String.class, VARCHAR);
> > > > > map.put(UUID.class, UUID);
> > > > > map.put(byte[].class, BINARY);
> > > > > }
> > > > >
> > > > > As I was using java.util.Date and not the java.sql.Date it was
> > > translated
> > > > > as TIMESTAMP
> > > > > and not as DATE. Are there any particular reason for java.util.Date
> > > being
> > > > > treated as a
> > > > > TIMESTAMP?
> > > > >
> > > > > In our Binary marshaler we use java.sql.Date and when I try to
> change
> > > > > configuration and
> > > > > make the Date field to be of the type java.sql.Date I've got an
> > error,
> > > > > because this field value
> > > > > deserialized as java.sql.Date:
> > > > >
> > > > > lass org.apache.ignite.IgniteCheckedException: Failed to execute
> SQL
> > > > query.
> > > > > at
> > > > >
> > > >
> > >
> >
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> > > > > [...]
> > > > > at
> > > > >
> > > >
> > >
> >
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> > > > > Caused by: org.h2.jdbc.JdbcSQLException:
> > "java.lang.ClassCastException:
> > > > > java.util.Date cannot be cast to java.sql.Date"
> > > > >
> > > > >
> > > > > Best Regards,
> > > > > Igor
> > > > >
> > > > > On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <
> > > vozerov@gridgain.com>
> > > > > wrote:
> > > > >
> > > > >> There was some changes in how .NET interoperate w/ Java on binary
> > > level.
> > > > >> No
> > > > >> changes were made to cache or query logic.
> > > > >> I performed a smoke test in Java and observed that Date field was
> > > > >> correctly
> > > > >> mapped to H2 date and then vice versa.
> > > > >>
> > > > >> Probably this is a kind of configuration problem.
> > > > >>
> > > > >> Vladimir.
> > > > >>
> > > > >> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
> > > > >> dsetrakyan@apache.org>
> > > > >> wrote:
> > > > >>
> > > > >> > I remember seeing some work done for the .NET support to provide
> > > > better
> > > > >> > precision for time data values. Could it be that SQL now
> converts
> > > > >> > everything to Timestamp because of that?
> > > > >> >
> > > > >> > D.
> > > > >> >
> > > > >> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <
> > isapego@gridgain.com
> > > >
> > > > >> > wrote:
> > > > >> >
> > > > >> > > Hello,
> > > > >> > >
> > > > >> > > Recently I've been working on implementation of the Date and
> > > > Timestamp
> > > > >> > > types support for C++ client [1] and I today have faced an
> issue
> > > > when
> > > > >> I
> > > > >> > was
> > > > >> > > running tests with Date and SqlFieldQuery.
> > > > >> > >
> > > > >> > > Here is the issue. I have class that have field of type
> 'Date'.
> > I
> > > > was
> > > > >> > able
> > > > >> > > to create
> > > > >> > > instances of that class and put them in a cache, but when I
> > tried
> > > to
> > > > >> > > retrieve
> > > > >> > > these fields with SQL query I've got an exception. After the
> > short
> > > > >> debug
> > > > >> > > session
> > > > >> > > I have found that the values that SQL queries return are of
> type
> > > > >> > > 'Timestamp'.
> > > > >> > >
> > > > >> > > So now I'm wonder, is it an expected behavior? Because to me
> it
> > > > looks
> > > > >> > like
> > > > >> > > something that may be very confusing for a user. User knows
> that
> > > > >> object's
> > > > >> > > field
> > > > >> > > is of type 'Date' but when they try to call GetNext<Date> on
> > query
> > > > row
> > > > >> > they
> > > > >> > > get an
> > > > >> > > exception.
> > > > >> > >
> > > > >> > > I have also tested simple caches with Date where Date is a
> value
> > > and
> > > > >> > these
> > > > >> > > tests
> > > > >> > > work just fine with 'Date' returned as a result of Cache#Get()
> > > > >> requests.
> > > > >> > >
> > > > >> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data
> types
> > > > >> support
> > > > >> > for
> > > > >> > > binary protocol. <
> > > https://issues.apache.org/jira/browse/IGNITE-2222
> > > > >
> > > > >> > > Best Regards,
> > > > >> > > Igor
> > > > >> > >
> > > > >> >
> > > > >>
> > > > >
> > > > >
> > > >
> > >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Vladimir Ozerov <vo...@gridgain.com>.
I do not think we are going to change BinaryMarshaller that way.
java.util.Date is widely used and accepted data type. To the contrast,
java.sql.Date is very specific data type usually used somewhere near JDBC
layer.
On Thu, Feb 11, 2016 at 11:06 PM, Igor Sapego <is...@gridgain.com> wrote:
> I guess we should switch to java.sql.Date in BinaryMarshaller then.
>
> Best Regards,
> Igor
>
> On Thu, Feb 11, 2016 at 7:20 PM, Sergi Vladykin <se...@gmail.com>
> wrote:
>
> > This is because there is no java.util.Date in SQL, we have to either
> treat
> > it as BLOB or as native SQL type Timestamp. We've chosen the latter
> > approach.
> >
> > Sergi
> >
> > 2016-02-11 18:24 GMT+03:00 Igor Sapego <is...@gridgain.com>:
> >
> > > Sorry, I meant In our Binary marshaler we use *java.util.Date.*
> > >
> > > Best Regards,
> > > Igor
> > >
> > > On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <is...@gridgain.com>
> > wrote:
> > >
> > > > Ok, It seems like I have found what was causing the issue.
> > > >
> > > > In our
> > > >
> > apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
> > > >
> > > > /**
> > > > * Initialize map of DB types.
> > > > */
> > > > static {
> > > > map.put(int.class, INT);
> > > > map.put(Integer.class, INT);
> > > > map.put(boolean.class, BOOL);
> > > > map.put(Boolean.class, BOOL);
> > > > map.put(byte.class, TINYINT);
> > > > map.put(Byte.class, TINYINT);
> > > > map.put(short.class, SMALLINT);
> > > > map.put(Short.class, SMALLINT);
> > > > map.put(long.class, BIGINT);
> > > > map.put(Long.class, BIGINT);
> > > > map.put(BigDecimal.class, DECIMAL);
> > > > map.put(double.class, DOUBLE);
> > > > map.put(Double.class, DOUBLE);
> > > > map.put(float.class, REAL);
> > > > map.put(Float.class, REAL);
> > > > map.put(Time.class, TIME);
> > > > map.put(Timestamp.class, TIMESTAMP);
> > > > map.put(java.util.Date.class, TIMESTAMP);
> > > > map.put(java.sql.Date.class, DATE);
> > > > map.put(String.class, VARCHAR);
> > > > map.put(UUID.class, UUID);
> > > > map.put(byte[].class, BINARY);
> > > > }
> > > >
> > > > As I was using java.util.Date and not the java.sql.Date it was
> > translated
> > > > as TIMESTAMP
> > > > and not as DATE. Are there any particular reason for java.util.Date
> > being
> > > > treated as a
> > > > TIMESTAMP?
> > > >
> > > > In our Binary marshaler we use java.sql.Date and when I try to change
> > > > configuration and
> > > > make the Date field to be of the type java.sql.Date I've got an
> error,
> > > > because this field value
> > > > deserialized as java.sql.Date:
> > > >
> > > > lass org.apache.ignite.IgniteCheckedException: Failed to execute SQL
> > > query.
> > > > at
> > > >
> > >
> >
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> > > > [...]
> > > > at
> > > >
> > >
> >
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> > > > Caused by: org.h2.jdbc.JdbcSQLException:
> "java.lang.ClassCastException:
> > > > java.util.Date cannot be cast to java.sql.Date"
> > > >
> > > >
> > > > Best Regards,
> > > > Igor
> > > >
> > > > On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <
> > vozerov@gridgain.com>
> > > > wrote:
> > > >
> > > >> There was some changes in how .NET interoperate w/ Java on binary
> > level.
> > > >> No
> > > >> changes were made to cache or query logic.
> > > >> I performed a smoke test in Java and observed that Date field was
> > > >> correctly
> > > >> mapped to H2 date and then vice versa.
> > > >>
> > > >> Probably this is a kind of configuration problem.
> > > >>
> > > >> Vladimir.
> > > >>
> > > >> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
> > > >> dsetrakyan@apache.org>
> > > >> wrote:
> > > >>
> > > >> > I remember seeing some work done for the .NET support to provide
> > > better
> > > >> > precision for time data values. Could it be that SQL now converts
> > > >> > everything to Timestamp because of that?
> > > >> >
> > > >> > D.
> > > >> >
> > > >> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <
> isapego@gridgain.com
> > >
> > > >> > wrote:
> > > >> >
> > > >> > > Hello,
> > > >> > >
> > > >> > > Recently I've been working on implementation of the Date and
> > > Timestamp
> > > >> > > types support for C++ client [1] and I today have faced an issue
> > > when
> > > >> I
> > > >> > was
> > > >> > > running tests with Date and SqlFieldQuery.
> > > >> > >
> > > >> > > Here is the issue. I have class that have field of type 'Date'.
> I
> > > was
> > > >> > able
> > > >> > > to create
> > > >> > > instances of that class and put them in a cache, but when I
> tried
> > to
> > > >> > > retrieve
> > > >> > > these fields with SQL query I've got an exception. After the
> short
> > > >> debug
> > > >> > > session
> > > >> > > I have found that the values that SQL queries return are of type
> > > >> > > 'Timestamp'.
> > > >> > >
> > > >> > > So now I'm wonder, is it an expected behavior? Because to me it
> > > looks
> > > >> > like
> > > >> > > something that may be very confusing for a user. User knows that
> > > >> object's
> > > >> > > field
> > > >> > > is of type 'Date' but when they try to call GetNext<Date> on
> query
> > > row
> > > >> > they
> > > >> > > get an
> > > >> > > exception.
> > > >> > >
> > > >> > > I have also tested simple caches with Date where Date is a value
> > and
> > > >> > these
> > > >> > > tests
> > > >> > > work just fine with 'Date' returned as a result of Cache#Get()
> > > >> requests.
> > > >> > >
> > > >> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types
> > > >> support
> > > >> > for
> > > >> > > binary protocol. <
> > https://issues.apache.org/jira/browse/IGNITE-2222
> > > >
> > > >> > > Best Regards,
> > > >> > > Igor
> > > >> > >
> > > >> >
> > > >>
> > > >
> > > >
> > >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Igor Sapego <is...@gridgain.com>.
I guess we should switch to java.sql.Date in BinaryMarshaller then.
Best Regards,
Igor
On Thu, Feb 11, 2016 at 7:20 PM, Sergi Vladykin <se...@gmail.com>
wrote:
> This is because there is no java.util.Date in SQL, we have to either treat
> it as BLOB or as native SQL type Timestamp. We've chosen the latter
> approach.
>
> Sergi
>
> 2016-02-11 18:24 GMT+03:00 Igor Sapego <is...@gridgain.com>:
>
> > Sorry, I meant In our Binary marshaler we use *java.util.Date.*
> >
> > Best Regards,
> > Igor
> >
> > On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <is...@gridgain.com>
> wrote:
> >
> > > Ok, It seems like I have found what was causing the issue.
> > >
> > > In our
> > >
> apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
> > >
> > > /**
> > > * Initialize map of DB types.
> > > */
> > > static {
> > > map.put(int.class, INT);
> > > map.put(Integer.class, INT);
> > > map.put(boolean.class, BOOL);
> > > map.put(Boolean.class, BOOL);
> > > map.put(byte.class, TINYINT);
> > > map.put(Byte.class, TINYINT);
> > > map.put(short.class, SMALLINT);
> > > map.put(Short.class, SMALLINT);
> > > map.put(long.class, BIGINT);
> > > map.put(Long.class, BIGINT);
> > > map.put(BigDecimal.class, DECIMAL);
> > > map.put(double.class, DOUBLE);
> > > map.put(Double.class, DOUBLE);
> > > map.put(float.class, REAL);
> > > map.put(Float.class, REAL);
> > > map.put(Time.class, TIME);
> > > map.put(Timestamp.class, TIMESTAMP);
> > > map.put(java.util.Date.class, TIMESTAMP);
> > > map.put(java.sql.Date.class, DATE);
> > > map.put(String.class, VARCHAR);
> > > map.put(UUID.class, UUID);
> > > map.put(byte[].class, BINARY);
> > > }
> > >
> > > As I was using java.util.Date and not the java.sql.Date it was
> translated
> > > as TIMESTAMP
> > > and not as DATE. Are there any particular reason for java.util.Date
> being
> > > treated as a
> > > TIMESTAMP?
> > >
> > > In our Binary marshaler we use java.sql.Date and when I try to change
> > > configuration and
> > > make the Date field to be of the type java.sql.Date I've got an error,
> > > because this field value
> > > deserialized as java.sql.Date:
> > >
> > > lass org.apache.ignite.IgniteCheckedException: Failed to execute SQL
> > query.
> > > at
> > >
> >
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> > > [...]
> > > at
> > >
> >
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> > > Caused by: org.h2.jdbc.JdbcSQLException: "java.lang.ClassCastException:
> > > java.util.Date cannot be cast to java.sql.Date"
> > >
> > >
> > > Best Regards,
> > > Igor
> > >
> > > On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <
> vozerov@gridgain.com>
> > > wrote:
> > >
> > >> There was some changes in how .NET interoperate w/ Java on binary
> level.
> > >> No
> > >> changes were made to cache or query logic.
> > >> I performed a smoke test in Java and observed that Date field was
> > >> correctly
> > >> mapped to H2 date and then vice versa.
> > >>
> > >> Probably this is a kind of configuration problem.
> > >>
> > >> Vladimir.
> > >>
> > >> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
> > >> dsetrakyan@apache.org>
> > >> wrote:
> > >>
> > >> > I remember seeing some work done for the .NET support to provide
> > better
> > >> > precision for time data values. Could it be that SQL now converts
> > >> > everything to Timestamp because of that?
> > >> >
> > >> > D.
> > >> >
> > >> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <isapego@gridgain.com
> >
> > >> > wrote:
> > >> >
> > >> > > Hello,
> > >> > >
> > >> > > Recently I've been working on implementation of the Date and
> > Timestamp
> > >> > > types support for C++ client [1] and I today have faced an issue
> > when
> > >> I
> > >> > was
> > >> > > running tests with Date and SqlFieldQuery.
> > >> > >
> > >> > > Here is the issue. I have class that have field of type 'Date'. I
> > was
> > >> > able
> > >> > > to create
> > >> > > instances of that class and put them in a cache, but when I tried
> to
> > >> > > retrieve
> > >> > > these fields with SQL query I've got an exception. After the short
> > >> debug
> > >> > > session
> > >> > > I have found that the values that SQL queries return are of type
> > >> > > 'Timestamp'.
> > >> > >
> > >> > > So now I'm wonder, is it an expected behavior? Because to me it
> > looks
> > >> > like
> > >> > > something that may be very confusing for a user. User knows that
> > >> object's
> > >> > > field
> > >> > > is of type 'Date' but when they try to call GetNext<Date> on query
> > row
> > >> > they
> > >> > > get an
> > >> > > exception.
> > >> > >
> > >> > > I have also tested simple caches with Date where Date is a value
> and
> > >> > these
> > >> > > tests
> > >> > > work just fine with 'Date' returned as a result of Cache#Get()
> > >> requests.
> > >> > >
> > >> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types
> > >> support
> > >> > for
> > >> > > binary protocol. <
> https://issues.apache.org/jira/browse/IGNITE-2222
> > >
> > >> > > Best Regards,
> > >> > > Igor
> > >> > >
> > >> >
> > >>
> > >
> > >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
This is because there is no java.util.Date in SQL, we have to either treat
it as BLOB or as native SQL type Timestamp. We've chosen the latter
approach.
Sergi
2016-02-11 18:24 GMT+03:00 Igor Sapego <is...@gridgain.com>:
> Sorry, I meant In our Binary marshaler we use *java.util.Date.*
>
> Best Regards,
> Igor
>
> On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <is...@gridgain.com> wrote:
>
> > Ok, It seems like I have found what was causing the issue.
> >
> > In our
> > apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
> >
> > /**
> > * Initialize map of DB types.
> > */
> > static {
> > map.put(int.class, INT);
> > map.put(Integer.class, INT);
> > map.put(boolean.class, BOOL);
> > map.put(Boolean.class, BOOL);
> > map.put(byte.class, TINYINT);
> > map.put(Byte.class, TINYINT);
> > map.put(short.class, SMALLINT);
> > map.put(Short.class, SMALLINT);
> > map.put(long.class, BIGINT);
> > map.put(Long.class, BIGINT);
> > map.put(BigDecimal.class, DECIMAL);
> > map.put(double.class, DOUBLE);
> > map.put(Double.class, DOUBLE);
> > map.put(float.class, REAL);
> > map.put(Float.class, REAL);
> > map.put(Time.class, TIME);
> > map.put(Timestamp.class, TIMESTAMP);
> > map.put(java.util.Date.class, TIMESTAMP);
> > map.put(java.sql.Date.class, DATE);
> > map.put(String.class, VARCHAR);
> > map.put(UUID.class, UUID);
> > map.put(byte[].class, BINARY);
> > }
> >
> > As I was using java.util.Date and not the java.sql.Date it was translated
> > as TIMESTAMP
> > and not as DATE. Are there any particular reason for java.util.Date being
> > treated as a
> > TIMESTAMP?
> >
> > In our Binary marshaler we use java.sql.Date and when I try to change
> > configuration and
> > make the Date field to be of the type java.sql.Date I've got an error,
> > because this field value
> > deserialized as java.sql.Date:
> >
> > lass org.apache.ignite.IgniteCheckedException: Failed to execute SQL
> query.
> > at
> >
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> > [...]
> > at
> >
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> > Caused by: org.h2.jdbc.JdbcSQLException: "java.lang.ClassCastException:
> > java.util.Date cannot be cast to java.sql.Date"
> >
> >
> > Best Regards,
> > Igor
> >
> > On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <vo...@gridgain.com>
> > wrote:
> >
> >> There was some changes in how .NET interoperate w/ Java on binary level.
> >> No
> >> changes were made to cache or query logic.
> >> I performed a smoke test in Java and observed that Date field was
> >> correctly
> >> mapped to H2 date and then vice versa.
> >>
> >> Probably this is a kind of configuration problem.
> >>
> >> Vladimir.
> >>
> >> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
> >> dsetrakyan@apache.org>
> >> wrote:
> >>
> >> > I remember seeing some work done for the .NET support to provide
> better
> >> > precision for time data values. Could it be that SQL now converts
> >> > everything to Timestamp because of that?
> >> >
> >> > D.
> >> >
> >> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <is...@gridgain.com>
> >> > wrote:
> >> >
> >> > > Hello,
> >> > >
> >> > > Recently I've been working on implementation of the Date and
> Timestamp
> >> > > types support for C++ client [1] and I today have faced an issue
> when
> >> I
> >> > was
> >> > > running tests with Date and SqlFieldQuery.
> >> > >
> >> > > Here is the issue. I have class that have field of type 'Date'. I
> was
> >> > able
> >> > > to create
> >> > > instances of that class and put them in a cache, but when I tried to
> >> > > retrieve
> >> > > these fields with SQL query I've got an exception. After the short
> >> debug
> >> > > session
> >> > > I have found that the values that SQL queries return are of type
> >> > > 'Timestamp'.
> >> > >
> >> > > So now I'm wonder, is it an expected behavior? Because to me it
> looks
> >> > like
> >> > > something that may be very confusing for a user. User knows that
> >> object's
> >> > > field
> >> > > is of type 'Date' but when they try to call GetNext<Date> on query
> row
> >> > they
> >> > > get an
> >> > > exception.
> >> > >
> >> > > I have also tested simple caches with Date where Date is a value and
> >> > these
> >> > > tests
> >> > > work just fine with 'Date' returned as a result of Cache#Get()
> >> requests.
> >> > >
> >> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types
> >> support
> >> > for
> >> > > binary protocol. <https://issues.apache.org/jira/browse/IGNITE-2222
> >
> >> > > Best Regards,
> >> > > Igor
> >> > >
> >> >
> >>
> >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Igor Sapego <is...@gridgain.com>.
Sorry, I meant In our Binary marshaler we use *java.util.Date.*
Best Regards,
Igor
On Thu, Feb 11, 2016 at 6:12 PM, Igor Sapego <is...@gridgain.com> wrote:
> Ok, It seems like I have found what was causing the issue.
>
> In our
> apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
>
> /**
> * Initialize map of DB types.
> */
> static {
> map.put(int.class, INT);
> map.put(Integer.class, INT);
> map.put(boolean.class, BOOL);
> map.put(Boolean.class, BOOL);
> map.put(byte.class, TINYINT);
> map.put(Byte.class, TINYINT);
> map.put(short.class, SMALLINT);
> map.put(Short.class, SMALLINT);
> map.put(long.class, BIGINT);
> map.put(Long.class, BIGINT);
> map.put(BigDecimal.class, DECIMAL);
> map.put(double.class, DOUBLE);
> map.put(Double.class, DOUBLE);
> map.put(float.class, REAL);
> map.put(Float.class, REAL);
> map.put(Time.class, TIME);
> map.put(Timestamp.class, TIMESTAMP);
> map.put(java.util.Date.class, TIMESTAMP);
> map.put(java.sql.Date.class, DATE);
> map.put(String.class, VARCHAR);
> map.put(UUID.class, UUID);
> map.put(byte[].class, BINARY);
> }
>
> As I was using java.util.Date and not the java.sql.Date it was translated
> as TIMESTAMP
> and not as DATE. Are there any particular reason for java.util.Date being
> treated as a
> TIMESTAMP?
>
> In our Binary marshaler we use java.sql.Date and when I try to change
> configuration and
> make the Date field to be of the type java.sql.Date I've got an error,
> because this field value
> deserialized as java.sql.Date:
>
> lass org.apache.ignite.IgniteCheckedException: Failed to execute SQL query.
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
> [...]
> at
> org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
> Caused by: org.h2.jdbc.JdbcSQLException: "java.lang.ClassCastException:
> java.util.Date cannot be cast to java.sql.Date"
>
>
> Best Regards,
> Igor
>
> On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <vo...@gridgain.com>
> wrote:
>
>> There was some changes in how .NET interoperate w/ Java on binary level.
>> No
>> changes were made to cache or query logic.
>> I performed a smoke test in Java and observed that Date field was
>> correctly
>> mapped to H2 date and then vice versa.
>>
>> Probably this is a kind of configuration problem.
>>
>> Vladimir.
>>
>> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <
>> dsetrakyan@apache.org>
>> wrote:
>>
>> > I remember seeing some work done for the .NET support to provide better
>> > precision for time data values. Could it be that SQL now converts
>> > everything to Timestamp because of that?
>> >
>> > D.
>> >
>> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <is...@gridgain.com>
>> > wrote:
>> >
>> > > Hello,
>> > >
>> > > Recently I've been working on implementation of the Date and Timestamp
>> > > types support for C++ client [1] and I today have faced an issue when
>> I
>> > was
>> > > running tests with Date and SqlFieldQuery.
>> > >
>> > > Here is the issue. I have class that have field of type 'Date'. I was
>> > able
>> > > to create
>> > > instances of that class and put them in a cache, but when I tried to
>> > > retrieve
>> > > these fields with SQL query I've got an exception. After the short
>> debug
>> > > session
>> > > I have found that the values that SQL queries return are of type
>> > > 'Timestamp'.
>> > >
>> > > So now I'm wonder, is it an expected behavior? Because to me it looks
>> > like
>> > > something that may be very confusing for a user. User knows that
>> object's
>> > > field
>> > > is of type 'Date' but when they try to call GetNext<Date> on query row
>> > they
>> > > get an
>> > > exception.
>> > >
>> > > I have also tested simple caches with Date where Date is a value and
>> > these
>> > > tests
>> > > work just fine with 'Date' returned as a result of Cache#Get()
>> requests.
>> > >
>> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types
>> support
>> > for
>> > > binary protocol. <https://issues.apache.org/jira/browse/IGNITE-2222>
>> > > Best Regards,
>> > > Igor
>> > >
>> >
>>
>
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Igor Sapego <is...@gridgain.com>.
Ok, It seems like I have found what was causing the issue.
In our
apache.ignite.internal.processors.queryh.h2.IgniteH2Indexing.DBTypeEnum:
/**
* Initialize map of DB types.
*/
static {
map.put(int.class, INT);
map.put(Integer.class, INT);
map.put(boolean.class, BOOL);
map.put(Boolean.class, BOOL);
map.put(byte.class, TINYINT);
map.put(Byte.class, TINYINT);
map.put(short.class, SMALLINT);
map.put(Short.class, SMALLINT);
map.put(long.class, BIGINT);
map.put(Long.class, BIGINT);
map.put(BigDecimal.class, DECIMAL);
map.put(double.class, DOUBLE);
map.put(Double.class, DOUBLE);
map.put(float.class, REAL);
map.put(Float.class, REAL);
map.put(Time.class, TIME);
map.put(Timestamp.class, TIMESTAMP);
map.put(java.util.Date.class, TIMESTAMP);
map.put(java.sql.Date.class, DATE);
map.put(String.class, VARCHAR);
map.put(UUID.class, UUID);
map.put(byte[].class, BINARY);
}
As I was using java.util.Date and not the java.sql.Date it was translated
as TIMESTAMP
and not as DATE. Are there any particular reason for java.util.Date being
treated as a
TIMESTAMP?
In our Binary marshaler we use java.sql.Date and when I try to change
configuration and
make the Date field to be of the type java.sql.Date I've got an error,
because this field value
deserialized as java.sql.Date:
lass org.apache.ignite.IgniteCheckedException: Failed to execute SQL query.
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:831)
[...]
at
org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.iterator(PlatformAbstractQueryCursor.java:134)
Caused by: org.h2.jdbc.JdbcSQLException: "java.lang.ClassCastException:
java.util.Date cannot be cast to java.sql.Date"
Best Regards,
Igor
On Thu, Feb 11, 2016 at 12:39 PM, Vladimir Ozerov <vo...@gridgain.com>
wrote:
> There was some changes in how .NET interoperate w/ Java on binary level. No
> changes were made to cache or query logic.
> I performed a smoke test in Java and observed that Date field was correctly
> mapped to H2 date and then vice versa.
>
> Probably this is a kind of configuration problem.
>
> Vladimir.
>
> On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <dsetrakyan@apache.org
> >
> wrote:
>
> > I remember seeing some work done for the .NET support to provide better
> > precision for time data values. Could it be that SQL now converts
> > everything to Timestamp because of that?
> >
> > D.
> >
> > On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <is...@gridgain.com>
> > wrote:
> >
> > > Hello,
> > >
> > > Recently I've been working on implementation of the Date and Timestamp
> > > types support for C++ client [1] and I today have faced an issue when I
> > was
> > > running tests with Date and SqlFieldQuery.
> > >
> > > Here is the issue. I have class that have field of type 'Date'. I was
> > able
> > > to create
> > > instances of that class and put them in a cache, but when I tried to
> > > retrieve
> > > these fields with SQL query I've got an exception. After the short
> debug
> > > session
> > > I have found that the values that SQL queries return are of type
> > > 'Timestamp'.
> > >
> > > So now I'm wonder, is it an expected behavior? Because to me it looks
> > like
> > > something that may be very confusing for a user. User knows that
> object's
> > > field
> > > is of type 'Date' but when they try to call GetNext<Date> on query row
> > they
> > > get an
> > > exception.
> > >
> > > I have also tested simple caches with Date where Date is a value and
> > these
> > > tests
> > > work just fine with 'Date' returned as a result of Cache#Get()
> requests.
> > >
> > > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types support
> > for
> > > binary protocol. <https://issues.apache.org/jira/browse/IGNITE-2222>
> > > Best Regards,
> > > Igor
> > >
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Vladimir Ozerov <vo...@gridgain.com>.
There was some changes in how .NET interoperate w/ Java on binary level. No
changes were made to cache or query logic.
I performed a smoke test in Java and observed that Date field was correctly
mapped to H2 date and then vice versa.
Probably this is a kind of configuration problem.
Vladimir.
On Thu, Feb 11, 2016 at 12:41 AM, Dmitriy Setrakyan <ds...@apache.org>
wrote:
> I remember seeing some work done for the .NET support to provide better
> precision for time data values. Could it be that SQL now converts
> everything to Timestamp because of that?
>
> D.
>
> On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <is...@gridgain.com>
> wrote:
>
> > Hello,
> >
> > Recently I've been working on implementation of the Date and Timestamp
> > types support for C++ client [1] and I today have faced an issue when I
> was
> > running tests with Date and SqlFieldQuery.
> >
> > Here is the issue. I have class that have field of type 'Date'. I was
> able
> > to create
> > instances of that class and put them in a cache, but when I tried to
> > retrieve
> > these fields with SQL query I've got an exception. After the short debug
> > session
> > I have found that the values that SQL queries return are of type
> > 'Timestamp'.
> >
> > So now I'm wonder, is it an expected behavior? Because to me it looks
> like
> > something that may be very confusing for a user. User knows that object's
> > field
> > is of type 'Date' but when they try to call GetNext<Date> on query row
> they
> > get an
> > exception.
> >
> > I have also tested simple caches with Date where Date is a value and
> these
> > tests
> > work just fine with 'Date' returned as a result of Cache#Get() requests.
> >
> > [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types support
> for
> > binary protocol. <https://issues.apache.org/jira/browse/IGNITE-2222>
> > Best Regards,
> > Igor
> >
>
Re: 'Date' and 'Timestamp' types in SQL queries
Posted by Dmitriy Setrakyan <ds...@apache.org>.
I remember seeing some work done for the .NET support to provide better
precision for time data values. Could it be that SQL now converts
everything to Timestamp because of that?
D.
On Wed, Feb 10, 2016 at 10:09 AM, Igor Sapego <is...@gridgain.com> wrote:
> Hello,
>
> Recently I've been working on implementation of the Date and Timestamp
> types support for C++ client [1] and I today have faced an issue when I was
> running tests with Date and SqlFieldQuery.
>
> Here is the issue. I have class that have field of type 'Date'. I was able
> to create
> instances of that class and put them in a cache, but when I tried to
> retrieve
> these fields with SQL query I've got an exception. After the short debug
> session
> I have found that the values that SQL queries return are of type
> 'Timestamp'.
>
> So now I'm wonder, is it an expected behavior? Because to me it looks like
> something that may be very confusing for a user. User knows that object's
> field
> is of type 'Date' but when they try to call GetNext<Date> on query row they
> get an
> exception.
>
> I have also tested simple caches with Date where Date is a value and these
> tests
> work just fine with 'Date' returned as a result of Cache#Get() requests.
>
> [1] - IGNITE-2222: CPP: Implement Date and Timestamp data types support for
> binary protocol. <https://issues.apache.org/jira/browse/IGNITE-2222>
> Best Regards,
> Igor
>