Adding a New Data Type to MariaDB with Type_handler – Part 4

This is part 4 of a series related to extending MariaDB with a custom data type using the Type_handler framework.

You can find the previous articles below:

• Adding a New Data Type to MariaDB with Type_handler – Part 0 – how to build MariaDB Server
• Adding a New Data Type to MariaDB with Type_handler – Part 1 – understanding the framework
• Adding a New Data Type to MariaDB with Type_handler – Part 2 – minimal working data type
• Adding a New Data Type to MariaDB with Type_handler – Part 3 – data type output transformation

Overriding Existing Types

In the previous examples, our MONEY data type inherits from DOUBLE and then we override some methods.

But all the methods of every type cannot be overridden. For example, if we would like to change how the money column is sorted, we would have to override the Field_double::sort_string() method. But this method is defined as final in sql/field.h:

void sort_string(uchar *buff, uint length) override final;

This means that we need to switch to Field_real for example. But Field_real is abstract and requires that we implement methods that Field_double provided for free.

Therefore, Field_money will have to provide its own numeric field operations (store, val_real, val_str, cmp, …).

This is a table derived directly from the source code that lists all field types:

Field type	Status	Main limitation
Field_num	Abstract base	Numeric helper layer only; not instantiable
Field_str	Abstract base	String helper layer only; not instantiable
Field_longstr	Abstract base	Requires max_data_length and packed_col_length implementation
Field_real	Abstract base	Floating/real helper only; key numeric operations must be implemented by child
Field_decimal	Concrete final	Fixed behavior; cannot subclass
Field_new_decimal	Concrete final	Fixed behavior; cannot subclass
Field_int	Abstract base	Requires type_limits_int implementation
Field_tiny	Concrete	Extendable, but still tied to integer semantics
Field_short	Concrete	Extendable, integer semantics
Field_medium	Concrete final	Cannot subclass
Field_long	Concrete final	Cannot subclass
Field_longlong	Concrete	Extendable, integer semantics
Field_vers_trx_id	Concrete	Specialized versioned transaction-id semantics
Field_float	Concrete final	Cannot subclass
Field_double	Concrete (many methods final)	Critical methods are locked; notably cmp is final in this class
Field_null	Concrete	Represents NULL-like behavior; many operations fixed
Field_temporal	Abstract base	Temporal helper layer only
Field_temporal_with_date	Abstract base	Requires store_TIME and get_TIME
Field_timestamp	Abstract base	Requires store_TIMEVAL
Field_timestamp0	Concrete	Timestamp(0)-style storage semantics
Field_timestamp_with_dec	Abstract base	Fractional timestamp base
Field_timestamp_hires	Concrete	Specialized hires timestamp behavior
Field_timestampf	Concrete	MySQL56 timestamp(0..6) variant
Field_year	Concrete final	Cannot subclass
Field_date_common	Abstract base	Shared date logic; concrete date classes implement storage details
Field_date	Concrete final	Cannot subclass
Field_newdate	Concrete final	Cannot subclass
Field_time	Abstract base	Requires store_TIME
Field_time0	Concrete final	Cannot subclass
Field_time_with_dec	Abstract base	Fractional time base
Field_time_hires	Concrete final	Cannot subclass
Field_timef	Concrete final	Cannot subclass
Field_datetime	Abstract base	Requires date-time low-level methods from child
Field_datetime0	Concrete final	Cannot subclass
Field_datetime_with_dec	Abstract base	Fractional datetime base
Field_datetime_hires	Concrete final	Cannot subclass
Field_datetimef	Concrete final	Cannot subclass
Field_string	Concrete final	Fixed CHAR behavior
Field_varstring	Concrete	Extendable VARCHAR behavior
Field_varstring_compressed	Concrete final	Cannot subclass; key/index behavior intentionally restricted
Field_blob	Concrete	Complex pointer/length semantics; engine-sensitive behavior
Field_blob_compressed	Concrete final	Cannot subclass; key/index operations restricted
Field_enum	Concrete	ENUM semantics; some optimizer paths intentionally restricted
Field_set	Concrete final	Cannot subclass
Field_bit	Concrete	Bit storage has special key/compare constraints
Field_bit_as_char	Concrete final	Cannot subclass
Field_row	Concrete final	Special virtual row type; many normal field operations are not applicable

“Abstract” means you cannot instantiate it directly; you must derive and implement missing behavior.
“Concrete” means instantiable.
“Final” means concrete and cannot be subclassed.

Deriving from Field_real

So, as we need to derive from Field_real to be able to modify the sorting, we need to implement these in our class to be concrete and useful:

1. type_handler
2. key_type
3. store from string
4. store from double
5. store from longlong
6. reset
7. val_real
8. val_int
9. val_str
10. send
11. cmp
12. sort_string
13. pack_length
14. row_pack_length
15. get_max_int_value
16. binlog_type_info

This is exactly what we did, and we modified even more the sort_string() method to sort the content of our MONEY column in reverse order:

void Field_money::sort_string(uchar *buff, uint length __attribute__((unused)))
{
  double nr;
  float8get(nr,ptr);
  change_double_for_sort(nr, buff);

  // Invert key bytes so lexicographic ascending comparison
  // yields descending order.
  for (uint i= 0; i < sizeof(double); i++)
    buff[i]^= 0xFF;
}

This is a stupid sort, but it’s there only as an example.

Let’s test it:

MariaDB [test]> install soname 'type_money';
Query OK, 0 rows affected (0.012 sec)

MariaDB [test]> create table t1 (id int auto_increment primary key, amount money(10,2)) engine=innodb;
Query OK, 0 rows affected (0.008 sec)

MariaDB [test]> insert into t1 (amount) values (15678.309);
Query OK, 1 row affected (0.016 sec)

MariaDB [test]> insert into t1 (amount) values (24.4);
Query OK, 1 row affected (0.003 sec)

MariaDB [test]> insert into t1 (amount) values (41578.4);
Query OK, 1 row affected (0.003 sec)

MariaDB [test]> select * from t1 order by amount;
+----+-----------+
| id | amount    |
+----+-----------+
|  3 | $41578.40 |
|  1 | $15678.31 |
|  2 | $24.40    |
+----+-----------+
3 rows in set (0.002 sec)

We can see that the order is reversed.

The code is available in the part4 branch of the GitHub repo.

Happy compiling and happy coding.