Error: QueryFailedError: VT12001: unsupported: LEFT JOIN with derived tables

[ehteshamshahzad]

I am using PlanetScale with NestJS and TypeORM. I have two tables:

• User
• Category

There is no relation between the two tables. However, when I try to run my code, I get this error in the terminal:

QueryFailedError: VT12001: unsupported: LEFT JOIN with derived tables
    at Query.onResult (/Users/ehteshamshahzad/Documents/Projects/F/Flok/flok-backend/src/driver/mysql/MysqlQueryRunner.ts:222:33)
    at Query.execute (/Users/ehteshamshahzad/Documents/Projects/F/Flok/flok-backend/node_modules/mysql2/lib/commands/command.js:36:14)
    at PoolConnection.handlePacket (/Users/ehteshamshahzad/Documents/Projects/F/Flok/flok-backend/node_modules/mysql2/lib/connection.js:490:32)
    at PacketParser.onPacket (/Users/ehteshamshahzad/Documents/Projects/F/Flok/flok-backend/node_modules/mysql2/lib/connection.js:95:12)
    at PacketParser.executeStart (/Users/ehteshamshahzad/Documents/Projects/F/Flok/flok-backend/node_modules/mysql2/lib/packet_parser.js:75:16)
    at TLSSocket.<anonymous> (/Users/ehteshamshahzad/Documents/Projects/F/Flok/flok-backend/node_modules/mysql2/lib/connection.js:389:25)
    at TLSSocket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:234:10)

If I comment out @Entity('category') from Category class or @Entity('user') from User class then run the code, it works fine.
Right now, synchronize option is set to true. I don't want to set it to false just yet because I am in extremely early stage of development, and I don't want to deal with generating migration files right now.

[orware]

Hello @ehteshamshahzad,

Unfortunately, this is a current known issue with TypeORM and our Vitess layer.

A partial fix is already implemented within PlanetScale, but the full fix won't be available until after Vitess 17 releases at the end of this month and from there it may take a little while for us to roll out Vitess 17 internally within PlanetScale's infrastructure.

There is an unofficial TypeORM fork available that may be a bit out of date now that implements a workaround approach in the meanwhile if you're interested in having a more immediate option available if you're willing to try it.

If not, aside from the other option of setting synchronize: false that you mentioned, the best recommendation I can provide at the moment would be to remain patient for a few more weeks and this issue should hopefully being going away soon once Vitess 17 has been released.

[leonardocintra]

I have the same problem. I will continue using railway until this solution is resolved.

Will we be notified @orware ?

[orware]

Hi @leonardocintra,

I'll make an effort to come back and provide a confirmation update here once I can see the issue is resolved on our end after Vitess 17 is rolled out in our environment for users.

[temiNoah]

Hi Guys,
I having similar issue. Thanks for pointing it out

[saksham2410]

Any update on this? I'm still getting this error. There is no relation between the two tables @orware

[orware]

@saksham2410, No update yet as we've primarily finished rolling database's onto Vitess 16 in recent weeks so there will still be a bit of a wait before databases have been moved onto Vitess 17 where the related fix should be included.

[mrjasan]

Hi @orware
I know this is still in the works, but do you have any workaround this? Even if not ideal?

[orware]

Hi @mrjasan,

The main one I had available is the unofficial fork option shared above, but from what I've noticed the TypeORM code has changed a fair amount since that fork occurred so I'm not sure how to apply the same fix implemented there in the newer code.

Basically though, the issue the fork was helping to solve at the time last year was to switch TypeORM away from using a lot of UNION statements together and instead use an IN statement for the various tables I think that needed to be included for the specific metadata queries that it creates.

[mrjasan]

Thanks for the quick reply. Actually I found a workaround just dropping the schema, as the changes I was trying to push were only on a dev branch.
It's no longer an issue for me. Thanks!

[orware]

That's good to know as well, thank you @mrjasan for sharing that!

[minjiground]

Any updates on this? I'm still getting this error..

[orware]

Hello @minjispace,

New Hobby plan databases should be getting provisioned with Vitess 17 at this time as our team works on continuing to roll it out to all databases, so if you have recently created a new Hobby plan database and are still encountering the issue that would provide further confirmation that it isn't completely resolved with Vitess 17 as I was hoping/expecting.

If that is the case for you, sharing the full metadata query that TypeORM is generating would be helpful to share with us here so we can perform some additional testing and work on creating an additional internal issue on our end.

