<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">it is a PITA to get a function index to work right using sqlalchemy.</p>
<p dir="auto">for those that aren't familiar... postgres , oracle and a few other dbs all allow for indexes to be created as a function</p>
<p dir="auto">the idiomatic example is:</p>
<p dir="auto">create unique index user_email_address_uidx on user(lower(email_address));</p>
<p dir="auto">which allows for case-sensitive storing of the email address, and creates a lower version to speed searching and preserve uniqueness</p>
<p dir="auto">the planners automagically use the index on queries like:<br>
select * from user where lower(email_address) = %s</p>
<p dir="auto">i'm not sure what a long-term solution would be, but in the short term... for the two most common operations, my suggestion is to add two new operators to sql.compiler</p>
<p dir="auto">operators.lower_eq : lambda x, y: "lower(%s) = lower(%s)" % (x, y),</p>
<p dir="auto">operators.upper_eq : lambda x, y: "upper(%s) = upper(%s)" % (x, y),</p>
<p dir="auto">this should work too :</p>
<p dir="auto">operators.lower_eq : lambda x, y: "lower(%s) = %s" % (x),</p>
<p dir="auto">operators.upper_eq : lambda x, y: "upper(%s) = %s" % (x),</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">The following query:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="list(session.query(Employee, Employee.company))"><pre class="notranslate"><code class="notranslate">list(session.query(Employee, Employee.company))
</code></pre></div>
<p dir="auto">unexpectedly returns 5-element tuples</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="[e1, 1, u'e1', u'employee', 1),
 (Employee e2, 2, u'e2', u'employee', 1),
 (Employee e3, 3, u'e3', u'employee', 2)]((Employee)"><pre class="notranslate"><code class="notranslate">[e1, 1, u'e1', u'employee', 1),
 (Employee e2, 2, u'e2', u'employee', 1),
 (Employee e3, 3, u'e3', u'employee', 2)]((Employee)
</code></pre></div>
<p dir="auto">IMHO no one expects such a result.</p>
<p dir="auto">Full test case attached.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1328/sqlalchemy-bug-query_Employee_company.py">sqlalchemy-bug-query_Employee_company.py</a> | <a href="../wiki/imported_issue_attachments/1328/interpret-relations-as-query-entities.patch">interpret-relations-as-query-entities.patch</a></p>