Issue - unexpected values returned on query

Unexpected results are returned for SELECT...DISTINCT and SELECT...GROUP BY queries that include columns assigned the following data types:

• IDSET
• IDSETQ
• STRINGSET
• STRINGSETQ

Cause

In a typical database, each field – the intersection of row and column – contains an individual value.

FeatureBase SET and SETQ data types are designed to contain an array of comma-separated values within each field.

This means:

• data does not need to be normalized into separate tables,
• import/ingest actions are faster because there are less rows to import,
• queries are faster

Queries on SET and SETQ data return unexpected results:

• SELECT...DISTINCT returns the number of fields, not the number of unique values
• SELECT...GROUP BY returns the array of values in each field

Solution

• Learn how to use the FLATTEN() hint to return expected values

Further information

• SELECT statement

---