CrateDB 4.5 : SHOW CREATE TABLE problem on this version?

CrateDB
1

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 :

image
image1145×577 17.3 KB

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.

2

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)

3

Issue added on github.

4

@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

5

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

1 Like
6

Great ! thanks a lot :slight_smile: