Include does not work after migrating to v7.26.1 from v6

[bhosale-ajay]

Two entities

User 
{
 public string? Id { get; set; }
 public string? Name { get; set; }
}
UserInfo
{
    public string? Id { get; set; }
    public string SomeThing { get; set; }
}

Both are built out of projections

var userInfos = new Dictionary<string, UserInfo>();
var queryable = _session.Query<User>()
    .Where() // some where conditions   
    .Include(userInfos).On(x => x.Id!) // This syntax got changed in V7
    .ForAllTokens()
    .ForSomeTokens()
   .OrderBy() // Some ordering
var list = await queryable
                .ToPagedListAsync(paging.CurrentIndex, paging.PageSize, ct);

The last line throws below error.

Marten.Exceptions.MartenCommandException: Marten Command Failure:$
42703: column d.id does not exist

POSITION: 72
 ---> Npgsql.PostgresException (0x80004005): 42703: column d.id does not exist

POSITION: 72
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
   at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
   at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column d.id does not exist
    Hint: There is a column named "id" in table "d", but it cannot be referenced from this part of the query.
    Position: 72
    File: parse_relation.c
    Line: 3656
    Routine: errorMissingColumn
   --- End of inner exception stack trace ---
   at JasperFx.Core.Exceptions.ExceptionTransformExtensions.TransformAndThrow(IEnumerable`1 transforms, Exception ex)
   at JasperFx.Core.Exceptions.ExceptionTransforms.TransformAndThrow(Exception ex)
   at Marten.Exceptions.MartenExceptionTransformer.WrapAndThrow(Exception exception)
   at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
   at Marten.Linq.MartenLinqQueryable`1.ToListAsync[TResult](CancellationToken token)
   at Marten.Pagination.PagedList`1.InitAsync(IQueryable`1 queryable, Int32 pageNumber, Int32 pageSize, CancellationToken token)
   at Marten.Pagination.PagedList`1.CreateAsync(IQueryable`1 queryable, Int32 pageNumber, Int32 pageSize, CancellationToken token)
   at Marten.Pagination.PagedListQueryableExtensions.ToPagedListAsync[T](IQueryable`1 queryable, Int32 pageNumber, Int32 pageSize, CancellationToken token)

Actual Command

 select d.data from public.mt_doc_userinfos as d where d.id in (select d.id from mt_temp_id_list2 as d);

Is this a known issue, or misconfiguration with Include? The query works well after removing the include.

[jeremydmiller]

It's very likely that the problem -- and it may well be a Marten issue -- is in whatever Select() transform you're doing inside of your code here:

    .ForAllTokens()
    .ForSomeTokens()

We've had some issues in the past with the combo of Include() + Select(). Also make sure you're on the very latest version of Marten because there was a fix for Include() + Select() + ToPagedListAsync() in the very last release.

[bhosale-ajay]

Using the latest version of Marten v7.26.1.

[bhosale-ajay]

It throws same error without any where conditions

// no where, no select, no token
var queryable = _session.Query()
.Include(userStats).On(x => x.Id!);

// throws error
var list = await queryable
.ToPagedListAsync(paging.CurrentIndex, paging.PageSize, ct);

// but this works
var list = await queryable.ToListAsync(ct);

[jeremydmiller]

@bhosale-ajay Let me try again, I need more detailed information from you about the types you're using, the exact where clause, everything. We have existing tests that cover the combination of things you're trying to do, so it's not a generalized "it doesn't work". There's something about the exact permutation of what you're doing that is failing. I need to know that to have any chance at helping you here.

[bhosale-ajay]

I will share a repo with combinations to reproduce the error. Thanks for the quick response.

[bhosale-ajay]

Here is complete code.

// Program.cs

using Marten;
using Marten.Pagination;
using Weasel.Core;

const string connectionString = "host=localhost;database=demo_db;";

var store = DocumentStore.For(
    options =>
    {
        options.Connection(connectionString);
        options.AutoCreateSchemaObjects = AutoCreate.All;
        options.Schema.For<User>();
        options.Schema.For<UserInformation>();
    }
);

await using var session = store.QuerySession();
var userInfo = new Dictionary<string, UserInformation>();
var users = await session
    .Query<User>()
    .Include(userInfo).On(x => x.Id!)
    // .ToListAsync(); // This works
    .ToPagedListAsync(1, 1); // This does not
Console.WriteLine(users.Count);

public class User
{
    public string? Id { get; set; }
    public string? FirstName { get; set; }
}

public class UserInformation
{
    public string? Id { get; set; }
    public string? Company { get; set; }
}

// The project references

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Marten" Version="7.26.1" />
  </ItemGroup>
</Project>

[bhosale-ajay]

Stack Trace

Unhandled exception. Marten.Exceptions.MartenCommandException: Marten Command Failure:$
$
$
42703: column d.id does not exist

POSITION: 78
 ---> Npgsql.PostgresException (0x80004005): 42703: column d.id does not exist

POSITION: 78
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
   at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
   at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column d.id does not exist
    Hint: There is a column named "id" in table "d", but it cannot be referenced from this part of the query.
    Position: 78
    File: parse_relation.c
    Line: 3651
    Routine: errorMissingColumn
   --- End of inner exception stack trace ---
   at JasperFx.Core.Exceptions.ExceptionTransformExtensions.TransformAndThrow(IEnumerable`1 transforms, Exception ex)
   at JasperFx.Core.Exceptions.ExceptionTransforms.TransformAndThrow(Exception ex)
   at Marten.Exceptions.MartenExceptionTransformer.WrapAndThrow(Exception exception)
   at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
   at Marten.Linq.MartenLinqQueryable`1.ToListAsync[TResult](CancellationToken token)
   at Marten.Pagination.PagedList`1.InitAsync(IQueryable`1 queryable, Int32 pageNumber, Int32 pageSize, CancellationToken token)
   at Marten.Pagination.PagedList`1.CreateAsync(IQueryable`1 queryable, Int32 pageNumber, Int32 pageSize, CancellationToken token)
   at Marten.Pagination.PagedListQueryableExtensions.ToPagedListAsync[T](IQueryable`1 queryable, Int32 pageNumber, Int32 pageSize, CancellationToken token)
   at Program.<Main>$(String[] args) in /src/MartenApp/Program.cs:line 19
   at Program.<Main>$(String[] args) in /src/MartenApp/Program.cs:line 24
   at Program.<Main>(String[] args)

[bhosale-ajay]

After further testing I found that this works only with v6.4.1 (with change in Include syntax - which is no longer supported after v7.10.x). Even v7.0.0 does not work.

Code which works for v6.4.1 but not for v7.0.0

var users = await session
    .Query<User>()
    .Include(x => x.Id, userInfoList)
    // .ToListAsync(); // This works
    .ToPagedListAsync(1, 1); // This does not
Console.WriteLine(users.Count);

With all other code same as posted above.

[jeremydmiller]

@bhosale-ajay So first off, thank you for the bug reproduction because that's hugely helpful. But, "works on my box".

Do you maybe have some stale, pre-generated code leftover from the V6 version of your system? This worked out of the box on master -- and better, because there are a lot of people using this and there's a lot of existing test coverage that all passes as well. I'm closing this with another test you'll see linked to from the commit w/ your repro steps.

[bhosale-ajay]

After cloning the Marten repository and running my test code by adding a new project to the solution found following difference after debugging.

SQL statements generated when running tests

drop table if exists mt_temp_id_list1; 
create temp table mt_temp_id_list1 as (select d.id, d.data, count(*) OVER() as total_rows from bugs.mt_doc_user3351 as d LIMIT $1);
select d.id, d.data from bugs.mt_doc_userinformation3351 as d where d.id in (select d.id from mt_temp_id_list1 as d);
select * from mt_temp_id_list1 as d;

SQL statements generated when running project

drop table if exists mt_temp_id_list1; 
create temp table mt_temp_id_list1 as (select d.data, count(*) OVER() as total_rows from public.mt_doc_user3351 as d LIMIT $1);
select d.data from public.mt_doc_userinformation3351 as d where d.id in (select d.id from mt_temp_id_list1 as d);
select * from mt_temp_id_list1 as d;

Notice the temp table mt_temp_id_list1 dont have id column in 2nd batch.

[bhosale-ajay]

Created pull request #3356, Please check this branch.

Steps

1. Run docker compose up from the root.
2. Run cd src/TestWithDirectReference.
3. Run dotnet run.

Please let me know if you can see zero or an error.

[jeremydmiller]

@bhosale-ajay See my comments. I don't see any point in this. The test already passed. Did you ever check for stale pre-generated code?

[bhosale-ajay]

There is no pre-generated code. As I can see there is clear difference between SQL batch statements that's get generated when running tests and when running outside tests. Can you please run console app on the PR branch I created and see if you can run it?

[bhosale-ajay]

@jeremydmiller - There is a the difference in "SelectFields" when running tests and when running that console program (no pregenerated code). Any idea?

[bhosale-ajay]

If ToListAsync is used instead of ToPagedListAsync then the temp SQL table genertated does have id column.

drop table if exists mt_temp_id_list1;
create temp table mt_temp_id_list1 as (select d.data, d.id from public.mt_doc_user3351 as d);
select d.data from public.mt_doc_userinformation3351 as d where d.id in (select d.id from mt_temp_id_list1 as d);
select d.data, d.id from mt_temp_id_list1 as d

For ToPagedListAsync, the SQL query for temp table as mentioned in here.

[bhosale-ajay]

For future reference, solution is to use IDocumentSession instead of IQuerySession.
So instead of QuerySession, use LightweightSession.