<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">There is a very strange behavior with table joins and limit/offset:<br>
example:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *

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

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


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

T1.mapper = mapper(T1, t1, properties={&quot;b&quot;: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 = &quot;B%d&quot;%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"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">results:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">I have expected to see same results in the both cases.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/369/test_error_asc.py">test_error_asc.py</a></p>
<p dir="auto"><strong>Is your feature request related to a problem? Please describe.</strong><br>
While trying to switch from dropbox/sqlalchemy mypy plugin to the official sqlalchemy plugin, I found that in these stubs <code class="notranslate">nullable</code> and <code class="notranslate">primary_key</code> have no effect on the Colum types.</p>
<p dir="auto"><strong>Describe the solution you'd like</strong><br>
Currently the inferred type is always a <code class="notranslate">Union[..., None]</code>, however based on <code class="notranslate">nullable</code> and <code class="notranslate">primary_key</code> properties of the <code class="notranslate">Column</code> we could infer that columns should never contain <code class="notranslate">None</code>.</p>
<p dir="auto"><strong>Describe alternatives you've considered</strong><br>
N/A</p>
<p dir="auto"><strong>Additional context</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="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__ = &quot;a&quot;
    id = Column(Integer, primary_key=True, nullable=False)
    a = Column(Integer, nullable=True)

a = A()
reveal_type(a.id)
reveal_type(a.a)"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">schema</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">types</span> <span class="pl-k">import</span> <span class="pl-v">Integer</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span>.<span class="pl-s1">decl_api</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()

<span class="pl-k">class</span> <span class="pl-v">A</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"a"</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-s1">a</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-s1">a</span> <span class="pl-c1">=</span> <span class="pl-v">A</span>()
<span class="pl-en">reveal_type</span>(<span class="pl-s1">a</span>.<span class="pl-s1">id</span>)
<span class="pl-en">reveal_type</span>(<span class="pl-s1">a</span>.<span class="pl-s1">a</span>)</pre></div>
<p dir="auto">Results from <code class="notranslate">plugins = sqlalchemy.ext.mypy.plugin</code>:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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]'
"><pre class="notranslate"><code class="notranslate">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]'

</code></pre></div>
<p dir="auto">Results from <code class="notranslate">plugins = sqlmypy</code>:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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*'"><pre class="notranslate"><code class="notranslate">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*'
</code></pre></div>
<p dir="auto"><strong>Have a nice day!</strong></p>