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

Wrong data from Virtual Column by ordering on a SET

    XMLWordPrintable

Details

    Description

      SELECT * FROM device ORDER BY moduleTypes ASC LIMIT 1000;
      


      Without ordering:

      It looks right.

      CREATE TABLE `device` (
      	`deviceId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      	`atmType` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
      	`country` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
      	`customer` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
      	`flightRecordDir` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
      	`ignoreSnr` BIT(1) AS (`system` like '%!IGSNR%' or `country` like 'EGT' or `country` like 'PRD%') stored,
      	`isInternal` BIT(1) AS (octet_length(`country`) = 3 or `country` like 'PRD%' or `system` like '%!LAB%' or `system` like '%!CAT%' or `system` like '%!INT%' or `customer` like '%LAB') stored,
      	`moduleTypes` SET('UNDEFINED','POS','CRS','ATS','ATM','CCDM2','RDS','RM4H','RM4V','CMDV6C','CK1','CMDV6A','FIB','AFD','CM4','ICASH40','ICASH45','ICASH60','ICASH65','OTHER_RETAIL','PROBASE_TRACE','OTHER') NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
      	`path` VARCHAR(255) AS (concat(`country`,'\\',`customer`,'\\',`atmType`,'\\',`system`,'\\',`flightRecordDir`)) virtual,
      	`system` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
      	`systemHash` VARCHAR(32) AS (md5(concat(`country`,`customer`,`atmType`,`system`))) stored,
      	`traceType` ENUM('UNDEFINED','POS','CRS','ATS','ATM','CCDM2','RDS','RM4H','RM4V','CMDV6C','CK1','CMDV6A','FIB','AFD','CM4','ICASH40','ICASH45','ICASH60','ICASH65','OTHER_RETAIL','PROBASE_TRACE','OTHER') NOT NULL COLLATE 'utf8mb3_general_ci',
      	PRIMARY KEY (`deviceId`) USING BTREE,
      	INDEX `country_index` (`country`) USING BTREE,
      	INDEX `customer_index` (`customer`) USING BTREE,
      	INDEX `atmType_index` (`atmType`) USING BTREE,
      	INDEX `traceType_index` (`traceType`) USING BTREE,
      	INDEX `hash_index` (`systemHash`) USING BTREE
      )
      COLLATE='utf8mb3_general_ci'
      ENGINE=InnoDB
      AUTO_INCREMENT=13777
      ;
      

      Attachments

        1. image-2022-05-25-10-11-49-686.png
          26 kB
          Sebastian Stamm
        2. image-2022-05-25-10-14-16-380.png
          6 kB
          Sebastian Stamm
        3. image-2022-05-25-10-16-12-143.png
          7 kB
          Sebastian Stamm

        Issue Links

          Activity

            People

              nikitamalyavin Nikita Malyavin
              sstamm Sebastian Stamm
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.