Behavior inconsistent with Pandas' data type handling

[auxten]

When reading parquet files with datetime columns, chdb returns timezone-aware
datetimes with system timezone applied, while pandas returns timezone-naive datetimes.

This causes:

1. Value shift (e.g., 00:00:00 becomes 08:00:00+08:00 in Asia/Singapore timezone)
2. Type mismatch (datetime64[ns] vs datetime64[ns, Asia/Singapore])

Expected behavior: chdb should return the same datetime values as pandas.

#!/usr/bin/env python3
"""

Minimal reproduction of chdb datetime timezone issue.

When reading parquet files with datetime columns, chdb returns timezone-aware
datetimes with system timezone applied, while pandas returns timezone-naive datetimes.

This causes:
1. Value shift (e.g., 00:00:00 becomes 08:00:00+08:00 in Asia/Singapore timezone)
2. Type mismatch (datetime64[ns] vs datetime64[ns, Asia/Singapore])

Expected behavior: chdb should return the same datetime values as pandas.
"""

import chdb
import pandas as pd
import tempfile
import os

print(f"chdb version: {chdb.__version__}")
print(f"pandas version: {pd.__version__}")

# Create test data with datetime column
df = pd.DataFrame({
    'id': [1, 2, 3],
    'dt': pd.to_datetime(['2020-01-01 00:00:00', '2020-01-01 00:00:01', '2020-01-01 00:00:02'])
})

print("\n=== Original DataFrame ===")
print(df)
print(f"dt dtype: {df['dt'].dtype}")

# Save to parquet
with tempfile.NamedTemporaryFile(suffix='.parquet', delete=False) as f:
    parquet_path = f.name
    df.to_parquet(parquet_path)

print(f"\nParquet file: {parquet_path}")

# Read with pandas
print("\n=== pandas.read_parquet() ===")
pd_result = pd.read_parquet(parquet_path)
print(pd_result)
print(f"dt dtype: {pd_result['dt'].dtype}")
print(f"dt values: {pd_result['dt'].tolist()}")

# Read with chdb
print("\n=== chdb.query() ===")
sql = f"SELECT * FROM file('{parquet_path}', 'Parquet')"
chdb_result = chdb.query(sql, output_format='DataFrame')
print(chdb_result)
print(f"dt dtype: {chdb_result['dt'].dtype}")
print(f"dt values: {chdb_result['dt'].tolist()}")

# Compare
print("\n=== Comparison ===")
print(f"Values equal: {(pd_result['dt'] == chdb_result['dt']).all()}")
print(f"Dtypes equal: {pd_result['dt'].dtype == chdb_result['dt'].dtype}")

# Show the actual difference
print("\n=== Value Difference ===")
print(f"pandas[0]: {pd_result['dt'].iloc[0]} (type: {type(pd_result['dt'].iloc[0])})")
print(f"chdb[0]:   {chdb_result['dt'].iloc[0]} (type: {type(chdb_result['dt'].iloc[0])})")

# Workaround: use session_timezone='UTC'
print("\n=== Workaround: session_timezone='UTC' ===")
sql_utc = f"SELECT * FROM file('{parquet_path}', 'Parquet') SETTINGS session_timezone='UTC'"
chdb_utc = chdb.query(sql_utc, output_format='DataFrame')
print(f"dt dtype: {chdb_utc['dt'].dtype}")
print(f"dt values: {chdb_utc['dt'].tolist()}")
print(f"Values match after tz_localize(None): {(pd_result['dt'] == chdb_utc['dt'].dt.tz_localize(None)).all()}")

# Cleanup
os.unlink(parquet_path)

print("\n=== Summary ===")
print("Issue: chdb applies system timezone to datetime values from parquet files,")
print("       causing value shift and type mismatch compared to pandas.")
print("Expected: chdb should return timezone-naive datetimes matching pandas behavior.")

[auxten]

When data passes through chDB's Python() table function, the returned DataFrame
has different dtypes than the original pandas DataFrame:

1. Float columns with NaN: float64 → Float64Dtype() (nullable)
2. Integer columns with None: float64 → Float64Dtype()
3. Datetime columns: datetime64[ns] → datetime64[ns, <timezone>] (adds timezone)

This causes DataFrame.equals() to return False even though the values are
semantically equivalent.

Environment:

• Python: 3.11+
• chdb: v4.0.0b3
• pandas: 2.0+

#!/usr/bin/env python3
"""
Reproduction script for chDB dtype conversion issue.

To run:
    python scripts/chdb_dtype_issue_repro.py
"""

