[Bug] TableExists for mssql adapter uses database name as table schema when checking for existence

[eugenetriguba]

Bug: When I run aCREATE TABLE in the master database, Collection(<newly created table name>).Exists() always returns false when using the mssql adapter. I traced it down to this line here: https://github.com/upper/db/blob/master/adapter/mssql/database.go#L108. It looks like when a table is being checked for whether it exists, the table_schema is being filtered on with the name of the database. However, the table is being created in the master database with a dbo table schema, not master, the name of the database.

Steps to Reproduce:

docker-compose.yml

version: "3.8"

services:
  mssql_db:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql_db
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=testPASS123.?

In a shell:

$ docker compose up -d
$ docker exec -it mssql_db bash
mssql@b609aac2af07:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "testPASS123.?"
1> select db_name();
2> GO

--------------------------------------------------------------------------------------------------------------------------------
master

(1 rows affected)
1> create table tmp(id int primary key);
2> GO
1> select table_name from information_schema.tables;
2> GO
table_name
--------------------------------------------------------------------------------------------------------------------------------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
tmp
spt_values
spt_monitor
MSreplication_options

(7 rows affected)
1> select table_schema, table_catalog from information_schema.tables where table_name = 'tmp';
2> GO
table_schema                                                                                                                     table_catalog
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
dbo                                                                                                                              master

(1 rows affected)

That should establish that the query (https://github.com/upper/db/blob/master/adapter/mssql/database.go#L108) seems to be incorrect because the table_schema is dbo, not master, so sess.Name() wouldn't find it.

We can now also confirm this in Go using the library and see that the Exists method always returns false with mssql (I don't have this issue with the MySQL or Postgres adapters).

In a shell:

$ mkdir test
$ cd test
$ go mod init test
go: creating new go.mod: module test
$ go get github.com/upper/db/v4
go: added github.com/upper/db/v4 v4.7.0
$ go get github.com/upper/db/v4/adapter/mssql
go: downloading golang.org/x/crypto v0.12.0
$ touch main.go

main.go

package main

import (
	"fmt"
	"os"

	"github.com/upper/db/v4/adapter/mssql"
)

func main() {
	db, err := mssql.Open(mssql.ConnectionURL{
		User:     "SA",
		Password: "testPASS123.?",
		Host:     "localhost",
		Database: "master",
	})
	if err != nil {
		fmt.Println(err)
		os.Exit(1)
	}

	_, err = db.SQL().Exec(`CREATE TABLE tmp(id int PRIMARY KEY);`)
	if err != nil {
		fmt.Println(err)
		os.Exit(1)
	}

	// Show that the table shows up in the information_schema and was created.
	rows, err := db.SQL().Select("table_name").From("information_schema.tables").Query()
	if err != nil {
		fmt.Println(err)
		os.Exit(1)
	}
	defer rows.Close()
	fmt.Println("--- Tables names ---")
	for rows.Next() {
		var tableName string
		err := rows.Scan(&tableName)
		if err != nil {
			fmt.Println(err)
			os.Exit(1)
		}
		fmt.Println(tableName)
	}

	// Show that the query for exists doesn't find it.
	_, err = db.Collection("tmp").Exists()
	if err != nil {
		fmt.Println(err)
		os.Exit(1)
	}
}

Now, in a shell, we can run it:

$ go run main.go
--- Tables names ---
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
tmp
spt_values
spt_monitor
MSreplication_options
upper: collection does not exist
exit status 1