[YQL-14487] Improve pgtype/pgconst error reporting. Support PgConst(Pg*, ...)

ref:5d2ffc5083dfde76a9b0916b7db7b9f456fcddf7

2 files changed, 20 insertions, 5 deletions

diff --git a/ydb/library/yql/sql/v1/node.cpp b/ydb/library/yql/sql/v1/node.cpp
index 35724beb70..fccb14af00 100644
--- a/ydb/library/yql/sql/v1/node.cpp
+++ b/ydb/library/yql/sql/v1/node.cpp
@@ -3123,7 +3123,13 @@ TNodePtr BuildDataType(TPosition pos, const TString& typeName) {
 TMaybe<TString> LookupSimpleType(const TStringBuf& alias, bool flexibleTypes, bool isPgType) {
     TString normalized = to_lower(TString(alias));
     if (isPgType) {
-        // original pg type is passed (like _int4)
+        // expecting original pg type (like _int4 or varchar) with optional pg suffix (i.e. _pgint4, pgvarchar)
+        if (normalized.StartsWith("pg")) {
+            normalized = normalized.substr(2);
+        } else if (normalized.StartsWith("_pg")) {
+            normalized = "_" + normalized.substr(3);
+        }
+
         if (!NPg::HasType(normalized)) {
             return {};
         }
@@ -3155,9 +3161,10 @@ TMaybe<TString> LookupSimpleType(const TStringBuf& alias, bool flexibleTypes, bo
 }
 
 TNodePtr BuildSimpleType(TContext& ctx, TPosition pos, const TString& typeName, bool dataOnly) {
-    auto found = LookupSimpleType(typeName, ctx.FlexibleTypes, ctx.GetColumnReferenceState() == EColumnRefState::AsPgType);
+    bool explicitPgType = ctx.GetColumnReferenceState() == EColumnRefState::AsPgType;
+    auto found = LookupSimpleType(typeName, ctx.FlexibleTypes, explicitPgType);
     if (!found) {
-        ctx.Error(pos) << "Unknown simple type '" << typeName << "'";
+        ctx.Error(pos) << "Unknown " << (explicitPgType ? "pg" : "simple") <<  " type '" << typeName << "'";
         return {};
     }
 
diff --git a/ydb/library/yql/sql/v1/sql.cpp b/ydb/library/yql/sql/v1/sql.cpp
index 0ec196ed20..876ed51587 100644
--- a/ydb/library/yql/sql/v1/sql.cpp
+++ b/ydb/library/yql/sql/v1/sql.cpp
@@ -4240,8 +4240,16 @@ TNodePtr TSqlExpression::UnaryCasualExpr(const TUnaryCasualExprRule& node, const
         bool columnOrType = false;
         auto columnRefsState = Ctx.GetColumnReferenceState();
         bool explicitPgType = columnRefsState == EColumnRefState::AsPgType;
-        if (auto simpleType = LookupSimpleType(name, flexibleTypes, explicitPgType); simpleType && typePossible && suffixIsEmpty) {
-            if (tail.Count > 0 || columnRefsState == EColumnRefState::Deny || explicitPgType || !flexibleTypes) {
+        if (explicitPgType && typePossible && suffixIsEmpty) {
+            auto pgType = BuildSimpleType(Ctx, Ctx.Pos(), name, false);
+            if (pgType && tail.Count) {
+                Ctx.Error() << "Optional types are not supported in this context";
+                return {};
+            }
+            return pgType;
+        }
+        if (auto simpleType = LookupSimpleType(name, flexibleTypes, false); simpleType && typePossible && suffixIsEmpty) {
+            if (tail.Count > 0 || columnRefsState == EColumnRefState::Deny || !flexibleTypes) {
                 // a type
                 return AddOptionals(BuildSimpleType(Ctx, Ctx.Pos(), name, false), tail.Count);
             }