Bug 387798

Summary:	Referencing column aliases in SELECT and WHERE does not work in PostgreSQL
Product:	[Frameworks and Libraries] KDb	Reporter:	Jarosław Staniek <staniek>
Component:	Driver: PostgreSQL	Assignee:	Jarosław Staniek <staniek>
Status:	CONFIRMED ---
Severity:	normal	CC:	adam
Priority:	NOR
Version First Reported In:	3.1.0
Target Milestone:	---
Platform:	Other
OS:	Linux
Latest Commit:		Version Fixed/Implemented In:
Sentry Crash Report:

Description Jarosław Staniek 2017-12-11 16:22:34 UTC

Referencing column aliases in WHERE does not work in PostgreSQL. Reason is explained as SQL standard compliance. Nevertheless, MySQL and SQLite support it.

Example:

SELECT city AS c FROM customers WHERE c = 'Warsaw';

PGSQL ERROR: column "c" does not exist

This is explained e.g. at https://stackoverflow.com/q/3241352

SOLUTION #1: Idea from https://stackoverflow.com/a/8095413 but I find this even simpler as there is no need to reference temporary view name:

SELECT c FROM (SELECT *, city AS c FROM customers) AS city_alias WHERE c = 'Warsaw';

(The internal view gives access to the 'c' alias and its '*' gives access to all other columns so we have equivalend of 'FROM customers'. city_alias is necessary by definition but unused so it can be unique name obfuscated by KDb, e.g. '__kdb_customers_alias123')

SOLUTION #2: Just expand the column alias 'c' back to 'customers.city':

SELECT city AS c FROM customers WHERE customers.city = 'Warsaw';

Comment 1 Jarosław Staniek 2017-12-11 17:21:34 UTC

Extended scope to the SELECT section. Neither this work in PostgreSQL:

SELECT city as c, c || '!' FROM customers;

We have the same soultions as for WHERE:

1. SELECT c, c || '!' FROM (SELECT *, city AS c FROM customers) AS city_alias

or 2. SELECT customers.city as c, customers.city || '!' FROM customers

Comment 2 Adam Pigg 2017-12-11 19:18:21 UTC

How about being standards compliant, and generating valid SQL?

Comment 3 Jarosław Staniek 2017-12-11 19:35:14 UTC

> How about being standards compliant, and generating valid SQL?

No implementation is compliant, as someone noted in the stackoverflow thread even PostgreSQL has deviations for convenience of users. Notable convenience additions started to appear in our functions: https://community.kde.org/Kexi/Plugins/Queries/SQL_Functions.

Lack of support described in this thread is rather unfortunate for me as in practice it's nothing more than inconvenience, if one wants portability to MySQL and SQLite for example, this standard compliance is a step backward.

Comment 4 Jarosław Staniek 2017-12-11 22:38:28 UTC

Dev info for the record in KDb 9f68694395b (master):

in selectStatementInternal() we are kind of using the SOLUTION #2, see the code where s_where_sub is set (it is computed without aliases).

KEXI visual query designer offers setting aliases for fields but not for tables so WHERE section can't be controlled from this view. Relations between  are properly generated.

So the problem would be mostly reproducible in KDb API and Kexi SQL view.