Miscellaneous Functions Name Description IS_IPV6() Whether argument is an IPv6 address IS_UUID() Whether argument is a valid UUID MASTER_POS_WAIT() Block until the replica has read and applied all updates up to the specified position NAME_CONST() Cause the column to have the given name SLEEP() Sleep for a number of seconds UUID() Return a Universal Unique Identifier (UUID) UUID_SHORT() Return an integer-valued universal identifier UUID_TO_BIN() Convert string UUID to binary VALUES() Define the values to be used during an INSERT • ANY_VALUE( arg ) •Use  ANY_VALUE()  to refer to  address : •Alter the table to make  name  a primary key or a unique  NOT NULL  column. This enables MySQL to determine that  address  is functionally dependent on  name ; that is,  address  is uniquely determined by  name . (This technique is inapplicable if  NULL  must be permitted as a valid  name  value.) The failure occurs because  address  is a nonaggregated column that is neither named among  GROUP BY  columns nor functionally dependent on them. As a result, the  address  value for rows within each name  group is nondeterministic. There are multiple ways to cause MySQL to accept the query: For example, if  name  is a nonindexed column, the following query fails with  ONLY_FULL_GROUP_BY enabled: mysql>  SELECT name, address, MAX(age) FROM t GROUP BY name; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ’mydb.t.address’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by This function is useful for  GROUP BY  queries when the  ONLY_FULL_GROUP_BY  SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the  ONLY_FULL_GROUP_BY  SQL mode. In this case, MySQL ignores the nondeterminism of  address  values within each  name  group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group.  ANY_VALUE()  is not an aggregate function, unlike functions such as SUM()  or  COUNT() . It simply acts to suppress the test for nondeterminism. SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; ANY_VALUE()  is also useful if functional dependence exists between columns but MySQL cannot determine it. The following query is valid because  age  is functionally dependent on the grouping column age-1 , but MySQL cannot tell that and rejects the query with  ONLY_FULL_GROUP_BY  enabled: SELECT age FROM t GROUP BY age-1; 2439 •Disable  ONLY_FULL_GROUP_BY . This is equivalent to using  ANY_VALUE()  with ONLY_FULL_GROUP_BY  enabled, as described in the previous item.