[BUG REPORT] JDBC Create Index

classic Classic list List threaded Threaded
4 messages Options
joseheitor joseheitor
Reply | Threaded
Open this post in threaded view
|

[BUG REPORT] JDBC Create Index

Hi Ignite Team,

Here are three issues that I have picked up on a 2.8 nightly build (don't
know if these have been reported and logged previously...?):

Version: 2.8
Build: 20181226

*Issue 1:*

CREATE INDEX statements cannot be part of an SQL multi-statement. But they
can be executed individually.

*Issue 2:*

CREATE INDEX ignores first column of a multi-column index. For example...

CREATE INDEX multi_index ON public.table (id, k, v) INLINE_SIZE 100;

results in an index for columns 'k' and 'v' only.

*Issue 3:*

DROP INDEX IF EXISTS... fails
DROP INDEX... works ok



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: [BUG REPORT] JDBC Create Index

Hello!

1. This is certainly possible since we have at least on issue about CREATE INDEX in multiple statemen queries:


2. This is strange since this scenario is routinely uses. Do you have reproducer? How do you determine that index does not contain `id'?

3. Do you have reproducer for this case? Note that recently there were some troubles with index name capitalization, maybe that's the culprit.


Regards,

--
Ilya Kasnacheev

пн, 4 февр. 2019 г. в 08:10, joseheitor <[hidden email]>:
Hi Ignite Team,

Here are three issues that I have picked up on a 2.8 nightly build (don't
know if these have been reported and logged previously...?):

Version: 2.8
Build: 20181226

*Issue 1:*

CREATE INDEX statements cannot be part of an SQL multi-statement. But they
can be executed individually.

*Issue 2:*

CREATE INDEX ignores first column of a multi-column index. For example...

CREATE INDEX multi_index ON public.table (id, k, v) INLINE_SIZE 100;

results in an index for columns 'k' and 'v' only.

*Issue 3:*

DROP INDEX IF EXISTS... fails
DROP INDEX... works ok



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
joseheitor joseheitor
Reply | Threaded
Open this post in threaded view
|

Re: [BUG REPORT] JDBC Create Index

Thanks, Ilya.

1. - I see that this issue has been addressed - should I be able to verify
this by pulling the latest nightly-build?

2. - Issuing the following statement via a JDBC application or with a JDBC
client, such as DBeaver, results in the index being created, but only on the
2nd and 3rd specified columns (only tested with JDBC Thin Client):

Assuming a table with the following structure:
CREATE TABLE public.transactions (pk INT, id INT, k VARCHAR, v VARCHAR,
PRIMARY KEY (pk, id))
  WITH "TEMPLATE=PARTITIONED, BACKUPS=1, ATOMICITY=TRANSACTIONAL_SNAPSHOT,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC, AFFINITY_KEY=id";


*CREATE INDEX transactions_id_k_v ON public.transactions (id, k, v)
INLINE_SIZE 100;*
<http://apache-ignite-users.70518.x6.nabble.com/file/t1652/index-missing-first-column.png>

3. - [Apologies] Please ignore - it appears to be working correctly!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: [BUG REPORT] JDBC Create Index

Hello!

1) Neither of tickets is not RESOLVED so you probably can't.

2) I have failed to observe this problem:

0: jdbc:ignite:thin://localhost> CREATE TABLE public.transactions (pk INT, id INT, k VARCHAR, v VARCHAR,
. . . . . . . . . . . . . . . .> PRIMARY KEY (pk, id));
No rows affected (0,182 seconds)
0: jdbc:ignite:thin://localhost> CREATE INDEX transactions_id_k_v ON public.transactions (id, k, v)
. . . . . . . . . . . . . . . .> INLINE_SIZE 100;
No rows affected (0,043 seconds)
0: jdbc:ignite:thin://localhost> !indexes
TABLE_CAT         IGNITE
TABLE_SCHEM       PUBLIC
TABLE_NAME        TRANSACTIONS
NON_UNIQUE        true
INDEX_QUALIFIER  
INDEX_NAME        TRANSACTIONS_ID_K_V
TYPE              3
ORDINAL_POSITION  1
COLUMN_NAME       ID
ASC_OR_DESC       A
CARDINALITY       0
PAGES             0
FILTER_CONDITION 

TABLE_CAT         IGNITE
TABLE_SCHEM       PUBLIC
TABLE_NAME        TRANSACTIONS
NON_UNIQUE        true
INDEX_QUALIFIER  
INDEX_NAME        TRANSACTIONS_ID_K_V
TYPE              3
ORDINAL_POSITION  2
COLUMN_NAME       K
ASC_OR_DESC       A
CARDINALITY       0
PAGES             0
FILTER_CONDITION 

TABLE_CAT         IGNITE
TABLE_SCHEM       PUBLIC
TABLE_NAME        TRANSACTIONS
NON_UNIQUE        true
INDEX_QUALIFIER  
INDEX_NAME        TRANSACTIONS_ID_K_V
TYPE              3
ORDINAL_POSITION  3
COLUMN_NAME       V
ASC_OR_DESC       A
CARDINALITY       0
PAGES             0
FILTER_CONDITION 

Regards,
--
Ilya Kasnacheev


пн, 4 февр. 2019 г. в 19:33, joseheitor <[hidden email]>:
Thanks, Ilya.

1. - I see that this issue has been addressed - should I be able to verify
this by pulling the latest nightly-build?

2. - Issuing the following statement via a JDBC application or with a JDBC
client, such as DBeaver, results in the index being created, but only on the
2nd and 3rd specified columns (only tested with JDBC Thin Client):

Assuming a table with the following structure:
CREATE TABLE public.transactions (pk INT, id INT, k VARCHAR, v VARCHAR,
PRIMARY KEY (pk, id))
  WITH "TEMPLATE=PARTITIONED, BACKUPS=1, ATOMICITY=TRANSACTIONAL_SNAPSHOT,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC, AFFINITY_KEY=id";


*CREATE INDEX transactions_id_k_v ON public.transactions (id, k, v)
INLINE_SIZE 100;*
<http://apache-ignite-users.70518.x6.nabble.com/file/t1652/index-missing-first-column.png>

3. - [Apologies] Please ignore - it appears to be working correctly!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/