Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Since version 5.7.5 Mysql has the "ONLY_FULL_GROUP_BY" flag by default enabled and I am trying to make some software that I maintain to be compatible with that.
The most clean way to achieve that is with the ANY_VALUE() function. If you give a query like
SELECT ANY_VALUE(var1), ANY_VALUE(var2), color FROM mybase GROUP BY color
Mysql will not complain that var1 and var2 are not determined.
Unfortunately when I use this under MariaDb I get the following error:
MySQL error 1728: Cannot load from mysql.proc. The table is probably corrupted
As soon as you remove the ANY_VALUE the error disappears - so it must be related to this function.
See also MDEV-30766 where this function is needed for the query to return correct results
Attachments
Issue Links
- duplicates
-
MDEV-12481 The ANY_VALUE() function is not yet included in MariaDB 10.2.5 (RC)
-
- Closed
-
- is part of
-
-
- Open
-
- relates to
-
-
- Closed
-
- links to
Activity
| Field | Original Value | New Value |
|---|---|---|
| Labels | need_feedback |
| Labels | need_feedback |
| Labels | need_feedback |
| Labels | need_feedback |
| Summary | ANY_VALUE gives error 1728: table corrupted | ANY_VALUE function as a workaround for ONLY_FULL_GROUP_BY mode and compatibility with MySQL 5.7 |
| Affects Version/s | 10.1.16 [ 22019 ] | |
| Environment | Windows 10 | |
| Issue Type | Bug [ 1 ] | Task [ 3 ] |
| Component/s | Data Manipulation - Subquery [ 10107 ] |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.2 [ 14601 ] |
| Link |
This issue duplicates |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Labels | Compatibility compat57 |
| Workflow | MariaDB v3 [ 76479 ] | MariaDB v4 [ 130509 ] |
| Labels | Compatibility compat57 | Compatibility compat57 compat80 |
| Link | This issue is part of MDEV-28906 [ MDEV-28906 ] |
| Link |
This issue relates to |
| Description |
Since version 5.7.5 Mysql has the "ONLY_FULL_GROUP_BY" flag by default enabled and I am trying to make some software that I maintain to be compatible with that.
The most clean way to achieve that is with the ANY_VALUE() function. If you give a query like *_SELECT ANY_VALUE(var1), ANY_VALUE(var2), color FROM mybase GROUP BY color_* Mysql will not complain that var1 and var2 are not determined. Unfortunately when I use this under MariaDb I get the following error: *{color:#14892c}MySQL error 1728: Cannot load from mysql.proc. The table is probably corrupted{color}* As soon as you remove the ANY_VALUE the error disappears - so it must be related to this function. |
Since version 5.7.5 Mysql has the "ONLY_FULL_GROUP_BY" flag by default enabled and I am trying to make some software that I maintain to be compatible with that.
The most clean way to achieve that is with the ANY_VALUE() function. If you give a query like *_SELECT ANY_VALUE(var1), ANY_VALUE(var2), color FROM mybase GROUP BY color_* Mysql will not complain that var1 and var2 are not determined. Unfortunately when I use this under MariaDb I get the following error: *{color:#14892c}MySQL error 1728: Cannot load from mysql.proc. The table is probably corrupted{color}* As soon as you remove the ANY_VALUE the error disappears - so it must be related to this function. ---- See also |
| Fix Version/s | 11.2 [ 28603 ] |
| Assignee | Sergei Petrunia [ psergey ] |
| Labels | Compatibility compat57 compat80 | Compatibility compat57 compat80 sql2023 |
| Fix Version/s | 11.3 [ 28565 ] | |
| Fix Version/s | 11.2 [ 28603 ] |
| Fix Version/s | 11.4 [ 29301 ] | |
| Fix Version/s | 11.3 [ 28565 ] |
| Remote Link | This issue links to "SQL-2023 - ANY_VALUE (T626) (Web Link)" [ 36329 ] |
| Summary | ANY_VALUE function as a workaround for ONLY_FULL_GROUP_BY mode and compatibility with MySQL 5.7 | ANY_VALUE function as a ONLY_FULL_GROUP_BY mode, SQL-2023-T626, and compatibility with MySQL 5.7 |
| Summary | ANY_VALUE function as a ONLY_FULL_GROUP_BY mode, SQL-2023-T626, and compatibility with MySQL 5.7 | ANY_VALUE function as a workaround for ONLY_FULL_GROUP_BY mode, SQL-2023-T626, and compatibility with MySQL 5.7 |
| Fix Version/s | 11.5 [ 29506 ] | |
| Fix Version/s | 11.4 [ 29301 ] |
| Fix Version/s | 11.6 [ 29515 ] | |
| Fix Version/s | 11.5 [ 29506 ] |
| Fix Version/s | 11.6 [ 29515 ] |