Oracle: Fails to retrieve table comments when table name is a reserved keyword

[homersimpsons]

Bug Report

Q	A
Version	3.10.x
Previous Version if the bug is a regression	x.y.z

Summary

On oracle, schema introspection fails to retrieve comments if the table name is a reserved keyword

Current behavior

Getting the comment on a table with a reserverd keyword returns NULL.

Expected behavior

Getting the comment on a table with a reserverd keyword should return the comment.

How to reproduce

I did setup oracle / oci driver using docker. If you have a working oracle setup it should be more straghtforward.

In the dbal sources I created those files directly.

./compose.yaml

services:
  oracle:
    image: wnameless/oracle-xe-11g-r2
  php:
    build: .
    volumes:
      - .:/var/www/html

./Dockerfile

# From https://github.com/silencesys/docker-php-oci8
FROM php:8.3-fpm
Update OS packages and install required ones.
RUN apt-get update && 

apt-get upgrade -y && 

apt-get install -y --no-install-recommends 

curl 

libmemcached-dev 

libz-dev 

libpq-dev 

libjpeg-dev 

libpng-dev 

libfreetype6-dev 

libssl-dev 

libmcrypt-dev 

zip 

unzip 

build-essential 

libaio1 

libzip-dev 

&& apt-get clean 

&& rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/* 

&& rm /var/log/lastlog /var/log/faillog
Download oracle packages and install OCI8
RUN curl -o instantclient-basic-linux.x64-19.6.0.0.0dbru.zip https://download.oracle.com/otn_software/linux/instantclient/19600/instantclient-basic-linux.x64-19.6.0.0.0dbru.zip 

&& unzip instantclient-basic-linux.x64-19.6.0.0.0dbru.zip -d /usr/lib/oracle/ 

&& rm instantclient-basic-linux.x64-19.6.0.0.0dbru.zip 

&& curl -o instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip https://download.oracle.com/otn_software/linux/instantclient/19600/instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip 

&& unzip instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip -d /usr/lib/oracle/ 

&& rm instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip 

&& echo /usr/lib/oracle/instantclient_19_6 > /etc/ld.so.conf.d/oracle-instantclient.conf 

&& ldconfig
ENV LD_LIBRARY_PATH /usr/lib/oracle/instantclient_19_6
RUN docker-php-ext-configure oci8 --with-oci8=instantclient,/usr/lib/oracle/instantclient_19_6 

&& docker-php-ext-install -j$(nproc) oci8
Expose port 9000 and start php-fpm server
CMD ["php-fpm"]

EXPOSE 9000

<?php
// ./index.php

declare(strict_types=1);

require __DIR__ . '/vendor/autoload.php';

use Doctrine\DBAL\DriverManager;

$connection = DriverManager::getConnection([
    'driver'   => 'oci8',
    'host'     => 'oracle',
    'user'     => 'system',
    'password' => 'oracle',
    'dbname'   => 'XE',
    'port'     => 1521,
]);

// $connection->executeStatement('DROP TABLE "users" PURGE'); // Uncomment on subsequent runs
$connection->executeStatement('CREATE TABLE "users" ("users" NUMBER)');
$connection->executeStatement('COMMENT ON TABLE "users" IS \'table comment\'');
$connection->executeStatement('COMMENT ON COLUMN "users"."users" IS \'column comment\'');

$schemaManager = $connection->createSchemaManager();
$schema = $schemaManager->introspectSchema();
$table = $schema->getTable('users');
var_dump('table comment', $table->getComment()); // Output: table comment, NULL (ISSUE: should be table comment, table comment)
$id = $table->getColumn('users');
var_dump('column comment', $table->getColumn('users')->getComment()); // Ouput: column comment, column comment (correct)

Then the issue is visible after running:

1. docker compose up (optional, if you do not have a working oracle setup)
2. docker compose exec php php index.php

Exploration results

Non-reserved keyword works

$connection->executeStatement('CREATE TABLE test ("id" NUMBER)');
$connection->executeStatement('COMMENT ON TABLE test IS \'table comment\'');
$connection->executeStatement('COMMENT ON COLUMN test."id" IS \'column comment\'');

$schemaManager = $connection->createSchemaManager();
$schema = $schemaManager->introspectSchema();
$table = $schema->getTable('test');
var_dump('table comment', $table->getComment()); // ==> table comment, table comment
$id = $table->getColumn('id');
var_dump('column comment', $table->getColumn('id')->getComment()); // ==> column comment, column comment

AbstractSchemaManager

The error seems to come from those calls:

dbal/src/Schema/AbstractSchemaManager.php

Lines 408 to 411 in 818eae9

	$tableColumnsByTable = $this->fetchTableColumnsByTable($database);
	$indexColumnsByTable = $this->fetchIndexColumnsByTable($database);
	$foreignKeyColumnsByTable = $this->fetchForeignKeyColumnsByTable($database);
	$tableOptionsByTable = $this->fetchTableOptionsByTable($database);

In fact here $tableColumnsByTable contains "users" while $indexColumnsByTable contains users (notice the missing quotes) which leads to the following lines returning an empty array:

dbal/src/Schema/AbstractSchemaManager.php

Line 427 in 818eae9

$tableOptionsByTable[$tableName] ?? [],

References

• Original implementation: Support for comments on table in all databases #3512
• Possible related issue: Object names are always introspected as unquoted #5776
• Possible related issue: The support for quoting identifiers is fundamentally broken #4357
• Originally implemented in Support for comments on table in all databases #3512