YQL-20253: Support `FLATTEN [OPTIONAL] BY` for any data type

Support `FLATTEN [OPTIONAL] BY` for any data type
commit_hash:6fb221fadf29d1dcc7ea4590a21c115fc506aa51

20 files changed, 298 insertions, 32 deletions

diff --git a/yql/essentials/core/type_ann/type_ann_core.cpp b/yql/essentials/core/type_ann/type_ann_core.cpp
index 1e724b86877..d213d32f1c8 100644
--- a/yql/essentials/core/type_ann/type_ann_core.cpp
+++ b/yql/essentials/core/type_ann/type_ann_core.cpp
@@ -1521,7 +1521,7 @@ namespace NTypeAnnImpl {
         return IGraphTransformer::TStatus::Ok;
     }
 
-    IGraphTransformer::TStatus FlattenByColumns(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx) {
+    IGraphTransformer::TStatus FlattenByColumns(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx) {
         Y_UNUSED(output);
         if (!EnsureMinArgsCount(*input, 2, ctx.Expr)) {
             return IGraphTransformer::TStatus::Error;
@@ -1626,32 +1626,23 @@ namespace NTypeAnnImpl {
             const bool fieldOptional = fieldType->GetKind() == ETypeAnnotationKind::Optional;
             if (fieldOptional) {
                 fieldType = fieldType->Cast<TOptionalExprType>()->GetItemType();
-            } else {
-                allFieldOptional = false;
-            }
-
-            if (mode == "optional" && !fieldOptional) {
-                ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(structObj->Pos()), TStringBuilder() <<
-                    "Expected optional type in field of struct: '" << fieldName <<
-                    "', but got: " << *field->GetItemType()));
-                return IGraphTransformer::TStatus::Error;
             }
 
-            if (mode == "list" && RemoveOptionalType(field->GetItemType())->GetKind() != ETypeAnnotationKind::List) {
+            if (mode == "list" && fieldType->GetKind() != ETypeAnnotationKind::List) {
                 ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(structObj->Pos()), TStringBuilder() <<
                     "Expected (optional) list type in field of struct: '" << fieldName <<
                     "', but got: " << *field->GetItemType()));
                 return IGraphTransformer::TStatus::Error;
             }
 
-            if (mode == "dict" && RemoveOptionalType(field->GetItemType())->GetKind() != ETypeAnnotationKind::Dict) {
+            if (mode == "dict" && fieldType->GetKind() != ETypeAnnotationKind::Dict) {
                 ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(structObj->Pos()), TStringBuilder() <<
                     "Expected (optional) dict type in field of struct: '" << fieldName <<
                     "', but got: " << *field->GetItemType()));
                 return IGraphTransformer::TStatus::Error;
             }
 
