"Prettier-SQL.tabulateAlias": true ignores back ticks

[theking2]

Input data

Which SQL and options did you provide as input?

select
    coalesce(FAMILY_ITEMS_DESCRIPTION, 'nicht zugewiesen')  `Item family`,
    coalesce(SUBFAMILY_ITEMS_DESCRIPTION, 'nicht zugewiesen') `Item subfamily`,
    product.ITEMNR                                                  `Art-Nr.`,
    product.DESCRIPTION                                             Bezeichnung,
    coalesce(LOCATIONQTY, 0)                                        `Verfügbar`,
    STANDARDCOST                                                    EP,
    coalesce(StockAmount, 0)                                        `Lagerwert`,
    KONTONR
    /*, concat( KONTOBEZEICHNUNG, '|', KONTONR ) Account*/
from
    product
    left join product_family pf on ITEMFAMILY = FAMILY_ITEMS
    and ITEMSUBFAMILY = SUBFAMILY_ITEMS
    join (
        select
            ITEMNR,
            sum(LOCATIONQTY)                               LOCATIONQTY,
            sum(StockAmount)                               StockAmount,
            str_to_date(IP_TIMEDATEPK_REPORTING, '%Y%m%d') `last_date`,
            STANDARDCOST
        from
            report_stock_300
            join warehouse_300_state using (IP_WAREHOUSE, STOCKTYPE)
        where
            `State` = 'Verfügbar'
            and TDYEAR = $year
            and TDMONTH = $month
        group by
            ITEMNR,
            IP_TIMEDATEPK_REPORTING
    ) r300 using (ITEMNR)
order by
    coalesce(FAMILY_ITEMS, 'zz'),
    SUBFAMILY_ITEMS,
    product.DESCRIPTION

Expected Output

select 
    coalesce(FAMILY_ITEMS_DESCRIPTION, 'nicht zugewiesen')    `Item family`,
    coalesce(SUBFAMILY_ITEMS_DESCRIPTION, 'nicht zugewiesen') `Item subfamily`,
    product.ITEMNR                                            `Art-Nr.`,
    product.DESCRIPTION                                       Bezeichnung,
    coalesce(LOCATIONQTY, 0)                                  `Verfügbar`,
    STANDARDCOST                                              EP,
    coalesce(StockAmount, 0)                                  `Lagerwert`,
    KONTONR
    /*, concat( KONTOBEZEICHNUNG, '|', KONTONR ) Account*/
from
    product
    left join product_family pf on ITEMFAMILY = FAMILY_ITEMS
    and ITEMSUBFAMILY = SUBFAMILY_ITEMS
    join (
        select
            ITEMNR,
            sum(LOCATIONQTY)                               LOCATIONQTY,
            sum(StockAmount)                               StockAmount,
            str_to_date(IP_TIMEDATEPK_REPORTING, '%Y%m%d') `last_date`,
            STANDARDCOST
        from
            report_stock_300
            join warehouse_300_state using (IP_WAREHOUSE, STOCKTYPE)
        where
            `State` = 'Verfügbar'
            and TDYEAR = $year
            and TDMONTH = $month
        group by
            ITEMNR,
            IP_TIMEDATEPK_REPORTING
    ) r300 using (ITEMNR)
order by
    coalesce(FAMILY_ITEMS, 'zz'),
    SUBFAMILY_ITEMS,
    product.DESCRIPTION

Actual Output

select
    coalesce(FAMILY_ITEMS_DESCRIPTION, 'nicht zugewiesen') `Item    family`,
    coalesce(SUBFAMILY_ITEMS_DESCRIPTION, 'nicht zugewiesen') `Item subfamily`,
    product.ITEMNR                                                  `Art-Nr.`,
    product.DESCRIPTION                                             Bezeichnung,
    coalesce(LOCATIONQTY, 0)                                        `Verfügbar`,
    STANDARDCOST                                                    EP,
    coalesce(StockAmount, 0)                                        `Lagerwert`,
    KONTONR
    /*, concat( KONTOBEZEICHNUNG, '|', KONTONR ) Account*/
from
    product
    left join product_family pf on ITEMFAMILY = FAMILY_ITEMS
    and ITEMSUBFAMILY = SUBFAMILY_ITEMS
    join (
        select
            ITEMNR,
            sum(LOCATIONQTY)                               LOCATIONQTY,
            sum(StockAmount)                               StockAmount,
            str_to_date(IP_TIMEDATEPK_REPORTING, '%Y%m%d') `last_date`,
            STANDARDCOST
        from
            report_stock_300
            join warehouse_300_state using (IP_WAREHOUSE, STOCKTYPE)
        where
            `State` = 'Verfügbar'
            and TDYEAR = $year
            and TDMONTH = $month
        group by
            ITEMNR,
            IP_TIMEDATEPK_REPORTING
    ) r300 using (ITEMNR)
order by
    coalesce(FAMILY_ITEMS, 'zz'),
    SUBFAMILY_ITEMS,
    product.DESCRIPTION

Usage

• How are you calling / using the library?
• What SQL language(s) does this apply to?
• Which SQL Formatter version are you using?

[nene]

Prettier-SQL VSCode extension is outdated. Please read the FAQ.

However the tabulateAlias feature has been removed from SQL Formatter since 14.0.0 (because it was fundamentally broken, as demonstrated by the bug report above).

[gvidasja]

@nene are there any plans to reintroduce the tabulateAlias option? it was very useful

[nene]

Not really. The architecture of SQL Formatter makes it pretty much impossible to implement correctly.

It's also a formatting style that in general is problematic to apply automatically. It works for simple cases like this:

SELECT
  customer.fname   AS first_name,
  customer.lname   AS last_name,
  customer.address AS address
FROM
  customer;

But it quickly breaks down or gets silly when the code gets more complex, like:

SELECT
  CONCAT(customer.title, ' ', customer.fname, ' ', customer.lname) AS full_name,
  customer.address                                                 AS address
FROM
  customer;

or perhaps like:

SELECT
  CONCAT(
    customer.title,
    ' ',
    customer.fname,
    ' ',
    customer.lname
  )                AS full_name,
  customer.address AS address
FROM
  customer;

With the latter also being a particularly tricky case of formatting to implement.