Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-836

Single Update with nested functions where internal functions return distinct values are returning identical values

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 2.3.0, 2.2.4, 2.4.0, 2.5.0, 2.6.0, 2.7.0
    • N/A
    • JDBC compatibility
    • None
    • MacOS 10.14.6, AWS Aurora (MySQL), MySQL 5.7

    Description

      I'm trying to update the Java connector in one of my applications from version 2.2.3 to 2.2.4.
      However, one of my SQL statements started behaving differently with the new version.

      UPDATE TEST_UUID SET new_id = UNHEX(REPLACE(uuid(), '-', ''))

      Up to version 2.2.3, this SQL would set a distinct value to `new_id` in each of the rows in the table.
      From version 2.2.4, this sets the same exact value to each of the rows.
      I've tested this with up to version 2.7.0, the behavior is always the same starting from 2.2.4.

      If I run the statement directly on MySQL server (using mysql>), the result is always as expected (distinct values on each row).
      I've tried that on MySQL 5.7.22 (locally installed in my env), MySQL 5.7.30 (test docker container), and more importantly, Aurora DB (apparently running MySQL 5.6.10).

      I've written a very simple Java application to replicate the issue and do some investigation myself.
      The 2 files are attached to the ticket.

      From running the application it is clear that the problem is not really with the call to `UUID()`. It is `REPLACE(UUID())` that causes identical values to be returned on 2.2.4.

      Is there some parameter I must pass for that to work correctly from 2.2.4?

      P.S.:

      In case you want to run the attached application:

      1 - connect to each of the DBs and run the following queries:

      create table testUUID (ID int, new_ID varchar(100), new_ID2 varchar(100), new_ID3 BINARY(16));
      insert into testUUID (id) values (1),(2),(3);

      2 - replace the connection info masked for security reasons and run it on each of the DBs.

      3 - set the version of mariadb client to use in the test in build.gradle

      4 - run main()

      If the output shows 3 for each of the distinct queries, the update stmt created distinct values in each column. If it shows 1, the update set the same value to all rows for that column. The queries should all return 3.

      What I saw, for all DBs:

      With mariadb client 2.2.3, it returns 3 for all queries.
      With mariadb client 2.2.4, it returns 1 for the second and third queries (new_ID2 and new_ID3).

      Attachments

        1. build.gradle
          0.2 kB
          Davi de Paula Cavalcanti
        2. MariaDBTestUUID.java
          4 kB
          Davi de Paula Cavalcanti

        Issue Links

          Activity

            People

              diego dupin Diego Dupin
              davipc Davi de Paula Cavalcanti
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.