<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 think it might be better to just get the array detection to do the right thing</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.dialects import postgresql


c1 = column('foo', Integer)

expr = array([c1])

print expr.compile(dialect=postgresql.dialect())

# fails
function = func.array_agg(expr)

# fails
function = func.array_agg(expr, type_=ARRAY(Integer))"><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.dialects import postgresql


c1 = column('foo', Integer)

expr = array([c1])

print expr.compile(dialect=postgresql.dialect())

# fails
function = func.array_agg(expr)

# fails
function = func.array_agg(expr, type_=ARRAY(Integer))
</code></pre></div>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">Starting with mysql 8.0.20 we get a deprecated syntax warning from <code class="notranslate">on_duplicate_key_update</code>, and at some point this will break.</p>
<p dir="auto">The cause and how to fix are explained in <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="571864941" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/5173" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/5173/hovercard?comment_id=593163758&amp;comment_type=issue_comment" href="https://github.com/sqlalchemy/sqlalchemy/issues/5173#issuecomment-593163758">#5173 (comment)</a>.  However I ran into this bug in the wild, and spent time tracking this down, so I'm putting it into the bug tracker with context for the next person who runs into this error.</p>
<p dir="auto">Separate note.  I tried to do the <code class="notranslate">-Werror</code> flag and that somehow got in the way of connecting to MySQL.  That's not your bug, but is why I didn't give a stack backtrace as requested.</p>
<h3 dir="auto">To Reproduce</h3>
<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>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="/opt/homebrew/lib/python3.9/site-packages/aiomysql/cursors.py:239: Warning: 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead"><pre class="notranslate"><code class="notranslate">/opt/homebrew/lib/python3.9/site-packages/aiomysql/cursors.py:239: Warning: 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS:</li>
<li>Python:</li>
<li>SQLAlchemy:</li>
<li>Database:</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient):</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto"><em>No response</em></p>