2

I'm trying to run the following query:

INSERT INTO `user_domain_rating_days`
            (
                        `user_domain_rating_id`,
                        `start`,
                        `user_id`,
                        `domain_id`,
                        `modified_count`,
                        `modified_count_diff`,
                        `rating`,
                        `rating_diff`,
                        `transformed_rating`,
                        `transformed_rating_diff`,
                        `q_score`,
                        `q_score_diff`,
                        `timestamp_diff`,
                        `session_count`,
                        `last_full_session`
            )
SELECT    MAX(`latest`.`user_domain_rating_id`),
          :date,
          MAX(`latest`.`user_id`),
          MAX(`latest`.`domain_id`),
          @modified_count := MAX(`latest`.`modified_count`),
          @modified_count_diff := SUM(`group_by`.`modified_count_diff`),
          @rating := MAX(`latest`.`rating`),
          @rating_diff := SUM(`group_by`.`rating_diff`),
          @transformed_rating := MAX(`latest`.`transformed_rating`),
          @transformed_rating_diff := SUM(`group_by`.`transformed_rating_diff`),
          @q_score := MAX(`latest`.`q_score`),
          @q_score_diff := SUM(`group_by`.`q_score_diff`),
          @timestamp_diff := SUM(`group_by`.`timestamp_diff`),
          @session_count := MAX(`latest`.`session_count`),
          @last_full_session := MAX(`latest`.`last_full_session`)
FROM      `user_domain_rating_hours` AS `group_by`
LEFT JOIN
          (
                 SELECT *
                 FROM   `user_domain_rating_hours`
                 WHERE  `id` IN
                        (
                                 SELECT   MAX(`id`)
                                 FROM     `user_domain_rating_hours`
                                 WHERE    `start` >= :date
                                 AND      `start` < DATE_ADD(DATE(:date), INTERVAL 1 DAY)
                                 GROUP BY `user_id`,
                                          `domain_id` ) ) AS `latest`
ON        `group_by`.`user_id` = `latest`.`user_id`
AND       `group_by`.`domain_id` = `latest`.`domain_id`
WHERE     1
AND       NOW() > DATE_ADD(DATE(:date), INTERVAL 1 DAY)
AND       `group_by`.`start` >= :date
AND       `group_by`.`start` < DATE_ADD(DATE(:date), INTERVAL 1 DAY)
GROUP BY  `group_by`.`user_id`,
          `group_by`.`domain_id` LOCK IN SHARE MODE
ON DUPLICATE KEY
UPDATE `user_domain_rating_days`.`id` = `user_domain_rating_days`.`id` ;

which worked fine when last_full_session was not present yet. This new column is created like this:

ALTER TABLE `user_domain_rating_days` ADD `last_full_session` timestamp NULL AFTER `session_count`;

We are running mysql (Percona 5.7 to be specific) with the following sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION. When I remove NO_ZERO_DATE from the sql_mode, the query runs fine (not even 0000-00-00 00:00:00 values in the destination table).

When I only run the SELECT part of the query it also runs fine. @last_full_session := MAX(latest.last_full_session) is NULL which is supported by the destination table. However in combination with the INSERT INTO something changes, NULL seems to be converted to 0000-00-00 00:00:00.

What am I missing?

mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
CREATE TABLE `user_domain_rating_days` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_domain_rating_id` int(10) unsigned NOT NULL,
  `start` date NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `domain_id` int(10) unsigned NOT NULL,
  `modified_count` int(10) unsigned NOT NULL,
  `modified_count_diff` int(10) unsigned NOT NULL,
  `rating` double NOT NULL,
  `rating_diff` double NOT NULL,
  `transformed_rating` double DEFAULT NULL,
  `transformed_rating_diff` double NOT NULL,
  `q_score` smallint(4) unsigned DEFAULT NULL,
  `q_score_diff` smallint(4) NOT NULL,
  `timestamp_diff` int(10) unsigned NOT NULL,
  `session_count` int(10) unsigned DEFAULT NULL,
  `last_full_session` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_domain_day` (`user_id`,`domain_id`,`start`),
  UNIQUE KEY `user_domain_rating_id_day` (`user_domain_rating_id`,`start`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `user_domain_rating_days_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `user_domain_rating_days_ibfk_2` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`),
  CONSTRAINT `user_domain_rating_days_ibfk_3` FOREIGN KEY (`user_domain_rating_id`) REFERENCES `user_domain_ratings` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.24-27 |
