alter() loses an attribute's values when it renames a core-type attribute #1631

Summary

alter() loses an attribute's values when it renames a core-type attribute. A comment that opens with {old_name} asks alter() to rename that attribute. _make_attribute_alter looks for the marker in the compiled column SQL, where it is not always found:

• A core type or codec puts its :type: marker before the comment: COMMENT ":float32:{value} the value". The marker no longer opens the comment, so the rename is not detected.
• PostgreSQL keeps comments out of the column SQL, so no rename is ever detected there.

The undetected rename becomes "drop the old attribute, add the new one".

• MySQL: the new attribute is filled with the type's zero value, or NULL for a nullable attribute.
• PostgreSQL: a nullable attribute is filled with NULL; a required one fails with a NOT NULL error.

With prompt=False nothing warns. With the prompt, the statement shown is a DROP and an ADD, which is easy to miss.

Reproduction

DataJoint 2.3.4 and master:

import datajoint as dj

schema = dj.Schema("scratch_alter_rename")


@schema
class Measurement(dj.Manual):
    definition = """
    measurement_id : int32
    ---
    value : float32   # the value
    """


Measurement.insert([(1, 2.5)])
Measurement.definition = """
measurement_id : int32
---
reading : float32   # {value} the value
"""
Measurement.alter(prompt=False)
print(Measurement.to_dicts())
# MySQL: [{'measurement_id': 1, 'reading': 0.0}]

Expected behavior

A rename keeps the values: MySQL's CHANGE, and RENAME COLUMN on PostgreSQL. Where a rename cannot be done, alter() refuses before changing anything. It never drops the attribute.