INSERT fails with a suffix on quoted column name

[duckbrain]

Description

Pop can now generate column names in a different sorted order when the column names are quoted. This causes inserts not to work or to insert data in the wrong columns.

Steps to Reproduce the Problem

Please describe in painful detail what you did (so others can play along with you) to get to this point. This includes things like the exact command(s) you used, or the curl command you used, that sort of thing.

1. Take an existing Buffalo project and update it to 0.14.9, so we can use the updated escaped column names
2. Convert the database.yml from Postgres to MySQL
3. Run some code, encounter new errors on inserts.

My Specific inputs and outputs:

My struct looks like this:

type Transaction struct {
	ID                uuid.UUID     `json:"id" db:"id"`
	CreatedAt         time.Time     `json:"created_at" db:"created_at"`
	UpdatedAt         time.Time     `json:"updated_at" db:"updated_at"`
	HospitalID        uuid.UUID     `json:"hospital_id" db:"hospital_id"`
	PharmacyID        uuid.UUID     `json:"pharmacy_id" db:"pharmacy_id"`
	UserID            uuid.UUID     `json:"user_id" db:"user_id"`
	ProductID         nulls.UUID    `json:"product_id" db:"product_id"`
	ProcedureID       nulls.UUID    `json:"procedure_id" db:"procedure_id"`
	KitVendorID       nulls.UUID    `json:"kit_vendor_id" db:"kit_vendor_id"`
	KitManufacturerID nulls.UUID    `json:"kit_manufacturer_id" db:"kit_manufacturer_id"`
	InternalID        nulls.Int64   `json:"internal_id" db:"internal_id"`
	InternalLotNumber nulls.String  `json:"internal_lot_number" db:"internal_lot_number"`
	Amount            nulls.Float64 `json:"amount" db:"amount"`
	AmountUnit        nulls.String  `json:"amount_unit" db:"amount_unit"`
	Volume            nulls.Float64 `json:"volume" db:"volume"`
	VolumeUnit        nulls.String  `json:"volume_unit" db:"volume_unit"`
	Quantity          uint64        `json:"quantity" db:"quantity"`
	BilledAmount      int64         `json:"billed_amount" db:"billed_amount"`
	Credited          bool          `json:"credited" db:"credited"`
	CreditAmount      nulls.Int64   `json:"credit_amount" db:"credit_amount"`
	OrderedAt         time.Time     `json:"ordered_at" db:"ordered_at"`
	ShippedAt         time.Time     `json:"shipped_at" db:"shipped_at"`
	CalibrationAt     time.Time     `json:"calibration_at" db:"calibration_at"`
	StatementNumber   string        `json:"statement_number" db:"statement_number"`
	StatementFrom     nulls.Time    `json:"statement_from" db:"statement_from"`
	StatementTo       nulls.Time    `json:"statement_to" db:"statement_to"`
	InvoiceID         nulls.Int64   `json:"invoice_id" db:"invoice_id"`
	ReportAt          nulls.Time    `json:"report_at" db:"report_at"`
	DeletedAt         nulls.Time    `json:"deleted_at" db:"deleted_at"`
}

And the generated INSERT looks like this (with whitespace added):

INSERT INTO `transactions` (
    `amount_unit`,
    `amount`,
    `billed_amount`,
    `calibration_at`,
    `created_at`,
    `credit_amount`,
    `credited`,
    `deleted_at`,
    `hospital_id`,
    `id`,
    `internal_id`,
    `internal_lot_number`,
    `invoice_id`,
    `kit_manufacturer_id`,
    `kit_vendor_id`,
    `ordered_at`,
    `pharmacy_id`,
    `procedure_id`,
    `product_id`,
    `quantity`,
    `report_at`,
    `shipped_at`,
    `statement_from`,
    `statement_number`,
    `statement_to`,
    `updated_at`,
    `user_id`,
    `volume_unit`,
    `volume`
) VALUES (
    :amount,
    :amount_unit,
    :billed_amount,
    :calibration_at,
    :created_at,
    :credit_amount,
    :credited,
    :deleted_at,
    :hospital_id,
    :id,
    :internal_id,
    :internal_lot_number,
    :invoice_id,
    :kit_manufacturer_id,
    :kit_vendor_id,
    :ordered_at,
    :pharmacy_id,
    :procedure_id,
    :product_id,
    :quantity,
    :report_at,
    :shipped_at,
    :statement_from,
    :statement_number,
    :statement_to,
    :updated_at,
    :user_id,
    :volume,
    :volume_unit
)

Notice that the XXX_unit columns are sorted before their XXX counterparts in the escaped version, but in reverse for the symbolized column names.

Expected Behavior

The columns would be in a consistent order and the query would have worked.

Actual Behavior

The query as described above appeared and it attempted to put a string into a float column.

Info

	github.com/gobuffalo/pop v4.11.3+incompatible

Yes, using Pop through Buffalo.

Test case

I was able to produce a test that demonstrates the issue in columns/columns_test.go:

type fooOrder struct {
	Amount      float64 `db:"amount"`
	AmountUnits string  `db:"amount_units"`
}
type fooQuoter struct{}

func (fooQuoter) Quote(key string) string {
	return fmt.Sprintf("`%v`", key)
}

func Test_Columns_Sort(t *testing.T) {
	r := require.New(t)

	c := columns.ForStruct(fooOrder{}, "fooOrder")
	r.Equal(len(c.Cols), 2)
	r.Equal(c.SymbolizedString(), ":amount, :amount_units")
	r.Equal(c.String(), "amount, amount_units")
	r.Equal(c.QuotedString(fooQuoter{}), "`amount`, `amount_units`")
}

I also have a simple fix that I will create a PR for.

[stanislas-m]

Fixed with #430.