WHERE statements are loosing their quotes

[Bengreen]

When i make a WHERE statement against a table it is loosing the quotes before they are passed as qual parameters. As a result the value field of the qual does not have quotes and hence is not distinguishable from trying to make a match against another column of the name given in value.

e.g. select age,name from constanttable WHERE name='Ben'; This results in a qual parameter of : [name = Ben] when i log str(qual) Which fails as it is NOT quoted. i could artifically add the quotes, but this seems incorrect as i would be prevented from matching to other columns (e.g. below)

If i want to run this query looking for a match of the column name with the text 'age'. Because of the missing quotes i cannot disambiguate the qual if this is supposed to be matching a column called age or the text 'age' in column name. I should be able to do both and would be able to if the quotes were passed down to the qual. select age,name from constanttable WHERE name='age';

Is this a feature that the quotes are stripped from the qual or a bug? I would prefer to see the quotes preserved and passed down.

[rdunklau]

There is no support for expressions of the form COLUMN OPERATOR COLUMN.

So yes, it is a featture: the value you obtain is necessarily a constant. This constant has the same type as the underlying value. For example, querying WHERE id = 1 would result in a Python int value instead of the string value.

[Bengreen]

So when postgres processes a query such as WHERE name=age I understand that it passes this COLUMN OPERATOR COLUMN into the FDW wrapper with the intention that the wrapper could interpret it. Thus if Multicorn could disambiguate from the quoted and quoted this would be something that could be used inside the wrapper. My intention is to use this capability to pass as much as possible onto the wrapped database (e.g. HIVE).

If i were to examine the multicorn code and propose changes. Would there be a consideration of this or would this be rejected automatically? (NOTE: It would imply passing constants through into python with quotes OR passing a flag to indicate the value is a constant or a column (quoted or unquoted coming from postgres).

[rdunklau]

It would of course be considered !

The most difficult par of this would be to find a way to implement such a feature without breaking backward compatibility with other FDWs. Thus, "just" quoting the value would not be an option, neither would be passing a flag.

This is closely related to #76 . One of the main (original) goals of Multicorn was to be as simple as possible, and the API was designed with that in mind. With time, users feel like they should be allowed to push more quals to the foreign side.

Another thing which doesn't make me want to rush such an implementation is the current design discussion about pushing foreign joins. I don't want to design such an API, only to have to tear it down once PostgreSQL proposes a way to implement join push down. This API should be able to accomodate new use cases like this.

So, the main problem is not the code itself, it is designing an API which can stand the test of time (unlike the current one).

If you have any idea on how to tackle this problem, I would be really interested. I hope I didn't discourage you.

[rdunklau]

Several issues are about the same thing, closing this as duplicate.