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

FR: Very needed ability merging the two or more dynamic columns for massive update operations

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Dynamic Columns
    • None

    Description

      -- Create example table
      		 
      CREATE TABLE `test` (
      		 
        `id_profile` VARBINARY(36) NOT NULL,
      		 
        `id_profile_type` INT(10) UNSIGNED NOT NULL,
      		 
        `dynamic_cols` BLOB,
      		 
        PRIMARY KEY (`id_profile`)
      		 
      ) ENGINE=INNODB DEFAULT CHARSET=utf8
      		 
       
      		 
      -- Fill table with some data
      		 
      INSERT INTO `test` (`id_profile`,`id_profile_type`,`dynamic_cols`)  VALUES
      		 
      ('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('sum',200)),
      		 
      ('00a65240-aacc-11e5-98e4-0050563c3a6d', 2, COLUMN_CREATE('sum',300)),
      		 
      ('0a843922-aacc-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('sum',100));
      		 
       
      		 
      -- Here I want add new dynamic column to existed column
      		 
      INSERT INTO `test` (`id_profile`,`id_profile_type`, `dynamic_cols`)  VALUES
      		 
      ('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('addition_col','100500')),
      		 
      ('00a65240-aacc-11e5-98e4-0050563c3a6d', 2, COLUMN_CREATE('addition_col','200300')),
      		 
      ('0a843922-aacc-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('addition_col','000200'))
      		 
      ON DUPLICATE KEY UPDATE
      		 
      `dynamic_cols` = COLUMN_ADD(`dynamic_cols`, VALUES(`dynamic_cols`));

      I propose add support merging columns in COLUMN_ADD operator or add new operator COLUMN_REPLACE and COLUMN_REPLACE_RECURSIVE

      Expected behavior described here: http://php.net/manual/en/function.array-replace.php and http://php.net/manual/en/function.array-replace-recursive.php

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          mikhail Mikhail Gavrilov created issue -
          mikhail Mikhail Gavrilov made changes -
          Field Original Value New Value
          Description {code:SQL}
          -- Create example table
          CREATE TABLE `test` (
            `id_profile` VARBINARY(36) NOT NULL,
            `id_profile_type` INT(10) UNSIGNED NOT NULL,
            `dynamic_cols` BLOB,
            PRIMARY KEY (`id_profile`)
          ) ENGINE=INNODB DEFAULT CHARSET=utf8

          -- Fill table with some data
          INSERT INTO `test` (`id_profile`,`id_profile_type`,`dynamic_cols`) VALUES
          ('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('sum',200)),
          ('00a65240-aacc-11e5-98e4-0050563c3a6d', 2, COLUMN_CREATE('sum',300)),
          ('0a843922-aacc-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('sum',100));

          -- Here I want add new dynamic column to existed column
          INSERT INTO `test` (`id_profile`,`id_profile_type`, `dynamic_cols`) VALUES
          ('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('addition_col','100500')),
          ('00a65240-aacc-11e5-98e4-0050563c3a6d', 2, COLUMN_CREATE('addition_col','200300')),
          ('0a843922-aacc-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('addition_col','000200'))
          ON DUPLICATE KEY UPDATE
          `dynamic_cols` = COLUMN_ADD(`dynamic_cols`, VALUES(`dynamic_cols`));
          {code}

          I propose add support merging columns in COLUMN_ADD operator or add new operator COLUMN_MERGE           		{code:SQL}
          -- Create example table
          CREATE TABLE `test` (
            `id_profile` VARBINARY(36) NOT NULL,
            `id_profile_type` INT(10) UNSIGNED NOT NULL,
            `dynamic_cols` BLOB,
            PRIMARY KEY (`id_profile`)
          ) ENGINE=INNODB DEFAULT CHARSET=utf8

          -- Fill table with some data
          INSERT INTO `test` (`id_profile`,`id_profile_type`,`dynamic_cols`) VALUES
          ('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('sum',200)),
          ('00a65240-aacc-11e5-98e4-0050563c3a6d', 2, COLUMN_CREATE('sum',300)),
          ('0a843922-aacc-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('sum',100));

          -- Here I want add new dynamic column to existed column
          INSERT INTO `test` (`id_profile`,`id_profile_type`, `dynamic_cols`) VALUES
          ('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('addition_col','100500')),
          ('00a65240-aacc-11e5-98e4-0050563c3a6d', 2, COLUMN_CREATE('addition_col','200300')),
          ('0a843922-aacc-11e5-98e4-0050563c3a6d', 1, COLUMN_CREATE('addition_col','000200'))
          ON DUPLICATE KEY UPDATE
          `dynamic_cols` = COLUMN_ADD(`dynamic_cols`, VALUES(`dynamic_cols`));
          {code}

          I propose add support merging columns in COLUMN_ADD operator or add new operator COLUMN_REPLACE and COLUMN_REPLACE_RECURSIVE

          Expected behavior described here: http://php.net/manual/en/function.array-replace.php and http://php.net/manual/en/function.array-replace-recursive.php
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 73969 ] MariaDB v4 [ 130437 ]

          People

            Unassigned Unassigned
            mikhail Mikhail Gavrilov
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.