You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Valentin Kulichenko <va...@gmail.com> on 2016/07/22 21:43:27 UTC
Enums and SQL queries
Folks,
I noticed that we currently have a very weird limitation for enum fields
when running SQL queries. Basically, you can't use enum value names as many
users would expect.
Technically this happens because binary format stores only ordinal and the
only way to run such query is to do something like this:
SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
enumField = ?");
qry.setArgs(MyEnum.ENUM_VALUE);
cache.query(qry);
This means that this query can be executed only if:
- IgniteCache API is used. So it doesn't work in JDBC driver, Zeppelin, etc.
- The client that executes the query has the enum class on classpath.
My first thought about fixing this is to somehow store value names in
binary metadata and patch the SQL engine so that it understands how to use
this information.
Thoughts?
-Val
Re: Enums and SQL queries
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Sergi, in this case, can you please describe the design n the ticket, so
someone could pick this up?
On Thu, Jul 28, 2016 at 4:14 AM, Sergi Vladykin <se...@gmail.com>
wrote:
> No, as I told you, we have to contribute this feature to H2.
>
> Sergi
>
> 2016-07-28 10:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
>
> > Sergi, is there a standard way to add a custom type to H2? Is it
> > documented?
> >
> > On Thu, Jul 28, 2016 at 2:17 AM, Sergi Vladykin <
> sergi.vladykin@gmail.com>
> > wrote:
> >
> > > Sure.
> > >
> > > https://issues.apache.org/jira/browse/IGNITE-3595
> > >
> > > Sergi
> > >
> > > 2016-07-27 22:51 GMT+03:00 Dmitriy Setrakyan <dsetrakyan@gridgain.com
> >:
> > >
> > > > Sorry, Sergi. What I meant is to file a ticket with the design you
> > > > suggested
> > > >
> > > > Dmitriy
> > > >
> > > >
> > > >
> > > > > On Jul 27, 2016, at 9:18 AM, Sergi Vladykin <
> > sergi.vladykin@gmail.com>
> > > > wrote:
> > > > >
> > > > > I don't see how this can work to file a ticket, could you please
> give
> > > > more
> > > > > details?
> > > > >
> > > > > Sergi
> > > > >
> > > > > 2016-07-27 15:50 GMT+03:00 Dmitriy Setrakyan <
> dsetrakyan@apache.org
> > >:
> > > > >
> > > > >> Sergi, I still thinks my solution works or can be made to work. In
> > the
> > > > >> example you provided, you can choose to return either String or
> Enum
> > > as
> > > > the
> > > > >> query result. However, this is not a clean solution, I agree, but
> > > could
> > > > be
> > > > >> a quick one.
> > > > >>
> > > > >> I don't mind breaking compatibility on this issue, given that it
> > never
> > > > >> worked properly anyway. Can you please file a ticket?
> > > > >>
> > > > >> D.
> > > > >>
> > > > >> On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <
> > > > sergi.vladykin@gmail.com>
> > > > >> wrote:
> > > > >>
> > > > >>> Nope. This will not work. Because take for example the following
> > > query:
> > > > >>>
> > > > >>> select my_enum_field from my_table where my_enum_field = 'BLA'
> > > > >>>
> > > > >>> Field well be evaluated only once here, but it must be a Sting
> and
> > > Enum
> > > > >> at
> > > > >>> the same time.
> > > > >>
> > > > >>
> > > > >>> Probably we could contribute to H2 `User defined value types` and
> > > > handle
> > > > >>> this stuff in our own Enum value types, which will support
> > comparison
> > > > >> with
> > > > >>> String. But this requires bug H2 Modification and is a
> > compatibility
> > > > >>> breaking change.
> > > > >>>
> > > > >>> Sergi
> > > > >>>
> > > > >>>
> > > > >>>
> > > > >>> 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <
> dsetrakyan@apache.org
> > >:
> > > > >>>
> > > > >>>> Hm... I would prefer to find a way to handle Enums
> automatically.
> > > > >>>>
> > > > >>>> In case if user expects a String, can we catch an exception and
> do
> > > > >>>> automatic conversion at that time? In this case, we could catch
> > the
> > > > >>>> exception once and remember the decision. Will something like
> this
> > > > >> work?
> > > > >>>>
> > > > >>>> D.
> > > > >>>>
> > > > >>>> On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
> > > > >>> sergi.vladykin@gmail.com>
> > > > >>>> wrote:
> > > > >>>>
> > > > >>>>> GridQueryProcessor contains two types: BinaryProperty and
> > > > >>> ClassProperty.
> > > > >>>>> Indexing uses them for fields access. Probably we can change
> > them,
> > > so
> > > > >>>> that
> > > > >>>>> they will always return String instead of Enum type but this
> > > > >>>>>
> > > > >>>>> 1. looks like a compatibility breaking change;
> > > > >>>>> 2. users doing queries like `select my_enum_field from
> my_table`
> > > > >>> probably
> > > > >>>>> still expect to get a Enum result but not a String.
> > > > >>>>>
> > > > >>>>> What do you think?
> > > > >>>>>
> > > > >>>>> Sergi
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> > > > >>>>> valentin.kulichenko@gmail.com
> > > > >>>>>> :
> > > > >>>>>
> > > > >>>>>> Sergi,
> > > > >>>>>>
> > > > >>>>>> Ideally, I would like this query to work:
> > > > >>>>>>
> > > > >>>>>> select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> > > > >>>>>>
> > > > >>>>>> The problem is that the field value is not a string, it's a
> > binary
> > > > >>>> object
> > > > >>>>>> with the enum value ordinal inside. We can add enum value
> names
> > > > >> into
> > > > >>>> the
> > > > >>>>>> metadata, so that we can always map the field value to its
> > string
> > > > >>>>>> representation, but SQL engine will have to know how to use
> it.
> > Is
> > > > >>> this
> > > > >>>>>> possible?
> > > > >>>>>>
> > > > >>>>>> -Val
> > > > >>>>>>
> > > > >>>>>> On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> > > > >>>>> sergi.vladykin@gmail.com
> > > > >>>>>>> wrote:
> > > > >>>>>>
> > > > >>>>>>> I'm not sure what exactly you are going to patch in SQL
> engine.
> > > We
> > > > >>>> could
> > > > >>>>>>> and some SQL function like enum('MY_ENUM_NAME') but probably
> it
> > > > >> will
> > > > >>>> not
> > > > >>>>>>> work if we will not pass enum type there as well, so it have
> > look
> > > > >>> like
> > > > >>>>>>> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> > > > >>>>>>>
> > > > >>>>>>> Sergi
> > > > >>>>>>>
> > > > >>>>>>> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> > > > >>>>>>> valentin.kulichenko@gmail.com
> > > > >>>>>>>> wrote:
> > > > >>>>>>>
> > > > >>>>>>>> Folks,
> > > > >>>>>>>>
> > > > >>>>>>>> I noticed that we currently have a very weird limitation for
> > > > >> enum
> > > > >>>>> fields
> > > > >>>>>>>> when running SQL queries. Basically, you can't use enum
> value
> > > > >>> names
> > > > >>>> as
> > > > >>>>>>> many
> > > > >>>>>>>> users would expect.
> > > > >>>>>>>>
> > > > >>>>>>>> Technically this happens because binary format stores only
> > > > >> ordinal
> > > > >>>> and
> > > > >>>>>>> the
> > > > >>>>>>>> only way to run such query is to do something like this:
> > > > >>>>>>>>
> > > > >>>>>>>> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table
> > > > >> where
> > > > >>>>>>>> enumField = ?");
> > > > >>>>>>>> qry.setArgs(MyEnum.ENUM_VALUE);
> > > > >>>>>>>> cache.query(qry);
> > > > >>>>>>>>
> > > > >>>>>>>> This means that this query can be executed only if:
> > > > >>>>>>>> - IgniteCache API is used. So it doesn't work in JDBC
> driver,
> > > > >>>>> Zeppelin,
> > > > >>>>>>>> etc.
> > > > >>>>>>>> - The client that executes the query has the enum class on
> > > > >>>> classpath.
> > > > >>>>>>>>
> > > > >>>>>>>> My first thought about fixing this is to somehow store value
> > > > >> names
> > > > >>>> in
> > > > >>>>>>>> binary metadata and patch the SQL engine so that it
> > understands
> > > > >>> how
> > > > >>>> to
> > > > >>>>>>> use
> > > > >>>>>>>> this information.
> > > > >>>>>>>>
> > > > >>>>>>>> Thoughts?
> > > > >>>>>>>>
> > > > >>>>>>>> -Val
> > > > >>
> > > >
> > >
> >
>
Re: Enums and SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
No, as I told you, we have to contribute this feature to H2.
Sergi
2016-07-28 10:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> Sergi, is there a standard way to add a custom type to H2? Is it
> documented?
>
> On Thu, Jul 28, 2016 at 2:17 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
>
> > Sure.
> >
> > https://issues.apache.org/jira/browse/IGNITE-3595
> >
> > Sergi
> >
> > 2016-07-27 22:51 GMT+03:00 Dmitriy Setrakyan <ds...@gridgain.com>:
> >
> > > Sorry, Sergi. What I meant is to file a ticket with the design you
> > > suggested
> > >
> > > Dmitriy
> > >
> > >
> > >
> > > > On Jul 27, 2016, at 9:18 AM, Sergi Vladykin <
> sergi.vladykin@gmail.com>
> > > wrote:
> > > >
> > > > I don't see how this can work to file a ticket, could you please give
> > > more
> > > > details?
> > > >
> > > > Sergi
> > > >
> > > > 2016-07-27 15:50 GMT+03:00 Dmitriy Setrakyan <dsetrakyan@apache.org
> >:
> > > >
> > > >> Sergi, I still thinks my solution works or can be made to work. In
> the
> > > >> example you provided, you can choose to return either String or Enum
> > as
> > > the
> > > >> query result. However, this is not a clean solution, I agree, but
> > could
> > > be
> > > >> a quick one.
> > > >>
> > > >> I don't mind breaking compatibility on this issue, given that it
> never
> > > >> worked properly anyway. Can you please file a ticket?
> > > >>
> > > >> D.
> > > >>
> > > >> On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <
> > > sergi.vladykin@gmail.com>
> > > >> wrote:
> > > >>
> > > >>> Nope. This will not work. Because take for example the following
> > query:
> > > >>>
> > > >>> select my_enum_field from my_table where my_enum_field = 'BLA'
> > > >>>
> > > >>> Field well be evaluated only once here, but it must be a Sting and
> > Enum
> > > >> at
> > > >>> the same time.
> > > >>
> > > >>
> > > >>> Probably we could contribute to H2 `User defined value types` and
> > > handle
> > > >>> this stuff in our own Enum value types, which will support
> comparison
> > > >> with
> > > >>> String. But this requires bug H2 Modification and is a
> compatibility
> > > >>> breaking change.
> > > >>>
> > > >>> Sergi
> > > >>>
> > > >>>
> > > >>>
> > > >>> 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <dsetrakyan@apache.org
> >:
> > > >>>
> > > >>>> Hm... I would prefer to find a way to handle Enums automatically.
> > > >>>>
> > > >>>> In case if user expects a String, can we catch an exception and do
> > > >>>> automatic conversion at that time? In this case, we could catch
> the
> > > >>>> exception once and remember the decision. Will something like this
> > > >> work?
> > > >>>>
> > > >>>> D.
> > > >>>>
> > > >>>> On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
> > > >>> sergi.vladykin@gmail.com>
> > > >>>> wrote:
> > > >>>>
> > > >>>>> GridQueryProcessor contains two types: BinaryProperty and
> > > >>> ClassProperty.
> > > >>>>> Indexing uses them for fields access. Probably we can change
> them,
> > so
> > > >>>> that
> > > >>>>> they will always return String instead of Enum type but this
> > > >>>>>
> > > >>>>> 1. looks like a compatibility breaking change;
> > > >>>>> 2. users doing queries like `select my_enum_field from my_table`
> > > >>> probably
> > > >>>>> still expect to get a Enum result but not a String.
> > > >>>>>
> > > >>>>> What do you think?
> > > >>>>>
> > > >>>>> Sergi
> > > >>>>>
> > > >>>>>
> > > >>>>> 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> > > >>>>> valentin.kulichenko@gmail.com
> > > >>>>>> :
> > > >>>>>
> > > >>>>>> Sergi,
> > > >>>>>>
> > > >>>>>> Ideally, I would like this query to work:
> > > >>>>>>
> > > >>>>>> select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> > > >>>>>>
> > > >>>>>> The problem is that the field value is not a string, it's a
> binary
> > > >>>> object
> > > >>>>>> with the enum value ordinal inside. We can add enum value names
> > > >> into
> > > >>>> the
> > > >>>>>> metadata, so that we can always map the field value to its
> string
> > > >>>>>> representation, but SQL engine will have to know how to use it.
> Is
> > > >>> this
> > > >>>>>> possible?
> > > >>>>>>
> > > >>>>>> -Val
> > > >>>>>>
> > > >>>>>> On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> > > >>>>> sergi.vladykin@gmail.com
> > > >>>>>>> wrote:
> > > >>>>>>
> > > >>>>>>> I'm not sure what exactly you are going to patch in SQL engine.
> > We
> > > >>>> could
> > > >>>>>>> and some SQL function like enum('MY_ENUM_NAME') but probably it
> > > >> will
> > > >>>> not
> > > >>>>>>> work if we will not pass enum type there as well, so it have
> look
> > > >>> like
> > > >>>>>>> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> > > >>>>>>>
> > > >>>>>>> Sergi
> > > >>>>>>>
> > > >>>>>>> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> > > >>>>>>> valentin.kulichenko@gmail.com
> > > >>>>>>>> wrote:
> > > >>>>>>>
> > > >>>>>>>> Folks,
> > > >>>>>>>>
> > > >>>>>>>> I noticed that we currently have a very weird limitation for
> > > >> enum
> > > >>>>> fields
> > > >>>>>>>> when running SQL queries. Basically, you can't use enum value
> > > >>> names
> > > >>>> as
> > > >>>>>>> many
> > > >>>>>>>> users would expect.
> > > >>>>>>>>
> > > >>>>>>>> Technically this happens because binary format stores only
> > > >> ordinal
> > > >>>> and
> > > >>>>>>> the
> > > >>>>>>>> only way to run such query is to do something like this:
> > > >>>>>>>>
> > > >>>>>>>> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table
> > > >> where
> > > >>>>>>>> enumField = ?");
> > > >>>>>>>> qry.setArgs(MyEnum.ENUM_VALUE);
> > > >>>>>>>> cache.query(qry);
> > > >>>>>>>>
> > > >>>>>>>> This means that this query can be executed only if:
> > > >>>>>>>> - IgniteCache API is used. So it doesn't work in JDBC driver,
> > > >>>>> Zeppelin,
> > > >>>>>>>> etc.
> > > >>>>>>>> - The client that executes the query has the enum class on
> > > >>>> classpath.
> > > >>>>>>>>
> > > >>>>>>>> My first thought about fixing this is to somehow store value
> > > >> names
> > > >>>> in
> > > >>>>>>>> binary metadata and patch the SQL engine so that it
> understands
> > > >>> how
> > > >>>> to
> > > >>>>>>> use
> > > >>>>>>>> this information.
> > > >>>>>>>>
> > > >>>>>>>> Thoughts?
> > > >>>>>>>>
> > > >>>>>>>> -Val
> > > >>
> > >
> >
>
Re: Enums and SQL queries
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Sergi, is there a standard way to add a custom type to H2? Is it documented?
On Thu, Jul 28, 2016 at 2:17 AM, Sergi Vladykin <se...@gmail.com>
wrote:
> Sure.
>
> https://issues.apache.org/jira/browse/IGNITE-3595
>
> Sergi
>
> 2016-07-27 22:51 GMT+03:00 Dmitriy Setrakyan <ds...@gridgain.com>:
>
> > Sorry, Sergi. What I meant is to file a ticket with the design you
> > suggested
> >
> > Dmitriy
> >
> >
> >
> > > On Jul 27, 2016, at 9:18 AM, Sergi Vladykin <se...@gmail.com>
> > wrote:
> > >
> > > I don't see how this can work to file a ticket, could you please give
> > more
> > > details?
> > >
> > > Sergi
> > >
> > > 2016-07-27 15:50 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> > >
> > >> Sergi, I still thinks my solution works or can be made to work. In the
> > >> example you provided, you can choose to return either String or Enum
> as
> > the
> > >> query result. However, this is not a clean solution, I agree, but
> could
> > be
> > >> a quick one.
> > >>
> > >> I don't mind breaking compatibility on this issue, given that it never
> > >> worked properly anyway. Can you please file a ticket?
> > >>
> > >> D.
> > >>
> > >> On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <
> > sergi.vladykin@gmail.com>
> > >> wrote:
> > >>
> > >>> Nope. This will not work. Because take for example the following
> query:
> > >>>
> > >>> select my_enum_field from my_table where my_enum_field = 'BLA'
> > >>>
> > >>> Field well be evaluated only once here, but it must be a Sting and
> Enum
> > >> at
> > >>> the same time.
> > >>
> > >>
> > >>> Probably we could contribute to H2 `User defined value types` and
> > handle
> > >>> this stuff in our own Enum value types, which will support comparison
> > >> with
> > >>> String. But this requires bug H2 Modification and is a compatibility
> > >>> breaking change.
> > >>>
> > >>> Sergi
> > >>>
> > >>>
> > >>>
> > >>> 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> > >>>
> > >>>> Hm... I would prefer to find a way to handle Enums automatically.
> > >>>>
> > >>>> In case if user expects a String, can we catch an exception and do
> > >>>> automatic conversion at that time? In this case, we could catch the
> > >>>> exception once and remember the decision. Will something like this
> > >> work?
> > >>>>
> > >>>> D.
> > >>>>
> > >>>> On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
> > >>> sergi.vladykin@gmail.com>
> > >>>> wrote:
> > >>>>
> > >>>>> GridQueryProcessor contains two types: BinaryProperty and
> > >>> ClassProperty.
> > >>>>> Indexing uses them for fields access. Probably we can change them,
> so
> > >>>> that
> > >>>>> they will always return String instead of Enum type but this
> > >>>>>
> > >>>>> 1. looks like a compatibility breaking change;
> > >>>>> 2. users doing queries like `select my_enum_field from my_table`
> > >>> probably
> > >>>>> still expect to get a Enum result but not a String.
> > >>>>>
> > >>>>> What do you think?
> > >>>>>
> > >>>>> Sergi
> > >>>>>
> > >>>>>
> > >>>>> 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> > >>>>> valentin.kulichenko@gmail.com
> > >>>>>> :
> > >>>>>
> > >>>>>> Sergi,
> > >>>>>>
> > >>>>>> Ideally, I would like this query to work:
> > >>>>>>
> > >>>>>> select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> > >>>>>>
> > >>>>>> The problem is that the field value is not a string, it's a binary
> > >>>> object
> > >>>>>> with the enum value ordinal inside. We can add enum value names
> > >> into
> > >>>> the
> > >>>>>> metadata, so that we can always map the field value to its string
> > >>>>>> representation, but SQL engine will have to know how to use it. Is
> > >>> this
> > >>>>>> possible?
> > >>>>>>
> > >>>>>> -Val
> > >>>>>>
> > >>>>>> On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> > >>>>> sergi.vladykin@gmail.com
> > >>>>>>> wrote:
> > >>>>>>
> > >>>>>>> I'm not sure what exactly you are going to patch in SQL engine.
> We
> > >>>> could
> > >>>>>>> and some SQL function like enum('MY_ENUM_NAME') but probably it
> > >> will
> > >>>> not
> > >>>>>>> work if we will not pass enum type there as well, so it have look
> > >>> like
> > >>>>>>> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> > >>>>>>>
> > >>>>>>> Sergi
> > >>>>>>>
> > >>>>>>> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> > >>>>>>> valentin.kulichenko@gmail.com
> > >>>>>>>> wrote:
> > >>>>>>>
> > >>>>>>>> Folks,
> > >>>>>>>>
> > >>>>>>>> I noticed that we currently have a very weird limitation for
> > >> enum
> > >>>>> fields
> > >>>>>>>> when running SQL queries. Basically, you can't use enum value
> > >>> names
> > >>>> as
> > >>>>>>> many
> > >>>>>>>> users would expect.
> > >>>>>>>>
> > >>>>>>>> Technically this happens because binary format stores only
> > >> ordinal
> > >>>> and
> > >>>>>>> the
> > >>>>>>>> only way to run such query is to do something like this:
> > >>>>>>>>
> > >>>>>>>> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table
> > >> where
> > >>>>>>>> enumField = ?");
> > >>>>>>>> qry.setArgs(MyEnum.ENUM_VALUE);
> > >>>>>>>> cache.query(qry);
> > >>>>>>>>
> > >>>>>>>> This means that this query can be executed only if:
> > >>>>>>>> - IgniteCache API is used. So it doesn't work in JDBC driver,
> > >>>>> Zeppelin,
> > >>>>>>>> etc.
> > >>>>>>>> - The client that executes the query has the enum class on
> > >>>> classpath.
> > >>>>>>>>
> > >>>>>>>> My first thought about fixing this is to somehow store value
> > >> names
> > >>>> in
> > >>>>>>>> binary metadata and patch the SQL engine so that it understands
> > >>> how
> > >>>> to
> > >>>>>>> use
> > >>>>>>>> this information.
> > >>>>>>>>
> > >>>>>>>> Thoughts?
> > >>>>>>>>
> > >>>>>>>> -Val
> > >>
> >
>
Re: Enums and SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
Sure.
https://issues.apache.org/jira/browse/IGNITE-3595
Sergi
2016-07-27 22:51 GMT+03:00 Dmitriy Setrakyan <ds...@gridgain.com>:
> Sorry, Sergi. What I meant is to file a ticket with the design you
> suggested
>
> Dmitriy
>
>
>
> > On Jul 27, 2016, at 9:18 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
> >
> > I don't see how this can work to file a ticket, could you please give
> more
> > details?
> >
> > Sergi
> >
> > 2016-07-27 15:50 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> >
> >> Sergi, I still thinks my solution works or can be made to work. In the
> >> example you provided, you can choose to return either String or Enum as
> the
> >> query result. However, this is not a clean solution, I agree, but could
> be
> >> a quick one.
> >>
> >> I don't mind breaking compatibility on this issue, given that it never
> >> worked properly anyway. Can you please file a ticket?
> >>
> >> D.
> >>
> >> On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <
> sergi.vladykin@gmail.com>
> >> wrote:
> >>
> >>> Nope. This will not work. Because take for example the following query:
> >>>
> >>> select my_enum_field from my_table where my_enum_field = 'BLA'
> >>>
> >>> Field well be evaluated only once here, but it must be a Sting and Enum
> >> at
> >>> the same time.
> >>
> >>
> >>> Probably we could contribute to H2 `User defined value types` and
> handle
> >>> this stuff in our own Enum value types, which will support comparison
> >> with
> >>> String. But this requires bug H2 Modification and is a compatibility
> >>> breaking change.
> >>>
> >>> Sergi
> >>>
> >>>
> >>>
> >>> 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> >>>
> >>>> Hm... I would prefer to find a way to handle Enums automatically.
> >>>>
> >>>> In case if user expects a String, can we catch an exception and do
> >>>> automatic conversion at that time? In this case, we could catch the
> >>>> exception once and remember the decision. Will something like this
> >> work?
> >>>>
> >>>> D.
> >>>>
> >>>> On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
> >>> sergi.vladykin@gmail.com>
> >>>> wrote:
> >>>>
> >>>>> GridQueryProcessor contains two types: BinaryProperty and
> >>> ClassProperty.
> >>>>> Indexing uses them for fields access. Probably we can change them, so
> >>>> that
> >>>>> they will always return String instead of Enum type but this
> >>>>>
> >>>>> 1. looks like a compatibility breaking change;
> >>>>> 2. users doing queries like `select my_enum_field from my_table`
> >>> probably
> >>>>> still expect to get a Enum result but not a String.
> >>>>>
> >>>>> What do you think?
> >>>>>
> >>>>> Sergi
> >>>>>
> >>>>>
> >>>>> 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> >>>>> valentin.kulichenko@gmail.com
> >>>>>> :
> >>>>>
> >>>>>> Sergi,
> >>>>>>
> >>>>>> Ideally, I would like this query to work:
> >>>>>>
> >>>>>> select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> >>>>>>
> >>>>>> The problem is that the field value is not a string, it's a binary
> >>>> object
> >>>>>> with the enum value ordinal inside. We can add enum value names
> >> into
> >>>> the
> >>>>>> metadata, so that we can always map the field value to its string
> >>>>>> representation, but SQL engine will have to know how to use it. Is
> >>> this
> >>>>>> possible?
> >>>>>>
> >>>>>> -Val
> >>>>>>
> >>>>>> On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> >>>>> sergi.vladykin@gmail.com
> >>>>>>> wrote:
> >>>>>>
> >>>>>>> I'm not sure what exactly you are going to patch in SQL engine. We
> >>>> could
> >>>>>>> and some SQL function like enum('MY_ENUM_NAME') but probably it
> >> will
> >>>> not
> >>>>>>> work if we will not pass enum type there as well, so it have look
> >>> like
> >>>>>>> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> >>>>>>>
> >>>>>>> Sergi
> >>>>>>>
> >>>>>>> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> >>>>>>> valentin.kulichenko@gmail.com
> >>>>>>>> wrote:
> >>>>>>>
> >>>>>>>> Folks,
> >>>>>>>>
> >>>>>>>> I noticed that we currently have a very weird limitation for
> >> enum
> >>>>> fields
> >>>>>>>> when running SQL queries. Basically, you can't use enum value
> >>> names
> >>>> as
> >>>>>>> many
> >>>>>>>> users would expect.
> >>>>>>>>
> >>>>>>>> Technically this happens because binary format stores only
> >> ordinal
> >>>> and
> >>>>>>> the
> >>>>>>>> only way to run such query is to do something like this:
> >>>>>>>>
> >>>>>>>> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table
> >> where
> >>>>>>>> enumField = ?");
> >>>>>>>> qry.setArgs(MyEnum.ENUM_VALUE);
> >>>>>>>> cache.query(qry);
> >>>>>>>>
> >>>>>>>> This means that this query can be executed only if:
> >>>>>>>> - IgniteCache API is used. So it doesn't work in JDBC driver,
> >>>>> Zeppelin,
> >>>>>>>> etc.
> >>>>>>>> - The client that executes the query has the enum class on
> >>>> classpath.
> >>>>>>>>
> >>>>>>>> My first thought about fixing this is to somehow store value
> >> names
> >>>> in
> >>>>>>>> binary metadata and patch the SQL engine so that it understands
> >>> how
> >>>> to
> >>>>>>> use
> >>>>>>>> this information.
> >>>>>>>>
> >>>>>>>> Thoughts?
> >>>>>>>>
> >>>>>>>> -Val
> >>
>
Re: Enums and SQL queries
Posted by Dmitriy Setrakyan <ds...@gridgain.com>.
Sorry, Sergi. What I meant is to file a ticket with the design you suggested
Dmitriy
> On Jul 27, 2016, at 9:18 AM, Sergi Vladykin <se...@gmail.com> wrote:
>
> I don't see how this can work to file a ticket, could you please give more
> details?
>
> Sergi
>
> 2016-07-27 15:50 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
>
>> Sergi, I still thinks my solution works or can be made to work. In the
>> example you provided, you can choose to return either String or Enum as the
>> query result. However, this is not a clean solution, I agree, but could be
>> a quick one.
>>
>> I don't mind breaking compatibility on this issue, given that it never
>> worked properly anyway. Can you please file a ticket?
>>
>> D.
>>
>> On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <se...@gmail.com>
>> wrote:
>>
>>> Nope. This will not work. Because take for example the following query:
>>>
>>> select my_enum_field from my_table where my_enum_field = 'BLA'
>>>
>>> Field well be evaluated only once here, but it must be a Sting and Enum
>> at
>>> the same time.
>>
>>
>>> Probably we could contribute to H2 `User defined value types` and handle
>>> this stuff in our own Enum value types, which will support comparison
>> with
>>> String. But this requires bug H2 Modification and is a compatibility
>>> breaking change.
>>>
>>> Sergi
>>>
>>>
>>>
>>> 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
>>>
>>>> Hm... I would prefer to find a way to handle Enums automatically.
>>>>
>>>> In case if user expects a String, can we catch an exception and do
>>>> automatic conversion at that time? In this case, we could catch the
>>>> exception once and remember the decision. Will something like this
>> work?
>>>>
>>>> D.
>>>>
>>>> On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
>>> sergi.vladykin@gmail.com>
>>>> wrote:
>>>>
>>>>> GridQueryProcessor contains two types: BinaryProperty and
>>> ClassProperty.
>>>>> Indexing uses them for fields access. Probably we can change them, so
>>>> that
>>>>> they will always return String instead of Enum type but this
>>>>>
>>>>> 1. looks like a compatibility breaking change;
>>>>> 2. users doing queries like `select my_enum_field from my_table`
>>> probably
>>>>> still expect to get a Enum result but not a String.
>>>>>
>>>>> What do you think?
>>>>>
>>>>> Sergi
>>>>>
>>>>>
>>>>> 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
>>>>> valentin.kulichenko@gmail.com
>>>>>> :
>>>>>
>>>>>> Sergi,
>>>>>>
>>>>>> Ideally, I would like this query to work:
>>>>>>
>>>>>> select * from MyTable where myEnumField = 'MY_ENUM_NAME'
>>>>>>
>>>>>> The problem is that the field value is not a string, it's a binary
>>>> object
>>>>>> with the enum value ordinal inside. We can add enum value names
>> into
>>>> the
>>>>>> metadata, so that we can always map the field value to its string
>>>>>> representation, but SQL engine will have to know how to use it. Is
>>> this
>>>>>> possible?
>>>>>>
>>>>>> -Val
>>>>>>
>>>>>> On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
>>>>> sergi.vladykin@gmail.com
>>>>>>> wrote:
>>>>>>
>>>>>>> I'm not sure what exactly you are going to patch in SQL engine. We
>>>> could
>>>>>>> and some SQL function like enum('MY_ENUM_NAME') but probably it
>> will
>>>> not
>>>>>>> work if we will not pass enum type there as well, so it have look
>>> like
>>>>>>> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
>>>>>>>
>>>>>>> Sergi
>>>>>>>
>>>>>>> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
>>>>>>> valentin.kulichenko@gmail.com
>>>>>>>> wrote:
>>>>>>>
>>>>>>>> Folks,
>>>>>>>>
>>>>>>>> I noticed that we currently have a very weird limitation for
>> enum
>>>>> fields
>>>>>>>> when running SQL queries. Basically, you can't use enum value
>>> names
>>>> as
>>>>>>> many
>>>>>>>> users would expect.
>>>>>>>>
>>>>>>>> Technically this happens because binary format stores only
>> ordinal
>>>> and
>>>>>>> the
>>>>>>>> only way to run such query is to do something like this:
>>>>>>>>
>>>>>>>> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table
>> where
>>>>>>>> enumField = ?");
>>>>>>>> qry.setArgs(MyEnum.ENUM_VALUE);
>>>>>>>> cache.query(qry);
>>>>>>>>
>>>>>>>> This means that this query can be executed only if:
>>>>>>>> - IgniteCache API is used. So it doesn't work in JDBC driver,
>>>>> Zeppelin,
>>>>>>>> etc.
>>>>>>>> - The client that executes the query has the enum class on
>>>> classpath.
>>>>>>>>
>>>>>>>> My first thought about fixing this is to somehow store value
>> names
>>>> in
>>>>>>>> binary metadata and patch the SQL engine so that it understands
>>> how
>>>> to
>>>>>>> use
>>>>>>>> this information.
>>>>>>>>
>>>>>>>> Thoughts?
>>>>>>>>
>>>>>>>> -Val
>>
Re: Enums and SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
I don't see how this can work to file a ticket, could you please give more
details?
Sergi
2016-07-27 15:50 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> Sergi, I still thinks my solution works or can be made to work. In the
> example you provided, you can choose to return either String or Enum as the
> query result. However, this is not a clean solution, I agree, but could be
> a quick one.
>
> I don't mind breaking compatibility on this issue, given that it never
> worked properly anyway. Can you please file a ticket?
>
> D.
>
> On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
>
> > Nope. This will not work. Because take for example the following query:
> >
> > select my_enum_field from my_table where my_enum_field = 'BLA'
> >
> > Field well be evaluated only once here, but it must be a Sting and Enum
> at
> > the same time.
>
>
> > Probably we could contribute to H2 `User defined value types` and handle
> > this stuff in our own Enum value types, which will support comparison
> with
> > String. But this requires bug H2 Modification and is a compatibility
> > breaking change.
> >
> > Sergi
> >
> >
> >
> > 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> >
> > > Hm... I would prefer to find a way to handle Enums automatically.
> > >
> > > In case if user expects a String, can we catch an exception and do
> > > automatic conversion at that time? In this case, we could catch the
> > > exception once and remember the decision. Will something like this
> work?
> > >
> > > D.
> > >
> > > On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
> > sergi.vladykin@gmail.com>
> > > wrote:
> > >
> > > > GridQueryProcessor contains two types: BinaryProperty and
> > ClassProperty.
> > > > Indexing uses them for fields access. Probably we can change them, so
> > > that
> > > > they will always return String instead of Enum type but this
> > > >
> > > > 1. looks like a compatibility breaking change;
> > > > 2. users doing queries like `select my_enum_field from my_table`
> > probably
> > > > still expect to get a Enum result but not a String.
> > > >
> > > > What do you think?
> > > >
> > > > Sergi
> > > >
> > > >
> > > > 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> > > > valentin.kulichenko@gmail.com
> > > > >:
> > > >
> > > > > Sergi,
> > > > >
> > > > > Ideally, I would like this query to work:
> > > > >
> > > > > select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> > > > >
> > > > > The problem is that the field value is not a string, it's a binary
> > > object
> > > > > with the enum value ordinal inside. We can add enum value names
> into
> > > the
> > > > > metadata, so that we can always map the field value to its string
> > > > > representation, but SQL engine will have to know how to use it. Is
> > this
> > > > > possible?
> > > > >
> > > > > -Val
> > > > >
> > > > > On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> > > > sergi.vladykin@gmail.com
> > > > > > wrote:
> > > > >
> > > > >> I'm not sure what exactly you are going to patch in SQL engine. We
> > > could
> > > > >> and some SQL function like enum('MY_ENUM_NAME') but probably it
> will
> > > not
> > > > >> work if we will not pass enum type there as well, so it have look
> > like
> > > > >> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> > > > >>
> > > > >> Sergi
> > > > >>
> > > > >> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> > > > >> valentin.kulichenko@gmail.com
> > > > >> > wrote:
> > > > >>
> > > > >> > Folks,
> > > > >> >
> > > > >> > I noticed that we currently have a very weird limitation for
> enum
> > > > fields
> > > > >> > when running SQL queries. Basically, you can't use enum value
> > names
> > > as
> > > > >> many
> > > > >> > users would expect.
> > > > >> >
> > > > >> > Technically this happens because binary format stores only
> ordinal
> > > and
> > > > >> the
> > > > >> > only way to run such query is to do something like this:
> > > > >> >
> > > > >> > SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table
> where
> > > > >> > enumField = ?");
> > > > >> > qry.setArgs(MyEnum.ENUM_VALUE);
> > > > >> > cache.query(qry);
> > > > >> >
> > > > >> > This means that this query can be executed only if:
> > > > >> > - IgniteCache API is used. So it doesn't work in JDBC driver,
> > > > Zeppelin,
> > > > >> > etc.
> > > > >> > - The client that executes the query has the enum class on
> > > classpath.
> > > > >> >
> > > > >> > My first thought about fixing this is to somehow store value
> names
> > > in
> > > > >> > binary metadata and patch the SQL engine so that it understands
> > how
> > > to
> > > > >> use
> > > > >> > this information.
> > > > >> >
> > > > >> > Thoughts?
> > > > >> >
> > > > >> > -Val
> > > > >> >
> > > > >>
> > > > >
> > > > >
> > > >
> > >
> >
>
Re: Enums and SQL queries
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Sergi, I still thinks my solution works or can be made to work. In the
example you provided, you can choose to return either String or Enum as the
query result. However, this is not a clean solution, I agree, but could be
a quick one.
I don't mind breaking compatibility on this issue, given that it never
worked properly anyway. Can you please file a ticket?
D.
On Wed, Jul 27, 2016 at 3:01 AM, Sergi Vladykin <se...@gmail.com>
wrote:
> Nope. This will not work. Because take for example the following query:
>
> select my_enum_field from my_table where my_enum_field = 'BLA'
>
> Field well be evaluated only once here, but it must be a Sting and Enum at
> the same time.
> Probably we could contribute to H2 `User defined value types` and handle
> this stuff in our own Enum value types, which will support comparison with
> String. But this requires bug H2 Modification and is a compatibility
> breaking change.
>
> Sergi
>
>
>
> 2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
>
> > Hm... I would prefer to find a way to handle Enums automatically.
> >
> > In case if user expects a String, can we catch an exception and do
> > automatic conversion at that time? In this case, we could catch the
> > exception once and remember the decision. Will something like this work?
> >
> > D.
> >
> > On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <
> sergi.vladykin@gmail.com>
> > wrote:
> >
> > > GridQueryProcessor contains two types: BinaryProperty and
> ClassProperty.
> > > Indexing uses them for fields access. Probably we can change them, so
> > that
> > > they will always return String instead of Enum type but this
> > >
> > > 1. looks like a compatibility breaking change;
> > > 2. users doing queries like `select my_enum_field from my_table`
> probably
> > > still expect to get a Enum result but not a String.
> > >
> > > What do you think?
> > >
> > > Sergi
> > >
> > >
> > > 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> > > valentin.kulichenko@gmail.com
> > > >:
> > >
> > > > Sergi,
> > > >
> > > > Ideally, I would like this query to work:
> > > >
> > > > select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> > > >
> > > > The problem is that the field value is not a string, it's a binary
> > object
> > > > with the enum value ordinal inside. We can add enum value names into
> > the
> > > > metadata, so that we can always map the field value to its string
> > > > representation, but SQL engine will have to know how to use it. Is
> this
> > > > possible?
> > > >
> > > > -Val
> > > >
> > > > On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> > > sergi.vladykin@gmail.com
> > > > > wrote:
> > > >
> > > >> I'm not sure what exactly you are going to patch in SQL engine. We
> > could
> > > >> and some SQL function like enum('MY_ENUM_NAME') but probably it will
> > not
> > > >> work if we will not pass enum type there as well, so it have look
> like
> > > >> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> > > >>
> > > >> Sergi
> > > >>
> > > >> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> > > >> valentin.kulichenko@gmail.com
> > > >> > wrote:
> > > >>
> > > >> > Folks,
> > > >> >
> > > >> > I noticed that we currently have a very weird limitation for enum
> > > fields
> > > >> > when running SQL queries. Basically, you can't use enum value
> names
> > as
> > > >> many
> > > >> > users would expect.
> > > >> >
> > > >> > Technically this happens because binary format stores only ordinal
> > and
> > > >> the
> > > >> > only way to run such query is to do something like this:
> > > >> >
> > > >> > SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
> > > >> > enumField = ?");
> > > >> > qry.setArgs(MyEnum.ENUM_VALUE);
> > > >> > cache.query(qry);
> > > >> >
> > > >> > This means that this query can be executed only if:
> > > >> > - IgniteCache API is used. So it doesn't work in JDBC driver,
> > > Zeppelin,
> > > >> > etc.
> > > >> > - The client that executes the query has the enum class on
> > classpath.
> > > >> >
> > > >> > My first thought about fixing this is to somehow store value names
> > in
> > > >> > binary metadata and patch the SQL engine so that it understands
> how
> > to
> > > >> use
> > > >> > this information.
> > > >> >
> > > >> > Thoughts?
> > > >> >
> > > >> > -Val
> > > >> >
> > > >>
> > > >
> > > >
> > >
> >
>
Re: Enums and SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
Nope. This will not work. Because take for example the following query:
select my_enum_field from my_table where my_enum_field = 'BLA'
Field well be evaluated only once here, but it must be a Sting and Enum at
the same time.
Probably we could contribute to H2 `User defined value types` and handle
this stuff in our own Enum value types, which will support comparison with
String. But this requires bug H2 Modification and is a compatibility
breaking change.
Sergi
2016-07-27 9:23 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> Hm... I would prefer to find a way to handle Enums automatically.
>
> In case if user expects a String, can we catch an exception and do
> automatic conversion at that time? In this case, we could catch the
> exception once and remember the decision. Will something like this work?
>
> D.
>
> On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
>
> > GridQueryProcessor contains two types: BinaryProperty and ClassProperty.
> > Indexing uses them for fields access. Probably we can change them, so
> that
> > they will always return String instead of Enum type but this
> >
> > 1. looks like a compatibility breaking change;
> > 2. users doing queries like `select my_enum_field from my_table` probably
> > still expect to get a Enum result but not a String.
> >
> > What do you think?
> >
> > Sergi
> >
> >
> > 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> > valentin.kulichenko@gmail.com
> > >:
> >
> > > Sergi,
> > >
> > > Ideally, I would like this query to work:
> > >
> > > select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> > >
> > > The problem is that the field value is not a string, it's a binary
> object
> > > with the enum value ordinal inside. We can add enum value names into
> the
> > > metadata, so that we can always map the field value to its string
> > > representation, but SQL engine will have to know how to use it. Is this
> > > possible?
> > >
> > > -Val
> > >
> > > On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> > sergi.vladykin@gmail.com
> > > > wrote:
> > >
> > >> I'm not sure what exactly you are going to patch in SQL engine. We
> could
> > >> and some SQL function like enum('MY_ENUM_NAME') but probably it will
> not
> > >> work if we will not pass enum type there as well, so it have look like
> > >> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> > >>
> > >> Sergi
> > >>
> > >> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> > >> valentin.kulichenko@gmail.com
> > >> > wrote:
> > >>
> > >> > Folks,
> > >> >
> > >> > I noticed that we currently have a very weird limitation for enum
> > fields
> > >> > when running SQL queries. Basically, you can't use enum value names
> as
> > >> many
> > >> > users would expect.
> > >> >
> > >> > Technically this happens because binary format stores only ordinal
> and
> > >> the
> > >> > only way to run such query is to do something like this:
> > >> >
> > >> > SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
> > >> > enumField = ?");
> > >> > qry.setArgs(MyEnum.ENUM_VALUE);
> > >> > cache.query(qry);
> > >> >
> > >> > This means that this query can be executed only if:
> > >> > - IgniteCache API is used. So it doesn't work in JDBC driver,
> > Zeppelin,
> > >> > etc.
> > >> > - The client that executes the query has the enum class on
> classpath.
> > >> >
> > >> > My first thought about fixing this is to somehow store value names
> in
> > >> > binary metadata and patch the SQL engine so that it understands how
> to
> > >> use
> > >> > this information.
> > >> >
> > >> > Thoughts?
> > >> >
> > >> > -Val
> > >> >
> > >>
> > >
> > >
> >
>
Re: Enums and SQL queries
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Hm... I would prefer to find a way to handle Enums automatically.
In case if user expects a String, can we catch an exception and do
automatic conversion at that time? In this case, we could catch the
exception once and remember the decision. Will something like this work?
D.
On Tue, Jul 26, 2016 at 1:49 AM, Sergi Vladykin <se...@gmail.com>
wrote:
> GridQueryProcessor contains two types: BinaryProperty and ClassProperty.
> Indexing uses them for fields access. Probably we can change them, so that
> they will always return String instead of Enum type but this
>
> 1. looks like a compatibility breaking change;
> 2. users doing queries like `select my_enum_field from my_table` probably
> still expect to get a Enum result but not a String.
>
> What do you think?
>
> Sergi
>
>
> 2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <
> valentin.kulichenko@gmail.com
> >:
>
> > Sergi,
> >
> > Ideally, I would like this query to work:
> >
> > select * from MyTable where myEnumField = 'MY_ENUM_NAME'
> >
> > The problem is that the field value is not a string, it's a binary object
> > with the enum value ordinal inside. We can add enum value names into the
> > metadata, so that we can always map the field value to its string
> > representation, but SQL engine will have to know how to use it. Is this
> > possible?
> >
> > -Val
> >
> > On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <
> sergi.vladykin@gmail.com
> > > wrote:
> >
> >> I'm not sure what exactly you are going to patch in SQL engine. We could
> >> and some SQL function like enum('MY_ENUM_NAME') but probably it will not
> >> work if we will not pass enum type there as well, so it have look like
> >> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
> >>
> >> Sergi
> >>
> >> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> >> valentin.kulichenko@gmail.com
> >> > wrote:
> >>
> >> > Folks,
> >> >
> >> > I noticed that we currently have a very weird limitation for enum
> fields
> >> > when running SQL queries. Basically, you can't use enum value names as
> >> many
> >> > users would expect.
> >> >
> >> > Technically this happens because binary format stores only ordinal and
> >> the
> >> > only way to run such query is to do something like this:
> >> >
> >> > SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
> >> > enumField = ?");
> >> > qry.setArgs(MyEnum.ENUM_VALUE);
> >> > cache.query(qry);
> >> >
> >> > This means that this query can be executed only if:
> >> > - IgniteCache API is used. So it doesn't work in JDBC driver,
> Zeppelin,
> >> > etc.
> >> > - The client that executes the query has the enum class on classpath.
> >> >
> >> > My first thought about fixing this is to somehow store value names in
> >> > binary metadata and patch the SQL engine so that it understands how to
> >> use
> >> > this information.
> >> >
> >> > Thoughts?
> >> >
> >> > -Val
> >> >
> >>
> >
> >
>
Re: Enums and SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
GridQueryProcessor contains two types: BinaryProperty and ClassProperty.
Indexing uses them for fields access. Probably we can change them, so that
they will always return String instead of Enum type but this
1. looks like a compatibility breaking change;
2. users doing queries like `select my_enum_field from my_table` probably
still expect to get a Enum result but not a String.
What do you think?
Sergi
2016-07-26 3:17 GMT+03:00 Valentin Kulichenko <valentin.kulichenko@gmail.com
>:
> Sergi,
>
> Ideally, I would like this query to work:
>
> select * from MyTable where myEnumField = 'MY_ENUM_NAME'
>
> The problem is that the field value is not a string, it's a binary object
> with the enum value ordinal inside. We can add enum value names into the
> metadata, so that we can always map the field value to its string
> representation, but SQL engine will have to know how to use it. Is this
> possible?
>
> -Val
>
> On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <sergi.vladykin@gmail.com
> > wrote:
>
>> I'm not sure what exactly you are going to patch in SQL engine. We could
>> and some SQL function like enum('MY_ENUM_NAME') but probably it will not
>> work if we will not pass enum type there as well, so it have look like
>> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
>>
>> Sergi
>>
>> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
>> valentin.kulichenko@gmail.com
>> > wrote:
>>
>> > Folks,
>> >
>> > I noticed that we currently have a very weird limitation for enum fields
>> > when running SQL queries. Basically, you can't use enum value names as
>> many
>> > users would expect.
>> >
>> > Technically this happens because binary format stores only ordinal and
>> the
>> > only way to run such query is to do something like this:
>> >
>> > SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
>> > enumField = ?");
>> > qry.setArgs(MyEnum.ENUM_VALUE);
>> > cache.query(qry);
>> >
>> > This means that this query can be executed only if:
>> > - IgniteCache API is used. So it doesn't work in JDBC driver, Zeppelin,
>> > etc.
>> > - The client that executes the query has the enum class on classpath.
>> >
>> > My first thought about fixing this is to somehow store value names in
>> > binary metadata and patch the SQL engine so that it understands how to
>> use
>> > this information.
>> >
>> > Thoughts?
>> >
>> > -Val
>> >
>>
>
>
Re: Enums and SQL queries
Posted by Valentin Kulichenko <va...@gmail.com>.
Sergi,
Ideally, I would like this query to work:
select * from MyTable where myEnumField = 'MY_ENUM_NAME'
The problem is that the field value is not a string, it's a binary object
with the enum value ordinal inside. We can add enum value names into the
metadata, so that we can always map the field value to its string
representation, but SQL engine will have to know how to use it. Is this
possible?
-Val
On Fri, Jul 22, 2016 at 10:25 PM, Sergi Vladykin <se...@gmail.com>
wrote:
> I'm not sure what exactly you are going to patch in SQL engine. We could
> and some SQL function like enum('MY_ENUM_NAME') but probably it will not
> work if we will not pass enum type there as well, so it have look like
> enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
>
> Sergi
>
> On 23 июля 2016 г., 0:43, Valentin Kulichenko <
> valentin.kulichenko@gmail.com
> > wrote:
>
> > Folks,
> >
> > I noticed that we currently have a very weird limitation for enum fields
> > when running SQL queries. Basically, you can't use enum value names as
> many
> > users would expect.
> >
> > Technically this happens because binary format stores only ordinal and
> the
> > only way to run such query is to do something like this:
> >
> > SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
> > enumField = ?");
> > qry.setArgs(MyEnum.ENUM_VALUE);
> > cache.query(qry);
> >
> > This means that this query can be executed only if:
> > - IgniteCache API is used. So it doesn't work in JDBC driver, Zeppelin,
> > etc.
> > - The client that executes the query has the enum class on classpath.
> >
> > My first thought about fixing this is to somehow store value names in
> > binary metadata and patch the SQL engine so that it understands how to
> use
> > this information.
> >
> > Thoughts?
> >
> > -Val
> >
>
Re: Enums and SQL queries
Posted by Sergi Vladykin <se...@gmail.com>.
I'm not sure what exactly you are going to patch in SQL engine. We could
and some SQL function like enum('MY_ENUM_NAME') but probably it will not
work if we will not pass enum type there as well, so it have look like
enum('com.mycompany.MyEnum', 'MY_ENUM_NAME') which is ugly.
Sergi
On 23 июля 2016 г., 0:43, Valentin Kulichenko <valentin.kulichenko@gmail.com
> wrote:
> Folks,
>
> I noticed that we currently have a very weird limitation for enum fields
> when running SQL queries. Basically, you can't use enum value names as many
> users would expect.
>
> Technically this happens because binary format stores only ordinal and the
> only way to run such query is to do something like this:
>
> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Table where
> enumField = ?");
> qry.setArgs(MyEnum.ENUM_VALUE);
> cache.query(qry);
>
> This means that this query can be executed only if:
> - IgniteCache API is used. So it doesn't work in JDBC driver, Zeppelin,
> etc.
> - The client that executes the query has the enum class on classpath.
>
> My first thought about fixing this is to somehow store value names in
> binary metadata and patch the SQL engine so that it understands how to use
> this information.
>
> Thoughts?
>
> -Val
>