Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

mssql: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money #1406

Closed
mike-tesch opened this issue Aug 6, 2024 · 4 comments
Closed

mssql: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money #1406

mike-tesch opened this issue Aug 6, 2024 · 4 comments

Comments

@mike-tesch
Copy link

mike-tesch commented Aug 6, 2024 •
edited
Loading

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v4.16.2

What is your database and version (eg. Postgresql 10)

MSSQL 2022. docker container, mcr.microsoft.com/mssql/server:2022-latest

Can provide docker compose file if it helps

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

Full repro here with table and data script: https://github.com/mike-tesch/sqlboiler_test

        uid := mssql.UniqueIdentifier{}
	uid.Scan("5AB0412A-2B2F-430F-8830-002A42125148")

	test, err := models.TestTables(Where("ID = ?", uid)).One(ctx, db)
	if err != nil {
		// models: failed to execute a one query for TestTable: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money
		fmt.Println(err)
	} else {
		fmt.Println(test.Date)
	}

	test2, err := models.FindTestTable(ctx, db, uid)
	if err != nil {
		// models: unable to select from TestTable: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money
		fmt.Println(err)
	} else {
		fmt.Println(test2.Date)
	}

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

using driver: /home/mike/go/bin/sqlboiler-mssql
{
    "config": {
        "driver_name": "mssql",
        "driver_config": {
            "add-enum-types": false,
            "blacklist": null,
            "dbname": "sqlboiler_test",
            "enum-null-prefix": "Null",
            "foreign-keys": null,
            "host": "192.168.86.50",
            "pass": "replaced",
            "port": 1433,
            "schema": "dbo",
            "sslmode": "disable",
            "user": "sa",
            "whitelist": null
        },
        "pkg_name": "models",
        "out_folder": "models",
        "debug": true,
        "enum_null_prefix": "Null",
        "wipe": true,
        "struct_tag_cases": {
            "json": "snake",
            "yaml": "snake",
            "toml": "snake",
            "boil": "snake"
        },
        "struct_tag_casing": "snake",
        "relation_tag": "-",
        "imports": {
            "all": {
                "Standard": [
                    "\"database/sql\"",
                    "\"fmt\"",
                    "\"reflect\"",
                    "\"strconv\"",
                    "\"strings\"",
                    "\"sync\"",
                    "\"time\"",
                    "\"context\""
                ],
                "ThirdParty": [
                    "\"github.com/friendsofgo/errors\"",
                    "\"github.com/volatiletech/sqlboiler/v4/boil\"",
                    "\"github.com/volatiletech/sqlboiler/v4/queries\"",
                    "\"github.com/volatiletech/sqlboiler/v4/queries/qm\"",
                    "\"github.com/volatiletech/sqlboiler/v4/queries/qmhelper\"",
                    "\"github.com/volatiletech/strmangle\""
                ]
            },
            "test": {
                "Standard": [
                    "\"bytes\"",
                    "\"reflect\"",
                    "\"testing\"",
                    "\"context\""
                ],
                "ThirdParty": [
                    "\"github.com/volatiletech/randomize\"",
                    "\"github.com/volatiletech/sqlboiler/v4/boil\"",
                    "\"github.com/volatiletech/sqlboiler/v4/queries\"",
                    "\"github.com/volatiletech/strmangle\""
                ]
            },
            "singleton": {
                "boil_queries": {
                    "Standard": [
                        "\"regexp\""
                    ],
                    "ThirdParty": [
                        "\"github.com/volatiletech/sqlboiler/v4/drivers\"",
                        "\"github.com/volatiletech/sqlboiler/v4/queries\"",
                        "\"github.com/volatiletech/sqlboiler/v4/queries/qm\""
                    ]
                },
                "boil_types": {
                    "Standard": [
                        "\"strconv\""
                    ],
                    "ThirdParty": [
                        "\"github.com/friendsofgo/errors\"",
                        "\"github.com/volatiletech/sqlboiler/v4/boil\"",
                        "\"github.com/volatiletech/strmangle\""
                    ]
                },
                "mssql_upsert": {
                    "Standard": [
                        "\"fmt\"",
                        "\"strings\""
                    ],
                    "ThirdParty": [
                        "\"github.com/volatiletech/sqlboiler/v4/drivers\"",
                        "\"github.com/volatiletech/strmangle\""
                    ]
                }
            },
            "test_singleton": {
                "boil_main_test": {
                    "Standard": [
                        "\"database/sql\"",
                        "\"flag\"",
                        "\"fmt\"",
                        "\"math/rand\"",
                        "\"os\"",
                        "\"path/filepath\"",
                        "\"strings\"",
                        "\"testing\"",
                        "\"time\""
                    ],
                    "ThirdParty": [
                        "\"github.com/spf13/viper\"",
                        "\"github.com/volatiletech/sqlboiler/v4/boil\""
                    ]
                },
                "boil_queries_test": {
                    "Standard": [
                        "\"bytes\"",
                        "\"fmt\"",
                        "\"io\"",
                        "\"math/rand\"",
                        "\"regexp\""
                    ],
                    "ThirdParty": [
                        "\"github.com/volatiletech/sqlboiler/v4/boil\""
                    ]
                },
                "boil_relationship_test": {
                    "Standard": [
                        "\"testing\""
                    ],
                    "ThirdParty": null
                },
                "boil_suites_test": {
                    "Standard": [
                        "\"testing\""
                    ],
                    "ThirdParty": null
                },
                "mssql_main_test": {
                    "Standard": [
                        "\"bytes\"",
                        "\"database/sql\"",
                        "\"fmt\"",
                        "\"os\"",
                        "\"os/exec\"",
                        "\"regexp\"",
                        "\"strings\""
                    ],
                    "ThirdParty": [
                        "\"github.com/friendsofgo/errors\"",
                        "\"github.com/kat-co/vala\"",
                        "_ \"github.com/microsoft/go-mssqldb\"",
                        "\"github.com/spf13/viper\"",
                        "\"github.com/volatiletech/randomize\"",
                        "\"github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-mssql/driver\""
                    ]
                },
                "mssql_suites_test": {
                    "Standard": [
                        "\"testing\""
                    ],
                    "ThirdParty": []
                }
            },
            "based_on_type": {
                "mssql.UniqueIdentifier": {
                    "Standard": [
                        "\"github.com/microsoft/go-mssqldb\""
                    ],
                    "ThirdParty": []
                },
                "null.Bool": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Bytes": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Float32": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Float64": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Int": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Int16": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Int32": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Int64": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Int8": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.String": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Time": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Uint": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Uint16": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Uint32": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Uint64": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "null.Uint8": {
                    "Standard": [],
                    "ThirdParty": [
                        "\"github.com/volatiletech/null/v8\""
                    ]
                },
                "time.Time": {
                    "Standard": [
                        "\"time\""
                    ],
                    "ThirdParty": []
                },
                "types.Decimal": {
                    "Standard": [
                        "\"github.com/volatiletech/sqlboiler/v4/types\""
                    ],
                    "ThirdParty": []
                },
                "types.NullDecimal": {
                    "Standard": [
                        "\"github.com/volatiletech/sqlboiler/v4/types\""
                    ],
                    "ThirdParty": []
                }
            }
        },
        "aliases": {
            "tables": {
                "TestTable": {
                    "up_plural": "TestTables",
                    "up_singular": "TestTable",
                    "down_plural": "testTables",
                    "down_singular": "testTable",
                    "columns": {
                        "Date": "Date",
                        "Id": "ID"
                    }
                }
            }
        },
        "auto_columns": {},
        "inflections": {
            "Plural": {},
            "PluralExact": {},
            "Singular": {},
            "SingularExact": {},
            "Irregular": {}
        },
        "version": "4.16.2"
    },
    "driver_config": {
        "add-enum-types": false,
        "blacklist": null,
        "dbname": "sqlboiler_test",
        "enum-null-prefix": "Null",
        "foreign-keys": null,
        "host": "192.168.86.50",
        "pass": "replaced",
        "port": 1433,
        "schema": "dbo",
        "sslmode": "disable",
        "user": "sa",
        "whitelist": null
    },
    "schema": "dbo",
    "dialect": {
        "lq": 91,
        "rq": 93,
        "use_index_placeholders": true,
        "use_last_insert_id": false,
        "use_schema": true,
        "use_default_keyword": true,
        "use_top_clause": true,
        "use_output_clause": true,
        "use_case_when_exists_clause": true,
        "use_auto_columns": false
    },
    "tables": [
        {
            "name": "TestTable",
            "schema_name": "",
            "columns": [
                {
                    "name": "Id",
                    "type": "mssql.UniqueIdentifier",
                    "db_type": "uuid",
                    "default": "",
                    "comment": "",
                    "nullable": false,
                    "unique": true,
                    "validated": false,
                    "auto_generated": false,
                    "arr_type": null,
                    "udt_name": "",
                    "domain_name": null,
                    "full_db_type": "uniqueidentifier"
                },
                {
                    "name": "Date",
                    "type": "time.Time",
                    "db_type": "datetime",
                    "default": "",
                    "comment": "",
                    "nullable": false,
                    "unique": false,
                    "validated": false,
                    "auto_generated": false,
                    "arr_type": null,
                    "udt_name": "",
                    "domain_name": null,
                    "full_db_type": "datetime"
                }
            ],
            "p_key": {
                "name": "PK_TestTable",
                "columns": [
                    "Id"
                ]
            },
            "f_keys": null,
            "is_join_table": false,
            "to_one_relationships": null,
            "to_many_relationships": null,
            "is_view": false,
            "view_capabilities": {
                "can_insert": false,
                "can_upsert": false
            }
        }
    ],
    "templates": [
        {
            "name": "main/00_struct.go.tpl",
            "loader": "asset:main/00_struct.go.tpl"
        },
        {
            "name": "main/01_types.go.tpl",
            "loader": "asset:main/01_types.go.tpl"
        },
        {
            "name": "main/02_hooks.go.tpl",
            "loader": "asset:main/02_hooks.go.tpl"
        },
        {
            "name": "main/03_finishers.go.tpl",
            "loader": "asset:main/03_finishers.go.tpl"
        },
        {
            "name": "main/04_relationship_to_one.go.tpl",
            "loader": "asset:main/04_relationship_to_one.go.tpl"
        },
        {
            "name": "main/05_relationship_one_to_one.go.tpl",
            "loader": "asset:main/05_relationship_one_to_one.go.tpl"
        },
        {
            "name": "main/06_relationship_to_many.go.tpl",
            "loader": "asset:main/06_relationship_to_many.go.tpl"
        },
        {
            "name": "main/07_relationship_to_one_eager.go.tpl",
            "loader": "asset:main/07_relationship_to_one_eager.go.tpl"
        },
        {
            "name": "main/08_relationship_one_to_one_eager.go.tpl",
            "loader": "asset:main/08_relationship_one_to_one_eager.go.tpl"
        },
        {
            "name": "main/09_relationship_to_many_eager.go.tpl",
            "loader": "asset:main/09_relationship_to_many_eager.go.tpl"
        },
        {
            "name": "main/10_relationship_to_one_setops.go.tpl",
            "loader": "asset:main/10_relationship_to_one_setops.go.tpl"
        },
        {
            "name": "main/11_relationship_one_to_one_setops.go.tpl",
            "loader": "asset:main/11_relationship_one_to_one_setops.go.tpl"
        },
        {
            "name": "main/12_relationship_to_many_setops.go.tpl",
            "loader": "asset:main/12_relationship_to_many_setops.go.tpl"
        },
        {
            "name": "main/13_all.go.tpl",
            "loader": "asset:main/13_all.go.tpl"
        },
        {
            "name": "main/14_find.go.tpl",
            "loader": "asset:main/14_find.go.tpl"
        },
        {
            "name": "main/15_insert.go.tpl",
            "loader": "asset:main/15_insert.go.tpl"
        },
        {
            "name": "main/16_update.go.tpl",
            "loader": "asset:main/16_update.go.tpl"
        },
        {
            "name": "main/17_upsert.go.tpl",
            "loader": "base64:(sha256 of content): 9e45ff48314713460659a4c750dbadc0538052505eaea728be121ab959a42bba"
        },
        {
            "name": "main/18_delete.go.tpl",
            "loader": "asset:main/18_delete.go.tpl"
        },
        {
            "name": "main/19_reload.go.tpl",
            "loader": "asset:main/19_reload.go.tpl"
        },
        {
            "name": "main/20_exists.go.tpl",
            "loader": "asset:main/20_exists.go.tpl"
        },
        {
            "name": "main/21_auto_timestamps.go.tpl",
            "loader": "asset:main/21_auto_timestamps.go.tpl"
        },
        {
            "name": "main/singleton/boil_queries.go.tpl",
            "loader": "asset:main/singleton/boil_queries.go.tpl"
        },
        {
            "name": "main/singleton/boil_table_names.go.tpl",
            "loader": "asset:main/singleton/boil_table_names.go.tpl"
        },
        {
            "name": "main/singleton/boil_types.go.tpl",
            "loader": "asset:main/singleton/boil_types.go.tpl"
        },
        {
            "name": "main/singleton/boil_view_names.go.tpl",
            "loader": "asset:main/singleton/boil_view_names.go.tpl"
        },
        {
            "name": "main/singleton/mssql_upsert.go.tpl",
            "loader": "base64:(sha256 of content): 4a508e29f7617eea0dbf02f3590c2b7ebcf66e16abd59f3abc5b1f52bffed503"
        },
        {
            "name": "test/00_types.go.tpl",
            "loader": "asset:test/00_types.go.tpl"
        },
        {
            "name": "test/all.go.tpl",
            "loader": "asset:test/all.go.tpl"
        },
        {
            "name": "test/delete.go.tpl",
            "loader": "asset:test/delete.go.tpl"
        },
        {
            "name": "test/exists.go.tpl",
            "loader": "asset:test/exists.go.tpl"
        },
        {
            "name": "test/find.go.tpl",
            "loader": "asset:test/find.go.tpl"
        },
        {
            "name": "test/finishers.go.tpl",
            "loader": "asset:test/finishers.go.tpl"
        },
        {
            "name": "test/hooks.go.tpl",
            "loader": "asset:test/hooks.go.tpl"
        },
        {
            "name": "test/insert.go.tpl",
            "loader": "asset:test/insert.go.tpl"
        },
        {
            "name": "test/relationship_one_to_one.go.tpl",
            "loader": "asset:test/relationship_one_to_one.go.tpl"
        },
        {
            "name": "test/relationship_one_to_one_setops.go.tpl",
            "loader": "asset:test/relationship_one_to_one_setops.go.tpl"
        },
        {
            "name": "test/relationship_to_many.go.tpl",
            "loader": "asset:test/relationship_to_many.go.tpl"
        },
        {
            "name": "test/relationship_to_many_setops.go.tpl",
            "loader": "asset:test/relationship_to_many_setops.go.tpl"
        },
        {
            "name": "test/relationship_to_one.go.tpl",
            "loader": "asset:test/relationship_to_one.go.tpl"
        },
        {
            "name": "test/relationship_to_one_setops.go.tpl",
            "loader": "asset:test/relationship_to_one_setops.go.tpl"
        },
        {
            "name": "test/reload.go.tpl",
            "loader": "asset:test/reload.go.tpl"
        },
        {
            "name": "test/select.go.tpl",
            "loader": "asset:test/select.go.tpl"
        },
        {
            "name": "test/singleton/boil_main_test.go.tpl",
            "loader": "asset:test/singleton/boil_main_test.go.tpl"
        },
        {
            "name": "test/singleton/boil_queries_test.go.tpl",
            "loader": "asset:test/singleton/boil_queries_test.go.tpl"
        },
        {
            "name": "test/singleton/boil_relationship_test.go.tpl",
            "loader": "asset:test/singleton/boil_relationship_test.go.tpl"
        },
        {
            "name": "test/singleton/boil_suites_test.go.tpl",
            "loader": "asset:test/singleton/boil_suites_test.go.tpl"
        },
        {
            "name": "test/singleton/mssql_main_test.go.tpl",
            "loader": "base64:(sha256 of content): e348721821c4794b1cde065c37fb48f161fd289e5e5688bf7bc0b4665769e087"
        },
        {
            "name": "test/singleton/mssql_suites_test.go.tpl",
            "loader": "base64:(sha256 of content): a89b312831433fa034ad26757c27224bad4cf17b2e1a89a31574c5da21f68fa7"
        },
        {
            "name": "test/types.go.tpl",
            "loader": "asset:test/types.go.tpl"
        },
        {
            "name": "test/update.go.tpl",
            "loader": "asset:test/update.go.tpl"
        },
        {
            "name": "test/upsert.go.tpl",
            "loader": "base64:(sha256 of content): eae3e81ec3ef745d0ff230a106828f70e40bca5116a23d8f4059ecfe96390236"
        }
    ]
}

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

