tortoise / tortoise/tortoise-orm

SQlite: filter on aggregate uses invalid datetime serialization

Open
#572 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Describe the bug
Similar to #130. The generated SQLite query uses a serialized datetime format with a 'T' separator, creating an incorrect datetime predicate behavior. I could only reproduce with an aggregate function though, which is why #130 did not catch it I suppose. See below for a minimal reproduction code. All packages are "latest PyPi", see the toml lock file for exact versions.

To Reproduce

You can run this live on Repl.it.

from tortoise import run_async, fields, Tortoise
from tortoise.models import Model
from tortoise.functions import Max
import datetime


class Foo(Model):
    id = fields.IntField(pk=True)
    date = fields.DatetimeField()


async def test():
    await Tortoise.init(db_url="sqlite://:memory:", modules={"lol": ["__main__"]})
    await Tortoise.generate_schemas()

    x = datetime.datetime(2020, 1, 1)

    qs = (Foo
        .annotate(max=Max("date"))
        .filter(date__lt=x, max__lt=x))

    print(qs.sql())


if __name__ == "__main__":
    run_async(test())

# SELECT "date","id",MAX("date") "max" FROM "foo" 
# WHERE "date"<'2020-01-01 00:00:00' GROUP BY "id" 
# HAVING MAX("date")<'2020-01-01T00:00:00'
#                               ^ onoes :(

Expected behavior

HAVING MAX("date")<'2020-01-01 00:00:00'

Additional context

None required.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the provided SQLite in-memory reproduction and inspect the query-generation path that serializes datetime values for aggregate filters and HAVING clauses. Done means the generated HAVING predicate uses the same space-separated datetime format as the ordinary filter, with regression coverage for this reproduction.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlite
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.