+-----------+
# mysqld --version
mysqld  Ver 5.7.24-27 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '27', Revision 'bd42700')
1

This looks like a bug, so I suggest you file a bug report to either Percona or MySQL, with a smaller exammple that reproduces the error (keep only the problematic column).

In the mean time, a suggestion that may remove the error:

There are two GROUP BY operations in the query one performed in the latest subquery, to find the latest timestamps per group and another, performed in the main query, to get SUMs of various columns from the main table.

This requires the use of an aggregate (MAX) for the results coming from the latest subquery - although we have only 1 row per group from that. In order to avoid that (second GROUP BY applied to latest), we can move that (second GROUP BY) to another subquery and then join latest.

This would allow us to have no GROUP BY in the main query (and as I suspect get rid of the error which is somehow caused by applying MAX() to NULL valued timestamps that come from the latest subquery). The rewritten query:

-- INSERT part (unchanged)

INSERT INTO `user_domain_rating_days`
            (
                        `user_domain_rating_id`,
                        `start`,
                        `user_id`,
                        `domain_id`,
                        `modified_count`,
                        `modified_count_diff`,
                        `rating`,
                        `rating_diff`,
                        `transformed_rating`,
                        `transformed_rating_diff`,
                        `q_score`,
                        `q_score_diff`,
                        `timestamp_diff`,
                        `session_count`,
                        `last_full_session`
            )

-- main query SELECT (aggregates removed)

SELECT    `latest`.`user_domain_rating_id`,
          :date,
          `latest`.`user_id`,
          `latest`.`domain_id`,
          @modified_count := `latest`.`modified_count`,
          @modified_count_diff := `group_by`.`modified_count_diff`,
          @rating := `latest`.`rating`,
          @rating_diff := `group_by`.`rating_diff`,
          @transformed_rating := `latest`.`transformed_rating`,
          @transformed_rating_diff := `group_by`.`transformed_rating_diff`,
          @q_score := `latest`.`q_score`,
          @q_score_diff := `group_by`.`q_score_diff`,
          @timestamp_diff := `group_by`.`timestamp_diff`,
          @session_count := `latest`.`session_count`,
          @last_full_session := `latest`.`last_full_session`
FROM

-- group_by subquery

    (
    SELECT    user_id,
              domain_id,
              SUM(g.`modified_count_diff`) AS modified_count_diff,
              SUM(g.`rating_diff`) AS rating_diff,
              SUM(g.`transformed_rating_diff`) AS transformed_rating_diff,
              SUM(g.`q_score_diff`) AS q_score_diff,
              SUM(g.`timestamp_diff`) AS timestamp_diff,
    FROM      `user_domain_rating_hours` AS g
    WHERE     1
    AND       NOW() > DATE_ADD(DATE(:date), INTERVAL 1 DAY)
    AND       g.`start` >= :date
    AND       g.`start` < DATE_ADD(DATE(:date), INTERVAL 1 DAY)
    GROUP BY  g.`user_id`,
              g.`domain_id` LOCK IN SHARE MODE
    ) AS `group_by`

-- latest subquery (unchanged)

LEFT JOIN
          (
                 SELECT *
                 FROM   `user_domain_rating_hours`
                 WHERE  `id` IN
                        (
                                 SELECT   MAX(`id`)
                                 FROM     `user_domain_rating_hours`
                                 WHERE    `start` >= :date
                                 AND      `start` < DATE_ADD(DATE(:date), INTERVAL 1 DAY)
                                 GROUP BY `user_id`,
                                          `domain_id` ) ) AS `latest`
ON        `group_by`.`user_id` = `latest`.`user_id`
AND       `group_by`.`domain_id` = `latest`.`domain_id`

-- GROUP BY removed

-- GROUP BY ...

-- unchanged part

ON DUPLICATE KEY
UPDATE `user_domain_rating_days`.`id` = `user_domain_rating_days`.`id` ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.