https://www.getdaft.io logo
Join Slack
Powered by
:wave: I am querying trino via daft and running i...
# daft-dev
g
👋 I am querying trino via daft and running into some unexpected behavior. I have one query where I left join a table and one of the columns ends up having all null values. when written to parquet, this column ends up with a type of null, but it should have type string. this is unexpected behavior, right?
I only noticed when using 
union_all_by_name
, in which the case the column with type 
null
was duplicated because the matching column in the other dataset had the proper type since it had non-null values
c
Hey @Garrett Weaver, do you have a minimal reproducible example? Or are you able to share the plan?
Also, just want to check, does the column with null type come from the 
join
or from the 
union_all_by_name
?
g
here is a toy example:
Copy code
import sqlite3

import daft
import sqlalchemy

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute(
    """
CREATE TABLE IF NOT EXISTS students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
"""
)
cursor.execute(
    """
CREATE TABLE IF NOT EXISTS courses (
    course_id INTEGER PRIMARY KEY,
    course_name TEXT,
    student_id INTEGER,
    FOREIGN KEY (student_id) REFERENCES students (student_id)
)
"""
)

cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("John Doe", None))
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Jane Smith", None))

cursor.execute(
    "INSERT INTO courses (course_name, student_id) VALUES (?, ?)", ("Mathematics", 1)
)
cursor.execute(
    "INSERT INTO courses (course_name, student_id) VALUES (?, ?)", ("Science", 1)
)
cursor.execute(
    "INSERT INTO courses (course_name, student_id) VALUES (?, ?)", ("Literature", 2)
)

conn.commit()
conn.close()


def create_conn():
    return sqlalchemy.create_engine("sqlite:///school.db").connect()

daft.read_sql(
    "SELECT * FROM courses LEFT JOIN students ON courses.student_id = students.student_id",
    create_conn,
).show()
Screenshot 2025-06-02 at 2.31.59 PM.png
just want to check, does the column with null type come from the 
join
or from the 
union_all_by_name
?
from the join as in the toy example
c
I see, I tried doing a 
"SELECT * FROM students"
in this example, and the age column is also 
NULL
You can try providing a schema 'hint' into read_sql, e.g.
Copy code
daft.read_sql(
    "SELECT * FROM courses LEFT JOIN students ON courses.student_id = students.student_id",
    create_conn,
    schema={
        "age": daft.DataType.int64(),
    },
).show()
g
I see, is it possible for daft to use the types from resulting query in the source system, I am guessing type is being inferred right now?
only because in more complicated queries with many columns it may get cumbersome to list out all the expected types, but I can try that for now
c
Yeah it's because the types are being inferred. There's two parameters in read_sql that can help: 
infer_schema
and 
schema
, by default 
infer_schema
is true and 
schema
will act as a hinting mechanism, so you don't need to list out all the expected types, only the problematic ones. But if you set 
infer_schema=False
, then 
schema
must be passed in and will act as the definitive schema, bypassing schema inference
4 Views
Open in Slack
PreviousNext
Powered by