CREATE TABLE T()

in backend/schema/updater/schema_updater_tests/index.cc [328:905]

• 482 lines of code
• 13 McCabe index (conditional complexity)

        CREATE TABLE T (
          k1 INT64,
          c1 INT64
        ) PRIMARY KEY (k1 ASC)
      )sql",
                                               R"sql(
        CREATE INDEX Idx ON T(c1, k1)
      )sql"}));
  }

  auto idx = schema->FindIndex("Idx");
  EXPECT_NE(idx, nullptr);
  EXPECT_EQ(idx->key_columns().size(), 2);
  EXPECT_FALSE(idx->key_columns()[0]->is_descending());
  EXPECT_FALSE(idx->key_columns()[1]->is_descending());
  if (GetParam() == POSTGRESQL) {
    // Sorted NULLs last
    EXPECT_TRUE(idx->key_columns()[0]->is_nulls_last());
    EXPECT_TRUE(idx->key_columns()[1]->is_nulls_last());
  } else {
    // Sorted NULLs first
    EXPECT_FALSE(idx->key_columns()[0]->is_nulls_last());
    EXPECT_FALSE(idx->key_columns()[1]->is_nulls_last());
  }
}

TEST_P(SchemaUpdaterTest, CreateIndex_SharedPK) {
  // Null filtered indexes are not supported in PG.
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T (
        k1 INT64 NOT NULL,
        c1 STRING(MAX),
        c2 STRING(MAX)
      ) PRIMARY KEY (k1)
    )sql",
                                        R"sql(
      CREATE NULL_FILTERED INDEX Idx ON T(k1) STORING(c2)
    )sql"}));

  auto t = schema->FindTable("T");
  auto k1 = t->FindColumn("k1");

  auto idx = schema->FindIndex("Idx");
  EXPECT_NE(idx, nullptr);
  EXPECT_EQ(idx->stored_columns().size(), 1);
  EXPECT_EQ(idx->key_columns().size(), 1);

  auto idx_data = idx->index_data_table();
  EXPECT_EQ(idx_data->primary_key().size(), 1);
  EXPECT_THAT(idx_data->primary_key()[0]->column(), SourceColumnIs(k1));
}

TEST_P(SchemaUpdaterTest, CreateIndex_NullFiltered_Unique) {
  // Null filtered indexes are not supported in PG.
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 STRING(MAX),
        c2 STRING(MAX),
        c3 STRING(MAX) NOT NULL,
      ) PRIMARY KEY (k1)
    )sql",
                                        R"sql(
      CREATE UNIQUE NULL_FILTERED INDEX Idx ON T(c1) STORING(c2,c3)
    )sql"}));

  auto idx = schema->FindIndex("Idx");
  EXPECT_TRUE(idx->is_null_filtered());
  EXPECT_TRUE(idx->is_unique());

  auto idx_data = idx->index_data_table();
  auto data_columns = idx_data->columns();
  EXPECT_EQ(data_columns.size(), 4);

  // Indexed column is not nullable.
  EXPECT_THAT(data_columns[0], ColumnIs("c1", types::StringType()));
  EXPECT_FALSE(data_columns[0]->is_nullable());

  // Table PK nullability is retained.
  EXPECT_THAT(data_columns[1], ColumnIs("k1", types::Int64Type()));
  EXPECT_TRUE(data_columns[1]->is_nullable());

  // Stored columns nullability is retained.
  EXPECT_THAT(data_columns[2], ColumnIs("c2", types::StringType()));
  EXPECT_TRUE(data_columns[2]->is_nullable());

  EXPECT_THAT(data_columns[3], ColumnIs("c3", types::StringType()));
  EXPECT_FALSE(data_columns[3]->is_nullable());
}

TEST_P(SchemaUpdaterTest, CreateIndex_Interleave) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T1 (
        k1 INT64,
        k2 INT64
      ) PRIMARY KEY (k1)
    )sql",
                                        R"sql(
      CREATE TABLE T2 (
        k1 INT64,
        k2 INT64,
        c1 BYTES(MAX)
      ) PRIMARY KEY (k1,k2), INTERLEAVE IN PARENT T1
    )sql",
                                        R"sql(
      CREATE INDEX Idx ON T2(k1,c1), INTERLEAVE IN T1
    )sql"}));

  auto t1 = schema->FindTable("T1");
  EXPECT_NE(t1, nullptr);

  auto idx = schema->FindIndex("Idx");
  EXPECT_EQ(idx->parent(), t1);
  EXPECT_NE(idx, nullptr);
  auto idx_data = idx->index_data_table();
  EXPECT_EQ(idx_data->parent(), t1);
  EXPECT_THAT(idx_data, IsInterleavedIn(t1, Table::OnDeleteAction::kCascade));
}

