deparse altered "CREATE VIEW" statement, removed OR REPLACE, changed JOIN to INNER JOIN

[divyenduz]

Original query (with fingerprint 184ff1799cccd685):

CREATE OR REPLACE VIEW public.view_ticket AS
 SELECT a.id,
    a.name,
    a.project,
    a.search,
    a.labels,
    a.minutes,
    b.name AS "user",
    b.email,
    b.language,
    b.photo,
    b.company,
    a.iduser,
    a.iduserlast,
    a.idsolver,
    a.issolved,
    a.ispriority,
    b.isnotification,
    a.datecreated,
    a.dateupdated,
    b.minutes AS minutesuser,
    a.idsolution,
    b."position",
    a.countcomments
   FROM tbl_ticket a
     JOIN tbl_user b ON b.id::text = a.iduser::text
  WHERE a.isremoved = false

Deparsed query (with fingerprint 172d678f094d8695):

CREATE VIEW public.view_ticket AS SELECT a.id,
a.name,
a.project,
a.search,
a.labels,
a.minutes,
b.name AS "user",
b.email,
b.language,
b.photo,
b.company,
a.iduser,
a.iduserlast,
a.idsolver,
a.issolved,
a.ispriority,
b.isnotification,
a.datecreated,
a.dateupdated,
b.minutes AS minutesuser,
a.idsolution,
b.position,
a.countcomments FROM tbl_ticket AS a INNER JOIN tbl_user AS b ON b.id::text = a.iduser::text WHERE a.isremoved = FALSE;

• Raising because fingerprints are different
• Happy to create two separate issues for the two alterations

To reproduce:

const fs = require('fs')
const { parse, deparse } = require('pgsql-parser');

async function main() {
    const stmts = parse(`
      CREATE OR REPLACE VIEW public.view_ticket AS
 SELECT a.id,
    a.name,
    a.project,
    a.search,
    a.labels,
    a.minutes,
    b.name AS "user",
    b.email,
    b.language,
    b.photo,
    b.company,
    a.iduser,
    a.iduserlast,
    a.idsolver,
    a.issolved,
    a.ispriority,
    b.isnotification,
    a.datecreated,
    a.dateupdated,
    b.minutes AS minutesuser,
    a.idsolution,
    b."position",
    a.countcomments
   FROM tbl_ticket a
     JOIN tbl_user b ON b.id::text = a.iduser::text
  WHERE a.isremoved = false
    `)
    const stmts2 = deparse(stmts[0])
    console.log(stmts2)
}

main()

Schema at https://github.com/prisma/database-schema-examples/tree/main/postgres/helpdesk

[pyramation]

maybe we can separate this?

"removed OR REPLACE" sounds like a real bug.

"changed JOIN to INNER JOIN" sounds like a syntax that gets collapsed because it's treated the same by the AST.

[divyenduz]

Absolutely, here we go #56

"changed JOIN to INNER JOIN" sounds like a syntax that gets collapsed because it's treated the same by the AST.

I agree, there might be some other instances of these where I opened the issues but since the fingerprints were different, I wanted to run these by you. Please feel free to close them after you have skimmed through them.

[divyenduz]

Ha, totally forgot to close this one.