SNOW-1812881: Division broken

[jochenott]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using? 3.12 (but irrelevant)
2. What operating system and processor architecture are you using? macOS-14.7.1-arm64-arm-64bit (but irrelevant)
3. What are the component versions in the environment (pip freeze)? The relevant ones are: sqlalchemy 2.0.36 and snowflake-sqlalchemy 1.6.1
4. What did you do? Create a statement like a / b and it does not compile correctly.

from snowflake.sqlalchemy.snowdialect import SnowflakeDialect
from sqlalchemy.sql.compiler import SQLCompiler
import sqlalchemy as sa

dialect = SnowflakeDialect()

stmt = sa.Column("a") / sa.func.sqrt(sa.Column("b"))
print(str(SQLCompiler(dialect, stmt)))

This prints a / CAST(sqrt(b) AS NUMERIC). However, the cast in the denominator should not happen, and this cast leads to wrong results (!).

The fix is to set div_is_floordiv = False in SnowflakeDialect, see e.g. this as a test:

from snowflake.sqlalchemy.snowdialect import SnowflakeDialect
from sqlalchemy.sql.compiler import SQLCompiler
import sqlalchemy as sa

class FixedDialect(SnowflakeDialect):
   div_is_floordiv = False

dialect = FixedDialect()

stmt = sa.Column("a") / sa.func.sqrt(sa.Column("b"))
print(str(SQLCompiler(dialect, stmt)))

BTW: fixing div_is_floordiv also fixes the integer division operator, i.e. //. Right now, this is also wrong:

stmt = sa.Column("a", sa.Integer()) // sa.Column("b", sa.Integer())
print(str(SQLCompiler(dialect, stmt)))

currently prints "a / b". Using FixedDialect from above, it correctly prints FLOOR(a / b).

[sfc-gh-dszmolka]

hi - thank you for submitting this issue with all these details and the possible solution too, really appreciated! we'll look