Ignite SQL syntax: key fields, scalars, nested fields

[Pavel Tupitsyn-3]

Igniters,

In relation to .NET LINQ task [1], I'd like to know as much as possible
about Ignite-specific SQL syntax. Our docs [2] do not cover everything.

* Is it possible to query cache keys? E.g. "key > 10", or "key.field = 1"?
* Is it possible to query scalars, like "sum()"?
* What about nested fields? From examples, I see that nested fields get
flattened, so instead of Address.Zip you can just use Zip. Are there any
limitations? What if there is field name collision?

Where should I look to understand this better?

Thanks.

[1] https://issues.apache.org/jira/browse/IGNITE-1630
[2] https://apacheignite.readme.io/docs/sql-queries

--
--
Pavel Tupitsyn
GridGain Systems, Inc.
www.gridgain.com

[Sergi]

- Yes, in SQL it is possible to query cache key and value using aliases
_key and _val respectively.
- Aggregate functions like SUM, AVG, MIN, MAX are supported.
- Nested fields are supported and they are flattened, so name collisions
are prohibited.

Sergi

2016-01-25 15:26 GMT+03:00 Pavel Tupitsyn <[hidden email]>:

> Igniters,
>
> In relation to .NET LINQ task [1], I'd like to know as much as possible
> about Ignite-specific SQL syntax. Our docs [2] do not cover everything.
>
> * Is it possible to query cache keys? E.g. "key > 10", or "key.field = 1"?
> * Is it possible to query scalars, like "sum()"?
> * What about nested fields? From examples, I see that nested fields get
> flattened, so instead of Address.Zip you can just use Zip. Are there any
> limitations? What if there is field name collision?
>
> Where should I look to understand this better?
>
> Thanks.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-1630
> [2] https://apacheignite.readme.io/docs/sql-queries
>
> --
> --
> Pavel Tupitsyn
> GridGain Systems, Inc.
> www.gridgain.com
>

[Pavel Tupitsyn-3]

Thank you Sergi, more questions:

- How do I get the result of an aggregate? Via Fields query? Will it always
be a single value, or a value per node?
- If field names are flattened, what are QueryEntity.aliases for? Javadoc
talks about dot notation, I thought it is for nested fields.
- What is the purpose of SqlQuery.type? We use simple name of the class for
it everywhere. Does it relate to type id mapping somehow?
- I tried to use _key/_val aliases and could not get them to work:
   * "_val.Age > ?": Failed to parse query: SELECT
"cache".QueryPerson._key, "cache".QueryPerson._val FROM "cache".QueryPerson
WHERE _val.Age > ?
   * "_key > ?": Caused by: org.h2.jdbc.JdbcSQLException: Deserialization
failed, cause: "class org.apache.ignite.binary.BinaryObjectException: Not
enough data to read the value [position=1, requiredBytes=4,
remainingBytes=0]"; SQL statement: SELECT "cache".QUERYPERSON._KEY __C0,
"cache".QUERYPERSON._VAL __C1 FROM "cache".QUERYPERSON WHERE _KEY > ?1
[90027-175]



On Mon, Jan 25, 2016 at 6:38 PM, Sergi Vladykin <[hidden email]>
wrote:

> - Yes, in SQL it is possible to query cache key and value using aliases
> _key and _val respectively.
> - Aggregate functions like SUM, AVG, MIN, MAX are supported.
> - Nested fields are supported and they are flattened, so name collisions
> are prohibited.
>
> Sergi
>
> 2016-01-25 15:26 GMT+03:00 Pavel Tupitsyn <[hidden email]>:
>
> > Igniters,
> >
> > In relation to .NET LINQ task [1], I'd like to know as much as possible
> > about Ignite-specific SQL syntax. Our docs [2] do not cover everything.
> >
> > * Is it possible to query cache keys? E.g. "key > 10", or "key.field =
> 1"?
> > * Is it possible to query scalars, like "sum()"?
> > * What about nested fields? From examples, I see that nested fields get
> > flattened, so instead of Address.Zip you can just use Zip. Are there any
> > limitations? What if there is field name collision?
> >
> > Where should I look to understand this better?
> >
> > Thanks.
> >
> > [1] https://issues.apache.org/jira/browse/IGNITE-1630
> > [2] https://apacheignite.readme.io/docs/sql-queries
> >
> > --
> > --
> > Pavel Tupitsyn
> > GridGain Systems, Inc.
> > www.gridgain.com
> >
>

