sql中根据某个条件判断是否groupby_通过错误的SQL来测试推理SQL的解析过程

这是学习笔记的第 1977 篇文章

如果抛出一个问题，你是如何理解 MySQL 解析器的，它和 Oracle 解析器有什么差别？相信大多数同学都会比较迷茫，因为这个问题很难验证，要不是看源码，要不就是查看书上是怎么说的，其实这两种方法对我们去理解这个问题来说不是很合适，如果能够通过实践来做下理解就好了。

我们可以对问题做一下细化，对一条简单的SQL语句，整体上来说，解析流程如下所示：

整个流程上，我们做一些相关解释：

①　对SQL的文法检查，查看是否有文法错误，比如from、select拼写错误等。

②　在数据字典里校验SQL涉及的对象是否存在。

③　将对象进行名称转换，比如同义词转义成对应的对象或者select * from test t，其中t是一个同义词指向hr.test。

④　检查语句的用户是否具有访问对象的权限。

⑤　生成执行计划。

如何通过测试来验证呢，我们可以试一下以毒攻毒，即用错误的的SQL来推理SQL的解析过程，我们先来看一下在MySQL侧的解析情况。

1)MySQL解析器简单测试

如何在MySQL中进行验证呢， 我们可以在MySQL中创建表test：

使用如下的语句：

Create table test (id int,name varchar(30));

如果我们提交一个语法错误的SQL,在解析方面MySQL提供的信息非常有限，

mysql> select1 id3 from2 test1 where3 id2='aaa' group by4 id1 order by5 id0;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select1 id3 from2 test1 where3 id2='aaa' group by4 id1 order by5 id0' at line 1

这种错误没有下标，也没有明确的错误提示，是难以去理解语法解析的过程的，在对象和权限的解析方面，MySQL的解析方式也相对比较单一，即从左到右。

比如我们需要验证select子句，group by 子句，order by子句的解析顺序。

可以使用下面的语句：

mysql> select id1 from test where id=100 group by id2 order by id3;

ERROR 1054 (42S22): Unknown column 'id1' in 'field list'

错误在select子句，修复select子句，继续测试。

mysql> select id from test where id=100 group by id2 order by id3; 

ERROR 1054 (42S22): Unknown column 'id2' in 'group statement'

错误在group by子句，修复group by 子句，继续测试。

mysql> select id from test where id=100 group by id order by id3; 

ERROR 1054 (42S22): Unknown column 'id3' in 'order clause'

错误在order by子句

在此，我们需要明确的是，以上对于SQL语句测试，仅仅是简单测试了解析的过程，如果包含limit子句，整个SQL中是如下的顺序来执行的，和解析的方式是有较大的差异的。

1) FROM子句

2) WHERE子句

3) GROUP BY子句

4) HAVING子句

5) ORDER BY子句

6) SELECT子句

7) LIMIT子句

8) 最终结果

为什么解析顺序和执行顺序差别很大呢，归根节点，两种方式的差异总结来说，解析是在做SQL文本的解析，而运行则是在解析的基础上做数据的提取，一个是WHAT(是什么)的思维，一个是HOW(怎么做)的思维。

如果想要做一些较为完整的测试，该怎么办呢，我们可以借鉴Oracle的实现，有的同学可能会想测试Oracle部署环境还是比较麻烦的，其实可以走快捷通道，即Oracle官方提供的在线测试入口：https://livesql.oracle.com，所有的测试和操作都可以在线完成，完全不需要再部署环境了，我们以Oracle 19c的在线环境做测试，来验证下Oracle的解析器实现，加深我们对于SQL解析过程的理解。

2)Oracle解析器简单测试

首先准备一个测试表，如下。

create table test (id number,name varchar2(30));

准备好之后，开始测试。重申一下，思路是用有问题的语句来测试和推理。

下面的语句存在很多的问题，来看看Oracle的反应。

select1 id3 from2 test1 where3 id2='aaa' group by4 id1 order by5 id0;

ORA-20001: Query must begin with SELECT or WITH

解析发现select的语句错误，其实后面的from、where、group by、order by都有错误。但是首先发现的是select的部分，可见解析还是从左至右的方向来做文法解析。

开始修复select的文法错误。

select id1 from2 test1 where3 id1='aaa' group by4 id1 order  by5 id1;

ORA-00923: FROM keyword not found where expected

这个时候错误指向了FROM,进一步论证解析的顺序，我们修复from的错误，开始继续验证。

select id3 from test1 where3 id2='aaa' group by4 id1 order by5 id0;

ORA-00933: SQL command not properly ended

这个时候错误指向了where3，原因在于解析器把where3当作了表test1的别名，修复where的文法错误如下。

select id3 from test1 where id2='aaa' group by4 id1 order by5 id0;

ORA-00924: missing BY keyword

而这次又直接指向了group by的部分。通过这三次错误指向，更能断定文法解析是从左至右。对于是否存在表，是否字段存在问题都不会解析。

如下，修复了group by、order by的文法错误。

select id3 from test1 where id2='aaa' group by id1 order by id0;

ORA-00942: table or view does not exist

这时发现错误指向了test1，提示没有这个表。可见在文法解析之后只是开始校验是否存在这个表，还没有开始校验字段的情况。

修复了表名的错误，看看报错信息。

select id3 from test where id2='aaa' group by id1 order by id0;

ORA-00904: "ID0": invalid identifier

发现是在解析order by 的字段名，对于select、where、group by中的先不解析。

对于不同的Oracle版本，这里的输出结果是不一样的，在11g中是group by，order by的解析顺序，这里仅供参考。

我们修复order by中的错误，继续查看。

select id3 from test where id2='aaa' group by id1 order by id;

ORA-00904: "ID1": invalid identifier

这个时候解析到了group by 子句中的字段值。但是select,where中还没有开始解析。

修复group by 子句中的问题，继续测试。

select id3 from test where id2='aaa' group by id order by id;   

ORA-00904: "ID2": invalid identifier

错误指向了where子句，这个时候就剩下了select的部分,修复where的部分。

select id3 from test where id='aaa' group by id order by id;

ORA-00904: "ID3": invalid identifier

通过上面的错误测试，基本能够得到语句解析中的处理顺序，但是这里需要明确的是SQL的解析顺序和SQL数据处理的顺序是不一样的，仅仅作为一种参考的思路，我么来间接验证一下。

比如我们更进一步，查看如果字段ID为number，赋予varchar2的数据，是否会在解析的时候校验出来。

select id from test where id='aaa' group by id order  by id;

no rows selected

  从错误来看，目前还没有到执行阶段，是没有办法做出判断的。

而如果我们对表写入数据，再来看看效果。

select id from test where id='aaa' group by id order  by id;

ORA-01722: invalid number

此时会发现错误已经在校验数据的类型了。

当然在这些场景之外，我们还可以测试索引、统计信息的一些场景，限于篇幅就不扩展了，大家可以自行测试。