Deferred JOIN: can't handle JOIN USING without authoritative tables

Question

I have a basic database in which I'm trying to implement the deferred join technique. However, on Planetscale I keep getting the following error: can't handle JOIN USING without authoritative tables.

I'm not terribly well-versed in MYSQL, but I'm struggling to understand what might be the issue. Here is a sample query:

SELECT * FROM Articles
INNER JOIN (
  SELECT id FROM Story ORDER BY createdAt,id LIMIT 20 OFFSET xxx
) dummy USING (id)
ORDER BY
  createdAt,id;

+-----------+-----------------+------+-----+----------------------+-------------------+
| Field     | Type            | Null | Key | Default              | Extra             |
+-----------+-----------------+------+-----+----------------------+-------------------+
| id        | bigint unsigned | NO   | PRI |                      | auto_increment    |
| title     | varchar(255)    | NO   | MUL |                      |                   |
| synopsis  | varchar(2048)   | NO   |     |                      |                   |
| createdAt | datetime(3)     | NO   | MUL | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| updatedAt | datetime(3)     | YES  |     |                      |                   |
| content   | mediumtext      | NO   | MUL |                      |                   |
| wordcount | int             | NO   |     |                      |                   |
| authorId  | int             | NO   | MUL |                      |                   |
+-----------+-----------------+------+-----+----------------------+-------------------+

update, to add show create table:

CREATE TABLE `Articles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `synopsis` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) DEFAULT NULL,
  `content` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `wordcount` tinyint(1) NOT NULL DEFAULT '0',
  `authorId` int DEFAULT NULL, PRIMARY KEY(`id`),
  KEY `Story_authorId_idx`(`authorId`), 
  KEY `Story_createdAt_idx`(`createdAt`), 
  KEY `createdAt`(`createdAt`), 
  FULLTEXT KEY `Story_title_content_synopsis_idx`(`title`, `content`, `synopsis`), 
  FULLTEXT KEY `Story_title_idx`(`title`), 
  FULLTEXT KEY `Story_content_idx`(`content`)
)
ENGINE = InnoDB AUTO_INCREMENT = 7892518284260113982 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci

I originally generated the schema via Prisma (if that matters), but have since written things/queries natively because of performance issues.

Answer 1

You could try rewriting your query to the equivalent ON <expression> syntax.

This:

FROM Articles JOIN ( ...) AS dummy USING (id)

Can be written as:

FROM Articles JOIN ( ...) AS dummy ON Articles.id = dummy.id

There is special behavior in SQL for SELECT * when you have the USING syntax. The * wildcard expands to all columns, but columns that are known to be equal are included in the result set only once.

I have not tested this with PlanetScale, so I don't know if PlanetScale will handle it or not.

This issue claims that USING is supported since May 2022: https://github.com/vitessio/vitess/pull/10226 But I didn't find anything in the release notes since that date describing the feature. You may have more luck if you search more thoroughly.