[IGNITE-17285] Ambiguous output of INDEXES SytemView if cache is created via DDL

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Not A Problem
• Labels: ise
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Ilya Shishkov
• Created: 30/Jun/22 18:46
• Updated: 15/Oct/22 08:52
• Resolved: 14/Oct/22 10:32

Description

There is a difference in 'COLUMNS ' and 'INLINE_SIZE' columns content in system view 'INDEXES', when you create SQL-cache by means of QueryEntity and by means of DDL.
As you can see in reproducer SqlIndexSystemViewReproducerTest.patch , there are two "equal" attepmts to create cache: via DDL, and via Cache API + QueryEntity.
Primary keys contains equal set of fields, affinity fields are the same. Outputs of system views TABLES, TABLE_COLUMNS and BINARY_METADATA are the same for both ways of cache creation. Table content (i.e. select *) is also the same, if you do not take into account the order of output.
There are example sqlline outputs for table from reproducer:

1. create_table.txt - for table, created by DDL.
2. query_entity.txt - for table, created by Cache API.

As you can see, colums and content differs in INDEXES view: in case of DDL, indexes does not have '_KEY' column, and have explicit set of columns from primary key. Also, there is a duplication of affinity column 'ID' for :

"ID" ASC, "FIRSTNAME" ASC, "LASTNAME" ASC, "ID" ASC   

In case of creation table via Cache API + QueryEntity, no exlicit primary key columns are shown, but '_KEY' column is, and there is no duplication of affinity column 'ID' in '_key_PK_hash' index.

Reproducer dumps indexes (org.h2.index.Index) collection content, which is obtained from GridH2Table#getIndexes. It seems, that information differs in this class too.
Example output:

Cache API + QueryEntity

Index name               Columns
_key_PK__SCAN_           [_KEY, ID]
_key_PK_hash             [_KEY, ID]
_key_PK                  [_KEY, ID]
AFFINITY_KEY             [ID, _KEY]
PERSONINFO_CITYNAME_IDX  [CITYNAME, _KEY, ID]

DDL

Index name               Columns
_key_PK__SCAN_           [ID, FIRSTNAME, LASTNAME]
_key_PK_hash             [_KEY, ID]
_key_PK                  [ID, FIRSTNAME, LASTNAME]
AFFINITY_KEY             [ID, FIRSTNAME, LASTNAME]
PERSONINFO_CITYNAME_IDX  [CITYNAME, ID, FIRSTNAME, LASTNAME]

If such difference is not a bug, it should be documented.

Issue Links

• blocks: IGNITE-11368 use the same information about indexes for JDBC drivers as for system view INDEXES