[KDb] [Bug 387798] New: Referencing column aliases in WHERE does not work in PostgreSQL

[Jarosław Staniek]

https://bugs.kde.org/show_bug.cgi?id=387798

            Bug ID: 387798
           Summary: Referencing column aliases in WHERE does not work in
                    PostgreSQL
           Product: KDb
           Version: 3.1
          Platform: Other
                OS: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Driver: PostgreSQL
          Assignee: stan...@kde.org
          Reporter: stan...@kde.org
  Target Milestone: ---

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';

-- 
You are receiving this mail because:
You are watching all bug changes.