[Issue] Add AdapterInterface dialect helpers and replace MySQL with Postgres SQL and other SQL like RDBS

[m2-assistant]

This issue is automatically created based on existing pull request: #41129: Add AdapterInterface dialect helpers and replace MySQL with Postgres SQL and other SQL like RDBS

---

Description (*)

Magento core still embeds MySQL-only SQL (GROUP_CONCAT(), FIELD(), IFNULL() literals, backticks, CREATE TABLE … LIKE, CREATE TEMPORARY TABLE … ENGINE=… IGNORE (SELECT), ON DUPLICATE KEY UPDATE text, UNION of mixed types, UPDATE … LIMIT, SHOW VARIABLES LIKE 'version'). That is valid on MySQL and fatal on PostgreSQL.

This PR does not add a Postgres adapter. It adds dialect helpers on AdapterInterface / Pdo\Mysql and switches Magento call sites to those helpers (and to existing ones such as getIfNullSql() / quoteIdentifier() / insertOnDuplicate()).

The Postgres driver lives in a separate module: https://github.com/Genaker/postgento (fork of Kirill Morozov’s Morozov_PgCompat). That repo implements the same AdapterInterface methods on pdo_pgsql. Magento can review/test/merge this core PR on MySQL alone, and optionally install Postgento on a branch of this PR to run Magento on PostgreSQL. After this PR is in Magento, Postgento does not need the Magento SQL call-site patches.

The same change set is open for Mage-OS: mage-os/mageos-magento2#321

New adapter methods (MySQL implementations):

• getGroupConcatSql() → GROUP_CONCAT
• getFieldSql() → FIELD()
• castToText() / castToNumeric() (no-op / CAST(… AS DECIMAL) on MySQL; other engines can map to ::text / ::numeric)
• createTableLike() → CREATE TABLE … LIKE
• createTemporaryTableFromSelect() → existing MySQL CREATE TEMPORARY TABLE … ENGINE=innodb IGNORE (SELECT)

Also:

• Select constructor type-hint relaxed from Pdo\Mysql to Zend_Db_Adapter_Abstract so a non-MySQL AdapterInterface can call select().
• Setup --db-engine is passed through DbValidator / Installer::assertDbAccessible() so an optional engine can open the correct driver at install time. Default MySQL behavior is unchanged.

Behavior on MySQL is intended to be equivalent.

Related Pull Requests

• Mage-OS: Add AdapterInterface helpers and replace MySQL-with Postgres mage-os/mageos-magento2#321
• Postgres adapter (not in this repo): https://github.com/Genaker/postgento — please test or merge that module alongside this PR if you want a full Postgres install.

Fixed Issues (if relevant)

N/A — no Magento issue filed yet.

Manual testing scenarios (*)

On a MySQL Magento Open Source install (this PR must not require Postgres):

1. bin/magento setup:upgrade — exit 0, no SQLSTATE.
2. bin/magento indexer:reindex — all indexers complete (catalog price, stock, EAV, catalog rule, fulltext).
3. GraphQL: configurable product with variants (e.g. Luma WJ01) returns variants; GROUP_CONCAT / FIELD() call sites used by collection / search appliers.
4. Storefront: homepage, category, PDP, cart, customer login.
5. Admin: dashboard, catalog products, categories, EAV attributes, customers, CMS, indexers, sales orders.
6. Guest checkout placing a Check / Money Order order (sales sequence + quote).
7. Confirm setup:install --db-engine=mysql still validates and installs as today.

Optional Postgres: check out this PR, Composer-require https://github.com/Genaker/postgento (genaker/module-postgento), setup:install --db-engine=postgresql, then repeat indexer / GraphQL / storefront / admin / checkout.

Questions or comments

Happy to split adapter-interface methods vs call-site SQL into two PRs if that is easier to review. The adapter itself is Postgento — Magento can merge this PR first (MySQL-safe) and take Postgento as a Composer module whenever you want Postgres in the distribution.

Unit tests for the new Pdo\Mysql helpers, TemporaryTableService, and BatchIterator empty-batch minValue are in this PR.

Adobe CLA: signed as required for submissions to https://github.com/magento/magento2.

Contribution checklist (*)

• Pull request has a meaningful description of its purpose
• All commits are accompanied by meaningful commit messages
• All new or changed code is covered with unit/integration tests (if applicable)
• README.md files for modified modules are updated and included in the pull request if any README.md predefined sections require an update
• All automated tests passed successfully (all builds are green)

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.adobe.com/browse/AC-18048 is successfully created for this GitHub issue.

[engcom-Bravo]

Marking this as a Feature Request