Add new option CACHE_MODE in FAMILY settings

RFC: https://nda.ya.ru/t/vPsncOoC7HttdF
GitHub Issue: https://github.com/ydb-platform/ydb/issues/18695
commit_hash:96445a9aef7b1fede372b9e4c60b30eb9ef5fdc1

5 files changed, 115 insertions, 3 deletions

diff --git a/yql/essentials/sql/v1/node.h b/yql/essentials/sql/v1/node.h
index ba9606f0671..4399847da89 100644
--- a/yql/essentials/sql/v1/node.h
+++ b/yql/essentials/sql/v1/node.h
@@ -1183,6 +1183,7 @@ namespace NSQLTranslationV1 {
         TNodePtr Data;
         TNodePtr Compression;
         TNodePtr CompressionLevel;
+        TNodePtr CacheMode;
     };
 
     struct TVectorIndexSettings {
diff --git a/yql/essentials/sql/v1/query.cpp b/yql/essentials/sql/v1/query.cpp
index e0da16ff05d..af78efc0ab8 100644
--- a/yql/essentials/sql/v1/query.cpp
+++ b/yql/essentials/sql/v1/query.cpp
@@ -1141,6 +1141,9 @@ public:
                 if (family.CompressionLevel) {
                     familyDesc = L(familyDesc, Q(Y(Q("compression_level"), family.CompressionLevel)));
                 }
+                if (family.CacheMode) {
+                    familyDesc = L(familyDesc, Q(Y(Q("cache_mode"), family.CacheMode)));
+                }
                 columnFamilies = L(columnFamilies, Q(familyDesc));
             }
             opts = L(opts, Q(Y(Q("columnFamilies"), Q(columnFamilies))));
@@ -1545,6 +1548,9 @@ public:
                 if (family.CompressionLevel) {
                     familyDesc = L(familyDesc, Q(Y(Q("compression_level"), family.CompressionLevel)));
                 }
+                if (family.CacheMode) {
+                    familyDesc = L(familyDesc, Q(Y(Q("cache_mode"), family.CacheMode)));
+                }
                 columnFamilies = L(columnFamilies, Q(familyDesc));
             }
             actions = L(actions, Q(Y(Q("addColumnFamilies"), Q(columnFamilies))));
@@ -1564,6 +1570,9 @@ public:
                 if (family.CompressionLevel) {
                     familyDesc = L(familyDesc, Q(Y(Q("compression_level"), family.CompressionLevel)));
                 }
+                if (family.CacheMode) {
+                    familyDesc = L(familyDesc, Q(Y(Q("cache_mode"), family.CacheMode)));
+                }
                 columnFamilies = L(columnFamilies, Q(familyDesc));
             }
             actions = L(actions, Q(Y(Q("alterColumnFamilies"), Q(columnFamilies))));
