Vertica Quick Tip: Which Column in my Flattened Table caused the “Subquery used as an expression returned more than one row” Error?

This blog post was authored by Jim Knicely. A flattened table contains columns that are derived from query-expressions that must return only one row and column value, or none. If a column’s query-expression does return more than one row you will get a “Subquery used as an expression returned more than one row” error. If you have more than one derived column in your flattened table and you get that error, you’ll have to figure out which query-expression is the culprit as it is not indicated in the error message. Example: dbadmin=> \dt dim_* List of tables Schema | Name | Kind | Owner | Comment --------+-------+-------+---------+--------- public | dim_1 | table | dbadmin | public | dim_2 | table | dbadmin | public | dim_3 | table | dbadmin | (3 rows) dbadmin=> CREATE TABLE public.fact_flat dbadmin-> ( dbadmin(> c1 int, dbadmin(> dim_1_c2 varchar(10) SET USING ( SELECT dim_1.c2 FROM public.dim_1 WHERE (dim_1.c1 = fact_flat.c1)), dbadmin(> dim_2_c2 varchar(10) SET USING ( SELECT dim_2.c2 FROM public.dim_2 WHERE (dim_2.c1 = fact_flat.c1)), dbadmin(> dim_3_c2 varchar(10) SET USING ( SELECT dim_3.c2 FROM public.dim_3 WHERE (dim_3.c1 = fact_flat.c1)) dbadmin(> ); CREATE TABLE dbadmin=> INSERT INTO public.fact_flat (c1) SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> COMMIT; COMMIT dbadmin=> SELECT refresh_columns(‘public.fact_flat’, ”); ERROR 4840: Subquery used as an expression returned more than one row Uh oh! Which derived column is causing this error? We can find out by executing each of the derived columns individually dumping the output to a file. Once all the queries have executed, we can check the output file for errors to discover the offending column(s). Example: [dbadmin@s18384357 ~]$ vsql -Atc "SELECT 'SELECT (' || REPLACE(column_set_using, CHR(10), '') || ') AS ' || column_name || ' FROM ' || table_schema || '.' || table_name || ' LIMIT 1; --Derived Column: ' || column_name FROM v_catalog.columns WHERE table_schema || '.' || table_name = 'public.fact_flat' AND column_set_using <> '';" | vsql -a &> /home/dbadmin/refresh_columns_check.txt [dbadmin@s18384357 ~]$ cat /home/dbadmin/refresh_columns_check.txt SELECT (( SELECT dim_1.c2 FROM public.dim_1 WHERE (dim_1.c1 = fact_flat.c1))) AS dim_1_c2 FROM public.fact_flat LIMIT 1; –Derived Column: dim_1_c2 dim_1_c2 ———- TEST1 (1 row) SELECT (( SELECT dim_2.c2 FROM public.dim_2 WHERE (dim_2.c1 = fact_flat.c1))) AS dim_2_c2 FROM public.fact_flat LIMIT 1; –Derived Column: dim_2_c2 ERROR 4840: Subquery used as an expression returned more than one row SELECT (( SELECT dim_3.c2 FROM public.dim_3 WHERE (dim_3.c1 = fact_flat.c1))) AS dim_3_c2 FROM public.fact_flat LIMIT 1; –Derived Column: dim_3_c2 dim_3_c2 ———- TEST3 (1 row) In the example, we see that the column DIM_2_C2 is the offender here! Have Fun!