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

compat/oracle.sp-inout fails on macOS

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 11.4.2
    • Tests
    • None

    Description

      To reproduce this bug, it’s sufficient to run the query INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a); after creating table Persons. In the case of APFS, the default filesystem on Mac, it is case-insensitive. On such systems, we set lower_case_table_names is to 2. According to our KB at https://mariadb.com/docs/server/ref/mdb/system-variables/lower_case_table_names/ this means that “When the lower_case_table_names system variable is set to 2, the server uses a special mode for case-insensitive file systems. In this mode, table names and database names are stored as declared, but they are compared in lowercase.” This bug happens because the sp_name constructor discards the case information for the procedure name if lower_case_table_names is any value other than zero, and stores the name as all lowercase. Later, if we print or otherwise display this name, it’s shown as all lowercase, in contradiction to our documentation.
      To fix this bug, I think we need to remember the value in its original case, perhaps as a separate variable on the sp_name class, and show that whenever the name may be displayed to the user. We can retain the all-lowercase value for internal processing as we do today.

      Attachments

        Issue Links

          Activity

            what versions are affected?

            serg Sergei Golubchik added a comment - what versions are affected?
            bar Alexander Barkov added a comment - - edited

            Gosselin, can you please paste the diff from the test failure?

            In this test, PKG2 is neither a table nor a database name.
            It's a stored package name. Stored routine names are not relevant to --lower-case-table-names or the file system case sensitivity. They're always stored in memory in their original case, and should be displayed in their original case.

            If in some cases a package name is displayed with a wrong case, then this is likely a bug. I guess the underlying code makes a premature decision that PKG2.func() is a call for a function in the database PKG2 (and erroneously applies lower case conversion), while in fact this is a call for a function in the package PKG2 in the current datadabase and lower case conversion should not be applied.

            I don't think we need the copy of the original name.

            The fix should just distinguish `database PKG2` from `package PKG2 of the current database` properly and defer lower-casing until the moment when it's know if PKG2 is a database or a package.

            Package name resolution for functions is done in this method method:

            Item*
            Create_sp_func::create_with_db(THD *thd,
                                           const Lex_ident_db_normalized &db,
                                           const LEX_CSTRING &name,
                                           bool use_explicit_name, List<Item> *item_list)
            

            So the user typed version of the qualified name should come to here as is, without lower-casing.
            It'll need changes in the method signature, as Lex_ident_db_normalized is not obviously good here.

            bar Alexander Barkov added a comment - - edited Gosselin , can you please paste the diff from the test failure? In this test, PKG2 is neither a table nor a database name. It's a stored package name. Stored routine names are not relevant to --lower-case-table-names or the file system case sensitivity. They're always stored in memory in their original case, and should be displayed in their original case. If in some cases a package name is displayed with a wrong case, then this is likely a bug. I guess the underlying code makes a premature decision that PKG2.func() is a call for a function in the database PKG2 (and erroneously applies lower case conversion), while in fact this is a call for a function in the package PKG2 in the current datadabase and lower case conversion should not be applied. I don't think we need the copy of the original name. The fix should just distinguish `database PKG2` from `package PKG2 of the current database` properly and defer lower-casing until the moment when it's know if PKG2 is a database or a package. Package name resolution for functions is done in this method method: Item* Create_sp_func::create_with_db(THD *thd, const Lex_ident_db_normalized &db, const LEX_CSTRING &name, bool use_explicit_name, List<Item> *item_list) So the user typed version of the qualified name should come to here as is, without lower-casing. It'll need changes in the method signature, as Lex_ident_db_normalized is not obviously good here.

            As the bug is not serious, I propose to wait until MDEV-31340 is pushed to 11.5. It'll make it easier to fix this bug.

            In 11.4 (and earlier affected version) we can modify compat/oracle/sp-inout.test to use only lower-cased package names.

            bar Alexander Barkov added a comment - As the bug is not serious, I propose to wait until MDEV-31340 is pushed to 11.5. It'll make it easier to fix this bug. In 11.4 (and earlier affected version) we can modify compat/oracle/sp-inout.test to use only lower-cased package names.
            Gosselin Dave Gosselin added a comment -

            Hi bar, here is the diff from the test failure:

            CURRENT_TEST: compat/oracle.sp-inout
            --- /Users/dgosselin/11.4/server/mysql-test/suite/compat/oracle/r/sp-inout.result	2024-03-18 12:06:08
            +++ /Users/dgosselin/11.4/server/mysql-test/suite/compat/oracle/r/sp-inout.reject	2024-04-01 09:14:06
            @@ -727,7 +727,7 @@
             3	CCC	30
             set @a = 0;
             INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a);
            -ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here
            +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here
             DROP TABLE Persons;
             DROP PACKAGE pkg2;
             #
            @@ -808,7 +808,7 @@
             4	DDD	40
             set @a = 0;
             DELETE FROM Persons WHERE ID = PKG2.func(@a);
            -ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here
            +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here
             DROP TABLE Persons;
             DROP PACKAGE pkg2;
             #
             
            mysqltest: Result content mismatch
            

            Gosselin Dave Gosselin added a comment - Hi bar , here is the diff from the test failure: CURRENT_TEST: compat/oracle.sp-inout --- /Users/dgosselin/11.4/server/mysql-test/suite/compat/oracle/r/sp-inout.result 2024-03-18 12:06:08 +++ /Users/dgosselin/11.4/server/mysql-test/suite/compat/oracle/r/sp-inout.reject 2024-04-01 09:14:06 @@ -727,7 +727,7 @@ 3 CCC 30 set @a = 0; INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a); -ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here DROP TABLE Persons; DROP PACKAGE pkg2; # @@ -808,7 +808,7 @@ 4 DDD 40 set @a = 0; DELETE FROM Persons WHERE ID = PKG2.func(@a); -ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here DROP TABLE Persons; DROP PACKAGE pkg2; #   mysqltest: Result content mismatch
            Gosselin Dave Gosselin added a comment -

            Fixed by git sha e25edf2f46f012c0ad6a8bcb161eadfe6a866bf0

            Gosselin Dave Gosselin added a comment - Fixed by git sha e25edf2f46f012c0ad6a8bcb161eadfe6a866bf0

            People

              Gosselin Dave Gosselin
              Gosselin Dave Gosselin
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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