1

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.

wes
  • 734
  • 6
  • 14
  • 1
    sounds like planetscale is only sort of mysql-compatible? or is this error coming from whatever you are using to run the query, not server side? you could try making it `select Articles.* from Articles inner join (select id,createdAt from...) dummy on Articles.id=dummy.id order by dummy.createdAt, dummy.id` – ysth May 29 '23 at 17:04
  • @ysth PlanetScale uses a proxy to parse SQL and direct it to the shard(s) that hold the data needed by that query. It will approach, but never fully reach 100% compatibility with MySQL's syntax. – Bill Karwin May 29 '23 at 17:22
  • @ysth - yeah the error comes via their CLI tool. The syntax you provide works great (thanks!). I'm just a little baffled now b/c the query I posted comes from their site (w/ my table of course) – wes May 30 '23 at 00:43
  • Maybe you have an old version? – ysth May 30 '23 at 01:41
  • Please provide `SHOW CREATE TABLE`; it is more descriptive than `DESCRIBE`. – Rick James May 30 '23 at 04:01
  • How is the table sharded? That is, on what key? – Rick James May 30 '23 at 04:03
  • @RickJames unfortunately I don't know. If there's a way to see i can try it out, though I suspect it might be some of the "magic" that's taken care of for you. I also added the `show create table` if it's helpful – wes May 31 '23 at 00:21
  • 1
    @wes - For the task, the query and the schema are excellent. (Bill found the Answer to the actual Question.) – Rick James May 31 '23 at 04:30

1 Answers1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828