嵌套子查询和join查询报错

[zhonyue314]

Question

表结构 CREATE TABLE link_user_calss ( id int(11) NOT NULL, class_id int(11) DEFAULT NULL, t_id int(11) DEFAULT NULL, class_honor varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sql 语句 SELECT * FROM ( SELECT luc.cid, luc.classHonor, b.t_id AS tId FROM ( SELECT class_id AS cid, GROUP_CONCAT(class_honor ORDER BY class_honor SEPARATOR ';') AS classHonor FROM link_user_calss GROUP BY class_id ) luc JOIN link_user_calss b ON b.class_id = luc.cid GROUP BY luc.cid, luc.classHonor, b.t_id ) w LEFT JOIN link_user_calss lw ON w.tId = lw.t_id 报错 Caused by: java.lang.RuntimeException: com.alibaba.druid.sql.parser.ParserException: parse error. detail message is : Relation condition column must in select columns. source sql is : SELECT * FROM ( SELECT luc.cid, luc.classHonor, b.t_id AS tId FROM ( SELECT class_id AS cid, GROUP_CONCAT(class_honor ORDER BY class_honor SEPARATOR ';') AS classHonor FROM link_user_calss GROUP BY class_id ) luc JOIN link_user_calss b ON b.class_id = luc.cid GROUP BY luc.cid, luc.classHonor, b.t_id ) w LEFT JOIN link_user_calss lw ON w.tId = lw.t_id at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.init(ESAdapter.java:91) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na] at com.alibaba.otter.canal.client.adapter.es7x.ES7xAdapter.init(ES7xAdapter.java:50) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na] ... 42 common frames omitted

[zhonyue314]

public Map<FieldItem, List<FieldItem>> getRelationTableFields() { if (relationTableFields == null) { synchronized (SchemaItem.class) { if (relationTableFields == null) { relationTableFields = new LinkedHashMap<>();

                    getRelationFields().forEach(relationFieldsPair -> {
                        FieldItem leftFieldItem = relationFieldsPair.getLeftFieldItem();
                        FieldItem rightFieldItem = relationFieldsPair.getRightFieldItem();
                        FieldItem currentTableRelField = null;
                        if (getAlias().equals(leftFieldItem.getOwner())) {
                            currentTableRelField = leftFieldItem;
                        } else if (getAlias().equals(rightFieldItem.getOwner())) {
                            currentTableRelField = rightFieldItem;
                        }

                        if (currentTableRelField != null) {
                            List<FieldItem> selectFieldItem = getSchemaItem().getColumnFields()
                                .get(leftFieldItem.getOwner() + "." + leftFieldItem.getColumn().getColumnName());
                            if (selectFieldItem != null && !selectFieldItem.isEmpty()) {
                                relationTableFields.put(currentTableRelField, selectFieldItem);
                            } else {
                                selectFieldItem = getSchemaItem().getColumnFields()
                                    .get(rightFieldItem.getOwner() + "."
                                         + rightFieldItem.getColumn().getColumnName());
                                if (selectFieldItem != null && !selectFieldItem.isEmpty()) {
                                    relationTableFields.put(currentTableRelField, selectFieldItem);
                                } else {
                                    throw new UnsupportedOperationException(
                                        "Relation condition column must in select columns.");
                                }
                            }
                        }
                    });
                }
            }
        }
        return relationTableFields;
    }

拿着最外层的select字段取最里面一层检查on 条件，真尼玛人才

[agapple]

可以提交一个修复MR