Redshift: Parameterized query with specific WHERE clause generates error "XX000: ORDER/GROUP BY expression not found in targetlist"

[tcima7]

Hi there, I'm using Npgsql to interface with an Amazon Redshift database instance. I recently upgraded from Npgsql 2.0.14.3 to 3.1.7.

I am seeing an issue where one of my parameterized queries is consistently returning the error "XX000: ORDER/GROUP BY expression not found in targetlist" when executed. However, the query executes successfully if we hard-code all the parameterized values or we remove the "AND (1 = 0)" clause from the sample queries below.

Example query is attached: [query_failing.txt]
query_working.txt

And an example failing query with sample parameter values:
query_failing.txt

Again, if the "AND (1 = 0)" clause is removed, both of the above sample queries will work.

I would appreciate any advice or suggestion in troubleshooting this issue. Please let me know if you require any further information to investigate the problem.

Thank you!

[roji]

This doesn't really have anything to do with Npgsql - Npgsql just delivers your queries to the backend (PostgreSQL/Redshift), if your queries cause an error then it's a backend question... You're better off asking this question in Amazon's Redshift forums.

Having said that, what purpose does AND (1 = 0) serve in your WHERE clause?? Doesn't it make the query return an empty result no matter what?

[tcima7]

This is still an issue for us so I did some more investigation on my own. I wrote a test method to reproduce the issue, which is attached below. Using this test method, I tried running it on different versions of Npgsql. On Npgsql 2.x (tried 2.0.14.3 and 2.2.7), no error occurs. However on Npgsql 3.x (3.0.8 and 3.1.7 were tried), I can reproduce the error noted above.

Here's the test method code: test_method.txt

Of note, I had to make connection string changes to have the same code work on Npgsql 2.x vs Npgsql 3.x. Here are the connection strings used (sensitive values have been x'd out):

Npgsql 2.x: <add name="RedshiftConnectionString" connectionString="Server=xxxxxx;Database=xxxxxx;UID=xxxxxx;PWD=xxxxxx;Port=xxxx;SSL=true;Sslmode=Require;CommandTimeout=0;Pooling=false;" />

Npgsql 3.x: <add name="RedshiftConnectionString" connectionString="Server=xxxxxx;Database=xxxxxx;UID=xxxxxx;PWD=xxxxxx;Port=xxxx;Use SSL Stream=false;Server Compatibility Mode=Redshift;Sslmode=Require;CommandTimeout=0;Pooling=false;Trust Server Certificate=true;" />

Given that the primary variable in the above tests is the Npgsql version, I would appreciate if this issue can be reviewed again. If you need any further information to investigate, do not hesitate to ask.

Thank you.

[NinoFloris]

Closing due to inactivity. We haven't had any other users with the same issue commenting here. Additionally Redshift has also had a lot of releases since 2016 and the original problem may have solved itself.