CODEWITHSUNDEEP
mysqlindexing
Gustavo Fuhr
Gustavo Fuhr

Reputation: 37

Problem creating mysql index on virtual column

I have a Mysql table "Event" that has a Virtual column "campaignId". Im trying to create an index over this column with no success.

When I try to create the index:

ALTER TABLE `botbit`.`Event` 
ADD INDEX `IndexName` (`campaignId` ASC);

I get this error

Error Code: 1366. Incorrect integer value: 'null' for column 'campaignId' at row 1

The column campaignId is virtual, and its defined as follows:

ALTER TABLE `botbit`.`Event` 
ADD COLUMN `campaignId` INT(11) GENERATED ALWAYS AS (case when (json_unquote(json_extract(`customProps`,'$.campaignId')) IS NOT NULL) then json_unquote(json_extract(`customProps`,'$.campaignId')) else -4000 end);

If not exists the json attribute campaignId, the value of campaignId is set to -4000 (to avoid null values).

I've also tested if exists null values in that column, and there are none:

select * from Event where campaignId IS NULL LIMIT 1;

0 row(s) returned

I cant understand why mysql tells me "Incorrect value: null for column campaignId" if I don't have any null value in that column.

I have other indexes on other virutal columns and works fine, even when NULL values are present. So, I think there should be some data problem that I can not figure out.

Edit: looking for string 'null' values I get this results

SELECT id,campaignId FROM tbl
    WHERE json_unquote(json_extract(`customProps`,'$.campaignId'))
          = 'null';
| 21096314 |          0 |
| 21096315 |          0 |
| 21096316 |          0 |
| 21096317 |          0 |
| 21096318 |          0 |
| 21096319 |          0 |
| 21096320 |          0 |
| 21096321 |          0 |
| 21096322 |          0 |
| 21096323 |          0 |
| 21096324 |          0 |

Edit 2: Table creation

CREATE TABLE `Event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(5) unsigned NOT NULL,
  `subType` smallint(5) unsigned DEFAULT NULL,
  `storeId` mediumint(9) NOT NULL,
  `userId` int(11) DEFAULT NULL,
  `source` smallint(5) unsigned NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `customProps` json DEFAULT NULL,
  `timestamp_i` int(11) DEFAULT NULL,
  `mac` varchar(17) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.mac'))) VIRTUAL,
  `deviceId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.deviceId'))) VIRTUAL,
  `poc` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.poc'))) VIRTUAL,
  `registeredThrough` varchar(45) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.registeredThrough'))) VIRTUAL,
  `pointOfContactId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.pointOfContactId'))) VIRTUAL,
  `ticket` decimal(10,0) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.ticket'))) VIRTUAL,
  `promoCodeId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.promoCodeId'))) VIRTUAL,
  `date` date GENERATED ALWAYS AS (cast(`timestamp` as date)) VIRTUAL,
  `npsScore` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.score'))) VIRTUAL,
  `npsComment` varchar(2000) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.comment'))) VIRTUAL,
  `campaignName` varchar(2000) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.campaignName'))) VIRTUAL,
  `productId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.productId'))) VIRTUAL,
  `reservationId` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`customProps`,'$.id'))) VIRTUAL,
  `campaignId` int(11) GENERATED ALWAYS AS ((case when (json_unquote(json_extract(`customProps`,'$.campaignId')) is not null) then json_unquote(json_extract(`customProps`,'$.campaignId')) else -(4000) end)) VIRTUAL,
  `isCustomCampaign` tinyint(1) GENERATED ALWAYS AS ((case when (json_unquote(json_extract(`customProps`,'$.isCustomCampaign')) = 'true') then 1 when (json_unquote(json_extract(`customProps`,'$.isCustomCampaign')) = 'false') then 0 else 0 end)) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Event_id_uindex` (`id`),
  KEY `Event_userId_index` (`userId`),
  KEY `Event_subType_storeId_index` (`subType`,`storeId`),
  KEY `Event_timetamp_index` (`timestamp`),
  KEY `Event_subtype_storeId_userId_timestamp_index` (`subType`,`userId`,`storeId`,`timestamp`),
  KEY `Event_storeId_type` (`storeId`,`type`),
  KEY `Event_mac_index` (`mac`),
  KEY `Event_deviceId_index` (`deviceId`)
) ENGINE=InnoDB AUTO_INCREMENT=30693655 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Edit 3: record insertion example

INSERT INTO `Event` (`type`, `subType`, `storeId`, `userId`, `source`, `timestamp`, `customProps`) VALUES ('1', '16', '3', '1', '2', '2018-06-04 15:41:56', '{ \"campaignId\": 100, \"isCustomCampaign\": false }');

Thanks

Upvotes: 1

Views: 1353

Answers (1)

Rick James
Rick James

Reputation: 142296

Side note:

ADD COLUMN `campaignId` INT(11)
    GENERATED ALWAYS AS (
        case when (json_unquote(json_extract(`customProps`,'$.campaignId')) IS NOT NULL)
             then  json_unquote(json_extract(`customProps`,'$.campaignId'))
             else -4000 end);

Simpler:

ADD COLUMN `campaignId` INT(11)
    GENERATED ALWAYS AS (
        COALESCE(json_unquote(json_extract(`customProps`,'$.campaignId')),
                -4000)

As for the problem, see what you get for

SELECT * FROM tbl
    WHERE json_unquote(json_extract(`customProps`,'$.campaignId'))
          = 'null';

I am thinking that there is the 4-letter string "null" somewhere in the JSON.

(After Comments)

mysql> SET @j := '{ \"campaignId\": 100, \"isCustomCampaign\": false }';

SELECT json_unquote(json_extract(@j, '$.campaignId')) AS the_value,
       json_unquote(json_extract(@j, '$.campaignId')) = 'null' AS string_cmp,
       json_unquote(json_extract(@j, '$.campaignId')) IS NULL AS null_cmp,
       case when  (json_unquote(json_extract(@j, '$.campaignId')) IS NOT NULL)
             then  json_unquote(json_extract(@j, '$.campaignId'))
             else -4000 end  AS the_case;

+-----------+------------+----------+----------+
| the_value | string_cmp | null_cmp | the_case |
+-----------+------------+----------+----------+
| 100       |          0 |        0 | 100      |
+-----------+------------+----------+----------+

SET @j := '{  \"isCustomCampaign\": false }';
(then same query)

+-----------+------------+----------+----------+
| the_value | string_cmp | null_cmp | the_case |
+-----------+------------+----------+----------+
| NULL      |       NULL |        1 | -4000    |
+-----------+------------+----------+----------+

Does this give you some further clues? Perhaps it also gives you a way to experiment with the JSON without involving you big table.

Upvotes: 2

Related Questions