CrateDB 4.5 : SHOW CREATE TABLE problem on this version?

Hi,

I just tried a “SHOW CREATE TABLE” on version 4.5 which can’t be replayed (i spare you the table parameters) :

CREATE TABLE IF NOT EXISTS "doc"."books" (
   "id" INTEGER,
   "author" TEXT,
   "author_ft" TEXT INDEX USING FULLTEXT WITH (
      analyzer = 'mysqllike'
   ),
   "book_title" TEXT,
   "column_name" TEXT,
   "book_title_ft" TEXT INDEX USING FULLTEXT WITH (
      analyzer = 'mysqllike'
   ),
   PRIMARY KEY ("id"),
   INDEX "author_ft" USING FULLTEXT ("author"),
   INDEX "book_title_ft" USING FULLTEXT ("book_title")
)
....

Indexes are specified Twice … so CrateDB says :

But on version 4.4.3, the same table show me this for the SHOW CREATE TABLE statement :

CREATE TABLE IF NOT EXISTS "doc"."books" (
  "id" INTEGER,
  "author" TEXT,
  "book_title" TEXT,
  "column_name" TEXT,
  PRIMARY KEY ("id"),
  INDEX "author_ft" USING FULLTEXT ("author") WITH (
     analyzer = 'mysqllike'
  ),
  INDEX "book_title_ft" USING FULLTEXT ("book_title") WITH (
     analyzer = 'mysqllike'
  )
)
...

Which works fine.

So it seems the SHOW CREATE TABLE statement is buggy on the 4.5 version.

It seems that in 4.4.3, the ALTER… ADD COLUMN creates INDEX FULLTEXT columns as real columns …

Try to create this table :

CREATE TABLE IF NOT EXISTS "doc"."books" (
  "id" INTEGER,
  "author" TEXT,
  "book_title" TEXT,
  PRIMARY KEY ("id"),
  INDEX "author_ft" USING FULLTEXT ("author"),
  INDEX "book_title_ft" USING FULLTEXT ("book_title")
);

And then :

ALTER TABLE "doc"."books" ADD COLUMN "column_name" TEXT;

You will see that every fulltext indexed columns are now real columns … (seen on 4.4.3 and 4.5 but not verified on older version)

Issue added on github.

@MikeMax: I can reproduce this behaviour with CREATE TABLE & ADD COLUMN.

Thanks for reporting it.

Github link: Problem on SHOW CREATE TABLE (4.5) and ALTER ... ADD COLUMN (4.4.3 & 4.5) when fulltext index columns are present. · Issue #11216 · crate/crate · GitHub

@MikeMax As you may have seen the issue was fixed and will be released with the next version of CrateDB

1 Like

Great ! thanks a lot :slight_smile: