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

Variables in Subquery gives wrong result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 5.5, 10.0, 10.1, 10.1.26, 10.3.10, 10.2, 10.3
    • N/A
    • Optimizer
    • None
    • Debian stretch 9.5

    Description

      Following problem i've encountered after upgrading from MySql 5.1.73 (Where it does not happen) to 10.1.26. I've done the upgrade to 10.3.10 where it still behaves unexpected.

      CREATE TABLE test (Id INT);
       
      # Insert some rows
      INSERT INTO test VALUES(1);
      INSERT INTO test VALUES(2);
      INSERT INTO test VALUES(3);
       
      # The Following query is ok:
      SELECT
         SUM(VariableA),
         SUM(VariableB)
      FROM (
         SELECT 
            (SELECT @varA := 20) AS VariableA,
            @varA AS VariableB
         FROM test) as A;
       
      +----------------+----------------+
      | SUM(VariableA) | SUM(VariableB) |
      +----------------+----------------+
      |             60 |             60 |
      +----------------+----------------+
       
      # The same query, but remove SUM(VariableA) in first select. Should still give 60 for VariableB, but it returns NULL.
      SELECT
         SUM(VariableB)
      FROM (
         SELECT 
            (SELECT @varA := 20) AS VariableA,
            @varA AS VariableB
         FROM test) as A;
      +----------------+
      | SUM(VariableB) |
      +----------------+
      |           NULL |
      +----------------+
      

      I assume there is some optimization for the subquery going wrong.

      Attachments

        1. 50-mysqld_safe.cnf
          1 kB
        2. 50-server.cnf
          4 kB
        3. mariadb.cnf
          0.8 kB
        4. my.cnf
          0.8 kB

        Activity

          People

            alice Alice Sherepa
            Dominic Dominic Blattmann
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.