[YSQL] Unable to create partition after partial index creation in partitioned table

[Bhautik0110]

Jira Link: DB-3113

Problem

I have a partitioned table called logs. Here is the following schema.

id text,
status int,
path text,
date text,
file_id text

I created the partition by date.

Now I am creating the partial index on file_id column using

CREATE INDEX IF NOT EXISTS logs_file_id_idx ON logs(file_id) WHERE file_id IS NOT NULL ;

It throws the below error on creating the partition.

CREATE TABLE IF NOT EXISTS logs_2022_08_01 PARTITION OF logs FOR VALUES FROM ('2022-08-01') TO ('2022-08-02');  

SQL Error [XX000]: ERROR: unexpected varattno 23 in expression to be mapped

Without the partial index, Partition is usually created.

Version

Deployment using: docker Image: yugabytedb/yugabyte:2.14.0.0-b94

[backendArchitect]

Seems like the error is from your side

I tried it the same and got no ERROR.

[Bhautik0110]

@codesnail21 You put the output of partial index creation. Now can you create the partition?

[backendArchitect]

@codesnail21 You put the output of partial index creation. Now can you create the partition?

Yes Definitely.

[Bhautik0110]

Seems you've created a partial index on abc table, and created a partition on logs table!

[Bhautik0110]

@codesnail21 You should create the partition and partial index on the same partition table!

[backendArchitect]

Seems you've created a partial index on abc table, and created a partition on logs table!

My Bad updated the img Sorry

[ddorian]

@Bhautik0110 please include all DDL/DML queries and the version that you're using in the original post

[tedyu]

I wasn't able to reproduce using latest master branch:

yugabyte=# create table logs(id text, status int, path text,
yugabyte(# date text, file_id text) PARTITION BY RANGE(date);
CREATE TABLE
yugabyte=# CREATE INDEX IF NOT EXISTS logs_file_id_idx ON logs(file_id) WHERE file_id IS NOT NULL ;
CREATE INDEX
yugabyte=# CREATE TABLE IF NOT EXISTS logs_2022_08_01 PARTITION OF logs FOR VALUES FROM ('2022-08-01') TO ('2022-08-02');
CREATE TABLE

[kmuthukk]

@tedyu -- did you try to repro on the same release as the user: yugabytedb/yugabyte:2.14.0.0-b94 ?

[tedyu]

bin/ysqlsh -p 5433 -h 10.9.88.3
ysqlsh (11.2-YB-2.14.1.0-b0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

yugabyte=# create table logs(id text, status int, path text, date text, file_id text) PARTITION BY RANGE(date);
CREATE TABLE
yugabyte=# CREATE INDEX IF NOT EXISTS logs_file_id_idx ON logs(file_id) WHERE file_id IS NOT NULL ;
CREATE INDEX
yugabyte=# CREATE TABLE IF NOT EXISTS logs_2022_08_01 PARTITION OF logs FOR VALUES FROM ('2022-08-01') TO ('2022-08-02');
CREATE TABLE

[backendArchitect]

@tedyu -- did you try to repro on the same release as the user: yugabytedb/yugabyte:2.14.0.0-b94 ?

I tried in the same as @Bhautik0110

found the same, while creating a partition on date! earlier i was able to get succeed, maybe because of I have only little amount of date For now, I tested it in around approx 400K Data.

SQL Error [XX000]: ERROR: unexpected varattno 18 in expression to be mapped

Is it anyhow FIXABLE ???

[Bhautik0110]

Probably schema versioning is the issue:

How do I fix the schema versioning problem?