Issue querying id column only

classic Classic list List threaded Threaded
5 messages Options
Kurt Semba Kurt Semba
Reply | Threaded
Open this post in threaded view
|

Issue querying id column only

Hi all,

 

Is it possible for a table through the SQL interface to only return some subset of data if querying against a specific column?

 

e.g.

 

We have a cache configuration defined based on Java SQL Query annotations that contains an id field and some other string fields. The value of the id field in all entries also matches the value of the cache entry key).

 

The table contains 3 entries, however if I execute “select id from table” through sqlline, I only am able to see 1 entry. However, if I execute “select id, name from table”, I see all of them.

 

Are there any steps I can take to better diagnose this?  

 

Thank you

Kurt

 

Denis Mekhanikov Denis Mekhanikov
Reply | Threaded
Open this post in threaded view
|

Re: Issue querying id column only

Are you able to reproduce this issue using SQL only?
Could you share the DDL, insert and select statements that lead to the described issue?

I tried the following queries, but they work as expected.

CREATE TABLE people (id int PRIMARY key, first_name varchar, last_name varchar);


INSERT INTO people (id, first_name, last_name) VALUES (1, 'John', 'Doe');

INSERT INTO people (id, first_name, last_name) VALUES (2, 'John', 'Foe');


SELECT id FROM people;


Denis
On 17 Oct 2019, 09:02 +0300, Kurt Semba <[hidden email]>, wrote:

Hi all,

 

Is it possible for a table through the SQL interface to only return some subset of data if querying against a specific column?

 

e.g.

 

We have a cache configuration defined based on Java SQL Query annotations that contains an id field and some other string fields. The value of the id field in all entries also matches the value of the cache entry key).

 

The table contains 3 entries, however if I execute “select id from table” through sqlline, I only am able to see 1 entry. However, if I execute “select id, name from table”, I see all of them.

 

Are there any steps I can take to better diagnose this?  

 

Thank you

Kurt

 

Kurt Semba Kurt Semba
Reply | Threaded
Open this post in threaded view
|

RE: Issue querying id column only

Hi Denis,

 

The cache was defined in the spring configuration as follows and not generated through SQL DML:

 

                <bean class="org.apache.ignite.configuration.CacheConfiguration">

                    <property name="name" value="domainnode" />

                    <property name="sqlSchema" value="account" />

                    <property name="cacheStoreFactory">

                        <bean class="javax.cache.configuration.FactoryBuilder$SingletonFactory">

                            <constructor-arg>

                                <bean class="com.extremenetworks.ignite.store.DomainNodeStore" />

                            </constructor-arg>

                        </bean>

                    </property>

 

 

                    <property name="indexedTypes">

                        <list>

                            <value>java.lang.String</value>

                            <value>com.extremenetworks.ignite.model.CachedDomainNode</value>

                        </list>

                    </property>

                </bean>

 

The annotated fields in CachedDomainNode:

 

   @QuerySqlField(index = true)

    private String id;

 

    @QuerySqlField

    private String name;

 

    @QuerySqlField

    private String ipAddress;

 

    @QuerySqlField

    private Long lastUpdate;

   

    @QuerySqlField

    private int nodeType;

 

 

0: jdbc:ignite:thin://127.0.0.1> select * from account.cacheddomainnode;

+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+

|               ID               |              NAME              |           IPADDRESS            |           LASTUPDATE           |            NODETYPE            |

+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+

| 11111111-1111-1111-1111-111111111111 | Test-XMC                     | /127.0.0.1                     | 1571185515950                  | 0                              |

| 99488ecd-4cae-4ecc-8306-c3b4215452c2 | XMC-Justice1                   | /10.51.102.191                 | 1571278075994                  | 0                              |

| 99488ecd-4cae-4ecc-8306-c3b4215452c3 | XMC-Justice2                   | /10.51.102.192                 | 1571278108222                  | 0                              |

