Merge PR #10831, #11068, #11075, #11152

#11152 - Allow to choose normal or aggreation PG function

#11075 - Handle invalid base

#11068 - Allow more postgis functions

#10831 - Views: if exists / if not exists for DDL
commit_hash:0ebf35e45ac6de147c9000440ca25237db061d2e

7 files changed, 180 insertions, 53 deletions

diff --git a/yql/essentials/sql/v1/SQLv1.g.in b/yql/essentials/sql/v1/SQLv1.g.in
index 653fd7a4b0..9369b111a9 100644
--- a/yql/essentials/sql/v1/SQLv1.g.in
+++ b/yql/essentials/sql/v1/SQLv1.g.in
@@ -601,12 +601,12 @@ alter_external_data_source_action:
 
 drop_external_data_source_stmt: DROP EXTERNAL DATA SOURCE (IF EXISTS)? object_ref;
 
-create_view_stmt: CREATE VIEW object_ref
+create_view_stmt: CREATE VIEW (IF NOT EXISTS)? object_ref
     create_object_features?
     AS select_stmt
 ;
 
-drop_view_stmt: DROP VIEW object_ref;
+drop_view_stmt: DROP VIEW (IF EXISTS)? object_ref;
 
 upsert_object_stmt: UPSERT OBJECT object_ref
     LPAREN TYPE object_type_ref RPAREN
diff --git a/yql/essentials/sql/v1/SQLv1Antlr4.g.in b/yql/essentials/sql/v1/SQLv1Antlr4.g.in
index 875774323a..229b15dfae 100644
--- a/yql/essentials/sql/v1/SQLv1Antlr4.g.in
+++ b/yql/essentials/sql/v1/SQLv1Antlr4.g.in
@@ -600,12 +600,12 @@ alter_external_data_source_action:
 
 drop_external_data_source_stmt: DROP EXTERNAL DATA SOURCE (IF EXISTS)? object_ref;
 
-create_view_stmt: CREATE VIEW object_ref
+create_view_stmt: CREATE VIEW (IF NOT EXISTS)? object_ref
     create_object_features?
     AS select_stmt
 ;
 
-drop_view_stmt: DROP VIEW object_ref;
+drop_view_stmt: DROP VIEW (IF EXISTS)? object_ref;
 
 upsert_object_stmt: UPSERT OBJECT object_ref
     LPAREN TYPE object_type_ref RPAREN
