Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space ML1 and version 9.2
Excerpt

ClustrixDB supports Generated Columns, which are computed based on values from other columns. 

They are defined as part of the column’s definition and can be:

...

sql> explain select * from employees where fullname like 'smith%';
+-------------------------------------------------------------------------------------------------------------+-----------+-----------+
| Operation                                                                                                   | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------------------------------------------+-----------+-----------+
| stream_combine                                                                                              |    212.50 |      0.51 |
|      filter (1.fullname >= param(3)) and (1.fullname < param(2)) and like(1.fullname, param(1), param(0))     |    211.45 |      0.51 |
|        index_scan 1 := EMPLOYEES.__idx_EMPLOYEES__PRIMARY                                                     |    211.33 |      2.00 |
+------------------------------------------------------------------------------------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)

...

sql> explain select * from employees where (CONCAT(first_name,' ',middle_name, ' ', last_name)) like '%smith';
+-------------------------------------------------------------------------------------------------------------+-----------+-----------+
| Operation                                                                                                   | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------------------------------------------+-----------+-----------+
| stream_combine                                                                                                | 213.32    |      2.70 |
|      filter like(concat(1.first_name, param(3), 1.middle_name, param(2), 1.last_name), param(1), param(0))    | 212.05    |      2.70 |
|        index_scan 1 := EMPLOYEES.__idx_EMPLOYEES__PRIMARY                                                     | 211.99    |      3.00 |
+-------------------------------------------------------------------------------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)

...

ClustrixDB matches all MySQL functionality for generated columns except:

  • Clustrix ClustrixDB allows generated columns to be created on in-memory tables. MySQL does not.
  • ClustrixDB does not allow FROM_UNIXSTAMP to be used in generated columns
  • If a generated column uses a smaller integer type than the column it is based on, ClustrixDB will clip the values to fit. On MYSQL, the values will overflow.
  • ClustrixDB permits non-deterministic functions in generated columns. MySQL does not.
  • ClustrixDB does not permit inserting into generated columns with value DEFAULT.
  • Error messages generated by ClustrixDB differ from those of MySQL.

...