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

LP:912552 - SET optimizer_switch = REPLACE(...) causes ER_WRONG_VALUE_FOR_VAR

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.5.20
    • Component/s: None
    • Labels:
      None

      Description

      The following statement

      SET optimizer_switch = REPLACE( @@optimizer_switch, '=off', '=on' )

      as well as many other similar constructions, fails with ER_WRONG_VALUE_FOR_VAR: 1231: Variable 'optimizer_switch' can't be set to the value of 'table_elimination=on'.

      In 5.2 and 5.3 it works all right.

      It does not seem to be about the value, since this way it works:

      SET @a = REPLACE( @@optimizer_switch, '=off', '=on' );
      SET optimizer_switch = @a;

      But not this way

      SET @b = @@optimizer_switch;
      SET optimizer_switch = REPLACE( @b, '=off', '=on' );

      It also does not seem to be about the result of REPLACE being too long, since this one works:

      SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' )

      REPLACE statements are a convenient way to set optimizer_switch to a needed value in tests, and keep it version-independent; otherwise, it's not an important problem in itself, but it might signify an underlying issue, either with the REPLACE function, or with the optimizer_switch, so I find it worrisome.

      Test case:

       
      SET @saved_switch = @@optimizer_switch;
       
       # This works
      SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' );
       
      SET optimizer_switch = @saved_switch;
       
       # This doesn't
      SET optimizer_switch = REPLACE( @@optimizer_switch, '=off', '=on' );
       
      SET optimizer_switch = @saved_switch;
       
       # This works
      SET @a = REPLACE( @@optimizer_switch, '=off', '=on' );
      SET optimizer_switch = @a;
       
      SET optimizer_switch = @saved_switch;
       
       # This doesn't
       
      SET @b = @@optimizer_switch;
      SET optimizer_switch = REPLACE( @b, '=off', '=on' );
       

        Attachments

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: