SNOW-821408: Union does not validate field types

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

Python 3.8.16 (default, Mar 1 2023, 21:18:45) [Clang 14.0.6 ]

2. What operating system and processor architecture are you using?

macOS-13.3.1-arm64-arm-64bit

3. What are the component versions in the environment (pip freeze)?

anyio @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/croot-t_zs64wy/anyio_1644482593257/work/dist
appnope @ file:///Users/ktietz/demo/mc3/conda-bld/appnope_1629146036738/work
argon2-cffi @ file:///opt/conda/conda-bld/argon2-cffi_1645000214183/work
argon2-cffi-bindings @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/croot-wbf5edig/argon2-cffi-bindings_1644845754377/work
asn1crypto @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_325c15a5-a244-4e4d-9350-d6d0601426db54euu5k4/croots/recipe/asn1crypto_1652362646188/work
asttokens @ file:///opt/conda/conda-bld/asttokens_1646925590279/work
attrs @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_978y9aqcd7/croot/attrs_1668696180911/work
autopep8 @ file:///opt/conda/conda-bld/autopep8_1650463822033/work
backcall @ file:///home/ktietz/src/ci/backcall_1611930011877/work
beautifulsoup4 @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_fa78jvo_0n/croot/beautifulsoup4-split_1681493044306/work
black @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_29yzn6blpk/croot/black_1680737263429/work
bleach @ file:///opt/conda/conda-bld/bleach_1641577558959/work
Bottleneck @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_07078715-3ab7-4562-8d3d-d56b0eaa0f7dp504n_ny/croots/recipe/bottleneck_1657175566567/work
brotlipy==0.7.0
certifi @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_5d968ni_yn/croot/certifi_1671487774636/work/certifi
cffi @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_ab19r4bji3/croot/cffi_1670423206034/work
charset-normalizer @ file:///tmp/build/80754af9/charset-normalizer_1630003229654/work
click==8.1.3
cloudpickle @ file:///tmp/build/80754af9/cloudpickle_1632508026186/work
comm @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_b19kb7be6_/croot/comm_1671231124262/work
coverage==7.2.5
cryptography @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_a9zjqvif1f/croot/cryptography_1677533099634/work
debugpy @ file:///Users/builder/miniconda3/envs/prefect/conda-bld/debugpy_1637092214173/work
decorator @ file:///opt/conda/conda-bld/decorator_1643638310831/work
defusedxml @ file:///tmp/build/80754af9/defusedxml_1615228127516/work
entrypoints @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_croot-jb01gaox/entrypoints_1650293758411/work
executing @ file:///opt/conda/conda-bld/executing_1646925071911/work
fastjsonschema @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_43a0jaiddu/croots/recipe/python-fastjsonschema_1661368628129/work
filelock @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_20tv0lrp8x/croot/filelock_1672387134240/work
flit_core @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_b3azp31yen/croot/flit-core_1679397106077/work/source/flit_core
idna @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_771olrhiqw/croot/idna_1666125579282/work
importlib-metadata==6.6.0
importlib-resources==5.12.0
ipykernel @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_bappucl7zp/croot/ipykernel_1671488382153/work
ipython @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_2bkl1d0l7u/croot/ipython_1680701883396/work
ipython-genutils @ file:///tmp/build/80754af9/ipython_genutils_1606773439826/work
jaraco.classes==3.2.3
jedi @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/croot-f1t6hma6/jedi_1644315882177/work
Jinja2 @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_9fjgzv9ant/croot/jinja2_1666908141308/work
jsonschema @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_d9tjy9j5w9/croot/jsonschema_1676558687376/work
jupyter-server @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_c8338fkvq6/croot/jupyter_server_1671707627219/work
jupyter_client @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_701yya0pqm/croot/jupyter_client_1680171868326/work
jupyter_core @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_d1sy1hlz9t/croot/jupyter_core_1679906585151/work
jupyterlab-pygments @ file:///tmp/build/80754af9/jupyterlab_pygments_1601490720602/work
keyring==23.13.1
lxml @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_bbjv2ox7t8/croot/lxml_1679646469466/work
markdown-it-py==2.2.0
MarkupSafe==2.1.2
matplotlib-inline @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_f6fdc0hldi/croots/recipe/matplotlib-inline_1662014472341/work
mdurl==0.1.2
mistune @ file:///Users/ktietz/demo/mc3/conda-bld/mistune_1629356075445/work
more-itertools==9.1.0
mypy-extensions==0.4.3
nbclassic @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_d6oy9w0m3l/croot/nbclassic_1681756176477/work
nbclient @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_croot-08wgx75f/nbclient_1650373566605/work
nbconvert @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_36au3u9s44/croot/nbconvert_1668450648628/work
nbformat @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_94781rq_cg/croot/nbformat_1670352348390/work
nest-asyncio @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_6b_e0dr4lw/croot/nest-asyncio_1672387130036/work
notebook @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_a38gtdrjqf/croot/notebook_1681756176593/work
notebook_shim @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_f8mr1gjfb7/croot/notebook-shim_1668160580414/work
numexpr @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_76yyu1p9jk/croot/numexpr_1683221830860/work
numpy @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_d18vvv3mag/croot/numpy_and_numpy_base_1672336185310/work
oscrypto @ file:///tmp/build/80754af9/oscrypto_1633350059025/work
packaging @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_e946luvhc3/croot/packaging_1678965323926/work
pandas==1.5.3
pandocfilters @ file:///opt/conda/conda-bld/pandocfilters_1643405455980/work
parso @ file:///opt/conda/conda-bld/parso_1641458642106/work
pathspec @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_f954ebvbxx/croot/pathspec_1674681573969/work
pexpect @ file:///tmp/build/80754af9/pexpect_1605563209008/work
pickleshare @ file:///tmp/build/80754af9/pickleshare_1606932040724/work
pkgutil_resolve_name @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_0fpncw4e7o/croots/recipe/pkgutil-resolve-name_1661463323469/work
platformdirs @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_f7wx6m2jsp/croots/recipe/platformdirs_1662711384790/work
prometheus-client @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_25sgeyk0j5/croots/recipe/prometheus_client_1659455103277/work
prompt-toolkit @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_0blbsngvis/croot/prompt-toolkit_1672387317724/work
psutil @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_1310b568-21f4-4cb0-b0e3-2f3d31e39728k9coaga5/croots/recipe/psutil_1656431280844/work
ptyprocess @ file:///tmp/build/80754af9/ptyprocess_1609355006118/work/dist/ptyprocess-0.7.0-py2.py3-none-any.whl
pure-eval @ file:///opt/conda/conda-bld/pure_eval_1646925070566/work
pyarrow==10.0.1
pycodestyle @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_5b2mq44vl0/croot/pycodestyle_1674267228581/work
pycparser @ file:///tmp/build/80754af9/pycparser_1636541352034/work
pycryptodomex @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_28653skrg9/croots/recipe/pycryptodomex_1661442970080/work
Pygments==2.15.1
PyJWT @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_83311740-c3bc-48a8-9688-62d7e2d625f5s6jo920w/croots/recipe/pyjwt_1657544592579/work
pyOpenSSL @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_38h4axtq38/croot/pyopenssl_1677607699670/work
pyrsistent @ file:///Users/ktietz/demo/mc3/conda-bld/pyrsistent_1629278433598/work
PySocks @ file:///Users/ktietz/Code/oss/ci_pkgs/pysocks_1626781349491/work
python-dateutil @ file:///tmp/build/80754af9/python-dateutil_1626374649649/work
pytz @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_c56ru3yml9/croot/pytz_1671697451306/work
PyYAML==6.0
pyzmq @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_a7d9jpero7/croot/pyzmq_1682697648735/work
requests @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_e91odxd5at/croot/requests_1678709739765/work
requirements-parser==0.5.0
rich==13.3.5
scipy==1.10.1
Send2Trash @ file:///tmp/build/80754af9/send2trash_1632406701022/work
six @ file:///tmp/build/80754af9/six_1644875935023/work
sniffio @ file:///Users/ktietz/demo/mc3/conda-bld/sniffio_1629145892482/work
snowflake-cli-labs==0.2.8
snowflake-connector-python @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_76zscdwuzk/croot/snowflake-connector-python_1683041473497/work
snowflake-snowpark-python @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_959vpql0hu/croot/snowflake-snowpark-python_1680289916802/work
soupsieve @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_cfrx9hmp3g/croot/soupsieve_1680518480034/work
stack-data @ file:///opt/conda/conda-bld/stack_data_1646927590127/work
terminado @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_fcfvyc0an2/croot/terminado_1671751835701/work
tinycss2 @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_fcw5_i306t/croot/tinycss2_1668168825117/work
toml @ file:///tmp/build/80754af9/toml_1616166611790/work
tomli @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_d0e5ffbf-5cf1-45be-8693-c5dff8108a2awhthtjlq/croots/recipe/tomli_1657175508477/work
tornado @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_a61b4xoie9/croots/recipe/tornado_1662061692951/work
traitlets @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_6301rd5qbe/croot/traitlets_1671143894285/work
typer==0.9.0
types-setuptools==67.7.0.1
typing_extensions @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_10a8_u2ijw/croot/typing_extensions_1669923788997/work
urllib3 @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_c7xh0_scrc/croot/urllib3_1680254693282/work
wcwidth @ file:///Users/ktietz/demo/mc3/conda-bld/wcwidth_1629357192024/work
webencodings==0.5.1
websocket-client @ file:///Users/ktietz/demo/mc3/conda-bld/websocket-client_1629357070578/work
zipp==3.15.0

