Inserting value into DECIMAL column with check constraint >0.00 gives SQL error 1292 Truncated incorrect DOUBLE value

10.5.10-MariaDB

I have two tables, test1 and test2. Each has a single column 'a_number' of datatype DECIMAL(5,2). test1 has the constraint CHECK('a_number' > 0.00). test2 is a tablecopy of test1 with the check constraint deleted. I have added a single value into test2 of 1.23.

When I run the following query

    INSERT INTO test1 (a_number)
    SELECT a_number FROM test2;

I receive SQL error (1292) Truncated incorrect DOUBLE value 'a_number'

| test1 | CREATE TABLE `test1` (
  `a_number` decimal(5,2) NOT NULL,
  CONSTRAINT `CC1` CHECK ('a_number' > 0.00)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

| test2 | CREATE TABLE `test2` (
  `a_number` decimal(5,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |

SELECT * FROM test2;
+----------+
| a_number |
+----------+
|     1.23 |
+----------+

Internet searches suggest the error occurs due to comparing different datatypes. I have tried adding a CAST to the literal in the CHECK to convert from a string to decimal but this had no effect. Just adding single quotes as some posts seemed to suggest also did not work (the error went away but the constraint did not work indicating it was now treating the check literal as a string).

Solution

That was a tricky one

you used in the check constraint single quotes, so maradb compare the text 'a_namber' with a number, which lead to the error message

Please read carefully folowing thread When to use single quotes, double quotes, and backticks in MySQL

CREATE TABLE `test1` (
  `a_number` decimal(5,2) UNSIgNED NOT NULL,
  CONSTRAINT `CC1` CHECK (`a_number` > 0.00)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `test2` (
  `a_number` decimal(5,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

INSERT INTO test2 VALUES (1.1)

INSERT INTO test1 (a_number)
SELECT a_number FROM test2

✓

SELECT * FROM test1

| a_number |
| -------: |
|     1.10 |

db<>fiddle here