-            if (mode == "auto" && fieldOptional && RemoveOptionalType(field->GetItemType())->GetKind() == ETypeAnnotationKind::List) {
+            if (mode == "auto" && fieldOptional && fieldType->GetKind() == ETypeAnnotationKind::List) {
                 auto issue = TIssue(ctx.Expr.GetPosition(structObj->Pos()), "Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY");
                 SetIssueCode(EYqlIssueCode::TIssuesIds_EIssueCode_CORE_FLATTEN_BY_OPT, issue);
                 if (!ctx.Expr.AddWarning(issue)) {
@@ -1659,7 +1650,7 @@ namespace NTypeAnnImpl {
                 }
             }
 
-            if (mode == "auto" && fieldOptional && RemoveOptionalType(field->GetItemType())->GetKind() == ETypeAnnotationKind::Dict) {
+            if (mode == "auto" && fieldOptional && fieldType->GetKind() == ETypeAnnotationKind::Dict) {
                 auto issue = TIssue(ctx.Expr.GetPosition(structObj->Pos()), "Ambiguous FLATTEN BY statement, please choose FLATTEN DICT BY or FLATTEN OPTIONAL BY");
                 SetIssueCode(EYqlIssueCode::TIssuesIds_EIssueCode_CORE_FLATTEN_BY_OPT, issue);
                 if (!ctx.Expr.AddWarning(issue)) {
@@ -1668,27 +1659,25 @@ namespace NTypeAnnImpl {
             }
 
             const TTypeAnnotationNode* flattenItemType = nullptr;
-            if (fieldOptional) {
-                flattenItemType = fieldType;
-                if (mode == "list") {
-                    auto listType = fieldType->Cast<TListExprType>();
-                    flattenItemType = listType->GetItemType();
-                    allFieldOptional = false;
-                } else if (mode == "dict") {
-                    auto dictType = fieldType->Cast<TDictExprType>();
-                    const auto keyType = dictType->GetKeyType();
-                    const auto payloadType = dictType->GetPayloadType();
-                    flattenItemType = ctx.Expr.MakeType<TTupleExprType>(TTypeAnnotationNode::TListType({ keyType, payloadType }));
-                    allFieldOptional = false;
-                }
-            } else if (fieldType->GetKind() == ETypeAnnotationKind::List) {
+            if (mode == "list" || (mode == "auto" && !fieldOptional && fieldType->GetKind() == ETypeAnnotationKind::List)) {
                 auto listType = fieldType->Cast<TListExprType>();
                 flattenItemType = listType->GetItemType();
-            } else if (fieldType->GetKind() == ETypeAnnotationKind::Dict) {
+                allFieldOptional = false;
+            } else if (mode == "dict" || (mode == "auto" && !fieldOptional && fieldType->GetKind() == ETypeAnnotationKind::Dict)) {
                 auto dictType = fieldType->Cast<TDictExprType>();
                 const auto keyType = dictType->GetKeyType();
                 const auto payloadType = dictType->GetPayloadType();
                 flattenItemType = ctx.Expr.MakeType<TTupleExprType>(TTypeAnnotationNode::TListType({keyType, payloadType}));
+                allFieldOptional = false;
+            } else if (mode == "optional" || (mode == "auto" && fieldOptional)) {
+                if (!IsBackwardCompatibleFeatureAvailable(ctx.Types.LangVer, MakeLangVersion(2025, 03), ctx.Types.BackportMode) && !fieldOptional) {
+                    ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(structObj->Pos()), TStringBuilder() <<
+                        "Expected optional type in field of struct: '" << fieldName <<
+                        "', but got: " << *field->GetItemType()));
+                    return IGraphTransformer::TStatus::Error;
+                }
+
+                flattenItemType = fieldType;
             } else {
                 ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(structObj->Pos()), TStringBuilder() <<
                     "Expected list, dict or optional types in field of struct: '" << fieldName <<
@@ -12660,7 +12649,7 @@ template <NKikimr::NUdf::EDataSlot DataSlot>
         Functions["RemoveMembers"] = &RemoveMembersWrapper<false>;
         Functions["ForceRemoveMembers"] = &RemoveMembersWrapper<true>;
         Functions["DivePrefixMembers"] = &DivePrefixMembersWrapper;
-        Functions["FlattenByColumns"] = &FlattenByColumns;
+        ExtFunctions["FlattenByColumns"] = &FlattenByColumns;
         Functions["ExtractMembers"] = &ExtractMembersWrapper;
         Functions["FlattenStructs"] = &FlattenStructsWrapper;
         Functions["<"] = &CompareWrapper<false>;
diff --git a/yql/essentials/core/yql_opt_utils.cpp b/yql/essentials/core/yql_opt_utils.cpp
index 29ae63082d3..040b329b08f 100644
--- a/yql/essentials/core/yql_opt_utils.cpp
+++ b/yql/essentials/core/yql_opt_utils.cpp
@@ -1237,8 +1237,10 @@ TExprNode::TPtr ExpandFlattenByColumns(const TExprNode::TPtr& node, TExprContext
                 .Seal()
                 .Build();
         } else {
-            isList = flattenInfo.Type->GetKind() == ETypeAnnotationKind::List;
-            isDict = flattenInfo.Type->GetKind() == ETypeAnnotationKind::Dict;
+            if (mode != "optional") {
+                isList = flattenInfo.Type->GetKind() == ETypeAnnotationKind::List;
+                isDict = flattenInfo.Type->GetKind() == ETypeAnnotationKind::Dict;
+            }
         }
 
         if (isDict) {
@@ -1249,6 +1251,34 @@ TExprNode::TPtr ExpandFlattenByColumns(const TExprNode::TPtr& node, TExprContext
         }
 
         if (!isDict && !isList) {
+            bool knownNotNull = flattenInfo.Type->GetKind() != ETypeAnnotationKind::Optional && flattenInfo.Type->GetKind() != ETypeAnnotationKind::Null;
+
+            if (flattenInfo.Type->GetKind() == ETypeAnnotationKind::Pg) {
+                flattenInfo.ListMember = ctx.Builder(structObj->Pos())
+                    .Callable("If")
+                        .Callable(0, "Exists")
+                            .Add(0, flattenInfo.ListMember)
+                        .Seal()
+                        .Callable(1, "Just")
+                            .Add(0, flattenInfo.ListMember)
+                        .Seal()
+                        .Callable(2, "Nothing")
+                            .Callable(0, "OptionalType")
+                                .Callable(0, "TypeOf")
+                                    .Add(0, flattenInfo.ListMember)
+                                .Seal()
+                            .Seal()
+                        .Seal()
+                    .Seal()
+                    .Build();
+            } else if (knownNotNull) {
+                flattenInfo.ListMember = ctx.Builder(structObj->Pos())
+                    .Callable("Just")
+                    .Add(0, flattenInfo.ListMember)
+                    .Seal()
+                    .Build();
+            }
+
             flattenPriority.push_back(&flattenInfo);
         } else {
             flattenPriority.push_front(&flattenInfo);
diff --git a/yql/essentials/docs/en/changelog/2025.03.md b/yql/essentials/docs/en/changelog/2025.03.md
index e8d10b03910..e1af1a8b386 100644
--- a/yql/essentials/docs/en/changelog/2025.03.md
+++ b/yql/essentials/docs/en/changelog/2025.03.md
@@ -28,3 +28,7 @@
 ## Changes in the Udf function
 
 * Added the ability to specify a dependency on the calculation of another expression.
+
+## Changes in `FLATTEN OPTIONAL BY` semantics
+
+* `FLATTEN OPTIONAL BY` expression now accepts any non-Optional type, ensuring that the resulting column does not contain NULL.
diff --git a/yql/essentials/docs/en/syntax/flatten.md b/yql/essentials/docs/en/syntax/flatten.md
index 0dd81ceed4c..163f0e55273 100644
--- a/yql/essentials/docs/en/syntax/flatten.md
+++ b/yql/essentials/docs/en/syntax/flatten.md
@@ -82,6 +82,8 @@ To specify the type of container to convert to, you can use:
 * `FLATTEN OPTIONAL BY`
 
    To filter the `NULL` values without serialization, specify the operation by using `FLATTEN OPTIONAL BY`.
+   
+   Since YQL [2025.03](../changelog/2025.03.md), this expression also accepts any non-Optional type (in particular, application on the `NullType` returns `EmptyList`, Postgres types are filtered by non-`NULL`, and other data types are mapped as is).
 
 #### Examples
 
diff --git a/yql/essentials/docs/ru/changelog/2025.03.md b/yql/essentials/docs/ru/changelog/2025.03.md
index f86fc89f0e3..80c03f39079 100644
--- a/yql/essentials/docs/ru/changelog/2025.03.md
+++ b/yql/essentials/docs/ru/changelog/2025.03.md
@@ -28,3 +28,7 @@
 ## Изменения в функции Udf
 
 * Добавлена возможность указания зависимости от вычисления другого выражения
+
+## Изменения в семантике `FLATTEN OPTIONAL BY`
+
+* Выражение `FLATTEN OPTIONAL BY` теперь принимает любой не-Optional тип, гарантируя, что результирующая колонка не содержит NULL.
diff --git a/yql/essentials/docs/ru/syntax/flatten.md b/yql/essentials/docs/ru/syntax/flatten.md
index a33e83ed220..24bc6b81a1f 100644
--- a/yql/essentials/docs/ru/syntax/flatten.md
+++ b/yql/essentials/docs/ru/syntax/flatten.md
@@ -77,6 +77,8 @@ SELECT value, id FROM as_table($sample) FLATTEN BY (value);
 * `FLATTEN OPTIONAL BY`
 
    Чтобы фильтровать `NULL`-значения без размножения, необходимо уточнить операцию до `FLATTEN OPTIONAL BY`.
+   
+   Начиная с YQL [2025.03](../changelog/2025.03.md), данное выражение также принимает любой не-Optional тип (в частности, применение на типе `NullType` вернёт `EmptyList`, Postgres-типы будут отфильтрованы по не-`NULL`, а другие типы данных будут отображены как есть).
 
 #### Примеры
 
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index e3ac733cb36..255e1ee8db5 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -648,6 +648,20 @@
             "uri": "https://{canondata_backend}/1937367/9ad8b37cb00d1bb871bca912f5dd491e7738634c/resource.tar.gz#test.test_flatten_by-flatten_columns_non_struct-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[flatten_by-flatten_optional_by_data--Debug]": [
+        {
+            "checksum": "fb4b51eb61251909730e7765e85db52a",
+            "size": 1243,
+            "uri": "https://{canondata_backend}/212715/c1d5dcd6349aeee7aeaa0cb9666efb95c4bebee1/resource.tar.gz#test.test_flatten_by-flatten_optional_by_data--Debug_/opt.yql"
+        }
+    ],
+    "test.test[flatten_by-flatten_optional_by_data--Results]": [
+        {
+            "checksum": "3d65394dd6cf001635e5b9293b59d8bd",
+            "size": 6228,
+            "uri": "https://{canondata_backend}/212715/c1d5dcd6349aeee7aeaa0cb9666efb95c4bebee1/resource.tar.gz#test.test_flatten_by-flatten_optional_by_data--Results_/results.txt"
+        }
+    ],
     "test.test[flexible_types-functions-default.txt-Debug]": [
         {
             "checksum": "2f66127a50f9636139700cc9623d1a83",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 1f6421cb48e..6601fbe40ac 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -3870,6 +3870,34 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_flatten_by-flatten_expr_where_/sql.yql"
         }
     ],
+    "test_sql2yql.test[flatten_by-flatten_optional_by_data]": [
+        {
+            "checksum": "a80591c0e762cbed3bef839508990f9e",
+            "size": 11079,
+            "uri": "https://{canondata_backend}/1600758/260f14e78d1ecc078c9c6ecf41cb8692c6f78087/resource.tar.gz#test_sql2yql.test_flatten_by-flatten_optional_by_data_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[flatten_by-flatten_optional_by_list_2025_02]": [
+        {
+            "checksum": "76a9bfeef7ee17bb6a3d43e01b8af1f5",
+            "size": 2266,
+            "uri": "https://{canondata_backend}/1600758/260f14e78d1ecc078c9c6ecf41cb8692c6f78087/resource.tar.gz#test_sql2yql.test_flatten_by-flatten_optional_by_list_2025_02_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[flatten_by-flatten_optional_by_null_2025_02]": [
+        {
+            "checksum": "05521229e17676f06709345b2f11d50e",
+            "size": 2218,
+            "uri": "https://{canondata_backend}/1600758/260f14e78d1ecc078c9c6ecf41cb8692c6f78087/resource.tar.gz#test_sql2yql.test_flatten_by-flatten_optional_by_null_2025_02_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[flatten_by-flatten_optional_by_pgint4_2025_02]": [
+        {
+            "checksum": "a22fcad4dcf3974a54c017765066c658",
+            "size": 3035,
+            "uri": "https://{canondata_backend}/1600758/260f14e78d1ecc078c9c6ecf41cb8692c6f78087/resource.tar.gz#test_sql2yql.test_flatten_by-flatten_optional_by_pgint4_2025_02_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[flatten_by-flatten_persist_resource]": [
         {
             "checksum": "fa408182ff6ce4a7393a7d79cfac33a0",
@@ -10807,6 +10835,26 @@
             "uri": "file://test_sql_format.test_flatten_by-flatten_expr_where_/formatted.sql"
         }
     ],
+    "test_sql_format.test[flatten_by-flatten_optional_by_data]": [
+        {
+            "uri": "file://test_sql_format.test_flatten_by-flatten_optional_by_data_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[flatten_by-flatten_optional_by_list_2025_02]": [
+        {
+            "uri": "file://test_sql_format.test_flatten_by-flatten_optional_by_list_2025_02_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[flatten_by-flatten_optional_by_null_2025_02]": [
+        {
+            "uri": "file://test_sql_format.test_flatten_by-flatten_optional_by_null_2025_02_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[flatten_by-flatten_optional_by_pgint4_2025_02]": [
+        {
+            "uri": "file://test_sql_format.test_flatten_by-flatten_optional_by_pgint4_2025_02_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[flatten_by-flatten_persist_resource]": [
         {
             "uri": "file://test_sql_format.test_flatten_by-flatten_persist_resource_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_data_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_data_/formatted.sql
new file mode 100644
index 00000000000..b99752c1c56
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_data_/formatted.sql
@@ -0,0 +1,86 @@
+$data1 = (
+    SELECT
+        NULL AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+);
+
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data1
+    FLATTEN OPTIONAL BY a
+;
+
+$data2 = (
+    SELECT
+        12345 AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+);
+
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data2
+    FLATTEN OPTIONAL BY a
+;
+
+$pgint4_ty = Typeof(1p);
+
+$data3 = (
+    SELECT
+        12345p AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+    UNION
+    SELECT
+        CAST(NULL AS $pgint4_ty) AS a,
+        'should be' AS b,
+        'omitted' AS c
+);
+
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data3
+    FLATTEN OPTIONAL BY a
+;
+
+$data4 = (
+    SELECT
+        [1, 2, 3] AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+);
+
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data4
+    FLATTEN OPTIONAL BY a
+;
+
+$data5 = (
+    SELECT
+        {'a': 1, 'b': 2} AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+);
+
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data5
+    FLATTEN OPTIONAL BY a
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_list_2025_02_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_list_2025_02_/formatted.sql
new file mode 100644
index 00000000000..e2c0c8c2422
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_list_2025_02_/formatted.sql
@@ -0,0 +1,16 @@
+$data4 = (
+    SELECT
+        [1, 2, 3] AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+);
+
+/* custom error: Expected optional type in field of struct */
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data4
+    FLATTEN OPTIONAL BY a
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_null_2025_02_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_null_2025_02_/formatted.sql
new file mode 100644
index 00000000000..3a470ee66f5
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_null_2025_02_/formatted.sql
@@ -0,0 +1,16 @@
+$data1 = (
+    SELECT
+        NULL AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+);
+
+/* custom error: Expected optional type in field of struct */
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data1
+    FLATTEN OPTIONAL BY a
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_pgint4_2025_02_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_pgint4_2025_02_/formatted.sql
new file mode 100644
index 00000000000..6e087af7712
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_flatten_by-flatten_optional_by_pgint4_2025_02_/formatted.sql
@@ -0,0 +1,23 @@
+$pgint4_ty = Typeof(1p);
+
+$data3 = (
+    SELECT
+        12345p AS a,
+        'e,f,g,h' AS b,
+        'x' AS c
+    UNION
+    SELECT
+        CAST(NULL AS $pgint4_ty) AS a,
+        'should be' AS b,
+        'omitted' AS c
+);
+
+/* custom error: Expected optional type in field of struct */
+SELECT
+    a,
+    b,
+    c
+FROM
+    $data3
+    FLATTEN OPTIONAL BY a
+;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_data.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_data.cfg
new file mode 100644
index 00000000000..cd964fef9e8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_data.cfg
@@ -0,0 +1 @@
+langver 2025.03
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_data.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_data.sql
new file mode 100644
index 00000000000..dccba32a467
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_data.sql
@@ -0,0 +1,15 @@
+$data1 = SELECT Null AS a, "e,f,g,h" AS b, "x" AS c;
+SELECT a,b,c FROM $data1 FLATTEN OPTIONAL BY a;
+
+$data2 = SELECT 12345 AS a, "e,f,g,h" AS b, "x" AS c;
+SELECT a,b,c FROM $data2 FLATTEN OPTIONAL BY a;
+
+$pgint4_ty = Typeof(1p);
+$data3 = SELECT 12345p AS a, "e,f,g,h" AS b, "x" AS c UNION SELECT Cast(Null as $pgint4_ty) AS a, "should be" AS b, "omitted" AS c;
+SELECT a,b,c FROM $data3 FLATTEN OPTIONAL BY a;
+
+$data4 = SELECT [1,2,3] AS a, "e,f,g,h" AS b, "x" AS c;
+SELECT a,b,c FROM $data4 FLATTEN OPTIONAL BY a;
+
+$data5 = SELECT {"a": 1,"b": 2} AS a, "e,f,g,h" AS b, "x" AS c;
+SELECT a,b,c FROM $data5 FLATTEN OPTIONAL BY a;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_list_2025_02.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_list_2025_02.cfg
new file mode 100644
index 00000000000..989226cf3d4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_list_2025_02.cfg
@@ -0,0 +1,2 @@
+xfail
+langver 2025.02
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_list_2025_02.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_list_2025_02.sql
new file mode 100644
index 00000000000..a3afdc140e5
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_list_2025_02.sql
@@ -0,0 +1,3 @@
+$data4 = SELECT [1,2,3] AS a, "e,f,g,h" AS b, "x" AS c;
+/* custom error: Expected optional type in field of struct */
+SELECT a,b,c FROM $data4 FLATTEN OPTIONAL BY a;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_null_2025_02.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_null_2025_02.cfg
new file mode 100644
index 00000000000..989226cf3d4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_null_2025_02.cfg
@@ -0,0 +1,2 @@
+xfail
+langver 2025.02
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_null_2025_02.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_null_2025_02.sql
new file mode 100644
index 00000000000..d0339a2fe82
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_null_2025_02.sql
@@ -0,0 +1,3 @@
+$data1 = SELECT Null AS a, "e,f,g,h" AS b, "x" AS c;
+/* custom error: Expected optional type in field of struct */
+SELECT a,b,c FROM $data1 FLATTEN OPTIONAL BY a;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_pgint4_2025_02.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_pgint4_2025_02.cfg
new file mode 100644
index 00000000000..989226cf3d4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_pgint4_2025_02.cfg
@@ -0,0 +1,2 @@
+xfail
+langver 2025.02
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_pgint4_2025_02.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_pgint4_2025_02.sql
new file mode 100644
index 00000000000..68552474bfb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_optional_by_pgint4_2025_02.sql
@@ -0,0 +1,4 @@
+$pgint4_ty = Typeof(1p);
+$data3 = SELECT 12345p AS a, "e,f,g,h" AS b, "x" AS c UNION SELECT Cast(Null as $pgint4_ty) AS a, "should be" AS b, "omitted" AS c;
+/* custom error: Expected optional type in field of struct */
+SELECT a,b,c FROM $data3 FLATTEN OPTIONAL BY a;