Meituan-Dianping / Meituan-Dianping/SQLAdvisor

优化sql存在多数据库时,报【错误日志:Table 'db1.tableA' doesn't exist】错误

Open
#93 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C
Stars
5.6k
Forks
1.2k
PR merge metrics
No merged PRs in 30d

Description

sql

SELECT * FROM `db2`.`tableA` AS a 
LEFT JOIN `db1`.`tableB` AS b ON a.company_code = b.CompanyCode 
WHERE a.company_code = 'xxx' AND a.company_type = '1'

具体日志(对关键信息做了替换)

/data/sqladvisor/SQLAdvisor-master/sqladvisor/sqladvisor -h xxx -P 3306 -u xxx -p xxx -d db1 -q 
"SELECT * FROM \`db2\`.\`tableA\` AS a 
LEFT JOIN \`db1\`.\`tableB\` AS b ON a.company_code = b.CompanyCode 
WHERE a.company_code = 'xxx' AND a.company_type = '1'" -v 1
2022-09-29 17:20:47 24980 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` 
from (`db2`.`tableA` `a` left join `db1`.`tableB` `b` on((`a`.`company_code` = `b`.`CompanyCode`))) 
where ((`a`.`company_code` = 'xxx') and (`a`.`company_type` = '1'))

2022-09-29 17:20:47 24980 [Note] 第2步:开始解析where中的条件:(`a`.`company_code` = 'xxx')

2022-09-29 17:20:47 24980 [Note] show index from tableA

2022-09-29 17:20:47 24980 [Note] show table status like 'table'

2022-09-29 17:20:47 24980 [Note] select count(*) from ( select `company_code` from `tableA` FORCE INDEX( PRIMARY ) order by company_id DESC limit 1234) `a` where (`a`.`company_code` = 'xxx')

2022-09-29 17:20:47 24980 [Note] 第3步:表tableA的行数:1234,limit行数:1234,得到where条件中(`a`.`company_code` = 'xxx')的选择度:1234

2022-09-29 17:20:47 24980 [Note] 第4步:开始解析where中的条件:(`a`.`company_type` = '1')

2022-09-29 17:20:47 24980 [Note] show index from tableA

2022-09-29 17:20:47 24980 [Note] show table status like 'tableA'

2022-09-29 17:20:47 24980 [Note] select count(*) from ( select `company_type` from `tableA` FORCE INDEX( PRIMARY ) order by company_id DESC limit 1234) `a` where (`a`.`company_type` = '1')

2022-09-29 17:20:47 24980 [Note] 第5步:表tableA的行数:1234,limit行数:1234,得到where条件中(`a`.`company_type` = '1')的选择度:1

2022-09-29 17:20:47 24980 [Note] 第6步:开始解析join on条件:a.company_code=b.CompanyCode

2022-09-29 17:20:47 24980 [Note] 第7步:开始选择驱动表,一共有1个候选驱动表

2022-09-29 17:20:47 24980 [Note] explain select * from tableA as a where (`a`.`company_code` = 'xxx')

2022-09-29 17:20:47 24980 [Note] 第8步:SQLAdvisor结束!错误日志:Table 'db1.tableA' doesn't exist

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the issue with the SQLAdvisor command and cross-database query shown in the report, using db2.tableA and db1.tableB. Trace the table lookup after the driver-table selection and compare the qualified table name used by the input with the db1.tableA name in the error. Done means the query is processed without the incorrect missing-table error.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, mysql, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.