Error ) Alias "column_values.nonnull.unexpected_count' is not unique

GX Core Support
1

It works with Sybase and Sqlalchemy and is testing great expectations using the CLI.

Problem)

  1. If you use more than one expectation rule for one suit.json, there seems to be a query that overlaps according to the rule.
    ex) I only derived the column_values_to_be_in_set condition, but the issue of not null verification query running at the same time
  2. Problems that cause errors due to overlapping Alias’ names in the query used at this time

Therefore, I would like to think about a solution together.

gx version : 0.15.50
sqlalchemy version : 1.4.48

Example)

{
"data_asset_type":null,
"expectations_suite_name : "test_suite.json"
"expectations" : [
	{
		"expectation_type" : "expect_column_values_to_be_in_set"
		"kwargs": {
			"column" : "col1"
			"value_set" : [0,1]
		},
		"meta":{}
	},
	{
		"expectation_type" : "expect_column_values_to_be_between"
		"kwargs": {
			"column" : "col2"
			"min_value" : 1,
                        "max_value": 99
		},
		"meta":{}
	}
	]
}

error)

SELECT sum((CASE WHEN ("col1" IS NOT NULL AND "col1" IS NOT IN (0,1))) THEN 1 ELSE 0  END) AS "column_values.in_set.unexpected_count", 
sum(CASE WHEN ("col2" IS NOT NULL AND NOT "col2" >=1 and AND "co2" <=99)) THEN 1 ELSE 0 END)  AS "column_values_between.unexpected_count
, sum(CASE WHEN("col2" IS NULL) THEN 1 ELSE 0 END) AS "column_values.nonnull.unexpected_count"
, sum(CASE WHEN("col1" IS NULL) THEN 1 ELSE 0 END) AS "column_values.nonnull.unexpected_count FROM (SELECT * FROM TABLENAME) AS anon_1)

As we now know, we validate the data through sqlalchemy’s functions (in_,or_,etc) and we realize that they also act as python codes. I wonder where the query runs and if there is no problem changing the alias.

2

Hi @kang, thanks for reaching out! Welcome to the GX community.

As a first step, I’d recommend upgrading to the latest version of great-expectations. The CLI is deprecated, and version 0.15.x is no longer supported - as of this post, we support GX versions 0.17.x and 0.18.x. It’s possible this is a legacy issue that has been fixed in subsequent versions.

If you still encounter this issue after upgrading, please let us know and we’ll take a look.

3

Hello! :blush: I’m facing with the same issue. I’m using gx version 1.3.5 and sqalchemy 1.4.48.

An SQL execution Exception occurred. OperationalError: "(psycopg2.OperationalError) Duplicated field name in view schema: column_values.nonnull.unexpected_count
DETAIL: java.sql.SQLException: Duplicated field name in view schema: column_values.nonnull.unexpected_count

*[SQL: SELECT sum(CASE WHEN (comune_istat IS NULL) THEN %(param_1)s ELSE %(param_2)s END) AS “column_values.nonnull.unexpected_count”, sum(CASE WHEN (ssp IS NULL) THEN %(param_3)s ELSE %(param_4)s END) AS “column_values.nonnull.unexpected_count”, sum(CASE WHEN (anno IS NULL) THEN %(param_5)s ELSE %(param_6)s END) AS “column_values.nonnull.unexpected_count”, sum(CASE WHEN (gasolio IS NULL) THEN %(param_7)s ELSE %(param_8)s END) AS “column_values.nonnull.unexpected_count” *
*FROM (SELECT * *
*FROM (SELECT * from carburanti_rifornimenti_fvgreen_tipo_pv_stradale) AS anon_1 *
WHERE true) AS anon_1]
[parameters: {‘param_1’: 1, ‘param_2’: 0, ‘param_3’: 1, ‘param_4’: 0, ‘param_5’: 1, ‘param_6’: 0, ‘param_7’: 1, ‘param_8’: 0}]

Can you help me, please?

4

Hi there, thanks for reporting. I will escalate this to the team. In the meantime, a possible workaround here would be to create custom expectations where the alias is explicitly defined or to pre process the query before execution with a function that will customize the alias.

1 Like