bitemyapp / bitemyapp/esqueleto

Arithmetic operators don't represent SQL overloading

Open
#134 5 comments 1 reaction 0 assignees View on GitHub
Dominant language
Haskell
Stars
399
Forks
107
Avg merge
1d 17h
Merged PRs (30d)
1

Description

In SQL (Postgres, at least), arithmetic operators such `+`, `-`, `*`, and `/` are overloaded for date/time types. for example, the expression `timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00` results in `interval '1 day 15:00:00'` (see https://www.postgresql.org/docs/9.2/interactive/functions-datetime.html).

However, Esqueleto defines `(-.) :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a)`, which will generate the SQL needed to subtract timestamps, but gives the result the wrong type. Letting these operators be applied to just any type is incorrect anyway (e.g. you can't subtract strings in SQL), and forcing the argument and result types to all be the same is both unsound _and_ prevents valid queries from being written.

I'm not sure how this mess would best be represented in Esqueleto, but perhaps a set of additional operators, including one for `(TimeLike a, TimeLike b, IntervalLike c) => SqlExpr (Value a) -> SqlExpr (Value b) -> SqlExpr (Value c)` amongst several others. I'm not sure how many types would inhabit TimeLike, IntervalLike, etc. This isn't sounding super promising.

Also open to gross hacks that let me do the unsafe cast or something.

Contributor guide

No contributing guide indexed for this repository

Research direction

The payload names no files or tests; start by locating Esqueleto's definitions of (-.) and the related arithmetic operators, then compare their generated SQL and Haskell result types with PostgreSQL's date/time operator behavior. Done means valid date/time arithmetic can be expressed without an incorrect result type, while unsupported arbitrary operand combinations are not accepted.

Written by the indexing model from the issue text.

Assessment

Tech stack
haskell
Domain
databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.