support default values in create table for pg syntax KIKIMR-20022

3 files changed, 248 insertions, 4 deletions

diff --git a/ydb/core/kqp/provider/yql_kikimr_type_ann.cpp b/ydb/core/kqp/provider/yql_kikimr_type_ann.cpp
index d71a8a9602..55a44efa37 100644
--- a/ydb/core/kqp/provider/yql_kikimr_type_ann.cpp
+++ b/ydb/core/kqp/provider/yql_kikimr_type_ann.cpp
@@ -747,18 +747,35 @@ virtual TStatus HandleCreateTable(TKiCreateTable create, TExprContext& ctx) over
                             return TStatus::Error;
                         }
 
-                        if (defaultType->HasOptionalOrNull() && columnMeta.NotNull) {
+                        if (defaultType->HasNull() && columnMeta.NotNull) {
                             ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()), TStringBuilder() << "Default expr " << columnName
                                 << " is nullable or optional, but column has not null constraint. "));
                             return TStatus::Error;
                         }
 
-                        if (!IsSameAnnotation(*defaultType, *actualType)) {
+                        if (defaultType->GetKind() != actualType->GetKind()) {
                             ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()), TStringBuilder() << "Default expr " << columnName
-                                << " type mismatch, expected: " << (*type) << ", actual: " << *(actualType)));
+                                << " type mismatch, expected: " << (*actualType) << ", actual: " << *(defaultType)));
+                            
                             return TStatus::Error;
                         }
 
+                        bool skipAnnotationValidation = false;
+                        if (defaultType->GetKind() == ETypeAnnotationKind::Pg) {
+                            auto defaultPgType = defaultType->Cast<TPgExprType>();
+                            if (defaultPgType->GetName() == "unknown") {
+                                skipAnnotationValidation = true;
+                            }
+                        }
+
+                        if (!skipAnnotationValidation) {
+                            if (!IsSameAnnotation(*defaultType, *actualType)) {
+                                ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()), TStringBuilder() << "Default expr " << columnName
+                                    << " type mismatch, expected: " << (*actualType) << ", actual: " << *(defaultType)));
+                                return TStatus::Error;
+                            }
+                        }
+
                         if (columnMeta.IsDefaultKindDefined()) {
                             ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()), TStringBuilder() << "Default setting for " << columnName
                                 << " column is already set: "
@@ -767,7 +784,40 @@ virtual TStatus HandleCreateTable(TKiCreateTable create, TExprContext& ctx) over
                         }
 
                         columnMeta.SetDefaultFromLiteral();
