Oninit
Talk to an expert →

Informix Error -613: TEXT and BYTE columns are not allowed in the "distinct" clause.

Cause and resolution

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

This statement specifies DISTINCT values, but it also selects a BYTE or TEXT value. The database server does not know how to compare or order these values, so it cannot sort them, and it cannot locate duplicate values. You cannot use the DISTINCT keyword when these data types are selected. Reword your SELECT statement to either remove the keyword or select only normal columns.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-613 fires when a SELECT DISTINCT (or SELECT UNIQUE) includes a TEXT/BYTE column — per the official guidance, the server can't compare or order these values, so it can't sort them or locate duplicates, both of which DISTINCT depends on.

  1. SELECT DISTINCT with a TEXT/BYTE column in the select list — the direct, only cause.
  2. A generic deduplication query pattern applied to a table that includes a BLOB column, without excluding it from the DISTINCT select list.
Solutions / Resolution
  1. Remove the DISTINCT keyword, per the official guidance, if selecting the TEXT/BYTE column without deduplication is acceptable.
  2. Or select only normal (comparable) columns with DISTINCT, per the official guidance, leaving the TEXT/BYTE column out of that particular query, and fetch it separately (for example, via the -611-style ROWID-based pattern) for the rows of interest.
Examples
The disallowed attempt
SELECT DISTINCT content FROM documents;
-- -613: content is TEXT
Removing DISTINCT
SELECT content FROM documents;
Or selecting only comparable columns with DISTINCT
SELECT DISTINCT doc_id, title FROM documents;
Diagnostic Checks
  1. Scan SELECT DISTINCT/SELECT UNIQUE statements for a TEXT/BYTE column in the select list, and either drop DISTINCT or drop the BLOB column from that query.
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.
  • -612 — "TEXT and BYTE columns are not allowed in the group by clause." Same family.
  • -614 — "TEXT and BYTE columns are not allowed in the order by clause." Same family.

DISTINCT needs to compare values to find duplicates — TEXT/BYTE can't be compared, so drop either the keyword or the BLOB column from that query.