[icodealone]

I fired up a Hobby plan database yesterday and am experiencing the same issue.

This is the logging output.

SELECT s.* FROM ( SELECT * FROM INFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA= 'phoenix' ANDTABLE_NAME= 'users' UNION SELECT * FROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA= 'phoenix' ANDTABLE_NAME= 'accounts' UNION SELECT * FROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA= 'phoenix' ANDTABLE_NAME= 'sessions' )sLEFT JOIN ( SELECT * FROMINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSWHERECONSTRAINT_SCHEMA= 'phoenix' ANDTABLE_NAME= 'users' UNION SELECT * FROMINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSWHERECONSTRAINT_SCHEMA= 'phoenix' ANDTABLE_NAME= 'accounts' UNION SELECT * FROMINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSWHERECONSTRAINT_SCHEMA= 'phoenix' ANDTABLE_NAME= 'sessions' )rcONs.INDEX_NAME=rc.CONSTRAINT_NAMEANDs.TABLE_SCHEMA=rc.CONSTRAINT_SCHEMAWHEREs.INDEX_NAME!= 'PRIMARY' ANDrc.CONSTRAINT_NAME` IS NULL

error: Error: ER_UNKNOWN_ERROR: VT12001: unsupported: LEFT JOIN with derived tables
query: ROLLBACK
query failed:
SELECT
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
kcu.CONSTRAINT_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_TABLE_SCHEMA,
kcu.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME,
rc.DELETE_RULE ON_DELETE,
rc.UPDATE_RULE ON_UPDATE
FROM (
SELECT
*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = 'phoenix'
AND
kcu.TABLE_NAME = 'users'
UNION
SELECT
*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = 'phoenix'
AND
kcu.TABLE_NAME = 'accounts'
UNION
SELECT
*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = 'phoenix'
AND
kcu.TABLE_NAME = 'sessions'
) kcu
INNER JOIN (
SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = 'phoenix'
AND
TABLE_NAME = 'users'
UNION
SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = 'phoenix'
AND
TABLE_NAME = 'accounts'
UNION
SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = 'phoenix'
AND
TABLE_NAME = 'sessions'
) rc
ON
rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND
rc.TABLE_NAME = kcu.TABLE_NAME
AND
rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME

error: Error: ER_UNKNOWN_ERROR: VT12001: unsupported: JOIN between derived tables
`

[orware]

Hello @icodealone,

I appreciate you sharing those example queries!

I'll be adding some new comments to an internal issue we have about this to share with our team and there is also a public issue vitessio/vitess#9214 you can follow as well for additional updates.

[kurt-rutabaga]

I am experiencing this issue as well when I attempted to generate a new migration in typeorm. The workaround that I am using currently is to use a local MySQL instance for development along with typeorm: sync false, manually create migrations using typeorm migration:generate agains the local DB. From there I can switch configuration to our dev branch on Planetscale and run the migrations.

[syedolive]

Hi, any updates on this. This has really become a bottleneck.

[orware]

@icodealone,

It sounds like we may have a way to try out the recently merged in changes if you would be interested in giving them a shot for us to see how they behave for you.

If so, just send in a quick message over at https://support.planetscale.com/ referencing this discussion and your database name and I can make the needed changes. If the new changes do the trick then this should be making its way generally into all PlanetScale databases in the next few weeks.

[icodealone]

Thanks, will do.

[icodealone]

Not looking very promising sadly.

query failed:
                SELECT
                    *
                FROM
                    `INFORMATION_SCHEMA`.`COLUMNS`
                WHERE
                    `TABLE_SCHEMA` = 'phoenix'
                    AND
                    `TABLE_NAME` = 'users'
                 UNION
                SELECT
                    *
                FROM
                    `INFORMATION_SCHEMA`.`COLUMNS`
                WHERE
                    `TABLE_SCHEMA` = 'phoenix'
                    AND
                    `TABLE_NAME` = 'accounts'
                 UNION
                SELECT
                    *
                FROM
                    `INFORMATION_SCHEMA`.`COLUMNS`
                WHERE
                    `TABLE_SCHEMA` = 'phoenix'
                    AND
                    `TABLE_NAME` = 'sessions'

error: Error: ER_UNKNOWN_ERROR: unknown: uncaught panic: runtime error: invalid memory address or nil pointer dereference, vtgate: http://pscluster-blg4r54tgwuu-aws-euwest2b-1-vtgate-a0e64c96-5c57nwtj8:15000/

