SNOW-997694: Allow DataFrame object to be used as a subquery in filter operations

What is the current behavior?

Rows must be collected if they are to be used in filter operations.

This errors out:

df.filter(df.period_label == df.select(func.max(df.period_label))).show()
>>> "SnowparkPlanException: (1206): Cannot create a Literal for <class 'snowflake.snowpark.dataframe.DataFrame'>"

No longer lazy:

df.filter(df.period_label == subq.first()[0]).show()

Extra syntax:

subq = df.select(func.max(df.period_label).alias("max"))
df.join(subq, df.period_label==subq.max).show()

What is the desired behavior?:

Perhaps we can indicate that the object is meant to be used as a literal via a subquery designation:

df.filter(df.period_label == df.select(func.max(df.period_label)).subquery()).show()

How would this improve snowflake-snowpark-python?

Filtering on the results of a subquery is a very basic pattern. I want to keep execution deferred until the end vs having to pull out the raw value as a literal.

Start by reading the DataFrame.filter and select APIs, then trace the expression handling behind the reported DataFrame literal error. Define how a subquery designation should preserve lazy execution and how it composes in filter expressions. Done means the demonstrated max-period filter works without collecting the value, with coverage for the proposed subquery usage.