Merge "Consider default FROM DUAL for MySQL"

3 files changed, 70 insertions, 1 deletions

diff --git a/doc/build/changelog/unreleased_13/5481.rst b/doc/build/changelog/unreleased_13/5481.rst
new file mode 100644
index 000000000..2ebac73bf
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5481.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: usecase, mysql
+    :tickets: 5481
+
+    The MySQL dialect will render FROM DUAL for a SELECT statement that has no
+    FROM clause but has a WHERE clause. This allows things like "SELECT 1 WHERE
+    EXISTS (subuqery)" kinds of queries to be used as well as other use cases.
+
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index d4ffcabd5..76c2039cc 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1314,6 +1314,18 @@ class MySQLCompiler(compiler.SQLCompiler):
     extract_map = compiler.SQLCompiler.extract_map.copy()
     extract_map.update({"milliseconds": "millisecond"})
 
+    def default_from(self):
+        """Called when a ``SELECT`` statement has no froms,
+        and no ``FROM`` clause is to be appended.
+
+        """
+        if self.stack:
+            stmt = self.stack[-1]["selectable"]
+            if stmt._where_criteria:
+                return " FROM DUAL"
+
+        return ""
+
     def visit_random_func(self, fn, **kw):
         return "rand%s" % self.function_argspec(fn)
 
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index ad17ebb4a..9fb481676 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1,6 +1,5 @@
 import itertools
 
-from sqlalchemy import ForeignKey
 from .. import AssertsCompiledSQL
 from .. import AssertsExecutionResults
 from .. import config
@@ -14,9 +13,12 @@ from ... import bindparam
 from ... import case
 from ... import column
 from ... import Computed
+from ... import exists
 from ... import false
+from ... import ForeignKey
 from ... import func
 from ... import Integer
+from ... import literal
 from ... import literal_column
 from ... import null
 from ... import select
@@ -1018,6 +1020,53 @@ class ComputedColumnTest(fixtures.TablesTest):
             eq_(res, [(100, 40), (1764, 168)])
 
 
+class ExistsTest(fixtures.TablesTest):
+    __backend__ = True
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "stuff",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("data", String(50)),
+        )
+
+    @classmethod
+    def insert_data(cls, connection):
+        connection.execute(
+            cls.tables.stuff.insert(),
+            [
+                {"id": 1, "data": "some data"},
+                {"id": 2, "data": "some data"},
+                {"id": 3, "data": "some data"},
+                {"id": 4, "data": "some other data"},
+            ],
+        )
+
+    def test_select_exists(self, connection):
+        stuff = self.tables.stuff
+        eq_(
+            connection.execute(
+                select(literal(1)).where(
+                    exists().where(stuff.c.data == "some data")
+                )
+            ).fetchall(),
+            [(1,)],
+        )
+
+    def test_select_exists_false(self, connection):
+        stuff = self.tables.stuff
+        eq_(
+            connection.execute(
+                select(literal(1)).where(
+                    exists().where(stuff.c.data == "no data")
+                )
+            ).fetchall(),
+            [],
+        )
+
+
 class DistinctOnTest(AssertsCompiledSQL, fixtures.TablesTest):
     __backend__ = True
     __requires__ = ("standard_cursor_sql",)