Table with column name starting with _ fails to be created, followed by failure to drop or create the table again

[americast]

Search before asking

• I have searched the EvaDB issues and found no similar bug report.

Bug

I get an error if I run a CREATE TABLE query that has a column name starting with _:

CREATE TABLE hello (
        _row_id INTEGER,
        price TEXT,
        beds TEXT,
        bath TEXT,
        area TEXT,
        address TEXT);

This is the error I get:

ExecutorError: (sqlite3.IntegrityError) UNIQUE constraint failed: column_catalog.name, column_catalog.table_id
[SQL: INSERT INTO column_catalog (name, type, is_nullable, array_type, array_dimensions, table_id) VALUES (?, ?, ?, ?, ?, ?)]
[parameters: ('_row_id', 'INTEGER', 1, None, '()', 1)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

However, strangely, after this, I am unable to either drop nor create the table hello again.

CREATE TABLE hello (
        rowid INTEGER,
        price TEXT,
        beds TEXT,
        bath TEXT,
        area TEXT,
        address TEXT);

leads to ExecutorError: Table: hello already exists. And,

DROP TABLE IF EXISTS hello;

leads to ExecutorError: Failed to drop the table hello with Exception No table found with name hello.

The only way to recover from here seems to be to delete the evadb_data directory.

Environment

Latest staging on Ubuntu 20.04

Are you willing to submit a PR?

• Yes I'd like to help by submitting a PR!

[gaurav274]

@americast _row_id is a reserved keyword, and users cannot create a table with this column name.

[gaurav274]

I can reproduce the other error. Will fix it.