Included in https://github.com/mike-tesch/sqlboiler_test/blob/main/testtable.sql

Further information. What did you do, what did you expect?

I think it generated correctly and I think the code should be correct. Seems like something in the parameter binding for uniqueidentifier?
@mike-tesch
Copy link
Author 

---
version: "2.1"
services:

  mssql:
    container_name: mssql-db
    hostname: mssql-db
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      ACCEPT_EULA: 'Y'
      MSSQL_SA_PASSWORD: 'secure'
      MSSQL_DATA_DIR: /var/opt/mssql/data
      MSSQL_BACKUP_DIR: /var/opt/mssql/backups
      MSSQL_PID: 'Developer' 
      MSSQL_TCP_PORT: 1433 
      TZ: America/New_York
    ports: 
      - "1433:1433"
    volumes:
      - /mnt/containers/config/mssql/data:/var/opt/mssql/data
      - /mnt/containers/config/mssql/log:/var/opt/mssql/log
      - /mnt/containers/config/mssql/secrets:/var/opt/mssql/secrets
      - /mnt/containers/config/mssql/backups:/var/opt/mssql/backups

here's the mssql compose I used, just in case it makes it easier to get a repro set up

@mike-tesch
Copy link
Author

mike-tesch commented Aug 7, 2024 •
edited
Loading

