`select (tuple)` with `where` or `sortBy` generates `SELECT 1` instead of column projections (PostgreSQL, .NET 10.0.200)

[dlidstrom]

Environment

• SQLProvider: 1.5.17
• Database: PostgreSQL (Npgsql 9.0.4)
• .NET SDK: 10.0.200
• NullableColumnType.OPTION

Summary

When a query {} expression contains a where or sortBy clause and uses select (tuple) to project specific columns, SQLProvider generates SELECT 1 instead of selecting the actual columns. The result is that all tuple elements come back as None.

Without where or sortBy, select (tuple) works correctly.

Schema

CREATE SCHEMA repro;

CREATE TABLE repro.item (
  id            INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name          VARCHAR(100) NOT NULL,
  value         INT NOT NULL,
  optional_date TIMESTAMP WITHOUT TIME ZONE NULL
);

CREATE VIEW repro.v_item AS
  SELECT id, name, value, optional_date FROM repro.item;

INSERT INTO repro.item (name, value) VALUES ('test', 42);

Reproduction

module Repro

open FSharp.Data.Sql
open FSharp.Data.Sql.Common

[<Literal>]
let ConnString =
  "..."

type Db =
  SqlDataProvider<
    DatabaseProviderTypes.POSTGRESQL,
    ConnString,
    "",
    "lib",
    1000,
    NullableColumnType.OPTION,
    "repro">

[<EntryPoint>]
let main _ =
  let ctx = Db.GetDataContext()

  let test label q =
    printfn $"%s{label}: %A{q |> Seq.tryHead}"

  // Works: no WHERE or sortBy
  test "no filter, select tuple (works)"
    (query { for r in ctx.Repro.VItem do select (r.Id, r.Name) })

  // Broken: WHERE alone is enough to trigger it
  test "where only, select tuple (broken)"
    (query { for r in ctx.Repro.VItem do where (r.Value = Some 42); select (r.Id, r.Name) })

  // Broken: sortBy alone is enough to trigger it
  test "sortBy only, select tuple (broken)"
    (query { for r in ctx.Repro.VItem do sortBy r.Id; select (r.Id, r.Name) })

  // Workaround: select entity, project afterwards
  test "where + sortBy, select entity (workaround)"
    (query { for r in ctx.Repro.VItem do where (r.Value = Some 42); sortBy r.Id; select r }
     |> Seq.map (fun r -> r.Id, r.Name))

  0

Output:

no filter, select tuple (works): Some (Some 1, Some "test")
where only, select tuple (broken): Some (None, None)
sortBy only, select tuple (broken): Some (None, None)
where + sortBy, select entity (workaround): Some (Some 1, Some "test")

Workaround

Select the full entity and project to a tuple in F# after the query:

query { for r in ctx.Repro.VItem do where (r.Value = Some 42); sortBy r.Id; select r }
|> Seq.tryHead
|> Option.map (fun r -> r.Id, r.Name)
// Returns: Some (Some 1, Some "test")  ✓

[Thorium]

Hmm, sounds weird, I would expect the unit-test project is full of these kind of selects where it's working. I haven't tested with .NET 10 yet if they've modified the LINQ projections somehow.