--
--
Pavel Tupitsyn
GridGain Systems, Inc.
www.gridgain.com

[Sergi]

See inline

2016-01-25 19:16 GMT+03:00 Pavel Tupitsyn <[hidden email]>:

> Thank you Sergi, more questions:
>
> - How do I get the result of an aggregate? Via Fields query? Will it always
> be a single value, or a value per node?
>

Yes, using fields query. Supported aggregates must work correctly across
cluster.


> - If field names are flattened, what are QueryEntity.aliases for? Javadoc
> talks about dot notation, I thought it is for nested fields.
>

Exactly aliases are needed to resolve duplication cases. For example you
have entity Person it has field `child` which has field `age` and field
`car` which has field `age` as well. You can specify in config that
"child.age" must have SQL alias "child_age" and "car.age" will have alias
"car_age". Obviously you can't use `car.age` notation in sql as is.


> - What is the purpose of SqlQuery.type? We use simple name of the class for
> it everywhere. Does it relate to type id mapping somehow?
>

Sometimes (when we work with binary objects) we don't have classes at all.


> - I tried to use _key/_val aliases and could not get them to work:
>    * "_val.Age > ?": Failed to parse query: SELECT
> "cache".QueryPerson._key, "cache".QueryPerson._val FROM "cache".QueryPerson
> WHERE _val.Age > ?
>

There are no dot notation in SQL. You can operate on _val but not on
_val.property1.property2.property25.


>    * "_key > ?": Caused by: org.h2.jdbc.JdbcSQLException: Deserialization
> failed, cause: "class org.apache.ignite.binary.BinaryObjectException: Not
> enough data to read the value [position=1, requiredBytes=4,
> remainingBytes=0]"; SQL statement: SELECT "cache".QUERYPERSON._KEY __C0,
> "cache".QUERYPERSON._VAL __C1 FROM "cache".QUERYPERSON WHERE _KEY > ?1
> [90027-175]
>

Looks like a bug to me. Could you open Jira issue with simple reproducible
test?

Sergi

>
>
>
> On Mon, Jan 25, 2016 at 6:38 PM, Sergi Vladykin <[hidden email]>
> wrote:
>
> > - Yes, in SQL it is possible to query cache key and value using aliases
> > _key and _val respectively.
> > - Aggregate functions like SUM, AVG, MIN, MAX are supported.
> > - Nested fields are supported and they are flattened, so name collisions
> > are prohibited.
> >
> > Sergi
> >
> > 2016-01-25 15:26 GMT+03:00 Pavel Tupitsyn <[hidden email]>:
> >
> > > Igniters,
> > >
> > > In relation to .NET LINQ task [1], I'd like to know as much as possible
> > > about Ignite-specific SQL syntax. Our docs [2] do not cover everything.
> > >
> > > * Is it possible to query cache keys? E.g. "key > 10", or "key.field =
> > 1"?
> > > * Is it possible to query scalars, like "sum()"?
> > > * What about nested fields? From examples, I see that nested fields get
> > > flattened, so instead of Address.Zip you can just use Zip. Are there
> any
> > > limitations? What if there is field name collision?
> > >
> > > Where should I look to understand this better?
> > >
> > > Thanks.
> > >
> > > [1] https://issues.apache.org/jira/browse/IGNITE-1630
> > > [2] https://apacheignite.readme.io/docs/sql-queries
> > >
> > > --
> > > --
> > > Pavel Tupitsyn
> > > GridGain Systems, Inc.
> > > www.gridgain.com
> > >
> >
>
>
>
> --
> --
> Pavel Tupitsyn
> GridGain Systems, Inc.
> www.gridgain.com
>

[Pavel Tupitsyn-3]

Thank you again, Sergi, very helpful.

_key query works, QueryEntity.keyType was missing in my config.