4. What did you do?

from snowflake.snowpark.functions import col
from snowflake.snowpark.types import VariantType

# Create a dataframe with a StringType column
df = session.create_dataframe([('a', 1), ('b', 2)], schema=['a', 'b'])
# And a similar one with a VariantType column
df_2 = df.select(col('a').cast(VariantType()).alias('a'), col('b'))

# One direction of union runs successfully, implicitly converting the Variant to a String
df_3 = df.union_all_by_name(df_2)
df_3.take(4)

# The other direction will fail, but only once actually running the query. i.e. this call will succeed
df_4 = df_2.union_all_by_name(df)
# And this call will fail
df_4.take(4)

5. What did you expect to see?

The code should analyze the types upon running the union_all_by_name, and fail fast when incompatible types are attempted union. (What happened in our code, is that the error was discovered much later, when the eventual dataframe is written, despite the fact that it could've been detected much earlier).
It is worth noting that the existing union code already does a really similar validation with the attribute names - accessing their datatypes is not out of reach:

    def _union_by_name_internal(
        self, other: "DataFrame", is_all: bool = False
    ) -> "DataFrame":
        left_output_attrs = self._output
        right_output_attrs = other._output
        right_output_attr_by_name = {rattr.name: rattr for rattr in right_output_attrs}

6. Can you set logging to DEBUG and collect the logs?

df_3:

2023-05-17 16:41:34,541 - MainThread cursor.py:735 - execute() - DEBUG - executing SQL/command
2023-05-17 16:41:34,542 - MainThread cursor.py:800 - execute() - INFO - query: [SELECT  *  FROM (( SELECT "A", "B" FROM ( SELECT $1 AS "A", $2 AS "B" FROM  VALU...]
2023-05-17 16:41:34,542 - MainThread connection.py:1363 - _next_sequence_counter() - DEBUG - sequence counter: 3
2023-05-17 16:41:34,543 - MainThread cursor.py:467 - _execute_helper() - DEBUG - Request id: 38b44933-b5ef-47ac-86de-3499453a206a
2023-05-17 16:41:34,543 - MainThread cursor.py:469 - _execute_helper() - DEBUG - running query [SELECT  *  FROM (( SELECT "A", "B" FROM ( SELECT $1 AS "A", $2 AS "B" FROM  VALU...]
2023-05-17 16:41:34,544 - MainThread cursor.py:476 - _execute_helper() - DEBUG - is_file_transfer: True
2023-05-17 16:41:34,544 - MainThread connection.py:1035 - cmd_query() - DEBUG - _cmd_query
2023-05-17 16:41:34,545 - MainThread connection.py:1058 - cmd_query() - DEBUG - sql=[SELECT  *  FROM (( SELECT "A", "B" FROM ( SELECT $1 AS "A", $2 AS "B" FROM  VALU...], sequence_id=[3], is_file_transfer=[False]
2023-05-17 16:41:34,546 - MainThread network.py:1162 - _use_requests_session() - DEBUG - Session status for SessionPool 'xxxxxxxx-xxxxxxxx.snowflakecomputing.com', SessionPool 1/1 active sessions
2023-05-17 16:41:34,546 - MainThread network.py:847 - _request_exec_wrapper() - DEBUG - remaining request timeout: None, retry cnt: 1
2023-05-17 16:41:34,547 - MainThread network.py:828 - add_request_guid() - DEBUG - Request guid: 9b54a4ca-0867-467a-8a3f-f91a584f36f6
2023-05-17 16:41:34,547 - MainThread network.py:1021 - _request_exec() - DEBUG - socket timeout: 60
2023-05-17 16:41:34,905 - MainThread connectionpool.py:456 - _make_request() - DEBUG - https://xxxxxxxx-xxxxxxxx.snowflakecomputing.com:443 "POST /queries/v1/query-request?requestId=38b44933-b5ef-47ac-86de-3499453a206a&request_guid=9b54a4ca-0867-467a-8a3f-f91a584f36f6 HTTP/1.1" 200 1687
2023-05-17 16:41:34,910 - MainThread network.py:1047 - _request_exec() - DEBUG - SUCCESS
2023-05-17 16:41:34,911 - MainThread network.py:1167 - _use_requests_session() - DEBUG - Session status for SessionPool 'xxxxxxxx-xxxxxxxx.snowflakecomputing.com', SessionPool 0/1 active sessions
2023-05-17 16:41:34,911 - MainThread network.py:727 - _post_request() - DEBUG - ret[code] = None, after post request
2023-05-17 16:41:34,912 - MainThread network.py:751 - _post_request() - DEBUG - Query id: 01ac58f5-0001-9ffc-0021-d2870008105a
2023-05-17 16:41:34,914 - MainThread cursor.py:807 - execute() - DEBUG - sfqid: 01ac58f5-0001-9ffc-0021-d2870008105a
2023-05-17 16:41:34,915 - MainThread cursor.py:813 - execute() - INFO - query execution done
2023-05-17 16:41:34,916 - MainThread cursor.py:827 - execute() - DEBUG - SUCCESS
2023-05-17 16:41:34,917 - MainThread cursor.py:846 - execute() - DEBUG - PUT OR GET: False
2023-05-17 16:41:34,917 - MainThread cursor.py:941 - _init_result_and_meta() - DEBUG - Query result format: arrow
2023-05-17 16:41:34,920 - MainThread cursor.py:955 - _init_result_and_meta() - INFO - Number of results in first chunk: 4
2023-05-17 16:41:34,921 - MainThread server_connection.py:328 - run_query() - DEBUG - Execute query [queryID: 01ac58f5-0001-9ffc-0021-d2870008105a]  SELECT  *  FROM (( SELECT "A", "B" FROM ( SELECT $1 AS "A", $2 AS "B" FROM  VALUES ('a' :: STRING, 1 :: INT), ('b' :: STRING, 2 :: INT))) UNION ALL ( SELECT  CAST ("A" AS VARIANT) AS "A", "B" FROM ( SELECT $1 AS "A", $2 AS "B" FROM  VALUES ('a' :: STRING, 1 :: INT), ('b' :: STRING, 2 :: INT)))) LIMIT 4
2023-05-17 16:41:34,923 - MainThread arrow_iterator.cpython-38-darwin.so:0 - __cinit__() - DEBUG - Batches read: 1
2023-05-17 16:41:34,923 - MainThread CArrowIterator.cpp:16 - CArrowIterator() - DEBUG - Arrow BatchSize: 1
2023-05-17 16:41:34,923 - MainThread CArrowChunkIterator.cpp:50 - CArrowChunkIterator() - DEBUG - Arrow chunk info: batchCount 1, columnCount 2, use_numpy: 0
2023-05-17 16:41:34,925 - MainThread result_set.py:58 - result_set_iterator() - DEBUG - beginning to schedule result batch downloads
2023-05-17 16:41:34,925 - MainThread CArrowChunkIterator.cpp:74 - next() - DEBUG - Current batch index: 0, rows in current batch: 4

df_4:

2023-05-17 16:41:06,727 - MainThread cursor.py:735 - execute() - DEBUG - executing SQL/command
2023-05-17 16:41:06,728 - MainThread cursor.py:800 - execute() - INFO - query: [SELECT  *  FROM (( SELECT  CAST ("A" AS VARIANT) AS "A", "B" FROM ( SELECT $1 AS...]
2023-05-17 16:41:06,728 - MainThread connection.py:1363 - _next_sequence_counter() - DEBUG - sequence counter: 2
2023-05-17 16:41:06,728 - MainThread cursor.py:467 - _execute_helper() - DEBUG - Request id: 2df6d65c-1bc6-4358-9cde-924161967bb6
2023-05-17 16:41:06,729 - MainThread cursor.py:469 - _execute_helper() - DEBUG - running query [SELECT  *  FROM (( SELECT  CAST ("A" AS VARIANT) AS "A", "B" FROM ( SELECT $1 AS...]
2023-05-17 16:41:06,729 - MainThread cursor.py:476 - _execute_helper() - DEBUG - is_file_transfer: True
2023-05-17 16:41:06,729 - MainThread connection.py:1035 - cmd_query() - DEBUG - _cmd_query
2023-05-17 16:41:06,729 - MainThread connection.py:1058 - cmd_query() - DEBUG - sql=[SELECT  *  FROM (( SELECT  CAST ("A" AS VARIANT) AS "A", "B" FROM ( SELECT $1 AS...], sequence_id=[2], is_file_transfer=[False]
2023-05-17 16:41:06,730 - MainThread network.py:1162 - _use_requests_session() - DEBUG - Session status for SessionPool 'xxxxxxxx-xxxxxxxx.snowflakecomputing.com', SessionPool 1/1 active sessions
2023-05-17 16:41:06,730 - MainThread network.py:847 - _request_exec_wrapper() - DEBUG - remaining request timeout: None, retry cnt: 1
2023-05-17 16:41:06,730 - MainThread network.py:828 - add_request_guid() - DEBUG - Request guid: 7ae80b5d-f94d-4e3c-a8d1-691c0b05bb4c
2023-05-17 16:41:06,730 - MainThread network.py:1021 - _request_exec() - DEBUG - socket timeout: 60
2023-05-17 16:41:07,072 - MainThread connectionpool.py:456 - _make_request() - DEBUG - https://xxxxxxxx-xxxxxxxx.snowflakecomputing.com:443 "POST /queries/v1/query-request?requestId=2df6d65c-1bc6-4358-9cde-924161967bb6&request_guid=7ae80b5d-f94d-4e3c-a8d1-691c0b05bb4c HTTP/1.1" 200 434
2023-05-17 16:41:07,073 - MainThread network.py:1047 - _request_exec() - DEBUG - SUCCESS
2023-05-17 16:41:07,073 - MainThread network.py:1167 - _use_requests_session() - DEBUG - Session status for SessionPool 'xxxxxxxx-xxxxxxxx.snowflakecomputing.com', SessionPool 0/1 active sessions
2023-05-17 16:41:07,073 - MainThread network.py:727 - _post_request() - DEBUG - ret[code] = 001790, after post request
2023-05-17 16:41:07,074 - MainThread network.py:751 - _post_request() - DEBUG - Query id: 01ac58f5-0001-a00b-0021-d2870007908a
2023-05-17 16:41:07,074 - MainThread cursor.py:807 - execute() - DEBUG - sfqid: 01ac58f5-0001-a00b-0021-d2870007908a
2023-05-17 16:41:07,074 - MainThread cursor.py:813 - execute() - INFO - query execution done
2023-05-17 16:41:07,074 - MainThread cursor.py:888 - execute() - DEBUG - {'data': {'internalError': False, 'errorCode': '001790', 'age': 0, 'sqlState': '42601', 'queryId': '01ac58f5-0001-a00b-0021-d2870007908a', 'line': -1, 'pos': -1, 'type': 'COMPILATION'}, 'code': '001790', 'message': 'SQL compilation error:\ninconsistent data type for result columns for set operator input branches, expected VARCHAR(16777216), got VARIANT', 'success': False, 'headers': None}

Run the supplied reproduction and trace the DataFrame.union_all_by_name entry point, especially the StringType and VariantType handling. The work is done when incompatible field types are validated when the union is constructed rather than only when take() executes the query.