Repository: ClickHouse/ClickHouse
File: src/Analyzer/Passes/FunctionToSubcolumnsPass.cpp (C++)

Code before:
        {TypeIndex::Object, "distinctJSONPaths"}, optimizeDistinctJSONPaths,
    },
    {
        {TypeIndex::Object, "tupleElement"}, optimizeTupleOrVariantElement<DataTypeObject>,
    },
    {
        {TypeIndex::Object, "JSONExtractInt"}, optimizeJSONExtractToSubcolumn<>,
    },
    {
        {TypeIndex::Object, "JSONExtractUInt"}, optimizeJSONExtractToSubcolumn<>,
    },
    {
        {TypeIndex::Object, "JSONExtractFloat"}, optimizeJSONExtractToSubcolumn<>,
    },
    {
        {TypeIndex::Object, "JSONExtractBool"}, optimizeJSONExtractToSubcolumn<>,
    },
    {
        {TypeIndex::Object, "JSONExtractString"}, optimizeJSONExtractToSubcolumn<>,
    },
    {
        {TypeIndex::Object, "JSONExtractRaw"}, optimizeJSONExtractToSubcolumn<>,
    },
    {
        {TypeIndex::Object, "JSONExtract"}, optimizeJSONExtractToSubcolumn<true>,
    },
};

bool canOptimizeWithWherePrewhereOrGroupBy(const String & function_name)
{
    /// Optimization for distinctJSONPaths works correctly only if we request distinct JSON paths across whole table.
    return function_name != "distinctJSONPaths";
}

std::tuple<FunctionNode *, ColumnNode *, TableNode *> getTypedNodesForOptimization(const QueryTreeNodePtr & node, const ContextPtr & context)
{
    auto * function_node = node->as<FunctionNode>();
    if (!function_node)
        return {};

    auto & function_arguments_nodes = function_node->getArguments().getNodes();
    if (function_arguments_nodes.empty())
        return {};

    auto * first_argument_column_node = function_arguments_nodes.front()->as<ColumnNode>();
    if (!first_argument_column_node || first_argument_column_node->getColumnName() == "__grouping_set")
        return {};

    auto column_source = first_argument_column_node->getColumnSource();
    auto * table_node = column_source->as<TableNode>();
    if (!table_node)

Review comment (refactor):
There is a problem with `tupleElement` and `JSONExtract*` functions in terms of optimization to subcolumns:
In JSON column we store flattened JSON paths with values, so usual subcolumn with dot syntax `json_column.some.path` reads only the literal value with path `some.path` and doesn't return nested object if any. For example:
```sql
:) select arrayJoin(['{"a" : "Hello"}', '{"a" : {"b" : "Hello"}}'])::JSON as json, json.a

   ┌─json────────────────┬─json.a─┐
1. │ {"a":"Hello"}       │ Hello  │
2. │ {"a":{"b":"Hello"}} │ ᴺᵁᴸᴸ   │
   └─────────────────────┴────────┘
```

And this is a problem, because `tupleElement` and `JSONExtract*` functions would also return nested object:
```
:) select arrayJoin(['{"a" : "Hello"}', '{"a" : {"b" : "Hello"}}']) as json, JSONExtractRaw(json, 'a'), tupleElement(json::JSON, 'a')

   ┌─json────────────────────┬─JSONExtractRaw(json, 'a')─┬─tupleElement⋯SON'), 'a')─┐
1. │ {"a" : "Hello"}         │ "Hello"                   │ Hello                    │
2. │ {"a" : {"b" : "Hello"}} │ {"b":"Hello"}             │ {"b":"Hello"}            │
   └─────────────────────────┴───────────────────────────┴──────────────────────────┘
```

And we need optimization to subcolumns to be consistent with the function execution. In future we [plan](https://github.com/ClickHouse/ClickHouse/issues/68428#issuecomment-2299679966) to introduce a new type of subcolumn for JSON: `json_column.$some.path` that will read the combination of the literal value and the nested object if any. And we could use this subcolumn here, but unfortunately we don't have it yet.

We can try to make a workaround and replace it not to a single subcolumn, but to a combination of subcolumns `json_column.some.path` (that returns literal value) and `json_column.^some.path` (that returns only nested subobject). Something like this with `multiIf`:
```sql
:) select arrayJoin(['{"a" : "Hello"}', '{"a" : {"b" : "Hello"}}'])::JSON as json, multiIf(json.a is not Null, json.a, notEmpty(json.^a), json.^a, NULL)


   ┌─json────────────────┬─multiIf(isNo⋯^`a`, NULL)─┐
1. │ {"a":"Hello"}       │ Hello                    │
2. │ {"a":{"b":"Hello"}} │ {"b":"Hello"}            │
   └─────────────────────┴──────────────────────────┘
```

And in future we can replace it with the new subcolumn type.

Code after:

        if (auto * column_node = node->as<ColumnNode>())
        {
            enterImpl(*column_node);
            return;
        }

        auto [function_node, first_argument_node, table_node] = getTypedNodesForOptimization(node, getContext());
        if (function_node && first_argument_node && table_node)
        {
            enterImpl(*function_node, *first_argument_node, *table_node);
            return;
        }

        if (const auto * /*join_node*/ _ = node->as<JoinNode>())
        {
            can_wrap_result_columns_with_nullable |= getContext()->getSettingsRef()[Setting::join_use_nulls];
            return;
        }

        if (const auto * /*cross_join_node*/ _ = node->as<CrossJoinNode>())
        {
            can_wrap_result_columns_with_nullable |= getContext()->getSettingsRef()[Setting::join_use_nulls];
            return;
        }

        if (const auto * query_node = node->as<QueryNode>())
        {
            if (query_node->isGroupByWithCube() || query_node->isGroupByWithRollup() || query_node->isGroupByWithGroupingSets())
                can_wrap_result_columns_with_nullable |= getContext()->getSettingsRef()[Setting::group_by_use_nulls];
            has_where_prewhere_or_group_by = query_node->hasWhere() || query_node->hasPrewhere() || query_node->hasGroupBy();
            return;
        }
    }

    std::unordered_set<Identifier> getIdentifiersToOptimize() const
    {
        if (can_wrap_result_columns_with_nullable)
        {
            /// Do not optimize if we have JOIN with setting join_use_null.
            /// Do not optimize if we have GROUP BY WITH ROLLUP/CUBE/GROUPING SETS with setting group_by_use_nulls.
            /// It may change the behaviour if subcolumn can be converted
            /// to Nullable while the original column cannot (e.g. for Array type).
            return {};
        }

        /// TODO(ab): need to optimize for prewhere anyway
        /// Do not optimize if full column is requested in other context.
        /// It doesn't make sense because it doesn't reduce amount of read data
        /// and optimized functions are not computation heavy. But introducing
        /// new identifier complicates query analysis and may break it.