Repository: ariga/atlas
Pull request #1958 (2023-08-07 13:37:44)

sql/mysql: respect zerofill attribute in float/double types
Note, as of MySQL 8.0.17, the ZEROFILL attribute is deprecated and expected to be removed in future versions.

Fixed https://github.com/ariga/atlas/issues/1937


Patch:
diff --git a/sql/internal/sqlx/sqlx.go b/sql/internal/sqlx/sqlx.go
index 36d84684..2cc39230 100644
--- a/sql/internal/sqlx/sqlx.go
+++ b/sql/internal/sqlx/sqlx.go
@@ -14,6 +14,7 @@ import (
 	"reflect"
 	"strconv"
 	"strings"
+	"unicode"
 
 	"ariga.io/atlas/sql/schema"
 )
@@ -570,3 +571,13 @@ func V[T any](p *T) (v T) {
 	}
 	return
 }
+
+// IsUint reports whether the string represents an unsigned integer.
+func IsUint(s string) bool {
+	for _, r := range s {
+		if !unicode.IsDigit(r) {
+			return false
+		}
+	}
+	return true
+}
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index e09d8595..8ecb26f1 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -592,18 +592,15 @@ func parseColumn(typ string) (parts []string, size int, unsigned bool, err error
 	switch parts = strings.FieldsFunc(typ, func(r rune) bool {
 		return r == '(' || r == ')' || r == ' ' || r == ','
 	}); parts[0] {
+	case TypeBit, TypeBinary, TypeVarBinary, TypeChar, TypeVarchar:
 	case TypeTinyInt, TypeSmallInt, TypeMediumInt, TypeInt, TypeBigInt,
 		TypeDecimal, TypeNumeric, TypeFloat, TypeDouble, TypeReal:
 		if attr := parts[len(parts)-1]; attr == "unsigned" || attr == "zerofill" {
 			unsigned = true
 		}
-		if len(parts) > 2 || len(parts) == 2 && !unsigned {
-			size, err = strconv.Atoi(parts[1])
-		}
-	case TypeBit, TypeBinary, TypeVarBinary, TypeChar, TypeVarchar:
-		if len(parts) > 1 {
-			size, err = strconv.Atoi(parts[1])
-		}
+	}
+	if len(parts) > 1 && sqlx.IsUint(parts[1]) {
+		size, err = strconv.Atoi(parts[1])
 	}
 	if err != nil {
 		return nil, 0, false, fmt.Errorf("parse %q to int: %w", parts[1], err)
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index d4b7624a..f49c0ef6 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -158,7 +158,7 @@ func (d *diff) Normalize(from, to *schema.Table) error {
 			if used[i] {
 				continue
 			}
-			if fk2.Symbol == fk1.Symbol && !isNumber(fk1.Symbol) || sameFK(fk1, fk2) {
+			if fk2.Symbol == fk1.Symbol && !sqlx.IsUint(fk1.Symbol) || sameFK(fk1, fk2) {
 				fk1.Symbol = fk2.Symbol
 				used[i] = true
 			}
diff --git a/sql/sqlite/inspect.go b/sql/sqlite/inspect.go
index e4467556..3bfbce20 100644
--- a/sql/sqlite/inspect.go
+++ b/sql/sqlite/inspect.go
@@ -11,7 +11,6 @@ import (
 	"regexp"
 	"strconv"
 	"strings"
-	"unicode"
 
 	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/schema"
@@ -510,7 +509,7 @@ func columnParts(t string) []string {
 	})
 	for k := 0; k < 2; k++ {
 		// Join the type back if it was separated with space (e.g. 'varying character').
-		if len(parts) > 1 && !isNumber(parts[0]) && !isNumber(parts[1]) {
+		if len(parts) > 1 && !sqlx.IsUint(parts[0]) && !sqlx.IsUint(parts[1]) {
 			parts[1] = parts[0] + " " + parts[1]
 			parts = parts[1:]
 		}
@@ -531,16 +530,6 @@ func defaultExpr(x string) schema.Expr {
 	}
 }
 
-// isNumber reports whether the string is a number (category N).
-func isNumber(s string) bool {
-	for _, r := range s {
-		if !unicode.IsNumber(r) {
-			return false
-		}
-	}
-	return true
-}
-
 // blob literals are hex strings preceded by 'x' (or 'X).
 func isBlob(s string) bool {
 	if (strings.HasPrefix(s, "x'") || strings.HasPrefix(s, "X'")) && strings.HasSuffix(s, "'") {


Test patch:
diff --git a/sql/internal/sqlx/sqlx_test.go b/sql/internal/sqlx/sqlx_test.go
index 3cc280a2..e26b9821 100644
--- a/sql/internal/sqlx/sqlx_test.go
+++ b/sql/internal/sqlx/sqlx_test.go
@@ -246,3 +246,10 @@ func TestReverseChanges(t *testing.T) {
 		})
 	}
 }
+
+func TestIsUint(t *testing.T) {
+	require.True(t, IsUint("1"))
+	require.False(t, IsUint("-1"))
+	require.False(t, IsUint("1.2"))
+	require.False(t, IsUint("1.2.3"))
+}
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 12a8e8cf..dc2f6ac1 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -236,14 +236,15 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+------------+-------------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
-| table_name |    column_name    | column_type        | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name | generation_expression     |
-+------------+-------------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
-| users      |  float            | float              |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
-| users      |  double           | double             |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
-| users      |  float_unsigned   | float unsigned     |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
-| users      |  double_unsigned  | double unsigned    |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
-| users      |  float_unsigned_p | float(10) unsigned |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
++------------+----------------------------+--------------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
+| table_name |    column_name             | column_type              | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name | generation_expression     |
++------------+----------------------------+--------------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
+| users      |  float                     | float                    |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
+| users      |  double                    | double                   |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
+| users      |  float_unsigned            | float unsigned           |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
+| users      |  double_unsigned           | double unsigned          |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
+| users      |  float_unsigned_p          | float(10) unsigned       |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
+| users      |  doubled_zerofill_unsigned | double unsigned zerofill |                | NO          |            |                |       | NULL               | NULL           | NULL                      |
 +------------+-------------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
 `))
 				m.noIndexes()
@@ -258,6 +259,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "float_unsigned", Type: &schema.ColumnType{Raw: "float unsigned", Type: &schema.FloatType{T: "float", Unsigned: true}}},
 					{Name: "double_unsigned", Type: &schema.ColumnType{Raw: "double unsigned", Type: &schema.FloatType{T: "double", Unsigned: true}}},
 					{Name: "float_unsigned_p", Type: &schema.ColumnType{Raw: "float(10) unsigned", Type: &schema.FloatType{T: "float", Precision: 10, Unsigned: true}}},
+					{Name: "doubled_zerofill_unsigned", Type: &schema.ColumnType{Raw: "double unsigned zerofill", Type: &schema.FloatType{T: "double", Unsigned: true}}},
 				}, t.Columns)
 			},
 		},