Something not working on database joins (postgresql)

[martino-zito]

Describe the bug
i have just upgraded from 1.0.46 version to 1.0.57 and the relationship between tables have stopped working. When a relationship between two tables exist if you browse the related entry and the foreign key is missing (null on database) the related row is not displayed

es.

artist table
id | name | surname | role_id
1 | john | smith | null

band table
id | band name
1 | Smiths&sons

in this case if you set an existing relationship on band table with artist table in the artist data section you will see no rows at all.

It seems to me that instead of a left outer join a inner join is used.

[mike12345567]

Hi @martino-zito, thanks for reporting this, I can reproduce, will get a fix out asap.

[mike12345567]

Hi @martino-zito 1.0.59 has been released (https://github.com/Budibase/budibase/releases/tag/v1.0.59) you should be able to update to the latest image now.

[martino-zito]

@mike12345567 i think that something has gone wrong after the fix. Or that the revert is partial.

I've updated to 1.0.59 and as a side effect i'm now unable to create any query on postgres. The preview api is failing and complains for a validation error. i think that the client is sending parameters (name and schema) that are not allowed on the server side. removing them and curling the right payload the preview response comes back

`ERROR [1644644299532] (51 on 6d431d844748): Invalid body - "schema" is not allowed

BadRequestError: Invalid body - "schema" is not allowed

    at Object.throw (/app/node_modules/koa/lib/context.js:97:11)

    at /app/dist/middleware/joi-validator.js:17:22

    at dispatch (/app/node_modules/@koa/router/node_modules/koa-compose/index.js:44:32)

    at next (/app/node_modules/@koa/router/node_modules/koa-compose/index.js:45:18)

    at /app/node_modules/@budibase/backend-core/src/middleware/csrf.js:55:14

    at /app/dist/middleware/authorized.js:90:16

    at Generator.next (<anonymous>)

    at fulfilled (/app/dist/middleware/authorized.js:5:58)

    at processTicksAndRejections (internal/process/task_queues.js:95:5)

Trace: BadRequestError: Invalid body - "schema" is not allowed

at Object.throw (/app/node_modules/koa/lib/context.js:97:11)

at /app/dist/middleware/joi-validator.js:17:22

at dispatch (/app/node_modules/@koa/router/node_modules/koa-compose/index.js:44:32)

at next (/app/node_modules/@koa/router/node_modules/koa-compose/index.js:45:18)

at /app/node_modules/@budibase/backend-core/src/middleware/csrf.js:55:14

at /app/dist/middleware/authorized.js:90:16

at Generator.next (<anonymous>)

at fulfilled (/app/dist/middleware/authorized.js:5:58)

at processTicksAndRejections (internal/process/task_queues.js:95:5)

at /app/dist/api/index.js:69:21

at Generator.throw (<anonymous>)

at rejected (/app/dist/api/index.js:6:65)

at processTicksAndRejections (internal/process/task_queues.js:95:5)

INFO [1644644299567] (51 on 6d431d844748): request completed

res: {

  "statusCode": 400,

  "headers": {

    "vary": "Accept-Encoding",

    "content-type": "application/json; charset=utf-8",

    "content-length": "67"

  }

}

responseTime: 492

req: {

  "id": 1,

  "method": "POST",

  "url": "/api/queries/preview",

  "headers": {

    "host": "localhost:10000",

    "content-length": "332",

    "sec-ch-ua": "\" Not A;Brand\";v=\"99\", \"Chromium\";v=\"98\", \"Google Chrome\";v=\"98\"",

    "x-budibase-app-id": "app_dev_465e4581ea2f4c5ca072052e6905b423",

    "x-budibase-api-version": "1",

    "sec-ch-ua-mobile": "?0",

    "user-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.80 Safari/537.36",

    "content-type": "application/json",

    "accept": "application/json",

    "sec-ch-ua-platform": "\"macOS\"",

    "origin": "http://localhost:10000",

    "sec-fetch-site": "same-origin",

    "sec-fetch-mode": "cors",

    "sec-fetch-dest": "empty",

    "referer": "http://localhost:10000/builder/app/app_dev_465e4581ea2f4c5ca072052e6905b423/data/datasource/datasource_plus_4b7c356066344305b7c2536de8425613/new",

    "accept-encoding": "gzip, deflate, br",

    "accept-language": "en-GB,en-US;q=0.9,en;q=0.8,it;q=0.7",

    "cookie": "wp-settings-time-1=1631947101; wp-settings-1=mfold%3Do; menuPanel=visible; menuPanelType=main; shellInABox=1438978470:1010111010; rl_page_init_referrer=RudderEncrypt%3AU2FsdGVkX184CsPXSheQABRhsroe%2Fpv3U4ZYCvrLRA8%3D; rl_page_init_referring_domain=RudderEncrypt%3AU2FsdGVkX1%2BmjZ6%2FXaXyAf8MOmJNehZjjgFQ7OIlgmc%3D; budibase:auth=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VySWQiOiJ1c185NDQwYTgyMWU4Njk0OGMzYjQ5NjVjZWQyN2QxM2EwNCIsInNlc3Npb25JZCI6IjM2OGY0MmNiMWU0MDQzNTk4NjVkZjNiZDFiOTI4YmM3IiwidGVuYW50SWQiOiJkZWZhdWx0IiwiaWF0IjoxNjQzMzkyMzcyfQ.v66gKE20CW7b_Y2_tKvGllORZyjXSxx3fwSlYBbqGZI; budibase:auth.sig=n-t5VWu2EC_vTGUmsrtQ1pCncmU; budibase:init=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE2NDM3NDk5NjN9.G5mQSk-M5AM0CCQfy5Gtq9qpJ6qhYn9ZX5OBX9o8wSo; budibase:init.sig=p0QyW6An1XANq7di-MRvjILaV_M; rl_anonymous_id=RudderEncrypt%3AU2FsdGVkX19tLvRIMiGN6aFOF7GFwOYsKJ89BGVqU5B4dWOdnc%2BktTRfILWiUDP0ATs0Q5Mp%2F7APrE9tJ4V6%2FQ%3D%3D; rl_group_id=RudderEncrypt%3AU2FsdGVkX18v%2FDBOsECrALRR3Nd74vOLKbdrg2sNG6g%3D; rl_trait=RudderEncrypt%3AU2FsdGVkX186vt%2F2kr2swdSvQfW7VNd2ChuvlXrqQgA%3D; rl_group_trait=RudderEncrypt%3AU2FsdGVkX19KNtGh0rqmQ%2BFLenY416me%2Bd3FCgr09%2Bc%3D; rl_user_id=RudderEncrypt%3AU2FsdGVkX19RdFsyzm75kv6%2Fzg6%2Bl1AkiBx4DWuTSZVSAEaTosHaf5sZRPPJS3HJLwidoQU%2BFO8IU%2B07PvacwDIHnJsLS1jXRCPJHLRpTJnNuUxEFqjM0A4zyrPYWP1X; budibase:currentapp=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhcHBJZCI6ImFwcF9kZXZfNDY1ZTQ1ODFlYTJmNGM1Y2EwNzIwNTJlNjkwNWI0MjMiLCJpYXQiOjE2NDQ1NzQ5NTV9.Ner5QylnuKXzxtVVhPF0rFO5Wi6WnBhyCNaC0Z_873w",

    "x-forwarded-proto": "http",

    "x-request-id": "63071a49-8de5-445d-9991-9f5afd1c5658",

    "x-envoy-expected-rq-timeout-ms": "120000"

  },

  "remoteAddress": "::ffff:172.19.0.10",

  "remotePort": 34736

}`

this is the stacktrace on the bbapp side.

i think that the solution is here 7bab54b

[mike12345567]

Hi @martino-zito - we've release 1.0.62 that should resolve this issue.