<p dir="auto"><strong>Migrated issue, originally created by Serge Koval (<a href="https://github.com/joes">@joes</a>)</strong></p>
<p dir="auto">I'm working on query precompilation layer and stumbled upon a bug: PickleType column does not automatically unpickle data received from the database.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="[10]: m = db.session.query(Report).from_statement('SELECT * FROM reports LIMIT 1').all()

[11]: m[0].pickled_data
?}q(UurlqX634/654/aqgvppyv.jpgUserverqUcheetahUmodeqKUsizeqM?Du."><pre class="notranslate">[<span class="pl-c1">10</span>]: <span class="pl-s1">m</span> <span class="pl-c1">=</span> <span class="pl-s1">db</span>.<span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Report</span>).<span class="pl-en">from_statement</span>(<span class="pl-s">'SELECT * FROM reports LIMIT 1'</span>).<span class="pl-en">all</span>()

[<span class="pl-c1">11</span>]: <span class="pl-s1">m</span>[<span class="pl-c1">0</span>].<span class="pl-s1">pickled_data</span>
?}<span class="pl-s1">q</span>(<span class="pl-v">UurlqX634</span><span class="pl-c1">/</span><span class="pl-c1">654</span><span class="pl-c1">/</span><span class="pl-s1">aqgvppyv</span>.<span class="pl-s1">jpgUserverqUcheetahUmodeqKUsizeqM</span>?<span class="pl-v">Du</span>.</pre></div>
<p dir="auto">Just in case, using PostgreSQL.</p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">tons of use cases for TextAsFrom that should be intuitive that don't work.    When we make a TextAsFrom with a positional set of columns, those columns should be welded to it.  The statement should be able to work in any ORM context flawlessly, no reliance on names matching up should be needed as we do not target on name anymore:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship(&quot;B&quot;)


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

e = create_engine(&quot;sqlite://&quot;, echo='debug')
Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(bs=[B(), B()]),
    A(bs=[B(), B()])
])
s.commit()

b1 = aliased(B)

# works
sql = &quot;select a.id, ba.id as bid, ba.a_id from &quot;\
    &quot;a left outer join b as ba on a.id=ba.a_id&quot;

# fails.  why?
# sql = &quot;select a.id as aid, ba.id as bid, ba.a_id from &quot;\
#    &quot;a left outer join b as ba on a.id=ba.a_id&quot;

# fails. why?
# sql = &quot;select a.id as aid, ba.id, ba.a_id from &quot;\
#    &quot;a left outer join b as ba on a.id=ba.a_id&quot;


# are we relying upon names somehow?  we should be able to
# be 100% positional now
t = text(sql).columns(A.id, b1.id, b1.a_id)

q = s.query(A).from_statement(t).options(contains_eager(A.bs, alias=b1))

for a in q:
    print a.id
    print a, a.bs

# forget about if we try a1 = aliased(A) also..."><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B")


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(bs=[B(), B()]),
    A(bs=[B(), B()])
])
s.commit()

b1 = aliased(B)

# works
sql = "select a.id, ba.id as bid, ba.a_id from "\
    "a left outer join b as ba on a.id=ba.a_id"

# fails.  why?
# sql = "select a.id as aid, ba.id as bid, ba.a_id from "\
#    "a left outer join b as ba on a.id=ba.a_id"

# fails. why?
# sql = "select a.id as aid, ba.id, ba.a_id from "\
#    "a left outer join b as ba on a.id=ba.a_id"


# are we relying upon names somehow?  we should be able to
# be 100% positional now
t = text(sql).columns(A.id, b1.id, b1.a_id)

q = s.query(A).from_statement(t).options(contains_eager(A.bs, alias=b1))

for a in q:
    print a.id
    print a, a.bs

# forget about if we try a1 = aliased(A) also...
</code></pre></div>
<p dir="auto">I've added docs in <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/7d268d4bcb5e6205d05ace0ea2fd94895bc2efed/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/7d268d4bcb5e6205d05ace0ea2fd94895bc2efed"><tt>7d268d4</tt></a> and <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/4f51fa947ffa0cadeab7ad7dcab649ce3fbcf970/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/4f51fa947ffa0cadeab7ad7dcab649ce3fbcf970"><tt>4f51fa9</tt></a> that we may even have to dial back for versions that don't have this feature.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3501/3501.patch">3501.patch</a></p>