Bug #77918 Expose unique columns in VIEWS

• Submitted: 3 Aug 2015 11:05
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Open
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: >=5.0.x
• OS: Any
• CPU Architecture: Any

[3 Aug 2015 11:05] Peter Laursen

Description:
VIEWs are updatable if specific criteria are met.  However VIEWs don't have indexes, so when writing an UPDATE statement to be executed against a VIEW adressing a specific row and if you don't know (don't have access or for other reasons) the table definitions of the underlying table(s) you wll have to write the WHERE-clause of an UPDATE statement like 

"WHERE 1st_column = some_value AND 2nd_column = some_other_value AND .. "
(listing every column of the VIEW in the WHERE-clause)

You will not be able to identify unique column-values and cannot wríte it more effiently  like:
"WHERE unique_column = some_value"

There is further particular a problem with VIEWs having FLOAT/DOUBLE values due to this wellknown problem with such types http://dev.mysql.com/doc/refman/5.6/en/problems-with-float.html
Here the query with the full column listing may fail to UPDATE. 

How to repeat:
See above.

Suggested fix:
Somehow expose uniqueness of columns in a VIEW. In SHOW CREATE VIEW and/or in I_S.Views