`.orderBy` influence `sql` output

With the following code:

  const exchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed:
        sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`.mapWith(
          (value) => value === "1",
        ),
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code));

  console.log(exchanges.find((exch) => exch.code === "BR"));

  const orderedExchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed:
        sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`.mapWith(
          (value) => value === "1",
        ),
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code))
    .orderBy(asc(stockExchanges.name));

  console.log(orderedExchanges.find((exch) => exch.code === "BR"));

  const exchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed:
        sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`.mapWith(
          (value) => value === "1",
        ),
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code));

  console.log(exchanges.find((exch) => exch.code === "BR"));

  const orderedExchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed:
        sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`.mapWith(
          (value) => value === "1",
        ),
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code))
    .orderBy(asc(stockExchanges.name));

  console.log(orderedExchanges.find((exch) => exch.code === "BR"));

I get the following output:

{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: true
}
{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: false
}

{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: true
}
{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: false
}

Why would the orderBy influence the computed column isUsed (the expected result is true)?

6 Replies

binajmenOP•13mo ago

If I remove the .mapWith, things get more funny:

  const exchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed: sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`,
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code));

  console.log(exchanges.find((exch) => exch.code === "BR"));

  const orderedExchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed: sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`,
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code))
    .orderBy(asc(stockExchanges.name));

  console.log(orderedExchanges.find((exch) => exch.code === "BR"));

  const exchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed: sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`,
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code));

  console.log(exchanges.find((exch) => exch.code === "BR"));

  const orderedExchanges = await db
    .select({
      ...getTableColumns(stockExchanges),
      isUsed: sql<boolean>`case when ${usedExchanges.data} is not null then 1 else 0 end`,
    })
    .from(stockExchanges)
    .leftJoin(usedExchanges, eq(usedExchanges.data, stockExchanges.code))
    .orderBy(asc(stockExchanges.name));

  console.log(orderedExchanges.find((exch) => exch.code === "BR"));

The output:

{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: '1'
}
{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: 1
}

{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: '1'
}
{
  name: 'Euronext Brussels',
  code: 'BR',
  operatingMIC: 'XBRU',
  country: 'Belgium',
  currency: 'EUR',
  countryISO2: 'BE',
  countryISO3: 'BEL',
  isUsed: 1
}

Is used is casted as a string in the first query, and as a number in the second query. Yesterday, I already mentioned I was surprised the case when .. then 1 else 0 end would be casted a string. https://discord.com/channels/1043890932593987624/1179094189615628348 But now, it's even weirder. Why would the orderBy cast it "normally"?

Angelelz•13mo ago

Wow, this is definitely extra weird! 🤷

binajmenOP•13mo ago

ikr 🙈

Angelelz•13mo ago

I think this is interesting. I'll do some research and reach out to MySql2 author for his thoughts

binajmenOP•13mo ago

Should I open an issue on Drizzle's Github for the sake of tracking this?

Angelelz•13mo ago

No, I don't think it's a drizzle issue, let me investigate. I know drizzle is doing nothing to map the result and change the type because it doesn't have any knowledge of what is getting queried with sql

Gaming

Programming

`.orderBy` influence `sql` output