Oninit
Talk to an expert →

Informix Error -612: TEXT and BYTE columns are not allowed in the "group by" clause.

Cause and resolution

TEXT and BYTE columns are not allowed in the "group by" clause.

This SELECT statement selects one or more BYTE or TEXT values and also specifies those columns in the GROUP BY clause. This action is not supported. Since no defined lexical order to BYTE or TEXT values exists, the database server cannot order or compare them. Therefore it cannot group rows on their values. (This condition is true even of substrings selected from a BYTE or TEXT column.) Review your SELECT statement to ensure that the correct columns are named in the GROUP BY clause.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-612 fires when a SELECT's GROUP BY clause names a TEXT/BYTE column — per the official guidance, these types have no defined lexical order, so the server can't compare values to group rows by them, even when the value used is a substring of a TEXT/BYTE column rather than the whole thing.

  1. A TEXT/BYTE column named directly in GROUP BY — the direct, only cause.
  2. A substring of a TEXT/BYTE column named in GROUP BY, per the official guidance's explicit note — substring notation doesn't escape the restriction, since the underlying type is still uncomparable.
  3. A GROUP BY clause built to match a SELECT list that includes a BLOB column, under the assumption grouping works the same way it does for ordinary columns.
Solutions / Resolution
  1. Review the SELECT statement to confirm the correct columns are named in GROUP BY, per the official guidance — remove the TEXT/BYTE column (or substring of one) from the grouping clause.
  2. Group by a derived, comparable value instead, if grouping by some property of the BLOB content is genuinely needed — extract that property into a separate column at insert/update time (application-level, since Informix has no built-in conversion from TEXT/BYTE, per -608/-609), and group by that column instead.
Examples
The disallowed attempt
SELECT content, COUNT(*) FROM documents GROUP BY content;
-- -612: content is TEXT
Grouping by a derived column instead
ALTER TABLE documents ADD category VARCHAR(50);
-- category populated at the application level from the document's content
SELECT category, COUNT(*) FROM documents GROUP BY category;
Diagnostic Checks
  1. Scan the GROUP BY clause for any TEXT/BYTE column or substring of one, and replace it with a comparable derived column.
Related Errors / Related Topics
  • -611 — "Scroll cursor can't select TEXT or BYTE columns." Same family of restrictions rooted in TEXT/BYTE's lack of a defined lexical ordering.
  • -613 — "TEXT and BYTE columns are not allowed in the distinct clause." Same family.
  • -614 — "TEXT and BYTE columns are not allowed in the order by clause." Same family.

Applies even to a substring of a TEXT/BYTE column, per the official guidance — group by a derived, comparable column instead.