Meituan-Dianping / Meituan-Dianping/SQLAdvisor
优化sql存在多数据库时,报【错误日志:Table 'db1.tableA' doesn't exist】错误
Open
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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