diff --git a/yql/essentials/sql/v1/builtin.cpp b/yql/essentials/sql/v1/builtin.cpp
index e066cd846d..e327c2d2ea 100644
--- a/yql/essentials/sql/v1/builtin.cpp
+++ b/yql/essentials/sql/v1/builtin.cpp
@@ -3421,8 +3421,31 @@ TNodePtr BuildBuiltinFunc(TContext& ctx, TPosition pos, TString name, const TVec
         }
     } else if (ns == "datetime2" && (name == "Parse")) {
         return BuildUdf(ctx, pos, nameSpace, name, args);
-    } else if (ns == "pg") {
-        const bool isAggregateFunc = NYql::NPg::HasAggregation(name, NYql::NPg::EAggKind::Normal);
+    } else if (ns == "pg" || ns == "pgagg" || ns == "pgproc") {
+        bool isAggregateFunc = NYql::NPg::HasAggregation(name, NYql::NPg::EAggKind::Normal);
+        bool isNormalFunc = NYql::NPg::HasProc(name, NYql::NPg::EProcKind::Function);
+        if (!isAggregateFunc && !isNormalFunc) {
+            return new TInvalidBuiltin(pos, TStringBuilder() << "Unknown function: " << name);
+        }
+
+        if (isAggregateFunc && isNormalFunc) {
+            if (ns == "pg") {
+                return new TInvalidBuiltin(pos, TStringBuilder() << "Ambigious function: " << name << ", use either PgAgg:: or PgProc:: namespace");
+            } else if (ns == "pgagg") {
+                isNormalFunc = false;
+            } else {
+                isAggregateFunc = false;
+            }
+        }
+
+        if (isAggregateFunc && ns == "pgproc") {
+            return new TInvalidBuiltin(pos, TStringBuilder() << "Invalid namespace for aggregation function: " << name << ", use either Pg:: or PgAgg:: namespace");
+        }
+
+        if (isNormalFunc && ns == "pgagg") {
+            return new TInvalidBuiltin(pos, TStringBuilder() << "Invalid namespace for normal function: " << name << ", use either Pg:: or PgProc:: namespace");
+        }
+
         if (isAggregateFunc) {
             if (aggMode == EAggregateMode::Distinct) {
                 return new TInvalidBuiltin(pos, "Distinct is not supported yet for PG aggregation ");
@@ -3430,6 +3453,7 @@ TNodePtr BuildBuiltinFunc(TContext& ctx, TPosition pos, TString name, const TVec
 
             return BuildAggrFuncFactoryCallback(name, "", EAggrFuncTypeCallback::PG)(pos, args, aggMode, false);
         } else {
+            YQL_ENSURE(isNormalFunc);
             TVector<TNodePtr> pgCallArgs;
             pgCallArgs.push_back(BuildLiteralRawString(pos, name));
             pgCallArgs.insert(pgCallArgs.end(), args.begin(), args.end());
diff --git a/yql/essentials/sql/v1/format/sql_format_ut.h b/yql/essentials/sql/v1/format/sql_format_ut.h
index 951bf42798..6d92bd29eb 100644
--- a/yql/essentials/sql/v1/format/sql_format_ut.h
+++ b/yql/essentials/sql/v1/format/sql_format_ut.h
@@ -1538,9 +1538,16 @@ Y_UNIT_TEST(ObfuscatePragma) {
 }
 
 Y_UNIT_TEST(CreateView) {
-    TCases cases = {
-        {"creAte vIEw TheView wiTh (security_invoker = trUE) As SELect 1",
-            "CREATE VIEW TheView WITH (security_invoker = TRUE) AS\nSELECT\n\t1;\n"},
+    TCases cases = {{
+            "creAte vIEw TheView As SELect 1",
+            "CREATE VIEW TheView AS\nSELECT\n\t1;\n"
+        }, {
+            "creAte vIEw If Not ExIsTs TheView As SELect 1",
+            "CREATE VIEW IF NOT EXISTS TheView AS\nSELECT\n\t1;\n"
+        }, {
+            "creAte vIEw TheView wiTh (option = tRuE) As SELect 1",
+            "CREATE VIEW TheView WITH (option = TRUE) AS\nSELECT\n\t1;\n"
+        }
     };
 
     TSetup setup;
@@ -1548,9 +1555,13 @@ Y_UNIT_TEST(CreateView) {
 }
 
 Y_UNIT_TEST(DropView) {
-    TCases cases = {
-        {"dRop viEW theVIEW",
-            "DROP VIEW theVIEW;\n"},
+    TCases cases = {{
+            "dRop viEW theVIEW",
+            "DROP VIEW theVIEW;\n"
+        }, {
+            "dRop viEW iF EXistS theVIEW",
+            "DROP VIEW IF EXISTS theVIEW;\n"
+        }
     };
 
     TSetup setup;
diff --git a/yql/essentials/sql/v1/sql_query.cpp b/yql/essentials/sql/v1/sql_query.cpp
index d0fb8737e1..f3755bf6f0 100644
--- a/yql/essentials/sql/v1/sql_query.cpp
+++ b/yql/essentials/sql/v1/sql_query.cpp
@@ -1228,11 +1228,11 @@ bool TSqlQuery::Statement(TVector<TNodePtr>& blocks, const TRule_sql_stmt_core&
             break;
         }
         case TRule_sql_stmt_core::kAltSqlStmtCore42: {
-            // create_view_stmt: CREATE VIEW name WITH (k = v, ...) AS select_stmt;
+            // create_view_stmt: CREATE VIEW (IF NOT EXISTS)? name (WITH (k = v, ...))? AS select_stmt;
             auto& node = core.GetAlt_sql_stmt_core42().GetRule_create_view_stmt1();
             TObjectOperatorContext context(Ctx.Scoped);
-            if (node.GetRule_object_ref3().HasBlock1()) {
-                if (!ClusterExpr(node.GetRule_object_ref3().GetBlock1().GetRule_cluster_expr1(),
+            if (node.GetRule_object_ref4().HasBlock1()) {
+                if (!ClusterExpr(node.GetRule_object_ref4().GetBlock1().GetRule_cluster_expr1(),
                                  false,
                                  context.ServiceId,
                                  context.Cluster)) {
@@ -1240,34 +1240,36 @@ bool TSqlQuery::Statement(TVector<TNodePtr>& blocks, const TRule_sql_stmt_core&
                 }
             }
 
+            const bool existingOk = node.HasBlock3();
+
             std::map<TString, TDeferredAtom> features;
-            if (node.HasBlock4()) {
-                if (!ParseObjectFeatures(features, node.GetBlock4().GetRule_create_object_features1().GetRule_object_features2())) {
+            if (node.HasBlock5()) {
+                if (!ParseObjectFeatures(features, node.GetBlock5().GetRule_create_object_features1().GetRule_object_features2())) {
                     return false;
                 }
             }
-            if (!ParseViewQuery(features, node.GetRule_select_stmt6())) {
+            if (!ParseViewQuery(features, node.GetRule_select_stmt7())) {
                 return false;
             }
 
-            const TString objectId = Id(node.GetRule_object_ref3().GetRule_id_or_at2(), *this).second;
+            const TString objectId = Id(node.GetRule_object_ref4().GetRule_id_or_at2(), *this).second;
             constexpr const char* TypeId = "VIEW";
             AddStatementToBlocks(blocks,
                                  BuildCreateObjectOperation(Ctx.Pos(),
                                                             BuildTablePath(Ctx.GetPrefixPath(context.ServiceId, context.Cluster), objectId),
                                                             TypeId,
-                                                            false,
+                                                            existingOk,
                                                             false,
                                                             std::move(features),
                                                             context));
             break;
         }
         case TRule_sql_stmt_core::kAltSqlStmtCore43: {
-            // drop_view_stmt: DROP VIEW name;
+            // drop_view_stmt: DROP VIEW (IF EXISTS)? name;
             auto& node = core.GetAlt_sql_stmt_core43().GetRule_drop_view_stmt1();
             TObjectOperatorContext context(Ctx.Scoped);
-            if (node.GetRule_object_ref3().HasBlock1()) {
-                if (!ClusterExpr(node.GetRule_object_ref3().GetBlock1().GetRule_cluster_expr1(),
+            if (node.GetRule_object_ref4().HasBlock1()) {
+                if (!ClusterExpr(node.GetRule_object_ref4().GetBlock1().GetRule_cluster_expr1(),
                                  false,
                                  context.ServiceId,
                                  context.Cluster)) {
@@ -1275,13 +1277,15 @@ bool TSqlQuery::Statement(TVector<TNodePtr>& blocks, const TRule_sql_stmt_core&
                 }
             }
 
-            const TString objectId = Id(node.GetRule_object_ref3().GetRule_id_or_at2(), *this).second;
+            const bool missingOk = node.HasBlock3();
+
+            const TString objectId = Id(node.GetRule_object_ref4().GetRule_id_or_at2(), *this).second;
             constexpr const char* TypeId = "VIEW";
             AddStatementToBlocks(blocks,
                                  BuildDropObjectOperation(Ctx.Pos(),
                                                           BuildTablePath(Ctx.GetPrefixPath(context.ServiceId, context.Cluster), objectId),
                                                           TypeId,
-                                                          false,
+                                                          missingOk,
                                                           {},
                                                           context));
             break;
diff --git a/yql/essentials/sql/v1/sql_ut.cpp b/yql/essentials/sql/v1/sql_ut.cpp
index 6663fe9765..a4251890a7 100644
--- a/yql/essentials/sql/v1/sql_ut.cpp
+++ b/yql/essentials/sql/v1/sql_ut.cpp
@@ -2533,8 +2533,8 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         const auto result = SqlToYql(R"(USE plato;
             CREATE TABLE table (
                 pk INT32 NOT NULL,
-                col String, 
-                INDEX idx GLOBAL USING vector_kmeans_tree 
+                col String,
+                INDEX idx GLOBAL USING vector_kmeans_tree
                     ON (col) COVER (col)
                     WITH (distance=cosine, vector_type=float, vector_dimension=1024,),
                 PRIMARY KEY (pk))
@@ -2543,11 +2543,11 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     }
 
     Y_UNIT_TEST(AlterTableAddIndexVector) {
-        const auto result = SqlToYql(R"(USE plato; 
-            ALTER TABLE table ADD INDEX idx 
-                GLOBAL USING vector_kmeans_tree 
+        const auto result = SqlToYql(R"(USE plato;
+            ALTER TABLE table ADD INDEX idx
+                GLOBAL USING vector_kmeans_tree
                 ON (col) COVER (col)
-                WITH (distance=cosine, vector_type="float", vector_dimension=1024) 
+                WITH (distance=cosine, vector_type="float", vector_dimension=1024)
                 )");
         UNIT_ASSERT_C(result.IsOk(), result.Issues.ToString());
     }
@@ -2558,11 +2558,11 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     }
 
     Y_UNIT_TEST(AlterTableAddIndexMissedParameter) {
-        ExpectFailWithError(R"(USE plato; 
-            ALTER TABLE table ADD INDEX idx 
-                GLOBAL USING vector_kmeans_tree 
+        ExpectFailWithError(R"(USE plato;
+            ALTER TABLE table ADD INDEX idx
+                GLOBAL USING vector_kmeans_tree
                 ON (col)
-                WITH (distance=cosine, vector_type=float) 
+                WITH (distance=cosine, vector_type=float)
                 )",
             "<main>:5:52: Error: vector_dimension should be set\n");
     }
@@ -2790,7 +2790,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
             auto req = Sprintf(reqTpl, key.c_str(), value.c_str());
             auto res = SqlToYql(req);
             UNIT_ASSERT(res.Root);
-            
+
             TVerifyLineFunc verifyLine = [&key, &value](const TString& word, const TString& line) {
                 if (word == "Write") {
                     UNIT_ASSERT_VALUES_UNEQUAL(TString::npos, line.find("MyReplication"));
@@ -6716,6 +6716,28 @@ Y_UNIT_TEST_SUITE(TViewSyntaxTest) {
         UNIT_ASSERT_C(res.Root, res.Issues.ToString());
     }
 
+    Y_UNIT_TEST(CreateViewIfNotExists) {
+        constexpr const char* name = "TheView";
+        NYql::TAstParseResult res = SqlToYql(std::format(R"(
+                USE plato;
+                CREATE VIEW IF NOT EXISTS {} WITH (security_invoker = TRUE) AS SELECT 1;
+            )", name
+        ));
+        UNIT_ASSERT_C(res.Root, res.Issues.ToString());
+
+        TVerifyLineFunc verifyLine = [&](const TString& word, const TString& line) {
+            if (word == "Write!") {
+                UNIT_ASSERT_STRING_CONTAINS(line, name);
+                UNIT_ASSERT_STRING_CONTAINS(line, "createObjectIfNotExists");
+            }
+        };
+
+        TWordCountHive elementStat = { {"Write!"} };
+        VerifyProgram(res, elementStat, verifyLine);
+
+        UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
+    }
+
     Y_UNIT_TEST(CreateViewFromTable) {
         constexpr const char* path = "/PathPrefix/TheView";
         constexpr const char* query = R"(
@@ -6795,6 +6817,28 @@ Y_UNIT_TEST_SUITE(TViewSyntaxTest) {
         UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
     }
 
+    Y_UNIT_TEST(DropViewIfExists) {
+        constexpr const char* name = "TheView";
+        NYql::TAstParseResult res = SqlToYql(std::format(R"(
+                USE plato;
+                DROP VIEW IF EXISTS {};
+            )", name
+        ));
+        UNIT_ASSERT_C(res.Root, res.Issues.ToString());
+
+        TVerifyLineFunc verifyLine = [&](const TString& word, const TString& line) {
+            if (word == "Write!") {
+                UNIT_ASSERT_STRING_CONTAINS(line, name);
+                UNIT_ASSERT_STRING_CONTAINS(line, "dropObjectIfExists");
+            }
+        };
+
+        TWordCountHive elementStat = { {"Write!"} };
+        VerifyProgram(res, elementStat, verifyLine);
+
+        UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
+    }
+
     Y_UNIT_TEST(CreateViewWithTablePrefix) {
         NYql::TAstParseResult res = SqlToYql(R"(
                 USE plato;
@@ -6838,7 +6882,7 @@ Y_UNIT_TEST_SUITE(TViewSyntaxTest) {
 
         UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
     }
-    
+
     Y_UNIT_TEST(YtAlternativeSchemaSyntax) {
         NYql::TAstParseResult res = SqlToYql(R"(
             SELECT * FROM plato.Input WITH schema(y Int32, x String not null);
@@ -6907,7 +6951,7 @@ Y_UNIT_TEST_SUITE(CompactNamedExprs) {
             pragma CompactNamedExprs;
             pragma ValidateUnusedExprs;
 
-            define subquery $x() as 
+            define subquery $x() as
                 select count(1, 2);
             end define;
             select 1;
@@ -6930,7 +6974,7 @@ Y_UNIT_TEST_SUITE(CompactNamedExprs) {
             pragma CompactNamedExprs;
             pragma DisableValidateUnusedExprs;
 
-            define subquery $x() as 
+            define subquery $x() as
                 select count(1, 2);
             end define;
             select 1;
diff --git a/yql/essentials/sql/v1/sql_ut_antlr4.cpp b/yql/essentials/sql/v1/sql_ut_antlr4.cpp
index b92ea5f460..29a306cddf 100644
--- a/yql/essentials/sql/v1/sql_ut_antlr4.cpp
+++ b/yql/essentials/sql/v1/sql_ut_antlr4.cpp
@@ -2533,8 +2533,8 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         const auto result = SqlToYql(R"(USE plato;
             CREATE TABLE table (
                 pk INT32 NOT NULL,
-                col String, 
-                INDEX idx GLOBAL USING vector_kmeans_tree 
+                col String,
+                INDEX idx GLOBAL USING vector_kmeans_tree
                     ON (col) COVER (col)
                     WITH (distance=cosine, vector_type=float, vector_dimension=1024,),
                 PRIMARY KEY (pk))
@@ -2543,11 +2543,11 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     }
 
     Y_UNIT_TEST(AlterTableAddIndexVector) {
-        const auto result = SqlToYql(R"(USE plato; 
-            ALTER TABLE table ADD INDEX idx 
-                GLOBAL USING vector_kmeans_tree 
+        const auto result = SqlToYql(R"(USE plato;
+            ALTER TABLE table ADD INDEX idx
+                GLOBAL USING vector_kmeans_tree
                 ON (col) COVER (col)
-                WITH (distance=cosine, vector_type="float", vector_dimension=1024) 
+                WITH (distance=cosine, vector_type="float", vector_dimension=1024)
                 )");
         UNIT_ASSERT_C(result.IsOk(), result.Issues.ToString());
     }
@@ -2558,11 +2558,11 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     }
 
     Y_UNIT_TEST(AlterTableAddIndexMissedParameter) {
-        ExpectFailWithError(R"(USE plato; 
-            ALTER TABLE table ADD INDEX idx 
-                GLOBAL USING vector_kmeans_tree 
+        ExpectFailWithError(R"(USE plato;
+            ALTER TABLE table ADD INDEX idx
+                GLOBAL USING vector_kmeans_tree
                 ON (col)
-                WITH (distance=cosine, vector_type=float) 
+                WITH (distance=cosine, vector_type=float)
                 )",
             "<main>:5:52: Error: vector_dimension should be set\n");
     }
@@ -2790,7 +2790,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
             auto req = Sprintf(reqTpl, key.c_str(), value.c_str());
             auto res = SqlToYql(req);
             UNIT_ASSERT(res.Root);
-            
+
             TVerifyLineFunc verifyLine = [&key, &value](const TString& word, const TString& line) {
                 if (word == "Write") {
                     UNIT_ASSERT_VALUES_UNEQUAL(TString::npos, line.find("MyReplication"));
@@ -6713,6 +6713,28 @@ Y_UNIT_TEST_SUITE(TViewSyntaxTest) {
         UNIT_ASSERT_C(res.Root, res.Issues.ToString());
     }
 
+    Y_UNIT_TEST(CreateViewIfNotExists) {
+        constexpr const char* name = "TheView";
+        NYql::TAstParseResult res = SqlToYql(std::format(R"(
+                USE plato;
+                CREATE VIEW IF NOT EXISTS {} AS SELECT 1;
+            )", name
+        ));
+        UNIT_ASSERT_C(res.Root, res.Issues.ToString());
+
+        TVerifyLineFunc verifyLine = [&](const TString& word, const TString& line) {
+            if (word == "Write!") {
+                UNIT_ASSERT_STRING_CONTAINS(line, name);
+                UNIT_ASSERT_STRING_CONTAINS(line, "createObjectIfNotExists");
+            }
+        };
+
+        TWordCountHive elementStat = { {"Write!"} };
+        VerifyProgram(res, elementStat, verifyLine);
+
+        UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
+    }
+
     Y_UNIT_TEST(CreateViewFromTable) {
         constexpr const char* path = "/PathPrefix/TheView";
         constexpr const char* query = R"(
@@ -6792,6 +6814,28 @@ Y_UNIT_TEST_SUITE(TViewSyntaxTest) {
         UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
     }
 
+    Y_UNIT_TEST(DropViewIfExists) {
+        constexpr const char* name = "TheView";
+        NYql::TAstParseResult res = SqlToYql(std::format(R"(
+                USE plato;
+                DROP VIEW IF EXISTS {};
+            )", name
+        ));
+        UNIT_ASSERT_C(res.Root, res.Issues.ToString());
+
+        TVerifyLineFunc verifyLine = [&](const TString& word, const TString& line) {
+            if (word == "Write!") {
+                UNIT_ASSERT_STRING_CONTAINS(line, name);
+                UNIT_ASSERT_STRING_CONTAINS(line, "dropObjectIfExists");
+            }
+        };
+
+        TWordCountHive elementStat = { {"Write!"} };
+        VerifyProgram(res, elementStat, verifyLine);
+
+        UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
+    }
+
     Y_UNIT_TEST(CreateViewWithTablePrefix) {
         NYql::TAstParseResult res = SqlToYql(R"(
                 USE plato;
@@ -6835,7 +6879,7 @@ Y_UNIT_TEST_SUITE(TViewSyntaxTest) {
 
         UNIT_ASSERT_VALUES_EQUAL(elementStat["Write!"], 1);
     }
-    
+
     Y_UNIT_TEST(YtAlternativeSchemaSyntax) {
         NYql::TAstParseResult res = SqlToYql(R"(
             SELECT * FROM plato.Input WITH schema(y Int32, x String not null);
@@ -6904,7 +6948,7 @@ Y_UNIT_TEST_SUITE(CompactNamedExprs) {
             pragma CompactNamedExprs;
             pragma ValidateUnusedExprs;
 
-            define subquery $x() as 
+            define subquery $x() as
                 select count(1, 2);
             end define;
             select 1;
@@ -6927,7 +6971,7 @@ Y_UNIT_TEST_SUITE(CompactNamedExprs) {
             pragma CompactNamedExprs;
             pragma DisableValidateUnusedExprs;
 
-            define subquery $x() as 
+            define subquery $x() as
                 select count(1, 2);
             end define;
             select 1;