Cannot create SQL index with INLINE_SIZE if ` is used

classic Classic list List threaded Threaded
3 messages Options
Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

Cannot create SQL index with INLINE_SIZE if ` is used

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

https://hypi.io
Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

Re: Cannot create SQL index with INLINE_SIZE if ` is used

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

https://hypi.io
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Cannot create SQL index with INLINE_SIZE if ` is used

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,
--
Ilya Kasnacheev


сб, 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

https://hypi.io