[ https://issues.apache.org/jira/browse/IGNITE-10516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17766326#comment-17766326 ]
Ilya Shishkov edited comment on IGNITE-10516 at 9/19/23 11:12 AM: ------------------------------------------------------------------ As I see, problem is still actual both for calcite and h2 engines. According to [1], SQL indexes was not described by standard. Links: # https://en.wikipedia.org/wiki/Database_index#Standardization was (Author: shishkovilja): As I see, problem is still actual both for calcite and h2 engines. > Storage is corrupted after CREATE INDEX IF NOT EXISTS on different tables > ------------------------------------------------------------------------- > > Key: IGNITE-10516 > URL: https://issues.apache.org/jira/browse/IGNITE-10516 > Project: Ignite > Issue Type: Bug > Affects Versions: 2.15 > Reporter: Stanislav Lukyanov > Assignee: Ilya Shishkov > Priority: Major > Labels: ise > > Given two tables in the same schema, we can't create an index with the same > name for both tables. In other words, the following code leads to an error - > which is good. > {code} > CREATE INDEX IDX on T1 (COL); > CREATE INDEX IDX on T2 (COL); > {code} > If used with `IF NOT EXISTS`, the queries pass. It might be OK or not - one > needs to look into SQL spec to check if the second operation should be a > no-op (because IDX exists) or fail (because IDX exists for a different table, > so the caller is probably doing something wrong) > {code} > CREATE INDEX IDX on T1 (COL); > CREATE INDEX IF NOT EXISTS IDX on T2 (COL); > {code} > However, if persistence is enabled, the node will fail to restart complaining > about duplicate index names. > {code} > class org.apache.ignite.IgniteCheckedException: Duplicate index name > [cache=SQL_PUBLIC_T2, schemaName=PUBLIC, idxName=IDX, existingTable=T, > table=T2] > at org.apache.ignite.internal.IgniteKernal.start(IgniteKernal.java:1183) > at > org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start0(IgnitionEx.java:2040) > at > org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start(IgnitionEx.java:1732) > at org.apache.ignite.internal.IgnitionEx.start0(IgnitionEx.java:1158) > at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:656) > at > org.apache.ignite.testframework.junits.GridAbstractTest.startGrid(GridAbstractTest.java:959) > at > org.apache.ignite.testframework.junits.GridAbstractTest.startGrid(GridAbstractTest.java:900) > at > org.apache.ignite.testframework.junits.GridAbstractTest.startGrid(GridAbstractTest.java:888) > at > org.apache.ignite.testframework.junits.GridAbstractTest.startGrid(GridAbstractTest.java:854) > at > org.apache.ignite.IndexWithSameNameTest.test(IndexWithSameNameTest.java:77) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at junit.framework.TestCase.runTest(TestCase.java:176) > at > org.apache.ignite.testframework.junits.GridAbstractTest.access$001(GridAbstractTest.java:150) > at > org.apache.ignite.testframework.junits.GridAbstractTest$6.evaluate(GridAbstractTest.java:2104) > at > org.apache.ignite.testframework.junits.GridAbstractTest$7.run(GridAbstractTest.java:2119) > at java.lang.Thread.run(Thread.java:748) > Caused by: class org.apache.ignite.IgniteException: Duplicate index name > [cache=SQL_PUBLIC_T2, schemaName=PUBLIC, idxName=IDX, existingTable=T, > table=T2] > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.registerCache0(GridQueryProcessor.java:1650) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.onCacheStart0(GridQueryProcessor.java:803) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.onCacheStart(GridQueryProcessor.java:866) > at > org.apache.ignite.internal.processors.cache.GridCacheProcessor.startCacheInRecoveryMode(GridCacheProcessor.java:2595) > at > org.apache.ignite.internal.processors.cache.GridCacheProcessor.access$1400(GridCacheProcessor.java:204) > at > org.apache.ignite.internal.processors.cache.GridCacheProcessor$CacheRecoveryLifecycle.afterBinaryMemoryRestore(GridCacheProcessor.java:5481) > at > org.apache.ignite.internal.processors.cache.persistence.GridCacheDatabaseSharedManager.restoreBinaryMemory(GridCacheDatabaseSharedManager.java:947) > at > org.apache.ignite.internal.processors.cache.persistence.GridCacheDatabaseSharedManager.startMemoryRestore(GridCacheDatabaseSharedManager.java:1922) > at org.apache.ignite.internal.IgniteKernal.start(IgniteKernal.java:1050) > ... 18 more > {code} > It looks like the second index (on T2) is partially created after all. > Need to either block index creation by `CREATE INDEX IF NOT EXISTS` > completely, or just fail that query when the table names don't match (if SQL > spec allows it). -- This message was sent by Atlassian Jira (v8.20.10#820010)