<p dir="auto">Not sure if Bug or question, but at least unexpected behavior.</p>
<p dir="auto"><strong>Describe the bug</strong><br>
When querying duplicated results of the type <code class="notranslate">(User, int, datetime)</code>, an attempted deduplication using <code class="notranslate">.unique()</code> did not have any effect, and returned multiple rows containing the exact same values.</p>
<p dir="auto">The reason is that the default identification strategy amounts to an element-wise projection using <code class="notranslate">id</code>, and not <code class="notranslate">hash</code>.<br>
This causes the <code class="notranslate">int</code> and the <code class="notranslate">datetime</code> columns of two rows – even though having the same value – to be recognized as different.</p>
<p dir="auto">This is in contradiction to the “comparison by value“ assumption that I personally have of such a deduplication, and also in contradiction to what the <a href="https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Result.unique" rel="nofollow">docs</a> state:</p>
<blockquote>
<p dir="auto">The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple.</p>
</blockquote>
<p dir="auto">In particular, this issue is “resolved” by using an identity strategy, i.e. <code class="notranslate">.unique(lambda row: row)</code> .<br>
This works because the result of the strategy is collected in a set, whose notion of equality is based on <code class="notranslate">hash</code>.</p>
<p dir="auto">If this is not considered a bug, it should be a warning explicitly stated in the aforelinked section in the docs.</p>
<p dir="auto"><strong>To Reproduce</strong><br>
<em>(I'm really sorry, but it's a bit late in the night, I can create an MCVE later if required.)</em></p>
<p dir="auto">I can provide context from a debugging session. I'm breaking in <code class="notranslate">ResultInternal._allrows</code> in <a href="https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_4_18/lib/sqlalchemy/engine/result.py#L409">this line</a>.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; type(self)
&lt;class 'sqlalchemy.engine.result.ChunkedIteratorResult'&gt;
&gt;&gt;&gt; self._metadata._unique_filters
[&lt;built-in function id&gt;, &lt;built-in function id&gt;, &lt;built-in function id&gt;]
&gt;&gt;&gt; [tuple(id(el) for el in r) for r in made_rows[:4]]
[(140281848273408, 140281848246224, 140281848184384),
(140281848273408, 140281848267056, 140281848184480),
(140281848273552, 140281848267344, 140281848184672),
(140281848273552, 140281848267632, 140281848270896)]
&gt;&gt;&gt; [tuple(hash(el) for el in r) for r in made_rows[:4]]
[(8767615517088, 13812, -7323339362369131990),
(8767615517088, 13812, -7323339362369131990),
(8767615517097, 13815, -4473479583634129130),
(8767615517097, 13815, -4473479583634129130)]
&gt;&gt;&gt; [tuple(type(el) for el in r) for r in made_rows[:4]]
[(&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;),
(&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;),
 (&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;), 
(&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;)]"><pre class="notranslate"><code class="notranslate">&gt;&gt;&gt; type(self)
&lt;class 'sqlalchemy.engine.result.ChunkedIteratorResult'&gt;
&gt;&gt;&gt; self._metadata._unique_filters
[&lt;built-in function id&gt;, &lt;built-in function id&gt;, &lt;built-in function id&gt;]
&gt;&gt;&gt; [tuple(id(el) for el in r) for r in made_rows[:4]]
[(140281848273408, 140281848246224, 140281848184384),
(140281848273408, 140281848267056, 140281848184480),
(140281848273552, 140281848267344, 140281848184672),
(140281848273552, 140281848267632, 140281848270896)]
&gt;&gt;&gt; [tuple(hash(el) for el in r) for r in made_rows[:4]]
[(8767615517088, 13812, -7323339362369131990),
(8767615517088, 13812, -7323339362369131990),
(8767615517097, 13815, -4473479583634129130),
(8767615517097, 13815, -4473479583634129130)]
&gt;&gt;&gt; [tuple(type(el) for el in r) for r in made_rows[:4]]
[(&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;),
(&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;),
 (&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;), 
(&lt;class 'pycroft.model.user.User'&gt;, &lt;class 'int'&gt;, &lt;class 'datetime.datetime'&gt;)]
</code></pre></div>
<p dir="auto">Feel free to ask for other details.</p>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS:</li>
<li>Python: 3.9.1</li>
<li>SQLAlchemy:1.4.18</li>
<li>Database: Postgres</li>
</ul>
<p dir="auto"><strong>Have a nice <del>day</del> night!</strong></p>
<p dir="auto"><strong>Migrated issue, originally created by jvanasco (<a href="https://github.com/jvanasco">@jvanasco</a>)</strong></p>
<p dir="auto">I'm requesting that ScopedSession.session_factory become a public API method.</p>
<p dir="auto">This is currently implemented, but hidden and undocumented.</p>
<p dir="auto">Rationale / use-case:</p>
<p dir="auto">We have a transaction-safe PostgreSQL application.  There exists some tasks that require a separate database connection to operate independent of the transaction (generally things like logging various bits of information, such as AmazonS3 Activity, Failures due to Ratelimits, some error logging).</p>
<p dir="auto">This is often handled with a separate database setup, but that was a bit painful to handle in a situation... and after a bit of poking around on the internals, it seems that this works:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="dbSessionAlternate = dbSession.session_factory()
dbSessionAutocommit = dbSession.session_factory(autocommit = True)"><pre class="notranslate"><code class="notranslate">dbSessionAlternate = dbSession.session_factory()
dbSessionAutocommit = dbSession.session_factory(autocommit = True)
</code></pre></div>
<p dir="auto">Originally I hoped that this would work, but they don't:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="dbSessionA = dbSession()
dbSessionB = dbSession(autocommit = True)"><pre class="notranslate"><code class="notranslate">dbSessionA = dbSession()
dbSessionB = dbSession(autocommit = True)
</code></pre></div>
<p dir="auto"><code class="notranslate">dbSessionA</code> creates a new Session, but it's underlying connection is local to the scope/thead ( ie, dbSession.connection() == dbSessionA.connection() )</p>
<p dir="auto"><code class="notranslate">dbSessionB</code> raises an error because it is trying to duplicate the 'scoped_session', not just the underlying session,</p>