There is an edge case interaction between the Postgres query parser and
MSSQL foreign tables, where the query parser may drop sort clauses that
are redundant with applied filters. This leads to invalid MSSQL syntax,
because the resulting query has a `limit` but no `sort`, and the user
sees a confusing error message.
This PR detects this edge case on MSSQL foreign tables. There are three
cases:
1. The user filters by a column, but there are still other columns
available for sorting. The default search for a sorting column will
leave out the filtered column.
2. The user filters by a column/several columns, and there are no more
columns that can be used for sorting. We stop the query and show an
admonition.
3. The user filters by a column, then tries to sort by the same column.
We stop the query and show an admonition.
* Updated to use optimized search columns when getting user counts
* added unit tests for query builders
* minor
* Nit fix keywords URL param not getting loaded into search input field
* removed user footer
* updated tests for upstream
* updated integration tests
* updated tests to be int for paginated
* updated schema
* updated types
* updated type imports
* updated type imports
* Apply suggestion from @avallete
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
* updated literal import
* refactor: use common escaping (#40186)
refactor: use utils for sql escaping
---------
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
* feat(pgmeta): filter by composite values
Current behavior:
Only single columns can be used as filters: `where version = 2`
New behavior:
Composite values can be used as filters: `where (id, version) = (1, 2)`
Motivation:
In order to correct an existing bug when fetching table rows, we need to
use cursor pagination on the primary key, but in order to handle
composite primary keys, we need to be able to filter by composite
values.
* refactor(pgmeta): allowlist in-tuple operators
* Table editor cast column type to text if filtering with like based filter
* Update pg-meta tests
* Fix test cases for ~~ in advanced-query.test
* Fix tests in table-row-query.test
* Clean up usage of newOrgAiOptIn and useBedrockAssistant feature flags
* Remove all OpenAI endpoints
* Fix for self-hosted
* Default isLimited to false
* Update PG meta tests
* Fix unit tests for model
* Revert pg meta tests
* Fix test
---------
Co-authored-by: Alaister Young <alaister@users.noreply.github.com>
* fix: move table create update delete to query route
* chore: implement query to fetch a single table
* fix: retrieve table after update
* chore: assign type to update table payload
* chore: use updated table columns for edit
* chore: make executeSql castable with generic (#35685)
* Chore/refactor derivate more types from queries (#35687)
* chore: make executeSql castable with generic
* chore: derivate types from performed queries
- It allows to decouple more the frontend logic and the pg-meta/sql-query logic allowing to reduce the number of cast
and get closer types between what we do fetch and what we expect in our components
* fix: remove existing check
* chore: handle null comment and check
* fix: format check name as identifier
---------
Co-authored-by: avallete <andrew.valleteau@supabase.io>
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
* chore(studio): move Query to pgMeta add tests
- Move the Query builder from studio to pgMeta
- Add e2e tests over the generated sql to ensure syntax and runtime
result over pg database
- fix bug with orde by for table with undefined column
* chore: add table-row-query to pgMeta and tests
* chore: fix query import path
* chore: reduce maxArraySize
* chore: use pg-meta getTableRowsSql implementation in studio
* chore: add truncation on large array fields
* chore: set ES target for lint
* chore: update comment
* chore: reduce test size for CI
* chore(studio): move Query to pgMeta add tests
- Move the Query builder from studio to pgMeta
- Add e2e tests over the generated sql to ensure syntax and runtime
result over pg database
- fix bug with orde by for table with undefined column
* chore: fix query import path
* chore: set ES target for lint
* chore: add github action for pg-meta test package
* chore: add tsconfig to sparse checkout
* chore(pg-meta): port functions tests
* chore: add new test for CURRENT arg creation
* chore: revert query style change
* chore: restore PGFunction type inference
* wip: remove type cast int8 on arg_types
* Revert "wip: remove type cast int8 on arg_types"
This reverts commit 6878a93bfb.
* wip: make tests work with both clients
* chore: use pg and type parser
* chore: use only pg
* chore: migrate table-privileges tests
Andrew Valleteau