tried swapping to uuid via type replacement (mentioned in #1381) google/uuid and gofrs/uuid/v5. same errors

@mike-tesch
Copy link
Author

mike-tesch commented Aug 7, 2024 •
edited
Loading

and here's the output after I add boil.WithDebug(ctx, true)

SELECT [dbo].[TestTable].* FROM [dbo].[TestTable];
[]
2019-05-03 00:00:00 +0000 UTC
2022-02-18 00:00:00 +0000 UTC
2023-09-15 00:00:00 +0000 UTC
2019-07-12 00:00:00 +0000 UTC
2014-07-08 00:00:00 +0000 UTC
2015-03-27 00:00:00 +0000 UTC
2019-05-03 00:00:00 +0000 UTC
2021-07-23 00:00:00 +0000 UTC
2023-02-03 00:00:00 +0000 UTC
2020-07-03 00:00:00 +0000 UTC
SELECT  TOP (1) [dbo].[TestTable].* FROM [dbo].[TestTable] WHERE ([dbo].[TestTable].[Id] = $1);
[5AB0412A-2B2F-430F-8830-002A42125148]
models: failed to execute a one query for TestTable: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money
SELECT  TOP (1) [dbo].[TestTable].* FROM [dbo].[TestTable] WHERE (ID = $1);
[5AB0412A-2B2F-430F-8830-002A42125148]
models: failed to execute a one query for TestTable: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money
select * from [dbo].[TestTable] where [Id]=$1
[5AB0412A-2B2F-430F-8830-002A42125148]
models: unable to select from TestTable: bind failed to execute query: mssql: Operand type clash: uniqueidentifier is incompatible with money

Pretty sure it's interpreting the $1 as 1.00 in the money type instead of a parameter. So I don't think it's a uniqueidentifier issue.

@mike-tesch
Copy link
Author

ok, I figured it out. I was using the wrong sql driver when creating the db

	db, err := sql.Open("sqlserver", conn)

needed to be

	db, err := sql.Open("mssql", conn)

pebkac

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@mike-tesch