TEST_P(SchemaUpdaterTest, CreateIndex_NullFilteredInterleave) {
  // Null filtered indexes are not supported in PG.
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T1 (
        k1 INT64,
        k2 INT64
      ) PRIMARY KEY (k1)
    )sql",
                                        R"sql(
      CREATE TABLE T2 (
        k1 INT64,
        k2 INT64,
        c1 BYTES(MAX)
      ) PRIMARY KEY (k1,k2), INTERLEAVE IN PARENT T1
    )sql",
                                        R"sql(
      CREATE NULL_FILTERED INDEX Idx ON T2(k1,c1), INTERLEAVE IN T1
    )sql"}));

  auto t1 = schema->FindTable("T1");
  EXPECT_NE(t1, nullptr);

  auto idx = schema->FindIndex("Idx");
  EXPECT_EQ(idx->parent(), t1);
  EXPECT_NE(idx, nullptr);
  auto idx_data = idx->index_data_table();
  EXPECT_EQ(idx_data->parent(), t1);
  EXPECT_THAT(idx_data, IsInterleavedIn(t1, Table::OnDeleteAction::kCascade));

  EXPECT_TRUE(t1->FindColumn("k1")->is_nullable());
  EXPECT_FALSE(idx_data->FindColumn("k1")->is_nullable());
}

TEST_P(SchemaUpdaterTest, CreateIndex_InvalidInterleaved) {
  EXPECT_THAT(
      CreateSchema({R"sql(
      CREATE TABLE T1 (
        k1 INT64,
        k2 INT64
      ) PRIMARY KEY (k1)
    )sql",
                    R"sql(
      CREATE TABLE T2 (
        k1 INT64,
        k2 INT64,
        c1 BYTES(MAX)
      ) PRIMARY KEY (k1,k2)
    )sql",
                    R"sql(
      CREATE INDEX Idx ON T2(k1,c1), INTERLEAVE IN T1
    )sql"}),
      StatusIs(error::IndexInterleaveTableUnacceptable("Idx", "T2", "T1")));
}

TEST_P(SchemaUpdaterTest, CreateIndex_TableNotFound) {
  EXPECT_THAT(CreateSchema({"CREATE INDEX Idx ON T2(k1)"}),
              StatusIs(error::TableNotFound("T2")));
}

TEST_P(SchemaUpdaterTest, CreateIndex_ColumnNotFound) {
  EXPECT_THAT(CreateSchema({
                  R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1)
    )sql",
                  R"sql(
      CREATE INDEX Idx ON T(c2)
    )sql"}),
              StatusIs(error::IndexRefsNonExistentColumn("Idx", "c2")));
}

TEST_P(SchemaUpdaterTest, CreateIndex_DuplicateColumn) {
  EXPECT_THAT(CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1)
    )sql",
                            R"sql(
      CREATE INDEX Idx ON T(c1,c1)
    )sql"}),
              StatusIs(error::IndexRefsColumnTwice("Idx", "c1")));
}

TEST_P(SchemaUpdaterTest, CreateIndex_StoredRefsIndexKey) {
  EXPECT_THAT(CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1)
    )sql",
                            R"sql(
      CREATE INDEX Idx ON T(c1) STORING(c1)
    )sql"}),
              StatusIs(error::IndexRefsKeyAsStoredColumn("Idx", "c1")));
}

TEST_P(SchemaUpdaterTest, CreateIndex_UnsupportedArrayTypeKeyColumn) {
  EXPECT_THAT(CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 ARRAY<INT64>
      ) PRIMARY KEY (k1)
    )sql",
                            R"sql(
      CREATE INDEX Idx ON T(c1)
    )sql"}),
              StatusIs(error::CannotCreateIndexOnColumn("Idx", "c1", "ARRAY")));
}

TEST_P(SchemaUpdaterTest, CreateIndex_ArrayStoredColumn) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64,
        c2 ARRAY<INT64>
      ) PRIMARY KEY (k1)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1) STORING(c2)
    )sql"}));

  auto idx = schema->FindIndex("Idx");
  EXPECT_NE(idx, nullptr);
  EXPECT_EQ(idx->stored_columns().size(), 1);
  auto c2 = idx->stored_columns()[0];

  const zetasql::ArrayType* array_type;
  ZETASQL_ASSERT_OK(type_factory_.MakeArrayType(types::Int64Type(), &array_type));

  EXPECT_THAT(c2, ColumnIs("c2", array_type));
}

