LLMs.txt directory

Data value casing

You can define the case of columns in a Model, by changing the case of the columns at table level. By default, ThoughtSpot uses the LOWER function for string comparisons. In cases where this function impacts performance, you may want to use the data value casing feature to change the column’s case.

This feature is not the same as case-sensitive search.

Data value case behavior

The following table describes how ThoughtSpot generates SQL for string comparisons depending on the data value casing setting for a column.

Setting SQL behavior

DEFAULT

ThoughtSpot applies the LOWER() function to the column during string comparison. This is the standard behavior and matches the default case-insensitive search.

MIXED

ThoughtSpot applies the LOWER() function to the column during string comparison. Equivalent to DEFAULT.

ALL_LOWERCASE

ThoughtSpot assumes the stored data is already lowercase. The LOWER() function is not applied to the column during string comparison.

ALL_UPPERCASE

ThoughtSpot assumes the stored data is already uppercase. The LOWER() function is not applied to the column. Instead, ThoughtSpot converts the filter values you provide to uppercase before generating SQL, so the query contains uppercase string literals for comparison.
ThoughtSpot does not sample the data for these columns. If the actual data casing does not match the configured setting, query results may be incorrect.

Examples

The following examples use a COMPANY_NAME column filtered by the values acme industries and nakatomi corporation to illustrate how each casing setting affects the generated SQL.

DEFAULT

With the DEFAULT setting, ThoughtSpot applies LOWER() to the column on both sides of the comparison to perform case-insensitive matching.

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE LOWER("ta_1"."COMPANY_NAME") IN (
  LOWER('acme industries'), LOWER('nakatomi corporation')
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST

ALL_LOWERCASE

With ALL_LOWERCASE, ThoughtSpot assumes the stored data is already lowercase. The LOWER() function is not applied to the column or the search values during comparison.

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE "ta_1"."COMPANY_NAME" IN (
  'acme industries', 'nakatomi corporation'
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST
If the stored data is not actually lowercase, query results may be incorrect.

ALL_UPPERCASE

With ALL_UPPERCASE, ThoughtSpot assumes the stored data is already uppercase. The LOWER() function is not applied to the column. Instead, ThoughtSpot converts the filter values to uppercase before generating SQL, so the query contains uppercase string literals that match the stored casing.

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE "ta_1"."COMPANY_NAME" IN (
  'ACME INDUSTRIES', 'NAKATOMI CORPORATION'
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST
ThoughtSpot converts filter values to uppercase before generating SQL, instead of wrapping them with the UPPER() function. This improves query performance, but requires the stored data to actually be uppercase. If your stored data is not all uppercase, results may be incorrect.