Issues with SQL Subqueries

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql with 子查询问题

| username: 望海崖2084

The SQL is as follows:

WITH t AS (
SELECT id, name, type1, type2
FROM dim_table
WHERE type1 = 'xxx'
)

SELECT id, name, type2, COUNT(*)
FROM t
INNER JOIN fact_table f ON t.id = f.dim_id
WHERE f.dim_id IN (SELECT id FROM t)
GROUP BY id, name, type2 

The idea here is to first query the id of the target dim_table, and then use the index on fact_table.dim_id to directly query, avoiding a full table join between fact_table and dim_table.
[Encountered Problem] The SQL execution reported an error, it seems that TiDB does not support the use of WITH subqueries in the WHERE condition.

This issue did not reoccur, it might have been a problem with the SQL writing at that time. I don’t remember the exact error, but it seemed to be something like “dexxx one row” pointing to the subquery row.

| username: zhanggame1 | Original post link

What error are you encountering? TiDB supports your syntax. I tested a similar SQL with some random data and it worked fine.

WITH t AS (
    SELECT draw_id FROM game_draw WHERE draw_no='23011'
)
SELECT * FROM t
INNER JOIN game_draw_fund g ON t.draw_id=g.draw_id
WHERE g.draw_id IN (SELECT draw_id FROM t)
| username: 小龙虾爱大龙虾 | Original post link

What error is being reported?

| username: Miracle | Original post link

Isn’t the join condition of the inner join redundant with the where clause? It seems like adding or not adding the where clause results in the same outcome, right?

| username: 像风一样的男子 | Original post link

I have tried it and it supports the WITH subquery. Please provide a screenshot of the error.

| username: tidb菜鸟一只 | Original post link

There won’t be any errors…

| username: 有猫万事足 | Original post link

This “with” syntax is called CTE. It has been supported since version 6.1, so your TiDB version is outdated.

| username: zhanggame1 | Original post link

Support starts from 5.1, not 6.1
WITH | PingCAP Documentation Center

| username: 有猫万事足 | Original post link

:+1: Very rigorous, but I don’t know why.

| username: dba远航 | Original post link

When there is an error, be sure to report it.

| username: 像风一样的男子 | Original post link

I can use WITH in version 5.4.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.