'MySQL 8.0.28 ERROR 1264 (22003): Out of range value for column '(null)' at row 1
I'm not a programmer and not a MySQL expert. After upgrading to MySQL 8.0.28, an issue with INSERT FROM SELECT popped up.
Running only the SELECT the result is perfect:
SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ;
SELECT
t3.TIMESTAMP,t3.DEVICE,t3.READING,t3.VALUE
FROM (
SELECT
DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP,
t2.DEVICE,
@readingname AS READING,
cast(if(avg(t2.FACTOR) > 1.6, 1.6,
avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE
FROM (
SELECT * FROM (
SELECT
t1.TIMESTAMP,
t1.HOUR,
t1.DEVICE,
t1.READING,
t1.VALUE,
if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2))) AS DIFF,
if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR,
@diff:=t1.VALUE AS curr_V
FROM (
SELECT
h.TIMESTAMP,
date(h.TIMESTAMP) AS DATE,
hour(h.TIMESTAMP) AS HOUR,
h.DEVICE,
h.READING,
h.VALUE
FROM history AS h
WHERE h.DEVICE = @device
AND (h.READING = @reading1 OR h.READING = @reading2)
AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY)
AND h.TIMESTAMP <= CURDATE()
AND MINUTE(h.TIMESTAMP) = 0
AND h.VALUE >= 0
GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP
)t1
)tx
WHERE READING != @reading2
AND HOUR > 6
)t2
GROUP BY t2.HOUR,t2.DEVICE
)t3
WHERE
t3.VALUE != 0
ORDER BY TIMESTAMP
;
+---------------------+--------+------------------------------+-------+
| TIMESTAMP | DEVICE | READING | VALUE |
+---------------------+--------+------------------------------+-------+
| 2022-02-17 08:00:00 | WR_1 | Solar_Correction_Faktor_auto | 1.6 |
| 2022-02-17 09:00:00 | WR_1 | Solar_Correction_Faktor_auto | 1.6 |
| 2022-02-17 10:00:00 | WR_1 | Solar_Correction_Faktor_auto | 1.6 |
| 2022-02-17 11:00:00 | WR_1 | Solar_Correction_Faktor_auto | 1.2 |
| 2022-02-17 12:00:00 | WR_1 | Solar_Correction_Faktor_auto | 1.0 |
| 2022-02-17 13:00:00 | WR_1 | Solar_Correction_Faktor_auto | 0.8 |
| 2022-02-17 14:00:00 | WR_1 | Solar_Correction_Faktor_auto | 0.7 |
| 2022-02-17 15:00:00 | WR_1 | Solar_Correction_Faktor_auto | 0.6 |
| 2022-02-17 16:00:00 | WR_1 | Solar_Correction_Faktor_auto | 0.8 |
| 2022-02-17 17:00:00 | WR_1 | Solar_Correction_Faktor_auto | 0.2 |
+---------------------+--------+------------------------------+-------+
10 rows in set, 3 warnings (0.16 sec)
But adding the final INSERT, as it was running more than one year, will give an error:
INSERT INTO history
(TIMESTAMP,DEVICE,READING,VALUE)
SELECT
t3.TIMESTAMP,t3.DEVICE,t3.READING,t3.VALUE
FROM (
SELECT
DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP,
t2.DEVICE,
@readingname AS READING,
cast(if(avg(t2.FACTOR) > 1.6, 1.6,
avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE
FROM (
SELECT * FROM (
SELECT
t1.TIMESTAMP,
t1.HOUR,
t1.DEVICE,
t1.READING,
t1.VALUE,
if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2))) AS DIFF,
if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR,
@diff:=t1.VALUE AS curr_V
FROM (
SELECT
h.TIMESTAMP,
date(h.TIMESTAMP) AS DATE,
hour(h.TIMESTAMP) AS HOUR,
h.DEVICE,
h.READING,
h.VALUE
FROM history AS h
WHERE h.DEVICE = @device
AND (h.READING = @reading1 OR h.READING = @reading2)
AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY)
AND h.TIMESTAMP <= CURDATE()
AND MINUTE(h.TIMESTAMP) = 0
AND h.VALUE >= 0
GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP
)t1
)tx
WHERE READING != @reading2
AND HOUR > 6
)t2
GROUP BY t2.HOUR,t2.DEVICE
)t3
WHERE
t3.VALUE != 0
ON DUPLICATE KEY UPDATE
VALUE=t3.VALUE;
ERROR 1264 (22003): Out of range value for column '(null)' at row 1
This is the underlaying table definition
mysql> DESCRIBE history;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| TIMESTAMP | timestamp | NO | PRI | NULL | |
| DEVICE | varchar(64) | NO | PRI | NULL | |
| TYPE | varchar(64) | YES | | NULL | |
| EVENT | varchar(512) | YES | | NULL | |
| READING | varchar(64) | NO | PRI | NULL | |
| VALUE | varchar(255) | YES | | NULL | |
| UNIT | varchar(32) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
Is there a way to get that working as before, or have a slightly different SQL? I can't even understand the '(null)' column name.
Best regards Christian
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|