<p dir="auto">Hell, I have backend storage on Apache Phoenix:<br>
<a href="https://phoenix.apache.org/language/index.html" rel="nofollow">https://phoenix.apache.org/language/index.html</a></p>
<p dir="auto">How hard it could be to exten sqlalchemy for support this?<br>
Main difference I see is it uses UPSERT instead of INSERT/UPDATE, otherwise it is very close to any other sql engine.</p>
<p dir="auto">Any hints welcomed!</p>
<p dir="auto"><strong>Migrated issue, originally created by jvanasco (<a href="https://github.com/jvanasco">@jvanasco</a>)</strong></p>
<p dir="auto">This affects <code class="notranslate">subqueryload</code>, not <code class="notranslate">joinedload</code>.</p>
<p dir="auto">It is related to <a href="https://bitbucket.org/zzzeek/sqlalchemy/issues/3064/warn-when-limit-offset-used-w-o-order-by" rel="nofollow">https://bitbucket.org/zzzeek/sqlalchemy/issues/3064/warn-when-limit-offset-used-w-o-order-by</a></p>
<p dir="auto">Assume a simple relationship of Foo to Bar:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class Bar(Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    id_bar = Column(Integer, ForeignKey(&quot;bar.id&quot;), nullable=False)

    bar = sqlalchemy.orm.relationship(&quot;Bar&quot;)"><pre class="notranslate"><code class="notranslate">class Bar(Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    id_bar = Column(Integer, ForeignKey("bar.id"), nullable=False)

    bar = sqlalchemy.orm.relationship("Bar")
</code></pre></div>
<p dir="auto">The following query will yield unpredictable results for the 'bar' relationship, even when an id_bar is present:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="foos = s.query(Foo)\
        .options(subqueryload('bar'))\
        .limit(20)\
        .offset(10)\
        .all()"><pre class="notranslate"><code class="notranslate">foos = s.query(Foo)\
        .options(subqueryload('bar'))\
        .limit(20)\
        .offset(10)\
        .all()
</code></pre></div>
<p dir="auto">The issue is that the subqueryload logic generates a selection like such (reformatted for emphasis):</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="     SELECT bar.* FROM
 (SELECT DISTINCT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset )
      as anon_1 JOIN bar on bar.id = anon_1.foo_id_bar ORDER BY anon_1.foo_id_bar;"><pre class="notranslate"><code class="notranslate">     SELECT bar.* FROM
 (SELECT DISTINCT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset )
      as anon_1 JOIN bar on bar.id = anon_1.foo_id_bar ORDER BY anon_1.foo_id_bar;
</code></pre></div>
<p dir="auto">Because that inner select for the subquery is performing a DISTINCT, the LIMIT and OFFSET apply to the 'distinct' -- not overall -- list.</p>
<p dir="auto">one example scenario that will fail: the offset is greater than the total number of possible values.  if every foo is assigned an id_bar of only 2 values (1, 2), paginating an offset of 2 of greater will always return NULL and no <code class="notranslate">bar</code> relation will be set.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 1. make a bunch of Bars.  Let's do 20!
for i in range(0, 20):
    b = Bar(id=i)
    s.add(b)
    s.flush()
    s.commit()

# 2, okay, now make 100 foos... but only use 2 `bars`.
for i in range(0, 100):
    if i &lt; 50:
        id_bar = 1
    else:
        id_bar = 2
    f = Foo(id=i, id_bar=id_bar)
    s.add(f)
    f.id
    s.flush()
    s.commit()

# run the above select , and see the results:
for f in foos:
    print &quot;--&quot;
    print &quot;examing foo.%s&quot; % f.id
    print f.id_bar
    print f.bar

# you'll notice that `bar` will be missing even when id_bar is set"><pre class="notranslate"><code class="notranslate"># 1. make a bunch of Bars.  Let's do 20!
for i in range(0, 20):
    b = Bar(id=i)
    s.add(b)
    s.flush()
    s.commit()

# 2, okay, now make 100 foos... but only use 2 `bars`.
for i in range(0, 100):
    if i &lt; 50:
        id_bar = 1
    else:
        id_bar = 2
    f = Foo(id=i, id_bar=id_bar)
    s.add(f)
    f.id
    s.flush()
    s.commit()

# run the above select , and see the results:
for f in foos:
    print "--"
    print "examing foo.%s" % f.id
    print f.id_bar
    print f.bar

# you'll notice that `bar` will be missing even when id_bar is set
</code></pre></div>
<p dir="auto">another example scenario is when you have a lot of repetition within the dataset.  e.g. if there are fewer unique BARs than FOOs, you'll encounter errors when you paginate closer to the end.    i haven't been able to recreate a reliable dataset for this, but it happens in my production dataset when I paginate 80% through.</p>
<p dir="auto">Then to complicate further, this sort of becomes the other issue -- there is no implicit <code class="notranslate">order_by</code> on the inner query unless an <code class="notranslate">order_by</code> is explicitly applied to the outer query.</p>
<p dir="auto"><code class="notranslate">order_by(Foo.id.asc())</code> will create this inner query:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="(SELECT DISTINCT foo.id_bar AS foo_id_bar, foo.id AS foo_id 
 FROM foo ORDER BY foo.id ASC
 LIMIT ? OFFSET ?)"><pre class="notranslate"><code class="notranslate">(SELECT DISTINCT foo.id_bar AS foo_id_bar, foo.id AS foo_id 
 FROM foo ORDER BY foo.id ASC
 LIMIT ? OFFSET ?)
</code></pre></div>
<p dir="auto">however, no order_by will result in the inner query</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="(SELECT DISTINCT foo.id_bar AS foo_id_bar 
 FROM foo
 LIMIT ? OFFSET ?)"><pre class="notranslate"><code class="notranslate">(SELECT DISTINCT foo.id_bar AS foo_id_bar 
 FROM foo
 LIMIT ? OFFSET ?)
</code></pre></div>
<p dir="auto">there is no reason for the db to guarantee these results will be for the same ids returned in the previous query (the non-subqueryload 'master' query that sqlalchemy just issued).</p>
<p dir="auto">The other ticket talked about a more specific use-case with depth, but I'll address the larger and less detailed concept - in the absence of an explicit order_by, sqlalchemy appears to issue an implicit order_by on the primary key -- however does not extend that implicit order_by to the inner query.</p>
<p dir="auto">the fixes I suggest are:</p>
<ol dir="auto">
<li>
<p dir="auto">migrate this into 2 queries -- an inner that paginates and an outer that applies distinct (or group_by, which can be much faster for this in certain scenarios)</p>
<ul dir="auto">
<li>SELECT DISTINCT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset</li>
</ul>
<ul dir="auto">
<li>SELECT DISTINCT foo_id_bar FROM (SELECT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset) AS foo</li>
</ul>
</li>
<li>
<p dir="auto">in the absence of no order_by issued with a subquery, just apply the same implicit primary-key ordering to the inner query that was used on the outer query.</p>
</li>
</ol>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3650/test_orm_subqueryload.py">test_orm_subqueryload.py</a> | <a href="../wiki/imported_issue_attachments/3650/test_orm_subqueryload.1.py">test_orm_subqueryload.1.py</a></p>