[eintxaurtieta]

Same here. Any updates?

[orware]

@eintxaurtieta,

No updates just yet on this one, but the additional issues shared about two weeks ago above were passed along to our Vitess team for further review.

[devkiloton]

Any solutions? 🤧

[orware]

Hello @devkiloton,

I don't have any further updates yet on our end that I'm aware of. Did you also encounter the same issue yourself recently?

[devkiloton]

Yes, ended up generating the SQL with TypeORM and running on planetscale console :/

[Caryntjen]

Also encountered this issue when trying to set-up vendure with planetscale. So I am currently need to look into alternatives. When this is fixed, I am happy to try it out again.

[mikechabot]

Same problem with TypeORM, but a different error message: coercion should not try to coerce this value: BIT(""). Offending SQL is further below.

Note: I spun up a MySQL instance within AWS RDS and this issue does not occur. Same repository, same entities, different MySQL connection. This appears to be a problem with Vitess?

Notes

• The initial table creations are fine (i.e. drop all tables, start application, refresh tables in MySQL Workbench, looks good!)
• However if synchronize is true -- the error below is thrown whenever hot reload detects a change, be it to the schema itself, or really any piece of code that prompts it.
• I can replicate the error directly within MySQL Workbench when the offending SQL executed directly.
• Error occurs regardless of whether "Foreign key constraints" are enabled in PlanetScale.

Versions

 "typeorm": "^0.3.19",
 "mysql2": "^3.6.5",

Entities (Simple OneToMany / ManyToOne)

Organization has Workspaces

import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from 'typeorm';

import { Workspace } from '~entities/workspace.entity';

@Entity()
export class Organization {
  @PrimaryGeneratedColumn('increment')
  organizationId: number;

  @Column()
  organizationName: string;

  @OneToMany(() => Workspace, (workspace) => workspace.organization, {
    createForeignKeyConstraints: false,
  })
  workspaces: Workspace[];
}

Workspace are a part of Organization

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Organization } from '~entities/organization.entity';

@Entity()
export class Workspace {
  @PrimaryGeneratedColumn('increment')
  workspaceId: number;

  @Column()
  workspaceName: string;

  @ManyToOne(() => Organization, (organization) => organization.workspaces, {
    createForeignKeyConstraints: false,
  })
  @JoinColumn({ name: 'organizationId' })
  organization: Organization;
}

Error

query: SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_COMMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'skyline' AND `TABLE_NAME` = 'workspace' UNION SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_COMMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'skyline' AND `TABLE_NAME` = 'organization'
query failed: SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_COMMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'skyline' AND `TABLE_NAME` = 'workspace' UNION SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_COMMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'skyline' AND `TABLE_NAME` = 'organization'
error: Error: coercion should not try to coerce this value: BIT("")
query: ROLLBACK
[Nest] 162592  - 01/09/2024, 10:09:31 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (1)...
QueryFailedError: coercion should not try to coerce this value: BIT("")
    at Query.onResult (D:\_workspaces\skyline\node_modules\.pnpm\[email protected][email protected][email protected]\src\driver\mysql\MysqlQueryRunner.ts:246:33)
    at Query.execute (D:\_workspaces\skyline\node_modules\.pnpm\[email protected]\node_modules\mysql2\lib\commands\command.js:36:14)
    at PoolConnection.handlePacket (D:\_workspaces\skyline\node_modules\.pnpm\[email protected]\node_modules\mysql2\lib\connection.js:478:34)
    at PacketParser.onPacket (D:\_workspaces\skyline\node_modules\.pnpm\[email protected]\node_modules\mysql2\lib\connection.js:97:12)
    at PacketParser.executeStart (D:\_workspaces\skyline\node_modules\.pnpm\[email protected]\node_modules\mysql2\lib\packet_parser.js:75:16)
    at TLSSocket.<anonymous> (D:\_workspaces\skyline\node_modules\.pnpm\[email protected]\node_modules\mysql2\lib\connection.js:392:25)
    at TLSSocket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:234:10)

I really enjoy PlanetScale, but this is pretty crappy. Hoping there's some movement on this, unfortunately I'm going to need to look at Neon or DigitalOcean, or even just stick with RDS. Prisma works great with PlanetScale, but performance-wise, TypeORM is demonstrably faster.

[mikechabot]

Since this discussion is technically answered, I opened #590 to discuss further.