Complex queries cause Error on line 1905, column 1: System.QueryException: Invalid bind expression type of Date for column of type String

[sjurgis]

Took me a while to make this repro... It seems like changing the order of filters sometimes fixes this, but it's PITA and waste of time.

Consider this class:

public without sharing class Repro extends SOQL implements SOQL.Selector {
    public static Repro query() {
        return new Repro();
    }
    private Repro() {
        super(Account.SObjectType);
        with(new SObjectField[]{
                Account.Id
        });
        systemMode();
        withoutSharing();
    }
    public Repro withContacts(
            Date fromDate,
            Date toDate
    ) {
        with(
                SOQL.SubQuery.of('Contacts')
                        .with(new SObjectField[]{
                                Contact.Id
                        })
                        .whereAre(
                                SOQL.FilterGroup
                                        .add(SOQL.Filter.with(Contact.CreatedDate).lessOrEqual(fromDate))
                                        .add(SOQL.FilterGroup
                                                .add(SOQL.Filter.with(Contact.CreatedDate).isNull())
                                                .add(SOQL.Filter.with(Contact.CreatedDate).greaterOrEqual(toDate))
                                                .conditionLogic('1 OR 2')
                                        )
                        )
        );
        return this;
    }
    public Repro withOpportunities(String source, Date createdDate){
        with(
                SOQL.SubQuery.of('Opportunities')
                        .with(new SObjectField[]{
                                Opportunity.Id
                        })
                        .whereAre(SOQL.Filter.with(Opportunity.LeadSource).equal(source))
                        .whereAre(SOQL.Filter.with(Opportunity.CreatedDate).equal(createdDate))
        );
        return this;
    }
}

if you try to run this via

Repro.query()
        .withOpportunities(
                'foo',
                Date.newInstance(2024, 1, 1)
        )
        .withContacts(
                Date.newInstance(2024, 1, 2),
                Date.newInstance(2024, 1, 29)
        )
        .setLimit(1)
        .toMap();

It will cause

Error on line 1905, column 1: System.QueryException: Invalid bind expression type of Date for column of type String
Class.SOQL.WithoutSharing.toSObjects: line 1905, column 1
Class.SOQL.Executor.toList: line 1808, column 1
Class.SOQL.Executor.toMap: line 1819, column 1
Class.SOQL.toMap: line 720, column 1
AnonymousBlock: line 11, column 1
AnonymousBlock: line 11, column 1

Now, change the filters order to

                        .whereAre(SOQL.Filter.with(Opportunity.CreatedDate).equal(createdDate))
                        .whereAre(SOQL.Filter.with(Opportunity.LeadSource).equal(source))

And it works just fine.

Unsure if this is library bug or queryWithBinds bug, but I've got another query with this bug that I'm struggling to make work and would love to solve.

[pgajek2]

Hi @sjurgis , looks interesting. I am checkin!

[pgajek2]

@sjurgis one request from my side. Could you add .preview() to your query?

https://soql.beyondthecloud.dev/api/soql#preview

Repro.query()
        .withOpportunities(
                'foo',
                Date.newInstance(2024, 1, 1)
        )
        .withContacts(
                Date.newInstance(2024, 1, 2),
                Date.newInstance(2024, 1, 29)
        )
        .setLimit(1)
        .preview()
        .toMap();

And please let me know what are you getting in debugs

[sjurgis]

@pgajek2 This is preview of my repro above

============ SOQL Preview ============
SELECT Id , (SELECT Id FROM Opportunities WHERE CreatedDate = :v1 AND LeadSource = :v2), (SELECT Id FROM Contacts WHERE (CreatedDate <= :v1 AND (CreatedDate = :v2 OR CreatedDate >= :v3))) FROM Account LIMIT 1
=======================================

06:46:16.97 (166956025)|USER_DEBUG|[670]|ERROR

============ SOQL Binding ============
{
  "v3" : "2024-01-29",
  "v2" : null,
  "v1" : "2024-01-02"
}
=======================================

[pgajek2]

Got it, thank you! Give me couple of minutes.

[sjurgis]

Here's another example - looks like all the binds are gone

============ SOQL Preview ============
SELECT Id, FirstName, LastName, Payroll_Employee_ID__c, SOME_Status__c, Date_Retired__c, SOME_Id__c, MiddleName, Name, Date_of_Death__c, Pension_Fund_Number__c, Years_of_Service__c , (SELECT Type__c, Status__c, Deduction_Indicator__c, Elections_Indicator__c, Effective_From__c, Effective_To__c FROM Status__r WHERE ((Effective_From__c <= :v1 AND (Effective_To__c = :v2 OR Effective_To__c >= :v3)) OR (Effective_From__c <= :v4 AND (Effective_To__c = :v5 OR Effective_To__c >= :v6)))) FROM Contact WHERE Id IN (SELECT Contact__c FROM Status__c) AND Id IN :v1
=======================================

06:49:10.123 (3161937302)|USER_DEBUG|[670]|ERROR

============ SOQL Binding ============
{
  "v1" : [ "003...", ... ]
}
=======================================

[pgajek2]

I see where the problem is, and I am working on the solution. :>

[pgajek2]

@sjurgis ,

Please update your SOQL.cls with that code: https://github.com/beyond-the-cloud-dev/soql-lib/blob/119-complex-queries-cause-error-on-line-1905-column-1-systemqueryexception-invalid-bind-expression-type-of-date-for-column-of-type-string/force-app/main/default/classes/SOQL.cls

And SOQL_Test.cls with that one: https://github.com/beyond-the-cloud-dev/soql-lib/blob/119-complex-queries-cause-error-on-line-1905-column-1-systemqueryexception-invalid-bind-expression-type-of-date-for-column-of-type-string/force-app/main/default/classes/SOQL_Test.cls

And let me know if it works as expected 👯

[sjurgis]

@pgajek2 interestingly it's struggling to deploy

• classes/SOQL.cls: ERROR at line 787, column 37 - Method does not exist or incorrect signature: void isIn(List) from the type SOQL.Filter

[pgajek2]

It's because of the API version. Please use API 60.0.

<?xml version="1.0" encoding="UTF-8"?>
<ApexClass xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>60.0</apiVersion>
    <status>Active</status>
</ApexClass>

[sjurgis]

Yup just realised that :D

[pgajek2]

https://github.com/beyond-the-cloud-dev/soql-lib/releases/tag/v3.2.2

In the new version are changes that may affect you:

[sjurgis]

Hi, seems resolved but will keep an eye on this tomorrow. Thanks for pointing out v60 isIn update - indeed there were some uses I needed to update! Great mahi! Goodnight!

[pgajek2]

Perfect @sjurgis! Let me know if there are other issues (I hope not :D)! Good luck!