On Mon, Jan 25, 2016 at 8:08 PM, Sergi Vladykin <[hidden email]>
wrote:

> See inline
>
> 2016-01-25 19:16 GMT+03:00 Pavel Tupitsyn <[hidden email]>:
>
> > Thank you Sergi, more questions:
> >
> > - How do I get the result of an aggregate? Via Fields query? Will it
> always
> > be a single value, or a value per node?
> >
>
> Yes, using fields query. Supported aggregates must work correctly across
> cluster.
>
>
> > - If field names are flattened, what are QueryEntity.aliases for? Javadoc
> > talks about dot notation, I thought it is for nested fields.
> >
>
> Exactly aliases are needed to resolve duplication cases. For example you
> have entity Person it has field `child` which has field `age` and field
> `car` which has field `age` as well. You can specify in config that
> "child.age" must have SQL alias "child_age" and "car.age" will have alias
> "car_age". Obviously you can't use `car.age` notation in sql as is.
>
>
> > - What is the purpose of SqlQuery.type? We use simple name of the class
> for
> > it everywhere. Does it relate to type id mapping somehow?
> >
>
> Sometimes (when we work with binary objects) we don't have classes at all.
>
>
> > - I tried to use _key/_val aliases and could not get them to work:
> >    * "_val.Age > ?": Failed to parse query: SELECT
> > "cache".QueryPerson._key, "cache".QueryPerson._val FROM
> "cache".QueryPerson
> > WHERE _val.Age > ?
> >
>
> There are no dot notation in SQL. You can operate on _val but not on
> _val.property1.property2.property25.
>
>
> >    * "_key > ?": Caused by: org.h2.jdbc.JdbcSQLException: Deserialization
> > failed, cause: "class org.apache.ignite.binary.BinaryObjectException: Not
> > enough data to read the value [position=1, requiredBytes=4,
> > remainingBytes=0]"; SQL statement: SELECT "cache".QUERYPERSON._KEY __C0,
> > "cache".QUERYPERSON._VAL __C1 FROM "cache".QUERYPERSON WHERE _KEY > ?1
> > [90027-175]
> >
>
> Looks like a bug to me. Could you open Jira issue with simple reproducible
> test?
>
> Sergi
>
>
> >
> >
> >
> > On Mon, Jan 25, 2016 at 6:38 PM, Sergi Vladykin <
> [hidden email]>
> > wrote:
> >
> > > - Yes, in SQL it is possible to query cache key and value using aliases
> > > _key and _val respectively.
> > > - Aggregate functions like SUM, AVG, MIN, MAX are supported.
> > > - Nested fields are supported and they are flattened, so name
> collisions
> > > are prohibited.
> > >
> > > Sergi
> > >
> > > 2016-01-25 15:26 GMT+03:00 Pavel Tupitsyn <[hidden email]>:
> > >
> > > > Igniters,
> > > >
> > > > In relation to .NET LINQ task [1], I'd like to know as much as
> possible
> > > > about Ignite-specific SQL syntax. Our docs [2] do not cover
> everything.
> > > >
> > > > * Is it possible to query cache keys? E.g. "key > 10", or "key.field
> =
> > > 1"?
> > > > * Is it possible to query scalars, like "sum()"?
> > > > * What about nested fields? From examples, I see that nested fields
> get
> > > > flattened, so instead of Address.Zip you can just use Zip. Are there
> > any
> > > > limitations? What if there is field name collision?
> > > >
> > > > Where should I look to understand this better?
> > > >
> > > > Thanks.
> > > >
> > > > [1] https://issues.apache.org/jira/browse/IGNITE-1630
> > > > [2] https://apacheignite.readme.io/docs/sql-queries
> > > >
> > > > --
> > > > --
> > > > Pavel Tupitsyn
> > > > GridGain Systems, Inc.
> > > > www.gridgain.com
> > > >
> > >
> >
> >
> >
> > --
> > --
> > Pavel Tupitsyn
> > GridGain Systems, Inc.
> > www.gridgain.com
> >
>

--
--
Pavel Tupitsyn
GridGain Systems, Inc.
www.gridgain.com