supabase/wrappers

Warning with MSSQL "bit" types

Closed this issue · 3 comments

mtimour commented

Bug report

  • I confirm this is a bug with Supabase, not with my own application.
  • I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

System shows a warning when SQL against remote table contains boolean comparison against bit field defined on remote table and defined as bool in local foreign table.

To Reproduce

Remote table on MSSQL

create table FoobarRemote (
   Foo           int              not null,  
   IsDefault     bit              not null,
)

Local foreign table

create foreign table rm.foobar
(
    Foo           int    not null,
    IsDefault     bool   not null
)
  server remote_server
  options ( table 'FoobarRemote' );

This SQL

select * 
from rm.foobar
where isdefault is true

triggers the following warning

 [01000] unsupported qual: {BOOLEANTEST :arg {VAR :varno 4 :varattno 6 :vartype 16 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 4 :varattnosyn 6 :location 3365} :booltesttype 0 :location 3378}

So far, only boolean compression triggers the warning.

Expected behaviour

No warnings

Screenshots

System information

  • OS: macOS, Windows
  • postgres : PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1)
  • MSSQL: Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)

Additional context

burmecia commented

Thanks for reporting this issue. This issue is like the warning message said, boolean test condition, like isdefault is true or isdefault is not false, is not supported yet. We will added support for it soon.

mtimour commented

But the boolean condition works , the resultset returned from MSSQL reflects the boolean filter.
So I assumed MSSQL Wrapper supports boolean to bit conversion. Am I missing something ?

burmecia commented

That is working because it cannot handle boolean condition so it fetched all all table rows to local and then postgres applied the filter locally. The PR will improve this situation by recognising and pushing down that condition to MSSQL.

  • 👍1