bug: length() funciton in tianmu return different results with mysql

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> CREATE TABLE tin3(id int,val TINYINT(10) ZEROFILL);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tin3 VALUES(1,12),(2,7),(4,101);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tin3;
+------+------------+
| id   | val        |
+------+------------+
|    1 | 0000000012 |
|    2 | 0000000007 |
|    4 | 0000000101 |
+------+------------+
3 rows in set (0.00 sec)

mysql> SELECT LENGTH(val) FROM tin3 WHERE id=2;
+-------------+
| LENGTH(val) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

Expected behavior

mysql> SELECT LENGTH(val) FROM tin3 WHERE id=2;
+-------------+
| LENGTH(val) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

How To Reproduce

CREATE TABLE tin3(id int,val TINYINT(10) ZEROFILL);
INSERT INTO tin3 VALUES(1,12),(2,7),(4,101);
SELECT * FROM tin3;
SELECT LENGTH(val) FROM tin3 WHERE id=2;

Environment

root@ub01:/stonedb57/install# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: e395f89e1
        Last commit time: Date:   Tue Dec 6 01:04:24 2022 +0800
        Build time: Date: Tue Dec  6 16:03:58 CST 2022
root@ub01:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.5 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.5 LTS"
VERSION_ID="20.04"

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[davidshiz]

Maybe similar to #1092

[hustjieke]

This error is not caused by column attr zerofill, if the column is specified with key_part length, mysql will return specified length but tianmu just return the actuall type size. e.g.:

mysql> create table ln(a int(11)) engine=tianmu;
Query OK, 0 rows affected (4.26 sec)

mysql> insert into ln values(13),(14);
Query OK, 2 row affected (4.12 sec)

mysql> select length(a) from ln;
+-----------+
| length(a) |
+-----------+
|         2 |
|         2 |
+-----------+
2 rows in set (0.00 sec)

For length() operation, defined in item_func.h

class Item_func_length :public Item_int_func
{
  String value;
public:
  Item_func_length(const POS &pos, Item *a) :Item_int_func(pos, a) {}
  longlong val_int();
  const char *func_name() const { return "length"; }
  void fix_length_and_dec() { max_length=10; }
};

[hustjieke]

For int numeric types, fix the problem with mysql code in Item_tianmufield::val_str() like:

String *Field_short::val_str(String *val_buffer,...{
  if (zerofill) 
        prepend_zeros(val_buffer);   // reset value and m_length
...
}

The zerofill flag in Field_num cannot deal with bit type, also, the float, double has the same problem.

class Field_num :public Field {
public:
  const uint8 dec;
  bool zerofill,unsigned_flag;	// Purify cannot handle bit fields

[hustjieke]

For real number float, double, length() results diff with mysql, the field defines without zerofill:

mysql> create table float_t1(a float(6));
Query OK, 0 rows affected (0.01 sec)

mysql> create table float_i1(a float(6)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into float_t1 values(11.11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into float_i1 values(11.11);
Query OK, 1 row affected (0.01 sec)

mysql> select a from float_t1;
+-------+
| a     |
+-------+
| 11.11 |
+-------+
1 row in set (0.00 sec)

mysql> select a from float_i1;
+-------+
| a     |
+-------+
| 11.11 |
+-------+
1 row in set (0.00 sec)

mysql> select length(a) from float_i1;
+-----------+
| length(a) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

mysql> select length(a) from float_t1;
+-----------+
| length(a) |
+-----------+
|        18 |
+-----------+
1 row in set (0.01 sec)