记一次 shardingjdbc+mybatisplus+springboot 联表查询路由无法匹配到join后面的表

      遍寻众博客文章，发现没人有一样的错误，着实令人费解。

      使用版本：

<sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>

<!-- for spring boot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

     尝试更换版本也无果。

     跟踪源码发现了这一个东西：

  /**
     * Parse SQL.
     *
     * @param useCache use cache or not
     * @return parsed SQL statement
     */
    public SQLStatement parse(final boolean useCache) {
        Optional<SQLStatement> cachedSQLStatement = getSQLStatementFromCache(useCache);
        if (cachedSQLStatement.isPresent()) {
            return cachedSQLStatement.get();
        }
        LexerEngine lexerEngine = LexerEngineFactory.newInstance(dbType, sql);
        SQLStatement result = SQLParserFactory.newInstance(dbType, shardingRule, lexerEngine, shardingTableMetaData, sql).parse();
        if (useCache) {
            parsingResultCache.put(sql, result);
        }
        return result;
    }

   其中

LexerEngine

  里面根据数据库类型获取出来是一个列表

"TRIM" -> {DefaultKeyword@12645} "TRIM"
"MINUS" -> {DefaultKeyword@12647} "MINUS"
"EXPLAIN" -> {DefaultKeyword@12649} "EXPLAIN"
"PARTITION" -> {MySQLKeyword@12651} "PARTITION"
"STORAGE" -> {DefaultKeyword@12653} "STORAGE"
"LEFT" -> {DefaultKeyword@12655} "LEFT"
"RESTRICT" -> {MySQLKeyword@12657} "RESTRICT"
"WHERE" -> {DefaultKeyword@12659} "WHERE"
"AS" -> {DefaultKeyword@12661} "AS"
"DATABASE" -> {DefaultKeyword@12663} "DATABASE"
"ADVISE" -> {DefaultKeyword@12665} "ADVISE"
"XOR" -> {DefaultKeyword@12667} "XOR"
"ALTER" -> {DefaultKeyword@12669} "ALTER"
"SET" -> {DefaultKeyword@12671} "SET"
"CONSTRAINT" -> {DefaultKeyword@12673} "CONSTRAINT"
"PRECISION" -> {DefaultKeyword@12675} "PRECISION"
"ROLE" -> {DefaultKeyword@12677} "ROLE"
"UPPER" -> {DefaultKeyword@12679} "UPPER"
"REPAIR" -> {MySQLKeyword@12681} "REPAIR"
"BY" -> {DefaultKeyword@12683} "BY"
"STRAIGHT_JOIN" -> {MySQLKeyword@12685} "STRAIGHT_JOIN"
"CHARACTER" -> {DefaultKeyword@12687} "CHARACTER"
"INSTEAD" -> {DefaultKeyword@12689} "INSTEAD"
"DUAL" -> {MySQLKeyword@12691} "DUAL"
"INTERVAL" -> {DefaultKeyword@12693} "INTERVAL"
"ENGINE" -> {MySQLKeyword@12695} "ENGINE"
"CONNECTION" -> {MySQLKeyword@12697} "CONNECTION"
"COMPACT" -> {MySQLKeyword@12699} "COMPACT"
"TRUNCATE" -> {DefaultKeyword@12701} "TRUNCATE"
"CURSOR" -> {DefaultKeyword@12703} "CURSOR"
"STATEMENT" -> {DefaultKeyword@12705} "STATEMENT"
"ADD" -> {DefaultKeyword@12707} "ADD"
"LPAD" -> {DefaultKeyword@12709} "LPAD"
"DO" -> {DefaultKeyword@12711} "DO"
"LOW_PRIORITY" -> {MySQLKeyword@12713} "LOW_PRIORITY"
"INDEX" -> {DefaultKeyword@12715} "INDEX"
"VARYING" -> {DefaultKeyword@12717} "VARYING"
"DISABLE" -> {DefaultKeyword@12719} "DISABLE"
"SQL_BIG_RESULT" -> {MySQLKeyword@12721} "SQL_BIG_RESULT"
"FOR" -> {DefaultKeyword@12723} "FOR"
"FIXED" -> {MySQLKeyword@12725} "FIXED"
"ITERATE" -> {DefaultKeyword@12727} "ITERATE"
"CACHE" -> {MySQLKeyword@12729} "CACHE"
"USING" -> {DefaultKeyword@12731} "USING"
"END" -> {DefaultKeyword@12733} "END"
"REBUILD" -> {MySQLKeyword@12735} "REBUILD"
"SQL_BUFFER_RESULT" -> {MySQLKeyword@12737} "SQL_BUFFER_RESULT"
"BINARY" -> {MySQLKeyword@12739} "BINARY"
"SOME" -> {DefaultKeyword@12741} "SOME"
"KEY_BLOCK_SIZE" -> {MySQLKeyword@12743} "KEY_BLOCK_SIZE"
"SCHEMA" -> {DefaultKeyword@12745} "SCHEMA"
"SEQUENCE" -> {DefaultKeyword@12747} "SEQUENCE"
"OUTER" -> {DefaultKeyword@12749} "OUTER"
"COLUMNS" -> {MySQLKeyword@12751} "COLUMNS"
"RENAME" -> {DefaultKeyword@12753} "RENAME"
"IDENTIFIED" -> {DefaultKeyword@12755} "IDENTIFIED"
"COMPRESSION" -> {MySQLKeyword@12757} "COMPRESSION"
"INTERSECT" -> {DefaultKeyword@12759} "INTERSECT"
"WITH" -> {DefaultKeyword@12761} "WITH"
"OVER" -> {DefaultKeyword@12763} "OVER"
"GRANT" -> {DefaultKeyword@12765} "GRANT"
"REDUNDANT" -> {MySQLKeyword@12767} "REDUNDANT"
"UPGRADE" -> {MySQLKeyword@12769} "UPGRADE"
"VALIDATE" -> {DefaultKeyword@12771} "VALIDATE"
"CHAR_LENGTH" -> {DefaultKeyword@12773} "CHAR_LENGTH"
"DEFAULT" -> {DefaultKeyword@12775} "DEFAULT"
"JOIN" -> {DefaultKeyword@12777} "JOIN"
"LOCK" -> {DefaultKeyword@12779} "LOCK"
"AVG_ROW_LENGTH" -> {MySQLKeyword@12781} "AVG_ROW_LENGTH"
"ELSE" -> {DefaultKeyword@12783} "ELSE"
"MIN_ROWS" -> {MySQLKeyword@12785} "MIN_ROWS"
"IF" -> {DefaultKeyword@12787} "IF"
"FIELDS" -> {MySQLKeyword@12789} "FIELDS"
"IN" -> {DefaultKeyword@12791} "IN"
"DISTINCT" -> {DefaultKeyword@12793} "DISTINCT"
"BTREE" -> {MySQLKeyword@12795} "BTREE"
"IS" -> {DefaultKeyword@12797} "IS"
"HASH" -> {MySQLKeyword@12799} "HASH"
"FORCE" -> {MySQLKeyword@12801} "FORCE"
"ENCRYPTION" -> {MySQLKeyword@12803} "ENCRYPTION"
"MAX" -> {DefaultKeyword@12805} "MAX"
"CASCADE" -> {DefaultKeyword@12807} "CASCADE"
"TRANSACTION" -> {DefaultKeyword@12809} "TRANSACTION"
"TRUNC" -> {DefaultKeyword@12811} "TRUNC"
"IGNORE" -> {MySQLKeyword@12813} "IGNORE"
"ALWAYS" -> {MySQLKeyword@12815} "ALWAYS"
"OFF" -> {DefaultKeyword@12817} "OFF"
"COMMENT" -> {DefaultKeyword@12819} "COMMENT"
"QUICK" -> {MySQLKeyword@12821} "QUICK"
"DUPLICATE" -> {DefaultKeyword@12823} "DUPLICATE"
"RIGHT" -> {DefaultKeyword@12825} "RIGHT"
"UPDATE" -> {DefaultKeyword@12827} "UPDATE"
"COMPILE" -> {DefaultKeyword@12829} "COMPILE"
"FETCH" -> {DefaultKeyword@12831} "FETCH"
"NUMERIC" -> {DefaultKeyword@12833} "NUMERIC"
"REVOKE" -> {DefaultKeyword@12835} "REVOKE"
"DISTINCTROW" -> {MySQLKeyword@12837} "DISTINCTROW"
"COMPRESSED" -> {MySQLKeyword@12839} "COMPRESSED"
"USE" -> {DefaultKeyword@12841} "USE"
"FIRST" -> {MySQLKeyword@12843} "FIRST"
"SELECT" -> {DefaultKeyword@12948} "SELECT"
"TABLES" -> {MySQLKeyword@12950} "TABLES"
"DYNAMIC" -> {MySQLKeyword@12952} "DYNAMIC"
"GREATEST" -> {DefaultKeyword@12954} "GREATEST"
"NEW" -> {DefaultKeyword@12956} "NEW"
"ALL" -> {DefaultKeyword@12958} "ALL"
"COLUMN" -> {DefaultKeyword@12960} "COLUMN"
"DECIMAL" -> {DefaultKeyword@12962} "DECIMAL"
"VALUE" -> {MySQLKeyword@12964} "VALUE"
"AUTO_INCREMENT" -> {MySQLKeyword@12966} "AUTO_INCREMENT"
"ROW_FORMAT" -> {MySQLKeyword@12968} "ROW_FORMAT"
"COALESCE" -> {DefaultKeyword@12970} "COALESCE"
"STATUS" -> {MySQLKeyword@12972} "STATUS"
"DESCRIBE" -> {MySQLKeyword@12974} "DESCRIBE"
"SUBSTR" -> {DefaultKeyword@12976} "SUBSTR"
"NULL" -> {DefaultKeyword@12978} "NULL"
"TRUE" -> {DefaultKeyword@12980} "TRUE"
"TABLESPACE" -> {DefaultKeyword@12982} "TABLESPACE"
"PASSWORD" -> {DefaultKeyword@12984} "PASSWORD"
"AND" -> {DefaultKeyword@12986} "AND"
"REAL" -> {DefaultKeyword@12988} "REAL"
"ROW" -> {DefaultKeyword@12990} "ROW"
"DISK" -> {MySQLKeyword@12992} "DISK"
"NO" -> {DefaultKeyword@12994} "NO"
"FLOAT" -> {DefaultKeyword@12996} "FLOAT"
"ROUND" -> {DefaultKeyword@12998} "ROUND"
"ANY" -> {DefaultKeyword@13000} "ANY"
"ROLLBACK" -> {DefaultKeyword@13002} "ROLLBACK"
"NATURAL" -> {DefaultKeyword@13004} "NATURAL"
"OF" -> {DefaultKeyword@13006} "OF"
"KEYS" -> {MySQLKeyword@13008} "KEYS"
"CHANGE" -> {MySQLKeyword@13010} "CHANGE"
"ON" -> {DefaultKeyword@13012} "ON"
"OR" -> {DefaultKeyword@13014} "OR"
"BITMAP" -> {DefaultKeyword@13016} "BITMAP"
"PRIMARY" -> {DefaultKeyword@13018} "PRIMARY"
"TRANSLATE" -> {DefaultKeyword@13020} "TRANSLATE"
"REFERENCES" -> {DefaultKeyword@13022} "REFERENCES"
"SPATIAL" -> {MySQLKeyword@13024} "SPATIAL"
"CHECKSUM" -> {MySQLKeyword@13026} "CHECKSUM"
"CREATE" -> {DefaultKeyword@13028} "CREATE"
"TRIGGER" -> {DefaultKeyword@13030} "TRIGGER"
"LEAST" -> {DefaultKeyword@13032} "LEAST"
"AFTER" -> {DefaultKeyword@13034} "AFTER"
"BETWEEN" -> {DefaultKeyword@13036} "BETWEEN"
"CLOSE" -> {DefaultKeyword@13038} "CLOSE"
"CONVERT" -> {DefaultKeyword@13040} "CONVERT"
"POSITION" -> {DefaultKeyword@13042} "POSITION"
"DENY" -> {DefaultKeyword@13044} "DENY"
"DISCARD" -> {MySQLKeyword@13046} "DISCARD"
"DECODE" -> {DefaultKeyword@13048} "DECODE"
"INNER" -> {DefaultKeyword@13050} "INNER"
"EACH" -> {DefaultKeyword@13052} "EACH"
"VALIDATION" -> {MySQLKeyword@13054} "VALIDATION"
"SUM" -> {DefaultKeyword@13056} "SUM"
"MIN" -> {DefaultKeyword@13058} "MIN"
"FULLTEXT" -> {DefaultKeyword@13060} "FULLTEXT"
"VARCHAR" -> {DefaultKeyword@13062} "VARCHAR"
"LTRIM" -> {DefaultKeyword@13064} "LTRIM"
"THEN" -> {DefaultKeyword@13066} "THEN"
"KEY" -> {DefaultKeyword@13068} "KEY"
"INTO" -> {DefaultKeyword@13070} "INTO"
"REPEAT" -> {DefaultKeyword@13072} "REPEAT"
"DELAY_KEY_WRITE" -> {MySQLKeyword@13074} "DELAY_KEY_WRITE"
"INITCAP" -> {DefaultKeyword@13076} "INITCAP"
"ASC" -> {DefaultKeyword@13078} "ASC"
"GROUP" -> {DefaultKeyword@13080} "GROUP"
"DELETE" -> {DefaultKeyword@13082} "DELETE"
"MEMORY" -> {MySQLKeyword@13084} "MEMORY"
"TEMPORARY" -> {DefaultKeyword@13086} "TEMPORARY"
"OFFSET" -> {MySQLKeyword@13088} "OFFSET"
"GENERATED" -> {MySQLKeyword@13090} "GENERATED"
"PROCEDURE" -> {DefaultKeyword@13092} "PROCEDURE"
"IMPLICIT_TRANSACTIONS" -> {DefaultKeyword@13094} "IMPLICIT_TRANSACTIONS"
"ANALYZE" -> {MySQLKeyword@13096} "ANALYZE"
"OPEN" -> {DefaultKeyword@13098} "OPEN"
"TO" -> {DefaultKeyword@13100} "TO"
"UNION" -> {DefaultKeyword@13102} "UNION"
"INDEXES" -> {MySQLKeyword@13104} "INDEXES"
"LOOP" -> {DefaultKeyword@13106} "LOOP"
"HIGH_PRIORITY" -> {MySQLKeyword@13108} "HIGH_PRIORITY"
"VIEW" -> {DefaultKeyword@13110} "VIEW"
"DESC" -> {DefaultKeyword@13112} "DESC"
"REVERSE" -> {DefaultKeyword@13114} "REVERSE"
"REPLACE" -> {MySQLKeyword@13116} "REPLACE"
"INTEGER" -> {DefaultKeyword@13118} "INTEGER"
"UNIQUE" -> {DefaultKeyword@13120} "UNIQUE"
"TRAILING" -> {DefaultKeyword@13122} "TRAILING"
"DATABASES" -> {MySQLKeyword@13124} "DATABASES"
"FULL" -> {DefaultKeyword@13126} "FULL"
"BOOLEAN" -> {DefaultKeyword@13128} "BOOLEAN"
"NOT" -> {DefaultKeyword@13130} "NOT"
"AVG" -> {DefaultKeyword@13132} "AVG"
"LOWER" -> {DefaultKeyword@13134} "LOWER"
"AUTOCOMMIT" -> {DefaultKeyword@13136} "AUTOCOMMIT"
"HAVING" -> {DefaultKeyword@13138} "HAVING"
"RPAD" -> {DefaultKeyword@13140} "RPAD"
"STATS_SAMPLE_PAGES" -> {MySQLKeyword@13142} "STATS_SAMPLE_PAGES"
"DROP" -> {DefaultKeyword@13144} "DROP"
"REGEXP" -> {MySQLKeyword@13146} "REGEXP"
"FOREIGN" -> {DefaultKeyword@13225} "FOREIGN"
"SQL_SMALL_RESULT" -> {MySQLKeyword@13227} "SQL_SMALL_RESULT"
"LEAVE" -> {DefaultKeyword@13229} "LEAVE"
"GLOBAL" -> {DefaultKeyword@13231} "GLOBAL"
"SHOW" -> {MySQLKeyword@13233} "SHOW"
"EXISTS" -> {DefaultKeyword@13235} "EXISTS"
"INSTR" -> {DefaultKeyword@13237} "INSTR"
"TIME" -> {DefaultKeyword@13239} "TIME"
"ESCAPE" -> {DefaultKeyword@13241} "ESCAPE"
"DIRECTORY" -> {MySQLKeyword@13243} "DIRECTORY"
"FALSE" -> {DefaultKeyword@13245} "FALSE"
"TABLE" -> {DefaultKeyword@13247} "TABLE"
"WHEN" -> {DefaultKeyword@13249} "WHEN"
"LOCAL" -> {DefaultKeyword@13251} "LOCAL"
"LOGIN" -> {DefaultKeyword@13253} "LOGIN"
"EXCHANGE" -> {MySQLKeyword@13255} "EXCHANGE"
"CAST" -> {DefaultKeyword@13257} "CAST"
"SQL_CACHE" -> {MySQLKeyword@13259} "SQL_CACHE"
"INSERT_METHOD" -> {MySQLKeyword@13261} "INSERT_METHOD"
"VARCHAR2" -> {DefaultKeyword@13263} "VARCHAR2"
"FUNCTION" -> {DefaultKeyword@13265} "FUNCTION"
"LEADING" -> {DefaultKeyword@13267} "LEADING"
"CASE" -> {DefaultKeyword@13269} "CASE"
"OUT" -> {DefaultKeyword@13271} "OUT"
"OPTIMIZE" -> {MySQLKeyword@13273} "OPTIMIZE"
"WITHOUT" -> {DefaultKeyword@13275} "WITHOUT"
"CHECK" -> {DefaultKeyword@13277} "CHECK"
"COUNT" -> {DefaultKeyword@13279} "COUNT"
"NOSORT" -> {DefaultKeyword@13281} "NOSORT"
"LENGTH" -> {DefaultKeyword@13283} "LENGTH"
"CHAR" -> {DefaultKeyword@13285} "CHAR"
"BEGIN" -> {DefaultKeyword@13287} "BEGIN"
"MAX_ROWS" -> {MySQLKeyword@13289} "MAX_ROWS"
"ORDER" -> {DefaultKeyword@13291} "ORDER"
"IMPORT" -> {MySQLKeyword@13293} "IMPORT"
"SQL_CALC_FOUND_ROWS" -> {MySQLKeyword@13295} "SQL_CALC_FOUND_ROWS"
"REMOVE" -> {MySQLKeyword@13297} "REMOVE"
"STATS_AUTO_RECALC" -> {MySQLKeyword@13299} "STATS_AUTO_RECALC"
"VALUES" -> {DefaultKeyword@13301} "VALUES"
"DOUBLE" -> {DefaultKeyword@13303} "DOUBLE"
"MODIFY" -> {DefaultKeyword@13305} "MODIFY"
"COLLATE" -> {MySQLKeyword@13307} "COLLATE"
"SQL_NO_CACHE" -> {MySQLKeyword@13309} "SQL_NO_CACHE"
"EXECUTE" -> {DefaultKeyword@13311} "EXECUTE"
"PACK_KEYS" -> {MySQLKeyword@13313} "PACK_KEYS"
"KILL" -> {MySQLKeyword@13315} "KILL"
"BLOB" -> {DefaultKeyword@13317} "BLOB"
"NVL" -> {DefaultKeyword@13319} "NVL"
"SUBSTRING" -> {DefaultKeyword@13321} "SUBSTRING"
"FROM" -> {DefaultKeyword@13323} "FROM"
"BOTH" -> {DefaultKeyword@13325} "BOTH"
"ENABLE" -> {DefaultKeyword@13327} "ENABLE"
"EXCEPT" -> {DefaultKeyword@13329} "EXCEPT"
"DATE" -> {DefaultKeyword@13331} "DATE"
"ROLLUP" -> {MySQLKeyword@13333} "ROLLUP"
"LIKE" -> {DefaultKeyword@13335} "LIKE"
"DATA" -> {DefaultKeyword@13337} "DATA"
"INSERT" -> {DefaultKeyword@13339} "INSERT"
"INOUT" -> {DefaultKeyword@13341} "INOUT"
"REORGANIZE" -> {MySQLKeyword@13343} "REORGANIZE"
"ALGORITHM" -> {MySQLKeyword@13345} "ALGORITHM"
"LIMIT" -> {MySQLKeyword@13347} "LIMIT"
"INT" -> {DefaultKeyword@13349} "INT"
"STATS_PERSISTENT" -> {MySQLKeyword@13351} "STATS_PERSISTENT"
"RTRIM" -> {DefaultKeyword@13353} "RTRIM"
"DEC" -> {DefaultKeyword@13355} "DEC"
"COMMIT" -> {DefaultKeyword@13357} "COMMIT"
"USER" -> {DefaultKeyword@13359} "USER"
"SAVEPOINT" -> {DefaultKeyword@13361} "SAVEPOINT"
"UNTIL" -> {DefaultKeyword@13363} "UNTIL"
"BEFORE" -> {DefaultKeyword@13365} "BEFORE"
"TEMP" -> {DefaultKeyword@13367} "TEMP"
"DECLARE" -> {DefaultKeyword@13369} "DECLARE"
"CROSS" -> {DefaultKeyword@13371} "CROSS"
"SMALLINT" -> {DefaultKeyword@13373} "SMALLINT"
"WHILE" -> {DefaultKeyword@13375} "WHILE"

不巧的是在下的SQL中取了 一个别名叫user和他列表项重合了。导致它解析SQL时分解的不正确。 对就是这样，换个别名就好了！！好坑，官网也没有任何提示。

愿后人不要在此问题上卡一天。