MariaDB problem with bit column, check constraint and JSON - check constraint is violated

Question

I have the following table on 10.3.35 MariaDB server:

CREATE TABLE `_boat_product` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `standard` BIT(1) NOT NULL DEFAULT b'0',
    `selected_by_default` BIT(1) NOT NULL DEFAULT b'0',
    `date_created` DATETIME NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    CONSTRAINT `CC1` CHECK (cast(`standard` as signed) + cast(`selected_by_default` as signed) <= 1)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1486
;

I fetch data like this (PHP):

  function select($order) {

    $query = $this->db->query("SELECT id, "
            . "standard,"
            . "selected_by_default,"
            . "DATE_FORMAT(date_created, '%Y-%m-%d %H:%i') date_created "
            . "FROM _boat_product "
            . $order);
    return ['result' => 'OK', 'data' => $query->result()];
  }

  echo json_encode($this->select($order));

I have a tabulator.js table set like this (excerpt):

  columns: [
      {title: "standard", field: "standard", editor: "tickCross", minWidth: 50, maxWidth: 80, headerFilter: true, hozAlign: "center", headerVertical: true,
        editorParams: {
          trueValue: "1",
          falseValue: "0"
        }, formatter: "tickCross",
        formatterParams: {
          tickElement: "yes",
          crossElement: "no"
        }
      },
      {title: "default", field: "selected_by_default", editor: "tickCross", minWidth: 50, maxWidth: 80, headerFilter: true, hozAlign: "center", headerVertical: true,
        editorParams: {
          trueValue: "1",
          falseValue: "0"
        }, formatter: "tickCross",
        formatterParams: {
          tickElement: "yes",
          crossElement: "no"
        }
      },...

Data that I am sending to the server to update records looks like this:

{
    "data": {
        "data": [
            {
                "id": "483",
                "selected_by_default": "1",
                "standard": "0"
            }
        ],
        "function": "insertUpdate"
    }
}

and the update statement is like this:

UPDATE `_boat_product` SET `standard` = '0', `selected_by_default` = '1'
WHERE `id` = '483'

But I am getting an error that check constraint is violated:

Error Number: 4025

CONSTRAINT `CC1` failed for...

Why that is happening?

Dejan Dozet · Answer

The problem seems to be with the conversion from VARCHAR to BIT(1).

UPDATE `_boat_product` SET `standard` = '0', `selected_by_default` = '1'
WHERE `id` = '483'

would work only like this:

UPDATE `_boat_product` SET `standard` = b'0', `selected_by_default` = b'1'
WHERE `id` = '483'

but because I can't complicate it too much so the solution was to change BIT(1) column type to TINYINT(1).

Georg Richter · Answer

It is a common misconception that BIT(1) is the appropriate data type to store true/false values:

BIT(1) requires 1 byte storage (bytes = (bit_length + 7)/8 as does TINYINT. In contrast to fixed length TINYINT, BIT requires additional metadata to store the number of bits: In the binary log protocol, 2 additional bytes must be used for this in table_map event, on the client side it must be checked whether the value must be stored in a 1,2,4 or 8 byte integer.
BIT(1) values are 0x00 and 0x01 binary values and cannot be displayed without prior conversion, e.g. in the command line client (bin+0,hex(bin),oct(bin),bin(bin))
BIT values always have to be converted to a number or binary before you can modify it, while a TINYINT value can be passed as a number or string.

I would suggest to change the table definition from BIT(1) to TINYINT,if this is not possible you need to unset strict_mode:

MariaDB [test]> create table bit_test(a bit(1));
Query OK, 0 rows affected (0,016 sec)

MariaDB [test]> insert into bit_test values ("1");
ERROR 1406 (22001): Data too long for column 'a' at row 1
MariaDB [test]> set sql_mode=0;
Query OK, 0 rows affected (0,001 sec)

MariaDB [test]> insert into bit_test values ("1");
Query OK, 1 row affected, 1 warning (0,011 sec)

MariaDB problem with bit column, check constraint and JSON - check constraint is violated

2 Answers