Issue 1:
Migrated issue, originally created by Anonymous

There is a very strange behavior with table joins and limit/offset:

example:

from sqlalchemy import *

db = create_engine("mysql://tezzt:tezzt@localhost/tezzt")
m = BoundMetaData(db)
db.engine.echo = True

class T1(object):pass
class T2(object):pass

t1 = Table("a", m, Column("id", Integer, primary_key=True), Column("b_id", Integer, ForeignKey("b.id")))
t2 = Table("b", m, Column("id", Integer, primary_key=True), Column("name", String(10)))

T1.mapper = mapper(T1, t1, properties={"b":relation(T2, lazy=False, order_by=None)})
T2.mapper = mapper(T2, t2)

m.drop_all()
m.create_all()

session = create_session()

for a in xrange(1,10):
aa = T1()
bb = T2()
bb.name = "B%d"%a
aa.b = bb
session.save(aa)
session.save(bb)

session.flush()
session.clear()

for a in session.query(T1).select(order_by=[T2.c.name](T2.c.name), limit=10, offset=0):
print a.id, a.b.name

for a in session.query(T1).select(order_by=[asc(T2.c.name)](asc(T2.c.name)), limit=10, offset=0):
print a.id, a.b.name

results:

2006-11-15 17:20:26,450 INFO sqlalchemy.engine.base.Engine.0x..d4 SELECT b_1b29.id AS b_1b29_id, b_1b29.name AS b_1b29_name, a.id AS a_id, a.b_id AS a_b_id
FROM (SELECT a.id AS a_id, b.name AS b_name
FROM a, b ORDER BY b.name
LIMIT 10 OFFSET 0) AS tbl_row_count, a LEFT OUTER JOIN b AS b_1b29 ON b_1b29.id = a.b_id
WHERE a.id = tbl_row_count.a_id ORDER BY b_1b29.name
2006-11-15 17:20:26,452 INFO sqlalchemy.engine.base.Engine.0x..d4 [B1
2 B2
3 B3
4 B4
5 B5
6 B6
7 B7
8 B8
9 B9
2006-11-15 17:20:26,512 INFO sqlalchemy.engine.base.Engine.0x..d4 SELECT b_1b29.id AS b_1b29_id, b_1b29.name AS b_1b29_name, a.id AS a_id, a.b_id AS a_b_id
FROM (SELECT a.id AS a_id, b.name AS b_name
FROM a, b ORDER BY b.name ASC
LIMIT 10 OFFSET 0) AS tbl_row_count, a LEFT OUTER JOIN b AS b_1b29 ON b_1b29.id = a.b_id
WHERE a.id = tbl_row_count.a_id ORDER BY tbl_row_count.b_name ASC
2006-11-15 17:20:26,514 INFO sqlalchemy.engine.base.Engine.0x..d4 [](]
1)
3 B3
6 B6
9 B9
2 B2
5 B5
8 B8
1 B1
4 B4
7 B7

I have expected to see same results in the both cases.

Attachments: test_error_asc.py

Issue 2:
Is your feature request related to a problem? Please describe.

While trying to switch from dropbox/sqlalchemy mypy plugin to the official sqlalchemy plugin, I found that in these stubs nullable and primary_key have no effect on the Colum types.

Describe the solution you'd like

Currently the inferred type is always a Union[..., None], however based on nullable and primary_key properties of the Column we could infer that columns should never contain None.

Describe alternatives you've considered

N/A

Additional context

from sqlalchemy.schema import Column
from sqlalchemy.types import Integer
from sqlalchemy.orm.decl_api import declarative_base

Base = declarative_base()

class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True, nullable=False)
a = Column(Integer, nullable=True)

a = A()
reveal_type(a.id)
reveal_type(a.a)

Results from plugins = sqlalchemy.ext.mypy.plugin:

test3.py:14: note: Revealed type is 'Union[builtins.int, None]'
test3.py:15: note: Revealed type is 'Union[builtins.int, None]'
test3.py:16: note: Revealed type is 'Union[builtins.int, None]'

Results from plugins = sqlmypy:

test3.py:14: note: Revealed type is 'builtins.int*'
test3.py:15: note: Revealed type is 'Union[builtins.int*, None]'
test3.py:16: note: Revealed type is 'builtins.int*'

Have a nice day!