Problem with infinity timestamps in SQLAlchemy?

README.rst

Problem with infinity timestamps in SQLAlchemy?

I've noticed missing entries in table relationships which are joined using timestamp columns using "infinity" timestamps.

The files below demonstrate this. The example is stipped down to the bare minimum while still reproducing the issue.

Looking at the table data, when joining using name and ts we should get two rows (as seen in the select.sql file below).

But, when running this in Python we run into an issue: The infinity timestamp gets converted to a datetime instance using datetime(1, 1, 1, 0, 0). When accessing the relation on the entity, SQLAlchemy will emit a query using this value instead of -infinity, so the relation will not return anything.

app.py

import sys

from sqlalchemy import Column, Integer, String, __version__, create_engine
from sqlalchemy.dialects.postgresql import TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine('postgresql://malbert@/tsbug', echo=True)
Base = declarative_base(bind=engine)


class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    ts = Column(TIMESTAMP)

    bars = relationship(
        "Bar",
        primaryjoin='''and_(
            Foo.name == foreign(Bar.name),
            Foo.ts == foreign(Bar.ts)
        )'''
    )


class Bar(Base):
    __tablename__ = 'bar'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    ts = Column(TIMESTAMP)


Session = sessionmaker(bind=engine)
session = Session()

print('-- Postgres Version: %r' % session.execute('SELECT version()').scalar())
print('-- Python Version: %s' % sys.version)
print('-- SQLAlchemy Version: %s' % __version__)
print(' Query Output '.center(80, '-'))

for row in session.query(Foo):
    print(row.bars)

createdb.sql

BEGIN;

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ts timestamp
);

CREATE TABLE bar (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ts timestamp
);

INSERT INTO foo (name, ts) VALUES
    ('name1', '2010-01-01'),
    ('name2', '2010-01-02'),
    ('name2', '2010-01-03'),
    ('name2', '-infinity')
    ;

INSERT INTO bar (name, ts) VALUES
    ('name2', '2010-01-01'),
    ('name2', '2010-01-02'),
    ('name2', '-infinity')
    ;

COMMIT;

output.txt

2017-10-26 15:09:13,194 INFO sqlalchemy.engine.base.Engine select version()
2017-10-26 15:09:13,194 INFO sqlalchemy.engine.base.Engine {}
2017-10-26 15:09:13,197 INFO sqlalchemy.engine.base.Engine select current_schema()
2017-10-26 15:09:13,197 INFO sqlalchemy.engine.base.Engine {}
2017-10-26 15:09:13,199 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-10-26 15:09:13,199 INFO sqlalchemy.engine.base.Engine {}
2017-10-26 15:09:13,200 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-10-26 15:09:13,200 INFO sqlalchemy.engine.base.Engine {}
2017-10-26 15:09:13,201 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2017-10-26 15:09:13,201 INFO sqlalchemy.engine.base.Engine {}
2017-10-26 15:09:13,203 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-10-26 15:09:13,204 INFO sqlalchemy.engine.base.Engine SELECT version()
2017-10-26 15:09:13,204 INFO sqlalchemy.engine.base.Engine {}
-- Postgres Version: 'PostgreSQL 9.5.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit'
-- Python Version: 3.5.2 (default, Nov 17 2016, 17:05:23)
[GCC 5.4.0 20160609]
-- SQLAlchemy Version: 1.0.12
--------------------------------- Query Output ---------------------------------
2017-10-26 15:09:13,213 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id, foo.name AS foo_name, foo.ts AS foo_ts
FROM foo
2017-10-26 15:09:13,214 INFO sqlalchemy.engine.base.Engine {}
2017-10-26 15:09:13,218 INFO sqlalchemy.engine.base.Engine SELECT bar.id AS bar_id, bar.name AS bar_name, bar.ts AS bar_ts
FROM bar
WHERE %(param_1)s = bar.name AND %(param_2)s = bar.ts
2017-10-26 15:09:13,219 INFO sqlalchemy.engine.base.Engine {'param_2': datetime.datetime(2010, 1, 1, 0, 0), 'param_1': 'name1'}
[]
2017-10-26 15:09:13,221 INFO sqlalchemy.engine.base.Engine SELECT bar.id AS bar_id, bar.name AS bar_name, bar.ts AS bar_ts
FROM bar
WHERE %(param_1)s = bar.name AND %(param_2)s = bar.ts
2017-10-26 15:09:13,221 INFO sqlalchemy.engine.base.Engine {'param_2': datetime.datetime(2010, 1, 2, 0, 0), 'param_1': 'name2'}
[<__main__.Bar object at 0x7fd6d96fe710>]
2017-10-26 15:09:13,223 INFO sqlalchemy.engine.base.Engine SELECT bar.id AS bar_id, bar.name AS bar_name, bar.ts AS bar_ts
FROM bar
WHERE %(param_1)s = bar.name AND %(param_2) = bar.ts
2017-10-26 15:09:13,223 INFO sqlalchemy.engine.base.Engine {'param_2': datetime.datetime(2010, 1, 3, 0, 0), 'param_1': 'name2'}
[]
2017-10-26 15:09:13,227 INFO sqlalchemy.engine.base.Engine SELECT bar.id AS bar_id, bar.name AS bar_name, bar.ts AS bar_ts
FROM bar
WHERE %(param_1)s = bar.name AND %(param_2)s = bar.ts
2017-10-26 15:09:13,227 INFO sqlalchemy.engine.base.Engine {'param_2': datetime.datetime(1, 1, 1, 0, 0), 'param_1': 'name2'}
[]

select.sql

SELECT
    foo.*,
    bar.*
FROM foo
JOIN bar ON (foo.name = bar.name and foo.ts = bar.ts);

-- --- Expected Output ------------------------------------------------------
--
-- ┌────┬───────┬─────────────────────┬────┬───────┬─────────────────────┐
-- │ id │ name  │         ts          │ id │ name  │         ts          │
-- ├────┼───────┼─────────────────────┼────┼───────┼─────────────────────┤
-- │  4 │ name2 │ -infinity           │  3 │ name2 │ -infinity           │
-- │  2 │ name2 │ 2010-01-02 00:00:00 │  2 │ name2 │ 2010-01-02 00:00:00 │
-- └────┴───────┴─────────────────────┴────┴───────┴─────────────────────┘
-- (2 rows)