in backend/schema/updater/schema_updater_tests/index.cc [84:315]
CREATE TABLE T (
k1 INT64 NOT NULL,
c1 STRING(10),
c2 STRING(MAX),
c3 NUMERIC,
c4 JSON
) PRIMARY KEY (k1)
)sql",
R"sql(
CREATE INDEX Idx1 ON T(c1)
)sql",
R"sql(
CREATE INDEX Idx2 ON T(c1) STORING(c2, c3, c4))sql",
}));
}
auto idx = schema->FindIndex("Idx1");
EXPECT_NE(idx, nullptr);
auto t = schema->FindTable("T");
EXPECT_EQ(idx->indexed_table(), t);
EXPECT_FALSE(idx->is_null_filtered());
EXPECT_FALSE(idx->is_unique());
EXPECT_EQ(idx->key_columns().size(), 1);
EXPECT_EQ(idx->stored_columns().size(), 0);
// The data table is not discoverable in the Schema.
EXPECT_EQ(schema->FindTable(absl::StrCat(kIndexDataTablePrefix, "Idx1")),
nullptr);
auto idx_data = idx->index_data_table();
EXPECT_NE(idx_data, nullptr);
EXPECT_TRUE(idx_data->indexes().empty());
EXPECT_EQ(idx_data->primary_key().size(), 2);
auto data_pk = idx_data->primary_key();
auto t_c1 = t->FindColumn("c1");
EXPECT_THAT(data_pk[0]->column(), ColumnIs("c1", type_factory_.get_string()));
EXPECT_THAT(data_pk[0]->column(), SourceColumnIs(t_c1));
EXPECT_EQ(data_pk[0], idx->key_columns()[0]);
auto t_k1 = t->FindColumn("k1");
EXPECT_THAT(data_pk[1]->column(), ColumnIs("k1", type_factory_.get_int64()));
EXPECT_THAT(data_pk[1]->column(), SourceColumnIs(t_k1));
// For non-null-filtered indexes, the nullability of column matches
// the nullability of source column.
EXPECT_EQ(data_pk[0]->column()->is_nullable(), t_c1->is_nullable());
EXPECT_EQ(data_pk[1]->column()->is_nullable(), t_k1->is_nullable());
auto idx2 = schema->FindIndex("Idx2");
EXPECT_NE(idx2, nullptr);
EXPECT_EQ(idx2->stored_columns().size(), 3);
auto t_c2 = t->FindColumn("c2");
auto idx2_c2 = idx2->stored_columns()[0];
EXPECT_THAT(idx2_c2, ColumnIs("c2", type_factory_.get_string()));
EXPECT_THAT(idx2_c2, SourceColumnIs(t_c2));
auto t_c3 = t->FindColumn("c3");
auto idx2_c3 = idx2->stored_columns()[1];
if (GetParam() == POSTGRESQL) {
EXPECT_TRUE(idx2_c3->GetType()->IsExtendedType());
EXPECT_EQ(
static_cast<const SpannerExtendedType*>(idx2_c3->GetType())->code(),
PG_NUMERIC);
} else {
EXPECT_THAT(idx2_c3, ColumnIs("c3", type_factory_.get_numeric()));
}
EXPECT_THAT(idx2_c3, SourceColumnIs(t_c3));
auto t_c4 = t->FindColumn("c4");
auto idx2_c4 = idx2->stored_columns()[2];
if (GetParam() == POSTGRESQL) {
EXPECT_TRUE(idx2_c4->GetType()->IsExtendedType());
EXPECT_EQ(
static_cast<const SpannerExtendedType*>(idx2_c4->GetType())->code(),
PG_JSONB);
} else {
EXPECT_THAT(idx2_c4, ColumnIs("c4", type_factory_.get_json()));
}
EXPECT_THAT(idx2_c4, SourceColumnIs(t_c4));
}
TEST_P(SchemaUpdaterTest, CreateIndex_NoKeys) {
// Creating an index with no key columns is not supported in PG.
if (GetParam() == POSTGRESQL) GTEST_SKIP();
EXPECT_THAT(CreateSchema({R"sql(
CREATE TABLE T (
k1 INT64,
c1 INT64
) PRIMARY KEY (k1)
)sql",
R"sql(
CREATE INDEX Idx ON T()
)sql"}),
StatusIs(error::IndexWithNoKeys("Idx")));
}
TEST_P(SchemaUpdaterTest, CreateIndex_WithLocalityGroup) {
if (GetParam() == POSTGRESQL) GTEST_SKIP();
std::unique_ptr<const Schema> schema;
ZETASQL_ASSERT_OK_AND_ASSIGN(schema, CreateSchema({R"sql(
CREATE TABLE T (
k1 INT64,
c1 INT64
) PRIMARY KEY (k1)
)sql",
R"sql(
CREATE LOCALITY GROUP lg
OPTIONS (storage = 'ssd', ssd_to_hdd_spill_timespan = '10m')
)sql",
R"sql(
CREATE INDEX Idx ON T(c1) OPTIONS (locality_group = 'lg')
)sql"}));
const Index* idx = schema->FindIndex("Idx");
ASSERT_NOT_NULL(idx);
ASSERT_NOT_NULL(idx->locality_group());
EXPECT_EQ(idx->locality_group()->Name(), "lg");
}
TEST_P(SchemaUpdaterTest, CreateIndexIfNotExists) {
// IF NOT EXISTS isn't yet supported on the PG side of the emulator
if (GetParam() == POSTGRESQL) GTEST_SKIP();
EXPECT_THAT(CreateSchema({R"sql(
CREATE TABLE T (
k1 INT64,
c1 INT64
) PRIMARY KEY (k1)
)sql",
R"sql(
CREATE INDEX IF NOT EXISTS Idx ON T(c1)
)sql"}),
StatusIs(absl::OkStatus()));
}
TEST_P(SchemaUpdaterTest, CreateIndexWhereIsNotNull) {
std::unique_ptr<const Schema> schema;
ZETASQL_ASSERT_OK_AND_ASSIGN(schema, CreateSchema({
R"sql(
CREATE TABLE T (
k1 INT64,
c1 INT64,
s1 STRING(MAX),
) PRIMARY KEY (k1)
)sql",
R"sql(
CREATE INDEX Idx ON T(c1, s1) WHERE c1 IS NOT NULL AND s1 IS NOT NULL
)sql"}));
const Index* idx = schema->FindIndex("Idx");
EXPECT_NE(idx, nullptr);
const Table* base_table = schema->FindTable("T");
EXPECT_EQ(idx->indexed_table(), base_table);
EXPECT_FALSE(idx->is_null_filtered());
EXPECT_FALSE(idx->is_unique());
EXPECT_EQ(idx->key_columns().size(), 2);
EXPECT_EQ(idx->stored_columns().size(), 0);
// The data table is not discoverable in the Schema.
EXPECT_EQ(schema->FindTable(absl::StrCat(kIndexDataTablePrefix, "Idx")),
nullptr);
const Table* idx_data = idx->index_data_table();
EXPECT_NE(idx_data, nullptr);
EXPECT_TRUE(idx_data->indexes().empty());
EXPECT_EQ(idx_data->primary_key().size(), 3);
auto data_pk = idx_data->primary_key();
auto t_c1 = base_table->FindColumn("c1");
EXPECT_THAT(data_pk[0]->column(), ColumnIs("c1", type_factory_.get_int64()));
EXPECT_THAT(data_pk[0]->column(), SourceColumnIs(t_c1));
EXPECT_EQ(data_pk[0], idx->key_columns()[0]);
EXPECT_FALSE(data_pk[0]->column()->is_nullable());
auto t_s1 = base_table->FindColumn("s1");
EXPECT_THAT(data_pk[1]->column(), ColumnIs("s1", type_factory_.get_string()));
EXPECT_THAT(data_pk[1]->column(), SourceColumnIs(t_s1));
EXPECT_FALSE(data_pk[1]->column()->is_nullable());
auto t_k1 = base_table->FindColumn("k1");
EXPECT_THAT(data_pk[2]->column(), ColumnIs("k1", type_factory_.get_int64()));
EXPECT_THAT(data_pk[2]->column(), SourceColumnIs(t_k1));
EXPECT_EQ(data_pk[2]->column()->is_nullable(), t_k1->is_nullable());
}
TEST_P(SchemaUpdaterTest, CreateIndexIfNotExistsOnExistingIndex) {
// IF NOT EXISTS isn't yet supported on the PG side of the emulator
if (GetParam() == POSTGRESQL) GTEST_SKIP();
EXPECT_THAT(CreateSchema({R"sql(
CREATE TABLE T (
k1 INT64,
c1 INT64
) PRIMARY KEY (k1)
)sql",
R"sql(
CREATE INDEX Idx ON T(c1)
)sql",
R"sql(
CREATE INDEX IF NOT EXISTS Idx ON T(c1)
)sql"}),
StatusIs(absl::OkStatus()));
}
TEST_P(SchemaUpdaterTest, CreateIndex_DescKeys) {
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"}));
auto idx = schema->FindIndex("Idx");
EXPECT_NE(idx, nullptr);
EXPECT_EQ(idx->key_columns().size(), 2);
EXPECT_TRUE(idx->key_columns()[0]->is_descending());
EXPECT_TRUE(idx->key_columns()[1]->is_descending());
EXPECT_TRUE(idx->key_columns()[0]->is_nulls_last());
EXPECT_TRUE(idx->key_columns()[1]->is_nulls_last());
}
TEST_P(SchemaUpdaterTest, CreateIndex_AscKeys) {
std::unique_ptr<const Schema> schema;
if (GetParam() == POSTGRESQL) {
// Custom DDL statements are required because the original Spanner DDL would
// generate an ASC ordering by default. After the translation from Spanner
// to PG, the ordering of the PG DDL is also ASC instead of ASC_NULLS_LAST.
// If the ordering is not specified, the default ordering should be
// ASC_NULLS_LAST in PG.
ZETASQL_ASSERT_OK_AND_ASSIGN(schema,
CreateSchema({R"sql(