Cannot create SQL index with INLINE_SIZE if ` is used

[Courtney Robinson]

If you use

CREATE INDEX IF NOT EXISTS myIdx ON myTbl(myCol) INLINE_SIZE 200;

then this works as expected. If the column names are escaped then it fails with a syntax error

so

CREATE INDEX IF NOT EXISTS myIdx ON myTbl(`myCol`) INLINE_SIZE 200;

will fail with a syntax error.

I've found a workaround is to use " instead of `

CREATE INDEX IF NOT EXISTS myIdx ON myTbl("myCol") INLINE_SIZE 200;

First found in 2.8.1 and verified to still fail on 2.9.1.

[Courtney Robinson]

Test case to reproduce (added to JdbcThinMultiStatementSelfTest):

@Test
public void testInlineSizeOptionWithBacktick() throws Exception {
    execute(
        "CREATE TABLE public.backticks (pk INT, id INT, k VARCHAR, v VARCHAR, PRIMARY KEY (pk, id)); " +
        "CREATE INDEX backticks_id_k_v ON public.backticks (`id`, `k`, `v`) INLINE_SIZE 150; "
    );
}

On Sat, Feb 6, 2021 at 2:38 PM Courtney Robinson <[hidden email]> wrote:

If you use

CREATE INDEX IF NOT EXISTS myIdx ON myTbl(myCol) INLINE_SIZE 200;

then this works as expected. If the column names are escaped then it fails with a syntax error

so

CREATE INDEX IF NOT EXISTS myIdx ON myTbl(`myCol`) INLINE_SIZE 200;

will fail with a syntax error.

I've found a workaround is to use " instead of `

CREATE INDEX IF NOT EXISTS myIdx ON myTbl("myCol") INLINE_SIZE 200;

First found in 2.8.1 and verified to still fail on 2.9.1.

Regards,

Courtney Robinson

Founder and CEO, Hypi

Tel: ++44 208 123 2413 (GMT+0)

https://hypi.io

[ilya.kasnacheev]

Hello!

Please file a ticket about that, against Apache Ignite JIRA: https://issues.apache.org/jira/projects/IGNITE

Parsing of INLINE_SIZE, etc, is our own addendum to H2 grammar so we could have broken something there.

Regards,

сб, 6 февр. 2021 г. в 17:50, Courtney Robinson <[hidden email]>:

Test case to reproduce (added to JdbcThinMultiStatementSelfTest):

@Test
public void testInlineSizeOptionWithBacktick() throws Exception {
    execute(
        "CREATE TABLE public.backticks (pk INT, id INT, k VARCHAR, v VARCHAR, PRIMARY KEY (pk, id)); " +
        "CREATE INDEX backticks_id_k_v ON public.backticks (`id`, `k`, `v`) INLINE_SIZE 150; "
    );
}

On Sat, Feb 6, 2021 at 2:38 PM Courtney Robinson <[hidden email]> wrote:

If you use

CREATE INDEX IF NOT EXISTS myIdx ON myTbl(myCol) INLINE_SIZE 200;

then this works as expected. If the column names are escaped then it fails with a syntax error

so

CREATE INDEX IF NOT EXISTS myIdx ON myTbl(`myCol`) INLINE_SIZE 200;

will fail with a syntax error.

I've found a workaround is to use " instead of `

CREATE INDEX IF NOT EXISTS myIdx ON myTbl("myCol") INLINE_SIZE 200;

First found in 2.8.1 and verified to still fail on 2.9.1.

Regards,

Courtney Robinson

Founder and CEO, Hypi

Tel: ++44 208 123 2413 (GMT+0)

https://hypi.io