Bug #77918 Expose unique columns in VIEWS
Submitted: 3 Aug 2015 11:05
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Open Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:>=5.0.x OS:Any
Assigned to: CPU Architecture:Any

[3 Aug 2015 11:05] Peter Laursen
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