New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect DDEX SQL validation #2136

Open
warcha opened this Issue Aug 29, 2018 · 7 comments

Comments

Projects
None yet
3 participants
@warcha

warcha commented Aug 29, 2018

I have an issue where for certain sql, when I run it through the querybuilder, it first parsers the query and returns an error/warning.

For example, when I run this simple query:

Select N'test'

I get the following error message:

The following errors were encountered while parsing the contents of the SQL pane: 
Error in SELECT clause: expression near 'test'.
Missing FROM clause.
Unable to parse query text.

Then when I hit the OK button the query is run successfully.

Would you be able to tell me the origin of this error, is it from Npgsql or from Visual Studio itself.

Is there any way for me to override this warning or modify the parser?

Npgsql version: 3.2.6
PostgreSQL version: 10.0
Operating system: Windows 7

@austindrenski

This comment has been minimized.

Member

austindrenski commented Aug 29, 2018

What do you want the N prefix to do here? What do you see when you run this in a psql console connected to the same PostgreSQL backend as Visual Studio?

@warcha

This comment has been minimized.

warcha commented Aug 29, 2018

The N prefix is just an example to make the error/warning pop up. The N prefix is declaring that the following string is a NVARCHAR data type.

I'm actually trying to find a way to hook into the Visual Studio SQL validation that is occurring .

@austindrenski

This comment has been minimized.

Member

austindrenski commented Aug 29, 2018

Right... But PostgreSQL doesn't define NVARCHAR which is found in SQL Server/Oracle/etc.

The N prefix should result in something like a bpchar which is an internal (maybe deprecated?) alias for char(n) (as in _b_lank _p_added char). I'm not sure if a validator would (or should) recognize the prefix for an internal alias.

It would be helpful if you could give us some more information on what exactly you're trying to do, how you're trying to do it, etc.

@warcha

This comment has been minimized.

warcha commented Aug 30, 2018

Yes, you're right, in postgreSQL the N prefix will declare the string as a VARCHAR rather then text.

Please don't get caught up in the actual N prefix, I was just using this statement as a simple example to generate the error in the visual studio querybuilder. I'm trying to figure out how the querybuilder parses the sql statement in order to generate the error/warning. I have a scenario where I need to modify the validation that is occurring on the sql statement and can't figure out where this is happening. Is it the DDEX layer or the Npgsql driver parsing the query in order to generate the warning or is it Visual Studio itself doing the validity check?

@austindrenski

This comment has been minimized.

Member

austindrenski commented Aug 30, 2018

@warcha Ah, thank you for clarifying. I was indeed preoccupied with your example, apologies.

I would be surprised if Npgsql was performing any sort of query validation on the client. Our standard approach is to defer to PostgreSQL when feasible. But, the DDEX plumbing is a bit beyond my depth, so I may be mistaken.

@roji Could you weigh in?

@roji roji changed the title from Querybuilder sql parsing issue to Incorrect DDEX SQL validation Aug 30, 2018

@roji roji added the vsix label Aug 30, 2018

@roji

This comment has been minimized.

Member

roji commented Aug 30, 2018

Npgsql definitely does not look at or validate any SQL, ever (except for some light parsing to split on semicolons and replace parameter placeholders).

In addition, PostgreSQL does indeed support SELECT without FROM: SELECT N'test' should work just fine in psql or pgadmin.

This is very likely coming from Visual Studio itself or DDEX - someone is performing client-side validation. However, ADO.NET providers such as Npgsql do expose some metadata properties which tell DDEX whether some SQL feature is supported or not: take a look at the DataSourceInformation schema collection. So it's possible the Npgsql is somehow wrongly indicating to DDEX that SELECT without FROM isn't supported, although I can't see such a property in the docs.

As a way forward, I'd examine the schema collections returned by MSSQL, there may be an undocumented property returned there that controls this, in which case we can change Npgsql to return it too.

Hope all that is clear...

@roji roji added this to the Backlog milestone Aug 30, 2018

@warcha

This comment has been minimized.

warcha commented Aug 30, 2018

Thanks guys for all the help, really appreciate the time taken to explain things and suggest ways forward. I'll start looking into the DataSourceInformation schema collection as suggested and will let you know if I find anything.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment