Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
6.4.6, 22.08.8
-
None
-
2023-11
Description
create schema coltest; use coltest;
|
drop table if exists test_mult;
|
create table test_mult (
|
indemnity_paid int(11),
|
n_clms tinyint(3) unsigned
|
) engine=columnstore;
|
insert into test_mult (indemnity_paid, n_clms) values (-10, 1);
|
select indemnity_paid, n_clms, indemnity_paid * n_clms from test_mult;
|
+----------------+--------+-------------------------+
|
| indemnity_paid | n_clms | indemnity_paid * n_clms |
|
+----------------+--------+-------------------------+
|
| -10 | 1 | 18446744073709551606 |
|
+----------------+--------+-------------------------+
|
The multiplication produces an incorrect value.
For the same table defined engine=innodb, the multiplication produces an expected out-of-range error:
alter table test_mult engine=innodb;
|
select indemnity_paid, n_clms, indemnity_paid * n_clms from test_mult;
|
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`coltest`.`test_mult`.`indemnity_paid` * `coltest`.`test_mult`.`n_clms`'
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Rank | Ranked higher |
| Rank | Ranked lower |
| Labels | triage |
| Labels | triage | mcs_cs_ncci triage |
| Assignee | Sergey Zefirov [ JIRAUSER47362 ] |
| Fix Version/s | 23.10.1 [ 29105 ] |
BTW query can be rewritten
<code>
MariaDB [coltest]> select indemnity_paid, n_clms, indemnity_paid * cast(n_clms as signed) from test_mult;
-------------------------------------------------------------
| indemnity_paid | n_clms | indemnity_paid * cast(n_clms as signed) |
-------------------------------------------------------------
| -10 | 1 | -10 |
-------------------------------------------------------------
1 row in set (0.012 sec)
<code>
Here are some comments on how does type coercion happens in MDB/MySQL:
```
If both operands are integers and any of them are unsigned, the result is an unsigned integer. For subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is signed even if any operand is unsigned.
```
There is another important aspect of this issue. There is a JIT patch almost ready to be merged. This patch is affected by the fact MCS doesn't do overflow or value domain checks like we see in this issue when negative int64 value is bit-casted into uint64. This upcoming change must be also taken into account.
I suggest we search for similar issues filed previously by Bar. If not we should file an umbrella ticket to copy MDB behavior doing math with numerics.
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Labels | mcs_cs_ncci triage | mcs_cs_ncci rm_invalid_data triage |
| Sprint | 2023-11 [ 737 ] |
| Status | In Progress [ 3 ] | In Testing [ 10301 ] |
| Assigned for Review | Roman [ drrtuy ] | |
| Assigned for Testing | Kirill Perov [ JIRAUSER51446 ] |
1.
select indemnity_paid, n_clms, indemnity_paid / n_clms from test_mult;
---------------------------------------------
| indemnity_paid | n_clms | indemnity_paid / n_clms |
---------------------------------------------
| -10 | 1 | -10.0000 |
---------------------------------------------
1 row in set (0.019 sec)
I have expected it should give error according to: "If both operands are integers and any of them are unsigned, the result is an unsigned integer." But both MCS and MDB shows -10 as result here.
2. In non strict mode if out-of-range value is inserted and truncated, MDB shows 1 warning:
Warning (Code 1264): Out of range value for column 'n_clms' at row 1
and MCS shows 2 warnings
Warning (Code 1264): Out of range value for column 'n_clms' at row 1
Warning (Code 1264): CAL0001: MCS-2025: Data truncated for column 'n_clms'
3. For DECIMAL UNSIGNED, FLOAT UNSIGNED and DOUBLE UNSIGNED both MDB and MCS shows -10 as result, no error.
Right, integer operands division produces float.
Could you elaborate on whether integer division actually truncates for the 2nd scenario in your prev comment kirill.perov@mariadb.com?
drrtuy Second scenario was about insertion of values > 256 into tinyint unsigned column.
For division:
MariaDB [coltest]> select indemnity_paid, n_clms, indemnity_paid / n_clms from test_mult;
---------------------------------------------
| indemnity_paid | n_clms | indemnity_paid / n_clms |
---------------------------------------------
| -10 | 1 | -10.0000 |
| -10 | 253 | -0.0395 |
---------------------------------------------
2 rows in set (0.009 sec)
No errors or warnings there.
| Labels | mcs_cs_ncci rm_invalid_data triage | rm_invalid_data triage |
| Resolution | Fixed [ 1 ] | |
| Status | In Testing [ 10301 ] | Closed [ 6 ] |
| Zendesk Related Tickets | 155608 |
MDB promotes the mult type into UNSIGNED BIGINT however their mult processing checks if the signed column value is negative or not. If it is there is an error that is propagated upwards. MCS math processing lacks error propagation.