-                        FillLiteralProto(constraint.Value().Cast<TCoDataCtor>(), columnMeta.DefaultFromLiteral);
+
+                        if (auto pgConst = constraint.Value().Maybe<TCoPgConst>()) {
+                            auto actualPgType = actualType->Cast<TPgExprType>();
+                            YQL_ENSURE(actualPgType);
+
+                            auto* typeDesc = NKikimr::NPg::TypeDescFromPgTypeId(actualPgType->GetId());
+                            if (!typeDesc) {
+                                ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()),
+                                    TStringBuilder() << "Failed to parse default expr typename " << actualPgType->GetName()));
+                                return TStatus::Error;
+                            }
+
+                            TString content = TString(pgConst.Cast().Value().Value());
+                            auto parseResult = NKikimr::NPg::PgNativeBinaryFromNativeText(content, typeDesc);
+                            if (parseResult.Error) {
+                                ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()),
+                                    TStringBuilder() << "Failed to parse default expr for typename " << actualPgType->GetName()
+                                    << ", error reason: " << *parseResult.Error));
+                                return TStatus::Error;
+                            }
+
+                            columnMeta.DefaultFromLiteral.mutable_value()->set_bytes_value(parseResult.Str);
+                            auto* pg = columnMeta.DefaultFromLiteral.mutable_type()->mutable_pg_type();
+                            
+                            pg->set_type_name(NKikimr::NPg::PgTypeNameFromTypeDesc(typeDesc));
+                            pg->set_oid(NKikimr::NPg::PgTypeIdFromTypeDesc(typeDesc));
+                        } else if (auto literal = constraint.Value().Maybe<TCoDataCtor>()) {
+                            FillLiteralProto(constraint.Value().Cast<TCoDataCtor>(), columnMeta.DefaultFromLiteral);
+                        } else {
+                            ctx.AddError(TIssue(ctx.GetPosition(constraint.Pos()),
+                                TStringBuilder() << "Unsupported type of default value " << constraint.Value().Cast().Ptr()->Content()));
+                            return TStatus::Error;
+                        }
+                        
                     } else if (constraint.Name().Value() == "serial") {
 
                         if (columnMeta.IsDefaultKindDefined()) {
diff --git a/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp b/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp
index 33d6740bc3..2270cd8008 100644
--- a/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp
+++ b/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp
@@ -2808,6 +2808,183 @@ Y_UNIT_TEST_SUITE(KqpPg) {
         }
     }
 
+    Y_UNIT_TEST(InsertValuesFromTableWithDefault) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnablePreparedDdl(true);
+        auto setting = NKikimrKqp::TKqpSetting();
+        auto serverSettings = TKikimrSettings()
+            .SetAppConfig(appConfig)
+            .SetKqpSettings({setting});
+        TKikimrRunner kikimr(serverSettings.SetWithSampleTables(false));
+        auto db = kikimr.GetQueryClient();
+        auto settings = NYdb::NQuery::TExecuteQuerySettings().Syntax(NYdb::NQuery::ESyntax::Pg);
+        {
+            auto result = db.ExecuteQuery(R"(
+                CREATE TABLE t (a INT, b int DEFAULT 5, PRIMARY KEY(a));
+            )", NYdb::NQuery::TTxControl::NoTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                INSERT INTO t VALUES(1);
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                SELECT * FROM t;
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+
+            UNIT_ASSERT_C(!result.GetResultSets().empty(), "results are empty");
+            CompareYson(R"(
+                [["1";"5"]]
+            )", FormatResultSetYson(result.GetResultSet(0)));
+        }
+    }
+
+    Y_UNIT_TEST(InsertValuesFromTableWithDefaultBool) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnablePreparedDdl(true);
+        auto setting = NKikimrKqp::TKqpSetting();
+        auto serverSettings = TKikimrSettings()
+            .SetAppConfig(appConfig)
+            .SetKqpSettings({setting});
+        TKikimrRunner kikimr(serverSettings.SetWithSampleTables(false));
+        auto db = kikimr.GetQueryClient();
+        auto settings = NYdb::NQuery::TExecuteQuerySettings().Syntax(NYdb::NQuery::ESyntax::Pg);
+        {
+            auto result = db.ExecuteQuery(R"(
+                CREATE TABLE t (a INT, b bool DEFAULT false, PRIMARY KEY(a));
+            )", NYdb::NQuery::TTxControl::NoTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                INSERT INTO t VALUES(1);
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                SELECT * FROM t;
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+
+            UNIT_ASSERT_C(!result.GetResultSets().empty(), "results are empty");
+            CompareYson(R"(
+                [["1";"f"]]
+            )", FormatResultSetYson(result.GetResultSet(0)));
+        }
+    }
+
+    Y_UNIT_TEST(InsertValuesFromTableWithDefaultText) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnablePreparedDdl(true);
+        auto setting = NKikimrKqp::TKqpSetting();
+        auto serverSettings = TKikimrSettings()
+            .SetAppConfig(appConfig)
+            .SetKqpSettings({setting});
+        TKikimrRunner kikimr(serverSettings.SetWithSampleTables(false));
+        auto db = kikimr.GetQueryClient();
+        auto settings = NYdb::NQuery::TExecuteQuerySettings().Syntax(NYdb::NQuery::ESyntax::Pg);
+        {
+            auto result = db.ExecuteQuery(R"(
+                CREATE TABLE t (a INT, b text DEFAULT 'empty', PRIMARY KEY(a));
+            )", NYdb::NQuery::TTxControl::NoTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                INSERT INTO t VALUES(1);
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                SELECT * FROM t;
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+
+            UNIT_ASSERT_C(!result.GetResultSets().empty(), "results are empty");
+            CompareYson(R"(
+                [["1";"empty"]]
+            )", FormatResultSetYson(result.GetResultSet(0)));
+        }
+    }
+
+    Y_UNIT_TEST(InsertValuesFromTableWithDefaultTextNotNull) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnablePreparedDdl(true);
+        auto setting = NKikimrKqp::TKqpSetting();
+        auto serverSettings = TKikimrSettings()
+            .SetAppConfig(appConfig)
+            .SetKqpSettings({setting});
+        TKikimrRunner kikimr(serverSettings.SetWithSampleTables(false));
+        auto db = kikimr.GetQueryClient();
+        auto settings = NYdb::NQuery::TExecuteQuerySettings().Syntax(NYdb::NQuery::ESyntax::Pg);
+        {
+            auto result = db.ExecuteQuery(R"(
+                CREATE TABLE t (a INT, b text NOT NULL DEFAULT 'empty', PRIMARY KEY(a));
+            )", NYdb::NQuery::TTxControl::NoTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                INSERT INTO t VALUES(1);
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+        }
+        {
+            auto result = db.ExecuteQuery(R"(
+                SELECT * FROM t;
+            )", NYdb::NQuery::TTxControl::BeginTx().CommitTx(), settings).ExtractValueSync();
+            UNIT_ASSERT_VALUES_EQUAL_C(result.GetStatus(), EStatus::SUCCESS, result.GetIssues().ToString());
+
+            UNIT_ASSERT_C(!result.GetResultSets().empty(), "results are empty");
+            CompareYson(R"(
+                [["1";"empty"]]
+            )", FormatResultSetYson(result.GetResultSet(0)));
+        }
+    }
+
+    Y_UNIT_TEST(InsertValuesFromTableWithDefaultTextNotNullButNull) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnablePreparedDdl(true);
+        auto setting = NKikimrKqp::TKqpSetting();
+        auto serverSettings = TKikimrSettings()
+            .SetAppConfig(appConfig)
+            .SetKqpSettings({setting});
+        TKikimrRunner kikimr(serverSettings.SetWithSampleTables(false));
+        auto db = kikimr.GetQueryClient();
+        auto settings = NYdb::NQuery::TExecuteQuerySettings().Syntax(NYdb::NQuery::ESyntax::Pg);
+        {
+            auto result = db.ExecuteQuery(R"(
+                CREATE TABLE t (a INT, b text NOT NULL DEFAULT NULL, PRIMARY KEY(a));
+            )", NYdb::NQuery::TTxControl::NoTx(), settings).ExtractValueSync();
+            Cerr << result.GetIssues().ToString() << Endl;
+            UNIT_ASSERT(!result.IsSuccess());
+        }
+    }
+
+    Y_UNIT_TEST(InsertValuesFromTableWithDefaultNegativeCase) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnablePreparedDdl(true);
+        auto setting = NKikimrKqp::TKqpSetting();
+        auto serverSettings = TKikimrSettings()
+            .SetAppConfig(appConfig)
+            .SetKqpSettings({setting});
+        TKikimrRunner kikimr(serverSettings.SetWithSampleTables(false));
+        auto db = kikimr.GetQueryClient();
+        auto settings = NYdb::NQuery::TExecuteQuerySettings().Syntax(NYdb::NQuery::ESyntax::Pg);
+        {
+            auto result = db.ExecuteQuery(R"(
+                CREATE TABLE t (a INT, b int DEFAULT 'text', PRIMARY KEY(a));
+            )", NYdb::NQuery::TTxControl::NoTx(), settings).ExtractValueSync();
+            Cerr << result.GetIssues().ToString() << Endl;
+            UNIT_ASSERT_C(!result.IsSuccess(), result.GetIssues().ToString());
+        }
+    }
 
     Y_UNIT_TEST(InsertNoTargetColumns_SerialNotNull) {
         NKikimrConfig::TAppConfig appConfig;
diff --git a/ydb/library/yql/core/expr_nodes/yql_expr_nodes.json b/ydb/library/yql/core/expr_nodes/yql_expr_nodes.json
index 42daf56f28..c425c25d7f 100644
--- a/ydb/library/yql/core/expr_nodes/yql_expr_nodes.json
+++ b/ydb/library/yql/core/expr_nodes/yql_expr_nodes.json
@@ -515,6 +515,23 @@
             ]
         },
         {
+            "Name": "TCoPgType",
+            "Base": "TCallable",
+            "Match": {"Type": "Callable", "Name": "PgType"},
+            "Children": [
+                {"Index": 0, "Name": "TypeName", "Type": "TCoAtom"}
+            ]
+        },
+        {
+            "Name": "TCoPgConst",
+            "Base": "TCallable",
+            "Match": {"Type": "Callable", "Name": "PgConst"},
+            "Children": [
+                {"Index": 0, "Name": "Value", "Type": "TCoAtom"},
+                {"Index": 1, "Name": "Type", "Type": "TCoPgType"}
+            ]
+        },
+        {
             "Name": "TCoExtendBase",
             "VarArgBase": "TExprBase",
             "Match": {"Type": "CallableBase"}