Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.8.2
-
archlinux
also happens on debian with mariadb 10.11.11-MariaDB-0+deb12u1
-
Not for Release Notes
Description
TLDR:
create table test (col int(8));
|
insert into test values (5),(10),(20);
|
CREATE VIEW tview AS select '' AS sel from test;
|
create table failure as select if(sel > 0, sel, 666) from tview ;
|
*ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''*
|
despite all these work properly:
select if(sel > 0, sel, 666) from tview ;
|
select sel > 0 from tview ;
|
create table success as select sel from tview ;
|
Long Version:
I tried to fill data from a view into a table and got very obscure error messages. The error message referred to a line out of the scope of the view.
Here is a short snippet of two lines of mariadb input:
> select count( *) from V5Alle2;
|
| count( *) |
|
| *390* |
|
1 row in set (0.078 sec)
|
 |
> create table tt4 as select * from V5Alle2;
|
ERROR 1264 (22003): Out of range value for column '(null)' at row *391*
|
The view claims to have 390 lines but the error "happens" in line 391 !?!?
Unfortunately, the DB contains almost 100 tables and views (with personal content, size 19MB) and links to a second DB (uncritical but 117MB).
The view itself seems uncritical:
select `a`.`Name` AS `Name`,`b`.`WPID` AS `WPId`,`b`.`Anzahl` AS `Anzahl`,`c`.`Kurs` AS `Kurs`,if(`a`.`Wert` > 0,`a`.`Wert`,NULL) AS `Wert`,`a`.`Dividende` AS `Dividende`,if(`a`.`Saldo` > 0 or `a`.`Saldo` < 0,`a`.`Saldo`,NULL) AS `Saldo`,`a`.`relativ` * 100 AS `relativ`,`a`.`IRR` AS `Irr`,`a`.`isum` AS `isum`,`a`.`DID` AS `DID`,`c`.`Datum` AS `datum`,if(`a`.`PID` > 0,`a`.`PID`,0) AS `PID` from ((`finanzNG`.`V5Alle1` `a` left join `finanzNG`.`T2Bestand` `b` on(`a`.`DID` = `b`.`DID` and `a`.`WPId` = `b`.`WPID`)) left join `finanzNG`.`T4KursDatum` `c` on(`a`.`WPId` = `c`.`WPID`)) order by `a`.`DID`,`a`.`isum`,`a`.`Name` |
[one hour later:]
O.K. I went through pruning the database considerably, reducing it to 22 tables and views, most of them empty and remaining only because of foreign keys.
Now the error message has changed !?!?
> select count( *) from V5Alle2;
|
| count( *) |
|
| 272 |
|
1 row in set (0.020 sec)
|
 |
> create table tt4 as select * from V5Alle3;
|
ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''
|
[yet an hour later:]
It then boils down to:
create table tt7 as select if(`a`.`Wert` > 0,`a`.`Wert`,NULL) AS `Wert` from ((`V5Alle1` `a` left join `T2Bestand` `b` on(`a`.`DID` = `b`.`DID` and `a`.`WPId` = `b`.`WPID`)));
|
Truncated incorrect DECIMAL value: ''
|
and even further:
create table tt8 as select if(`Wert` > 0,`Wert`,666) AS `Wert` from `V5Alle1` ;
|
The "reason" seems buried in the fact that the view delivers some empty strings '' as `Wert`.
I can substitute the view `V5Alle1` by a dummy:
CREATE VIEW `tv1` AS select '' AS `Wert` from `Metaposten`; \# here `Metaposten` just serves as an arbitrary source for lines;
|
Then it happens what is further condensed and described under TLDR:
The combination of comparing a number with a string inside an `if`-statement does not go together with the `create table`.
This is where my capacity to further diagnose (save fix) the problem ends...
PS: these routines worked properly until about Sept. 2022 and were not changed since. At about that time changes to mariadb/mysql were introduced that were considerably more picky about `group by` statements. This is why I classify it as a regression.
The behaviour is "consistent" over numerous versions of mariadb. Also tested on debian and ubuntu.
Attachments
Issue Links
- relates to
-
MDEV-19362 New STRICT flags for sql_mode
-
- Open
-