import sys
import numpy as np
import pandas as pd
import chdb

print(f"Python: {sys.version}")
print(f"chdb: {chdb.__version__}")
print(f"pandas: {pd.__version__}")
print(f"numpy: {np.__version__}")
print()

# =============================================================================
# Issue 1: Float64 with NaN converted to nullable Float64Dtype
# =============================================================================
print("=" * 70)
print("Issue 1: Float columns with NaN")
print("=" * 70)

df1 = pd.DataFrame({"a": [1.0, 2.0, np.nan, 4.0]})
print(f"Original dtype: {df1['a'].dtype}")  # float64
print(f"Original values: {df1['a'].tolist()}")

result1 = chdb.query("SELECT * FROM Python(df1)", "DataFrame")
print(f"After chDB dtype: {result1['a'].dtype}")  # Float64
print(f"After chDB values: {result1['a'].tolist()}")

print(f"\nDataFrame.equals(): {df1.equals(result1)}")  # False!
print(f"Values semantically equal: {df1['a'].isna().tolist() == result1['a'].isna().tolist()}")

# =============================================================================
# Issue 2: Integer-like columns with None
# =============================================================================
print()
print("=" * 70)
print("Issue 2: Integer-like columns with None")
print("=" * 70)

df2 = pd.DataFrame({"a": [1, 2, None, 4]})  # pandas converts to float64 due to None
print(f"Original dtype: {df2['a'].dtype}")  # float64
print(f"Original values: {df2['a'].tolist()}")

result2 = chdb.query("SELECT * FROM Python(df2)", "DataFrame")
print(f"After chDB dtype: {result2['a'].dtype}")  # Float64
print(f"After chDB values: {result2['a'].tolist()}")

print(f"\nDataFrame.equals(): {df2.equals(result2)}")  # False!

# =============================================================================
# Issue 3: Datetime columns gain timezone
# =============================================================================
print()
print("=" * 70)
print("Issue 3: Datetime columns gain timezone")
print("=" * 70)

df3 = pd.DataFrame({"dt": pd.to_datetime(["2021-01-01", "2021-01-02", None, "2021-01-04"])})
print(f"Original dtype: {df3['dt'].dtype}")  # datetime64[ns]
print(f"Original values: {df3['dt'].tolist()}")

result3 = chdb.query("SELECT * FROM Python(df3)", "DataFrame")
print(f"After chDB dtype: {result3['dt'].dtype}")  # datetime64[ns, <timezone>]
print(f"After chDB values: {result3['dt'].tolist()}")

print(f"\nDataFrame.equals(): {df3.equals(result3)}")  # False!

# =============================================================================
# Summary
# =============================================================================
print()
print("=" * 70)
print("Summary")
print("=" * 70)
print("""
Expected behavior:
  DataFrame dtypes should be preserved when passing through Python() table function.

Actual behavior:
  1. float64 → Float64 (nullable pandas dtype)
  2. datetime64[ns] → datetime64[ns, <timezone>] (timezone added)

Impact:
  - DataFrame.equals() returns False despite values being semantically equivalent
  - Tests comparing DataStore (which uses chDB) with pandas fail on dtype
  - Users need to manually convert dtypes for comparison

Suggested fix options:
  1. Preserve original dtype when returning from Python() table function
  2. Add a flag to control nullable dtype conversion
  3. Document this as expected behavior if intentional
""")

# =============================================================================
# Workaround demonstration
# =============================================================================
print()
print("=" * 70)
print("Workaround: Manual dtype conversion")
print("=" * 70)

def normalize_chdb_dtypes(df: pd.DataFrame) -> pd.DataFrame:
    """Convert chDB nullable dtypes back to standard pandas dtypes."""
    result = df.copy()
    for col in result.columns:
        dtype_str = str(result[col].dtype)
        if dtype_str == "Float64":
            result[col] = result[col].astype("float64")
        elif dtype_str == "Int64":
            if result[col].isna().any():
                result[col] = result[col].astype("float64")
            else:
                result[col] = result[col].astype("int64")
        elif hasattr(result[col].dtype, "tz") and result[col].dtype.tz is not None:
            result[col] = result[col].dt.tz_localize(None)
    return result

result1_normalized = normalize_chdb_dtypes(result1)
print(f"After normalization dtype: {result1_normalized['a'].dtype}")
print(f"DataFrame.equals() after normalization: {df1.equals(result1_normalized)}")