MySQL Compatibility: Support AUTO_INCREMENT on floating-point types (DOUBLE, FLOAT)

[dengn]

Description

MatrixOne's AUTO_INCREMENT feature only supports integer types (INT, BIGINT, etc.) and does not support floating-point types (DOUBLE, FLOAT) that MySQL supports.

Error Message

ERROR 20105 (HY000) at line 1: not supported: the auto_incr column is only support integer type now

Failing SQL Statements

The following SQL statements fail in MatrixOne but work correctly in MySQL:

-- AUTO_INCREMENT on DOUBLE type
CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1));

-- AUTO_INCREMENT on FLOAT type
CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1));

-- AUTO_INCREMENT on DECIMAL type (if applicable)
CREATE TABLE t1 (c1 DECIMAL(10,2) NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1));

Expected Behavior

MySQL supports AUTO_INCREMENT on various numeric types including:

• Integer types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT (signed and unsigned)
• Floating-point types: FLOAT, DOUBLE
• Decimal types: DECIMAL (in some MySQL versions)

When using AUTO_INCREMENT on floating-point types, MySQL generates sequential floating-point values.

Test Context

This issue was discovered during MySQL compatibility testing using the official MySQL test suite (mysql-test/t/auto_increment.test). The test cases that use DOUBLE with AUTO_INCREMENT fail in MatrixOne.

Impact

• MySQL Compatibility: This limits compatibility with MySQL applications that use floating-point AUTO_INCREMENT columns
• Feature Limitation: Users cannot use AUTO_INCREMENT on non-integer numeric types
• Migration: Applications using floating-point AUTO_INCREMENT will fail when migrating to MatrixOne

Technical Details

AUTO_INCREMENT on floating-point types is less common but is a valid MySQL feature. The implementation would need to:

1. Generate sequential floating-point values
2. Handle precision and rounding appropriately
3. Maintain uniqueness constraints

Suggested Fix

1. Extend AUTO_INCREMENT support to include floating-point types (DOUBLE, FLOAT)
2. Implement proper value generation for floating-point AUTO_INCREMENT
3. Ensure uniqueness constraints work correctly with floating-point values
4. Add appropriate error handling for edge cases (precision, overflow, etc.)

Alternative (If Full Support is Not Feasible)

If full floating-point AUTO_INCREMENT support is not immediately feasible:

1. Provide a clearer error message indicating this is a known limitation
2. Document the limitation in compatibility guides
3. Consider providing a migration path or workaround for users