<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">i.e. the table with composite PK and a col==itself, like in test_relationships RelationTest2:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE TABLE task (
&quot;name&quot; text NOT NULL,
asset text NOT NULL,
parent_asset text,
&lt;more columns&gt;
CONSTRAINT &quot;task_PK&quot; PRIMARY KEY (asset, name),
CONSTRAINT &quot;task_parent_FK&quot; FOREIGN KEY (parent_asset, &quot;name&quot;)
     REFERENCES task (asset, &quot;name&quot;) MATCH SIMPLE
     ON UPDATE CASCADE ON DELETE CASCADE);"><pre class="notranslate"><code class="notranslate">CREATE TABLE task (
"name" text NOT NULL,
asset text NOT NULL,
parent_asset text,
&lt;more columns&gt;
CONSTRAINT "task_PK" PRIMARY KEY (asset, name),
CONSTRAINT "task_parent_FK" FOREIGN KEY (parent_asset, "name")
     REFERENCES task (asset, "name") MATCH SIMPLE
     ON UPDATE CASCADE ON DELETE CASCADE);
</code></pre></div>
<p dir="auto">I think what has to happen is the condition is detected in the PJ, and a new annotation is added to one side of <code class="notranslate">name==name</code> that somehow does the right thing in clause adaption - likely a flag passed to clause adaption only during the eagerload phase.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy.dialect.mysql import insert 
from sqlalchemy import func
insert_stmt=insert(t).values(col1=1,col2=2)
upsert_stmt=insert_stmt.on_duplicate_key_update(col2=func.coalesce(insert_stmt.inserted.col2,t.c.col2))"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">dialect</span>.<span class="pl-s1">mysql</span> <span class="pl-k">import</span> <span class="pl-s1">insert</span> 
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">func</span>
<span class="pl-s1">insert_stmt</span><span class="pl-c1">=</span><span class="pl-en">insert</span>(<span class="pl-s1">t</span>).<span class="pl-en">values</span>(<span class="pl-s1">col1</span><span class="pl-c1">=</span><span class="pl-c1">1</span>,<span class="pl-s1">col2</span><span class="pl-c1">=</span><span class="pl-c1">2</span>)
<span class="pl-s1">upsert_stmt</span><span class="pl-c1">=</span><span class="pl-s1">insert_stmt</span>.<span class="pl-en">on_duplicate_key_update</span>(<span class="pl-s1">col2</span><span class="pl-c1">=</span><span class="pl-s1">func</span>.<span class="pl-en">coalesce</span>(<span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">col2</span>,<span class="pl-s1">t</span>.<span class="pl-s1">c</span>.<span class="pl-s1">col2</span>))</pre></div>
<p dir="auto">the upsert statement is rendered incorrectly as</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="INSERT INTO t (col1,col2)  VALUES (%s, %s) ON DUPLICATE KEY UPDATE col2=coalesce(inserted.col2, t.col2)"><pre class="notranslate"><span class="pl-k">INSERT INTO</span> t (col1,col2)  <span class="pl-k">VALUES</span> (%s, %s) <span class="pl-k">ON</span> DUPLICATE KEY <span class="pl-k">UPDATE</span> col2<span class="pl-k">=</span>coalesce(<span class="pl-c1">inserted</span>.<span class="pl-c1">col2</span>, <span class="pl-c1">t</span>.<span class="pl-c1">col2</span>)</pre></div>