diff --git a/yql/essentials/sql/v1/sql_query.cpp b/yql/essentials/sql/v1/sql_query.cpp
index 1b355803e32..d48fb6ff023 100644
--- a/yql/essentials/sql/v1/sql_query.cpp
+++ b/yql/essentials/sql/v1/sql_query.cpp
@@ -2360,7 +2360,7 @@ bool TSqlQuery::AlterTableAlterFamily(const TRule_alter_table_alter_column_famil
     const TRule_family_setting_value& value = node.GetRule_family_setting_value6();
     if (to_lower(settingName.Name) == "data") {
         if (entry->Data) {
-            Ctx_.Error() << "Redefinition of 'data' setting for column family '" << name.Name
+            Ctx_.Error() << "Redefinition of " << to_upper(settingName.Name) << " setting for column family '" << name.Name
                 << "' in one alter";
             return false;
         }
@@ -2370,7 +2370,7 @@ bool TSqlQuery::AlterTableAlterFamily(const TRule_alter_table_alter_column_famil
         }
     } else if (to_lower(settingName.Name) == "compression") {
         if (entry->Compression) {
-            Ctx_.Error() << "Redefinition of 'compression' setting for column family '" << name.Name
+            Ctx_.Error() << "Redefinition of " << to_upper(settingName.Name) << " setting for column family '" << name.Name
                 << "' in one alter";
             return false;
         }
@@ -2380,13 +2380,23 @@ bool TSqlQuery::AlterTableAlterFamily(const TRule_alter_table_alter_column_famil
         }
     } else if (to_lower(settingName.Name) == "compression_level") {
         if (entry->CompressionLevel) {
-            Ctx_.Error() << "Redefinition of 'compression_level' setting for column family '" << name.Name << "' in one alter";
+            Ctx_.Error() << "Redefinition of " << to_upper(settingName.Name) << " setting for column family '" << name.Name << "' in one alter";
             return false;
         }
         if (!StoreInt(value, entry->CompressionLevel, Ctx_)) {
             Ctx_.Error() << to_upper(settingName.Name) << " value should be an integer";
             return false;
         }
+    } else if (to_lower(settingName.Name) == "cache_mode") {
+        if (entry->CacheMode) {
+            Ctx_.Error() << "Redefinition of " << to_upper(settingName.Name) << " setting for column family '" << name.Name
+                << "' in one alter";
+            return false;
+        }
+        if (!StoreString(value, entry->CacheMode, Ctx_)) {
+            Ctx_.Error() << to_upper(settingName.Name) << " value should be a string literal";
+            return false;
+        }
     } else {
         Ctx_.Error() << "Unknown table setting: " << settingName.Name;
         return false;
diff --git a/yql/essentials/sql/v1/sql_translation.cpp b/yql/essentials/sql/v1/sql_translation.cpp
index 52b1dc75dd4..4814f336eff 100644
--- a/yql/essentials/sql/v1/sql_translation.cpp
+++ b/yql/essentials/sql/v1/sql_translation.cpp
@@ -1691,6 +1691,11 @@ bool TSqlTranslation::FillFamilySettingsEntry(const TRule_family_settings_entry&
             Ctx_.Error() << to_upper(id.Name) << " value should be an integer";
             return false;
         }
+    } else if (to_lower(id.Name) == "cache_mode") {
+        if (!StoreString(value, family.CacheMode, Ctx_)) {
+            Ctx_.Error() << to_upper(id.Name) << " value should be a string literal";
+            return false;
+        }
     } else {
         Ctx_.Error() << "Unknown table setting: " << id.Name;
         return false;
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index 46d01395bec..8877f64735a 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -8812,6 +8812,54 @@ Y_UNIT_TEST_SUITE(ColumnFamily) {
         UNIT_ASSERT_STRING_CONTAINS(res.Issues.ToString(), "COMPRESSION_LEVEL value should be an integer");
     }
 
+    Y_UNIT_TEST(FieldCacheModeCorrectUsage) {
+        NYql::TAstParseResult res = SqlToYql(R"sql( use plato;
+            CREATE TABLE tableName (
+                Key Uint32 FAMILY default,
+                Value String FAMILY family1,
+                PRIMARY KEY (Key),
+                FAMILY default (
+                     DATA = "test",
+                     CACHE_MODE = "regular"
+                ),
+                FAMILY family1 (
+                     DATA = "test",
+                     CACHE_MODE = "in_memory"
+                )
+            );
+        )sql");
+        UNIT_ASSERT(res.IsOk());
+        UNIT_ASSERT(res.Issues.Size() == 0);
+        TVerifyLineFunc verifyLine = [](const TString& word, const TString& line) {
+            if (word == "Write") {
+                UNIT_ASSERT_VALUES_UNEQUAL(TString::npos, line.find("cache_mode"));
+                UNIT_ASSERT_VALUES_UNEQUAL(TString::npos, line.find("regular"));
+                UNIT_ASSERT_VALUES_UNEQUAL(TString::npos, line.find("in_memory"));
+            }
+        };
+
+        TWordCountHive elementStat = { { TString("Write"), 0 }, { TString("cache_mode"), 0 } };
+        VerifyProgram(res, elementStat, verifyLine);
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Write"]);
+        UNIT_ASSERT_VALUES_EQUAL(2, elementStat["cache_mode"]);
+    }
+
+    Y_UNIT_TEST(FieldCacheModeIsNotString) {
+        NYql::TAstParseResult res = SqlToYql(R"sql( use plato;
+            CREATE TABLE tableName (
+                Key Uint32 FAMILY default,
+                PRIMARY KEY (Key),
+                FAMILY default (
+                     DATA = "test",
+                     CACHE_MODE = 42
+                )
+            );
+        )sql");
+        UNIT_ASSERT(!res.IsOk());
+        UNIT_ASSERT(res.Issues.Size() == 1);
+        UNIT_ASSERT_STRING_CONTAINS(res.Issues.ToString(), "CACHE_MODE value should be a string literal");
+    }
+
     Y_UNIT_TEST(AlterCompressionCorrectUsage) {
         NYql::TAstParseResult res = SqlToYql(R"( use plato;
             ALTER TABLE tableName ALTER FAMILY default SET COMPRESSION "lz4";
@@ -8849,6 +8897,45 @@ Y_UNIT_TEST_SUITE(ColumnFamily) {
         UNIT_ASSERT(res.Issues.Size() == 1);
         UNIT_ASSERT_STRING_CONTAINS(res.Issues.ToString(), "COMPRESSION_LEVEL value should be an integer");
     }
+
+    Y_UNIT_TEST(AlterCompressionLevelFieldRedefinition) {
+        NYql::TAstParseResult res = SqlToYql(R"sql( use plato;
+            ALTER TABLE tableName
+                ALTER FAMILY default SET COMPRESSION_LEVEL 3,
+                ALTER FAMILY default SET COMPRESSION_LEVEL 5;
+        )sql");
+        UNIT_ASSERT(!res.IsOk());
+        UNIT_ASSERT(res.Issues.Size() == 1);
+        UNIT_ASSERT_STRING_CONTAINS(res.Issues.ToString(), "Redefinition of COMPRESSION_LEVEL setting");
+    }
+
+    Y_UNIT_TEST(AlterCacheModeCorrectUsage) {
+        NYql::TAstParseResult res = SqlToYql(R"sql( use plato;
+            ALTER TABLE tableName ALTER FAMILY default SET CACHE_MODE "in_memory";
+        )sql");
+        UNIT_ASSERT(res.IsOk());
+        UNIT_ASSERT(res.Issues.Size() == 0);
+    }
+
+    Y_UNIT_TEST(AlterCacheModeFieldIsNotInteger) {
+        NYql::TAstParseResult res = SqlToYql(R"sql( use plato;
+            ALTER TABLE tableName ALTER FAMILY default SET CACHE_MODE 42;
+        )sql");
+        UNIT_ASSERT(!res.IsOk());
+        UNIT_ASSERT(res.Issues.Size() == 1);
+        UNIT_ASSERT_STRING_CONTAINS(res.Issues.ToString(), "CACHE_MODE value should be a string literal");
+    }
+
+    Y_UNIT_TEST(AlterCacheModeFieldRedefinition) {
+        NYql::TAstParseResult res = SqlToYql(R"sql( use plato;
+            ALTER TABLE tableName
+                ALTER FAMILY default SET CACHE_MODE "in_memory",
+                ALTER FAMILY default SET CACHE_MODE "regular";
+        )sql");
+        UNIT_ASSERT(!res.IsOk());
+        UNIT_ASSERT(res.Issues.Size() == 1);
+        UNIT_ASSERT_STRING_CONTAINS(res.Issues.ToString(), "Redefinition of CACHE_MODE setting");
+    }
 }
 
 Y_UNIT_TEST_SUITE(Transfer) {