datachain: Column type inference fails after using mutate to subtract two columns

Description

I am creating two columns with cosine_distance and then I try to create a new column by subtracting one from the other. The code looks like this:

from sqlalchemy import func

embeddings_differences = (
    DataChain
    .from_dataset("embeddings")
    .mutate(
        query_sim_new = 1 - cosine_distance(C.document.embeddings_new, embedded_query_new),
        query_sim_old = 1 - cosine_distance(C.document.embeddings_old, embedded_query_old),
        )
    .mutate(abs_difference = func.abs(C.query_sim_old - C.query_sim_new))
    .save("embeddings-differences")
)

After running this I get this error:

CompileError: (in table 'ds_embeddings-differences_1', column 'abs_difference'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?

This fails even if I remove func.abs, i.e. when this is just an ordinary subtraction of the two columns. If I try to save the dataset after the first .mutate, load it and then save it as a different dataset after calling the second .mutate it fails as well.

If I simply try to show the results with .show or convert it to pandas, it works as expected.

How to reproduce:

Run this notebook and see the output of the cell with the code above.

Version Info

0.2.12.dev36+g0b3cf72
Python 3.12.4

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Reactions: 1
  • Comments: 16 (16 by maintainers)

Most upvoted comments

@ilongin thank you! This is a minimum reproducible example:

def test_ddd():
    fib = [1, 1, 2, 3, 5, 8]
    chain = DataChain.from_values(num=fib)

    ch = chain.mutate(add2=Column("num") - 2)
    ch = ch.save()
    assert list(ch.collect("add2")) == [x - 2 for x in fib]

@tibor-mach created new issue for order_by after mutate … looks like this wasn’t working even before DataChain (when we had only DatasetQuery) but no one have ran into it before you now. https://github.com/iterative/datachain/issues/166

@ilongin the workaround does seem to work but it also creates some new issues, in my example above, it breaks the .order_by method with the following error. It seems it thinks that the value is textual even though I cast it as Float.

CompileError: Can't resolve label reference for ORDER BY / GROUP BY / DISTINCT etc. Textual SQL expression 'abs_difference' should be explicitly declared as text('abs_difference')

The code is the same as above, only that one cell is rewritten like this:

from datachain.sql.types import Float
from sqlalchemy import cast



embeddings_differences = (
    DataChain
    .from_dataset("embeddings")
    .mutate(
        query_sim_new = 1 - cosine_distance(C.document.embeddings_new, embedded_query_new),
        query_sim_old = 1 - cosine_distance(C.document.embeddings_old, embedded_query_old),
        )
    .mutate(abs_difference = cast(func.abs(C.query_sim_old - C.query_sim_new), Float))
    .filter(C.abs_difference > 0.1)
    .order_by("abs_difference", descending=True)
    .save("embeddings-differences")
)

@ilongin hmm, this is kind of awkward, particularly with these simple operations it is kind of a hassle to have to manually cast the output. I wonder if we couldn’t just include our custom automatic inference.

But I will use the workaround for now ,thanks!

@tibor-mach Added PR https://github.com/iterative/datachain/pull/152

This PR just adds better error handling for this as I don’t think we can fix it in better way. The workaround, and only solution I think for now, is to manually cast to specific type when type cannot be infered, like in subtract example. In theory we could have complex logic to infer type ourselves if sqlalchemy fails to do so, but I have a feeling it would be too complicated as there is a reason sqlalchemy doesn’t do it.

Until we have cast function in DataChain (PR is merged) you can use it from SQLAlchemy directly, e.g from sqlalchemy import cast. Examples of how to cast to specific types are in the PR

The main cause for this is that subtract (and division) binary operations ends up as NullType() in SQLAlchemy for some reason

Example:

from sqlalchemy import column
print((column("x") + 5).type)   # Integer()
print((column("x") * 5).type)   # Integer()
print((column("x") - 5).type)   # NullType()
print((column("x") / 5).type)   # NullType()

Trying to investigate why is this the case and how can be fixed