PandasCursor with engine="pyarrow" changes string values: numeric-looking text, and NULL as 'nan' without infer_string

[laughingman7743]

Problem

With pd.options.future.infer_string = False, PandasCursor with engine="pyarrow" returns NULL (and empty) VARCHAR values as the string 'nan' instead of a missing value.
A real 'nan' string cannot be told apart, and isna() does not find these values.
The C engine (engine="auto", the default) and pandas' default infer_string=True return NaN.

The cause is in pandas' PyArrow engine: with infer_string disabled, dtype=str makes pandas.read_csv(engine="pyarrow") turn missing values into 'nan'.
DefaultPandasTypeConverter maps char, varchar, string, array, map, and row to str, so PyAthena hits it.
_read_csv_with_pyarrow() (#1057) reproduces pandas' PyArrow engine, so it returns the same values.

Reproduction

Measured on Athena with master a57325b (before #1057), pandas 3.0.6, pyarrow 25.0.1:

import pandas as pd
from pyathena import connect
from pyathena.pandas.cursor import PandasCursor

sql = """SELECT * FROM (VALUES
  (1, CAST(NULL AS VARCHAR), 'abcdefghijklmnopqrstuvwxyz0123456789'),
  (2, '', 'abcdefghijklmnopqrstuvwxyz0123456789'),
  (3, 'x', 'abcdefghijklmnopqrstuvwxyz0123456789')
) AS t(id, v, pad) ORDER BY id"""  # at least 100 bytes, so the PyArrow engine is used
cursor = connect(cursor_class=PandasCursor).cursor()
with pd.option_context("future.infer_string", False):
    cursor.execute(sql, engine="pyarrow")
    cursor.as_pandas()["v"].tolist()  # ['nan', 'nan', 'x']
    cursor.execute(sql)
    cursor.as_pandas()["v"].tolist()  # [nan, nan, 'x']

infer_string	engine	dtype of v	values
True	auto (C)	str	[nan, nan, 'x']
True	pyarrow	str	[nan, nan, 'x']
False	auto (C)	object	[nan, nan, 'x']
False	pyarrow	object	['nan', 'nan', 'x']

pandas alone, with PyAthena's read options (keep_default_na=False, na_values=("",), skip_blank_lines=False) on '"v","w"\n"a","1"\n,"2"\n', with infer_string=False:

engine	dtype={"v": str}	dtype={"v": object}	dtype={"v": "string"}
c	['a', nan]	['a', nan]	['a', <NA>]
pyarrow	['a', 'nan']	['a', nan]	['a', <NA>]

(The empty string also becoming a missing value is the existing na_values=("",) default, not part of this issue.)

Expected

Missing values stay missing (NaN in an object column), as with the C engine.

Also: numeric-looking strings with the default settings

With the default infer_string=True, the PyArrow engine also changes VARCHAR values when the whole column looks numeric: pyarrow infers a number type, and dtype=str then turns the numbers back into text.
_read_csv_with_pyarrow() (#1057) reproduces this as well.
This is pandas-dev/pandas#57666 (leading zeros stripped with dtype=str), which pandas cannot fix yet: pyarrow added the needed option (apache/arrow#47663), but pandas does not expose it.

Offline, pandas 3.0.6 and pyarrow 25.0.1, a v column of "1", NULL, "nan", "007", "1e3" with dtype=str and PyAthena's read options:

infer_string	reader	values of v
True	pandas C engine	['1', nan, 'nan', '007', '1e3']
True	pandas PyArrow engine	['1.0', nan, nan, '7.0', '1000.0']
True	_read_csv_with_pyarrow()	['1.0', nan, nan, '7.0', '1000.0']
False	pandas C engine	['1', nan, 'nan', '007', '1e3']
False	pandas PyArrow engine	['1.0', 'nan', 'nan', '7.0', '1000.0']
False	_read_csv_with_pyarrow()	['1.0', 'nan', 'nan', '7.0', '1000.0']

Plan (maintainer's choice)

_read_csv_with_pyarrow() reads the columns whose dtype is a string dtype as pyarrow strings (ConvertOptions(column_types=...)) and keeps missing values missing, so engine="pyarrow" returns the C engine's values for them.
The PyArrow engine reached through pandas.read_csv() (when execute() passes read options PyAthena does not read itself) keeps pandas' behavior, which the documentation will state.

Notes

• Found by the review of Read CSV results for the pandas PyArrow engine with pyarrow.csv #1057 (PandasCursor with engine="pyarrow" and PolarsCursor with use_pyarrow=True fail on multi-line CSV values that cross a block #1048).
• The NULL case comes from Series.astype(str) turning NaN into 'nan' with infer_string disabled, long-standing pandas behavior, so it is not reported upstream.

Environment

• PyAthena master a57325b, Python 3.13.1, pandas 3.0.6, pyarrow 25.0.1, PandasCursor.