Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36303

Allow for database name to prefix index name in CREATE INDEX statement

Details

    Description

      Oracle supports prefixing the index name in CREATE INDEX with the database name, MariaDB does not. For compatibility it would be reasonable to support this syntax, even if it is ignored, in SQL_MODE=Oracle. Two different SQL_MODE options would be reasonable
      IGNORE_INDEX_DBNAME - Always ignore the database name.
      IGNORE_INDEX_SAMEDBNAME - Ignore database name if it matches the table name, else raise an error.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          serg Sergei Golubchik added a comment -

          Oracle supports the syntax schema_name.index_name because indexes are schema objects. You can also do

          DROP INDEX schema_name.index_name;

          And, naturally, index names are unique within a schema.

          In MariaDB model an index is an object within a table. You cannot drop an index if you don't know the table name, and a schema can have many indexes with exactly the same name.

          We definitely cannot do Oracle-style DROP INDEX and while we can do CREATE INDEX as a syntax sugar, I expect it'd very confusing, because it implies something that MariaDB doesn't do.

          serg Sergei Golubchik added a comment - Oracle supports the syntax schema_name.index_name because indexes are schema objects . You can also do DROP INDEX schema_name.index_name; And, naturally, index names are unique within a schema. In MariaDB model an index is an object within a table . You cannot drop an index if you don't know the table name, and a schema can have many indexes with exactly the same name. We definitely cannot do Oracle-style DROP INDEX and while we can do CREATE INDEX as a syntax sugar, I expect it'd very confusing, because it implies something that MariaDB doesn't do.
          karlsson Anders Karlsson added a comment -

          I agree with what you say, largely, but in the name of compatibility I still think it is reasonable, assuming the sql_mode is set to allow it. It wouldn't be the first piece of, sometimes confusing, syntactic sugar we support. When you export a schema from Oracle using Oracle datapump, which is pretty much standard, then you always get index names prefixed with database names, even if the indexes are in the same database as the table. So this syntax is actually rather common.

          karlsson Anders Karlsson added a comment - I agree with what you say, largely, but in the name of compatibility I still think it is reasonable, assuming the sql_mode is set to allow it. It wouldn't be the first piece of, sometimes confusing, syntactic sugar we support. When you export a schema from Oracle using Oracle datapump, which is pretty much standard, then you always get index names prefixed with database names, even if the indexes are in the same database as the table. So this syntax is actually rather common.

          People

            Unassigned Unassigned
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.