TEST_P(SchemaUpdaterTest, AlterIndex_AddColumn) {
  const zetasql::ArrayType* int_array_type;
  ZETASQL_ASSERT_OK(type_factory_.MakeArrayType(types::Int64Type(), &int_array_type));

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64,
        c2 ARRAY<INT64>,
        c3 STRING(123),
      ) PRIMARY KEY (k1)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1) STORING(c2)
    )sql"}));

  ZETASQL_ASSERT_OK_AND_ASSIGN(
      auto new_schema,
      UpdateSchema(schema.get(),
                   {R"sql(ALTER INDEX Idx ADD STORED COLUMN c3)sql"}));
  auto idx = new_schema->FindIndex("Idx");
  ASSERT_NOT_NULL(idx);

  EXPECT_THAT(idx->stored_columns(),
              UnorderedElementsAre(ColumnIs("c2", int_array_type),
                                   ColumnIs("c3", types::StringType())));
}

TEST_P(SchemaUpdaterTest, AlterIndex_StoreNotNullColumn) {
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T (
        col1 INT64 NOT NULL,
        col2 STRING(MAX) NOT NULL,
        col3 INT64 NOT NULL,
      ) PRIMARY KEY (col1)
    )sql",
                                        R"sql(
      CREATE INDEX Idx ON T(col2)
    )sql"}));

  ZETASQL_EXPECT_OK(UpdateSchema(schema.get(), {R"sql(
      ALTER INDEX Idx ADD STORED COLUMN col3
    )sql"}));
}

TEST_P(SchemaUpdaterTest, AlterIndex_StoreNotNullColumnWithNullFilteredIndex) {
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T (
        col1 INT64 NOT NULL,
        col2 STRING(MAX) NOT NULL,
        col3 INT64 NOT NULL,
      ) PRIMARY KEY (col1)
    )sql",
                                        R"sql(
      CREATE NULL_FILTERED INDEX Idx ON T(col2)
    )sql"}));

  ZETASQL_EXPECT_OK(UpdateSchema(schema.get(), {R"sql(
      ALTER INDEX Idx ADD STORED COLUMN col3
    )sql"}));
}

TEST_P(SchemaUpdaterTest, AlterIndex_AddColumnNotFound) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1)
    )sql"}));

  EXPECT_THAT(
      UpdateSchema(schema.get(),
                   {R"sql(ALTER INDEX Idx ADD STORED COLUMN not_existed)sql"}),
      StatusIs(error::ColumnNotFound("T", "not_existed")));
}

TEST_P(SchemaUpdaterTest, AlterIndex_DropColumn) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64,
        c2 ARRAY<INT64>
      ) PRIMARY KEY (k1)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1) STORING(c2)
    )sql"}));

  ZETASQL_ASSERT_OK_AND_ASSIGN(
      auto new_schema,
      UpdateSchema(schema.get(),
                   {R"sql(ALTER INDEX Idx DROP STORED COLUMN c2)sql"}));

  auto idx = new_schema->FindIndex("Idx");
  ASSERT_NOT_NULL(idx);
  EXPECT_THAT(idx->stored_columns(), IsEmpty());
}

TEST_P(SchemaUpdaterTest, AlterIndex_DropColumnNotFound) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1)
    )sql"}));

  EXPECT_THAT(
      UpdateSchema(schema.get(),
                   {R"sql(ALTER INDEX Idx DROP STORED COLUMN not_existed)sql"}),
      StatusIs(error::ColumnNotFoundInIndex("Idx", "not_existed")));
}

TEST_P(SchemaUpdaterTest, AlterIndex_WithLocalityGroup) {
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T (
        col1 INT64 NOT NULL,
        col2 STRING(MAX) NOT NULL,
        col3 INT64 NOT NULL,
      ) PRIMARY KEY (col1)
    )sql",
                                        R"sql(
      CREATE LOCALITY GROUP lg
      OPTIONS (storage = 'ssd', ssd_to_hdd_spill_timespan = '10m')
    )sql",
                                        R"sql(
      CREATE INDEX Idx ON T(col2)
    )sql"}));

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema, UpdateSchema(schema.get(), {R"sql(
      ALTER INDEX Idx SET OPTIONS (locality_group = 'lg')
    )sql"}));

  const Index* idx = new_schema->FindIndex("Idx");
  ASSERT_NOT_NULL(idx);

  ASSERT_NOT_NULL(idx->locality_group());
  EXPECT_EQ(idx->locality_group()->Name(), "lg");
}

TEST_P(SchemaUpdaterTest, DropTable_WithIndex) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1 ASC)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx1 ON T(c1 DESC, k1 DESC)
    )sql"}));

  // Global index.
  EXPECT_THAT(UpdateSchema(schema.get(), {R"sql(
      DROP TABLE T
    )sql"}),
              StatusIs(error::DropTableWithDependentIndices("T", "Idx1")));

  ZETASQL_ASSERT_OK_AND_ASSIGN(schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1 ASC)
    )sql",
                                             R"sql(
      CREATE INDEX Idx2 ON T(k1), INTERLEAVE IN T
    )sql"}));

  // Interleaved index.
  EXPECT_THAT(UpdateSchema(schema.get(), {R"sql(
      DROP TABLE T
    )sql"}),
              StatusIs(error::DropTableWithDependentIndices("T", "Idx2")));
}