+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+

3 rows selected (0.04 seconds)

0: jdbc:ignite:thin://127.0.0.1> select id from account.cacheddomainnode;

+--------------------------------+

|               ID               |

+--------------------------------+

| 11111111-1111-1111-1111-111111111111 |

+--------------------------------+

1 row selected (0.013 seconds)

0: jdbc:ignite:thin://127.0.0.1> select _key from account.cacheddomainnode;

+--------------------------------+

|              _KEY              |

+--------------------------------+

| 11111111-1111-1111-1111-111111111111 |

| 99488ecd-4cae-4ecc-8306-c3b4215452c2 |

| 99488ecd-4cae-4ecc-8306-c3b4215452c3 |

+--------------------------------+

 

 

From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, October 17, 2019 11:52 AM
To: [hidden email]
Subject: Re: Issue querying id column only

 

External Email: Use caution in opening links or attachments.

Are you able to reproduce this issue using SQL only?

Could you share the DDL, insert and select statements that lead to the described issue?

 

I tried the following queries, but they work as expected.

 

CREATE TABLE people (id int PRIMARY key, first_name varchar, last_name varchar);

 

INSERT INTO people (id, first_name, last_name) VALUES (1, 'John', 'Doe');

INSERT INTO people (id, first_name, last_name) VALUES (2, 'John', 'Foe');

 

SELECT id FROM people;

 

Denis

On 17 Oct 2019, 09:02 +0300, Kurt Semba <[hidden email]>, wrote:

Hi all,

 

Is it possible for a table through the SQL interface to only return some subset of data if querying against a specific column?

 

e.g.

 

We have a cache configuration defined based on Java SQL Query annotations that contains an id field and some other string fields. The value of the id field in all entries also matches the value of the cache entry key).

 

The table contains 3 entries, however if I execute “select id from table” through sqlline, I only am able to see 1 entry. However, if I execute “select id, name from table”, I see all of them.

 

Are there any steps I can take to better diagnose this?  

 

Thank you

Kurt

 

Denis Mekhanikov Denis Mekhanikov
Reply | Threaded
Open this post in threaded view
|

RE: Issue querying id column only

Kurt,

I tried reproducing this issue using your data, but I didn’t manage to make the query for id return only one entry.

What version of Ignite do you use? How did you insert the data? SQL or cache API?

Denis
On 17 Oct 2019, 15:21 +0300, Kurt Semba <[hidden email]>, wrote:

Hi Denis,

 

The cache was defined in the spring configuration as follows and not generated through SQL DML:

 

                <bean class="org.apache.ignite.configuration.CacheConfiguration">

                    <property name="name" value="domainnode" />

                    <property name="sqlSchema" value="account" />

                    <property name="cacheStoreFactory">

                        <bean class="javax.cache.configuration.FactoryBuilder$SingletonFactory">

                            <constructor-arg>

                                <bean class="com.extremenetworks.ignite.store.DomainNodeStore" />

                            </constructor-arg>

                        </bean>

                    </property>

 

 

                    <property name="indexedTypes">

                        <list>

                            <value>java.lang.String</value>

                            <value>com.extremenetworks.ignite.model.CachedDomainNode</value>

                        </list>

                    </property>

                </bean>

 

The annotated fields in CachedDomainNode:

 

   @QuerySqlField(index = true)

    private String id;

 

    @QuerySqlField

    private String name;

 

    @QuerySqlField

    private String ipAddress;

 

    @QuerySqlField

    private Long lastUpdate;

   

    @QuerySqlField

    private int nodeType;

 

 

0: jdbc:ignite:thin://127.0.0.1> select * from account.cacheddomainnode;

+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+

|               ID               |              NAME              |           IPADDRESS            |           LASTUPDATE           |            NODETYPE            |

+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+

| 11111111-1111-1111-1111-111111111111 | Test-XMC                     | /127.0.0.1                     | 1571185515950                  | 0                              |

| 99488ecd-4cae-4ecc-8306-c3b4215452c2 | XMC-Justice1                   | /10.51.102.191                 | 1571278075994                  | 0                              |

| 99488ecd-4cae-4ecc-8306-c3b4215452c3 | XMC-Justice2                   | /10.51.102.192                 | 1571278108222                  | 0                              |

+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+

3 rows selected (0.04 seconds)

0: jdbc:ignite:thin://127.0.0.1> select id from account.cacheddomainnode;

+--------------------------------+

|               ID               |

+--------------------------------+

| 11111111-1111-1111-1111-111111111111 |

+--------------------------------+

1 row selected (0.013 seconds)

0: jdbc:ignite:thin://127.0.0.1> select _key from account.cacheddomainnode;

+--------------------------------+

|              _KEY              |

+--------------------------------+

| 11111111-1111-1111-1111-111111111111 |

| 99488ecd-4cae-4ecc-8306-c3b4215452c2 |

| 99488ecd-4cae-4ecc-8306-c3b4215452c3 |

+--------------------------------+

 

 

From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, October 17, 2019 11:52 AM
To: [hidden email]
Subject: Re: Issue querying id column only

 

External Email: Use caution in opening links or attachments.

Are you able to reproduce this issue using SQL only?

Could you share the DDL, insert and select statements that lead to the described issue?

 

I tried the following queries, but they work as expected.

 

CREATE TABLE people (id int PRIMARY key, first_name varchar, last_name varchar);

 

INSERT INTO people (id, first_name, last_name) VALUES (1, 'John', 'Doe');

INSERT INTO people (id, first_name, last_name) VALUES (2, 'John', 'Foe');

 

SELECT id FROM people;

 

Denis

On 17 Oct 2019, 09:02 +0300, Kurt Semba <[hidden email]>, wrote:

Hi all,

 

Is it possible for a table through the SQL interface to only return some subset of data if querying against a specific column?

 

e.g.

 

We have a cache configuration defined based on Java SQL Query annotations that contains an id field and some other string fields. The value of the id field in all entries also matches the value of the cache entry key).

 

The table contains 3 entries, however if I execute “select id from table” through sqlline, I only am able to see 1 entry. However, if I execute “select id, name from table”, I see all of them.

 

Are there any steps I can take to better diagnose this?  

 

Thank you

Kurt

 

Ivan Pavlukhin Ivan Pavlukhin
Reply | Threaded
Open this post in threaded view
|

Re: Issue querying id column only

Hi Kurt,

It might be that you faced https://issues.apache.org/jira/browse/IGNITE-12068
Was fixed in 2.7.6

пн, 21 окт. 2019 г. в 17:56, Denis Mekhanikov <[hidden email]>:

>
> Kurt,
>
> I tried reproducing this issue using your data, but I didn’t manage to make the query for id return only one entry.
>
> What version of Ignite do you use? How did you insert the data? SQL or cache API?
>
> Denis
> On 17 Oct 2019, 15:21 +0300, Kurt Semba <[hidden email]>, wrote:
>
> Hi Denis,
>
>
>
> The cache was defined in the spring configuration as follows and not generated through SQL DML:
>
>
>
>                 <bean class="org.apache.ignite.configuration.CacheConfiguration">
>
>                     <property name="name" value="domainnode" />
>
>                     <property name="sqlSchema" value="account" />
>
>                     <property name="cacheStoreFactory">
>
>                         <bean class="javax.cache.configuration.FactoryBuilder$SingletonFactory">
>
>                             <constructor-arg>
>
>                                 <bean class="com.extremenetworks.ignite.store.DomainNodeStore" />
>
>                             </constructor-arg>
>
>                         </bean>
>
>                     </property>
>
>
>
>
>
>                     <property name="indexedTypes">
>
>                         <list>
>
>                             <value>java.lang.String</value>
>
>                             <value>com.extremenetworks.ignite.model.CachedDomainNode</value>
>
>                         </list>
>
>                     </property>
>
>                 </bean>
>
>
>
> The annotated fields in CachedDomainNode:
>
>
>
>    @QuerySqlField(index = true)
>
>     private String id;
>
>
>
>     @QuerySqlField
>
>     private String name;
>
>
>
>     @QuerySqlField
>
>     private String ipAddress;
>
>
>
>     @QuerySqlField
>
>     private Long lastUpdate;
>
>
>
>     @QuerySqlField
>
>     private int nodeType;
>
>
>
>
>
> 0: jdbc:ignite:thin://127.0.0.1> select * from account.cacheddomainnode;
>
> +--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
>
> |               ID               |              NAME              |           IPADDRESS            |           LASTUPDATE           |            NODETYPE            |
>
> +--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
>
> | 11111111-1111-1111-1111-111111111111 | Test-XMC                     | /127.0.0.1                     | 1571185515950                  | 0                              |
>
> | 99488ecd-4cae-4ecc-8306-c3b4215452c2 | XMC-Justice1                   | /10.51.102.191                 | 1571278075994                  | 0                              |
>
> | 99488ecd-4cae-4ecc-8306-c3b4215452c3 | XMC-Justice2                   | /10.51.102.192                 | 1571278108222                  | 0                              |
>
> +--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
>
> 3 rows selected (0.04 seconds)
>
> 0: jdbc:ignite:thin://127.0.0.1> select id from account.cacheddomainnode;
>
> +--------------------------------+
>
> |               ID               |
>
> +--------------------------------+
>
> | 11111111-1111-1111-1111-111111111111 |
>
> +--------------------------------+
>
> 1 row selected (0.013 seconds)
>
> 0: jdbc:ignite:thin://127.0.0.1> select _key from account.cacheddomainnode;
>
> +--------------------------------+
>
> |              _KEY              |
>
> +--------------------------------+
>
> | 11111111-1111-1111-1111-111111111111 |
>
> | 99488ecd-4cae-4ecc-8306-c3b4215452c2 |
>
> | 99488ecd-4cae-4ecc-8306-c3b4215452c3 |
>
> +--------------------------------+
>
>
>
>
>
> From: Denis Mekhanikov <[hidden email]>
> Sent: Thursday, October 17, 2019 11:52 AM
> To: [hidden email]
> Subject: Re: Issue querying id column only
>
>
>
> External Email: Use caution in opening links or attachments.
>
> Are you able to reproduce this issue using SQL only?
>
> Could you share the DDL, insert and select statements that lead to the described issue?
>
>
>
> I tried the following queries, but they work as expected.
>
>
>
> CREATE TABLE people (id int PRIMARY key, first_name varchar, last_name varchar);
>
>
>
> INSERT INTO people (id, first_name, last_name) VALUES (1, 'John', 'Doe');
>
> INSERT INTO people (id, first_name, last_name) VALUES (2, 'John', 'Foe');
>
>
>
> SELECT id FROM people;
>
>
>
> Denis
>
> On 17 Oct 2019, 09:02 +0300, Kurt Semba <[hidden email]>, wrote:
>
> Hi all,
>
>
>
> Is it possible for a table through the SQL interface to only return some subset of data if querying against a specific column?
>
>
>
> e.g.
>
>
>
> We have a cache configuration defined based on Java SQL Query annotations that contains an id field and some other string fields. The value of the id field in all entries also matches the value of the cache entry key).
>
>
>
> The table contains 3 entries, however if I execute “select id from table” through sqlline, I only am able to see 1 entry. However, if I execute “select id, name from table”, I see all of them.
>
>
>
> Are there any steps I can take to better diagnose this?
>
>
>
> Thank you
>
> Kurt
>
>



--
Best regards,
Ivan Pavlukhin