TEST_P(SchemaUpdaterTest, DropIndex) {
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1 ASC)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1 DESC, k1 DESC)
    )sql"}));

  EXPECT_NE(schema->FindIndex("Idx"), nullptr);

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema, UpdateSchema(schema.get(), {R"sql(
      DROP INDEX Idx
    )sql"}));

  EXPECT_EQ(new_schema->FindIndex("Idx"), nullptr);

  // Check that the index data table (and other dependent nodes) are
  // also deleted.
  EXPECT_EQ(new_schema->GetSchemaGraph()->GetSchemaNodes().size(), 4);
}

TEST_P(SchemaUpdaterTest, DropIndexIfExists) {
  // IF NOT EXISTS isn't yet supported on the PG side of the emulator
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1 ASC)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1 DESC, k1 DESC)
    )sql"}));

  EXPECT_EQ(schema->GetSchemaGraph()->GetSchemaNodes().size(), 10);

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema, UpdateSchema(schema.get(), {R"sql(
      DROP INDEX Idx
    )sql"}));

  EXPECT_EQ(new_schema->FindIndex("Idx"), nullptr);

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema2, UpdateSchema(new_schema.get(), {R"sql(
      DROP INDEX IF EXISTS Idx
    )sql"}));

  EXPECT_EQ(new_schema2->FindIndex("Idx"), nullptr);
}

TEST_P(SchemaUpdaterTest, DropIndexIfExistsTwice) {
  // IF NOT EXISTS isn't yet supported on the PG side of the emulator
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1 ASC)
    )sql",
                                                  R"sql(
      CREATE INDEX Idx ON T(c1 DESC, k1 DESC)
    )sql"}));

  EXPECT_EQ(schema->GetSchemaGraph()->GetSchemaNodes().size(), 10);

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema, UpdateSchema(schema.get(), {R"sql(
      DROP INDEX IF EXISTS Idx
    )sql"}));

  EXPECT_EQ(new_schema->FindIndex("Idx"), nullptr);

  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema2, UpdateSchema(new_schema.get(), {R"sql(
      DROP INDEX IF EXISTS Idx
    )sql"}));

  EXPECT_EQ(new_schema2->FindIndex("Idx"), nullptr);
}

TEST_P(SchemaUpdaterTest, DropIndexIfExistsButIndexDoesNotExist) {
  // IF NOT EXISTS isn't yet supported on the PG side of the emulator
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({R"sql(
      CREATE TABLE T (
        k1 INT64,
        c1 INT64
      ) PRIMARY KEY (k1 ASC)
    )sql"}));

  EXPECT_EQ(schema->FindIndex("Idx"), nullptr);

  // Make sure dropping an index that doesn't exist is fine.
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto new_schema, UpdateSchema(schema.get(), {R"sql(
      DROP INDEX IF EXISTS Idx
    )sql"}));

  EXPECT_EQ(new_schema->FindIndex("Idx"), nullptr);
}

TEST_P(SchemaUpdaterTest, CreateIndexOnTableWithNoPK) {
  // Table with no key columns is not supported in PG.
  if (GetParam() == POSTGRESQL) GTEST_SKIP();
  ZETASQL_ASSERT_OK_AND_ASSIGN(auto schema, CreateSchema({
                                        R"sql(
      CREATE TABLE T ( col1 INT64 ) PRIMARY KEY ()
    )sql",
                                        R"sql(
      CREATE INDEX Idx ON T(col1)
    )sql"}));
  auto t = schema->FindTable("T");
  ASSERT_NE(t, nullptr);
  auto col1 = t->FindColumn("col1");
  ASSERT_NE(col1, nullptr);
  auto idx = schema->FindIndex("Idx");
  ASSERT_NE(idx, nullptr);

  EXPECT_EQ(idx->key_columns().size(), 1);

  auto idx_data = idx->index_data_table();
  auto data_columns = idx_data->columns();
  EXPECT_EQ(data_columns.size(), 1);
  EXPECT_THAT(data_columns[0], ColumnIs("col1", types::Int64Type()));

  EXPECT_THAT(idx_data->primary_key()[0]->column(), SourceColumnIs(col1));
}

TEST_P(SchemaUpdaterTest, CreateIndex_NumericColumn) {
  if (GetParam() == POSTGRESQL) {
    // PG.NUMERIC does not support for indexing yet.
    EXPECT_THAT(CreateSchema(
                    {