OceanBase 大赛测试答疑帖 - 2021/11/01 晚上

社区活动 OceanBase 数据库大赛
#22 
SELECT * FROM JOINTABLELARGE1 INNER JOIN JOINTABLELARGE2 ON JOINTABLELARGE1.ID=JOINTABLELARGE2.ID INNER JOIN JOINTABLELARGE3 ON JOINTABLELARGE1.ID=JOINTABLELARGE3.ID INNER JOIN JOINTABLELARGE4 ON JOINTABLERGE33.ID=JOINBLELARGE4.ID INNER JOIN JOINTABLELARGE5 ON 1=1 INNER JOIN JOINTABLELARGE6 ON JOINTABLELARGE5.ID=JOINTABLELARGE6.ID WHERE JOINTABLELARGE3.NUM3 <10 AND JOINTABLELARGE_5.NUM5>90;
#24 
SELECT * FROM JOIN_TABLE_1 INNER JOIN JOIN_TABLE_2 ON JOIN_TABLE_1.ID=JOIN_TABLE_2.ID AND JOIN_TABLE_2.NUM>13 WHERE JOIN_TABLE_1.NAME='B';
FAILURE
#25

还是炸了，还是钉钉或者图片吧.........


#26

可以看看group by和查询条件支持表达式的问题吗,

https://github.com/lhcmaple/miniob

#27 
SELECT * FROM DATE_TABLE WHERE U_DATE>'2020-1-20';
3 |
COUNT(ID) | U_DATE
SELECT * FROM DATE_TABLE WHERE U_DATE<'2019-12-31';
5 | 2019-12-21
6 | 2016-02-29
7 | 1970-01-01
8 | 2000-01-01
ID | U_DATE
SELECT * FROM DATE_TABLE WHERE U_DATE='2020-1-1';
2 |
COUNT(ID) | U_DATE
#28

只要有非法的表、字段、或者错误的日期，就可以返回FAILURE

#29 
数据返回不正确
SELECT * FROM SELECT_TABLES_1,SELECT_TABLES_2,SELECT_TABLES_3 WHERE SELECT_TABLES_1.U_NAME=SELECT_TABLES_2.U_NAME AND SELECT_TABLES_2.U_NAME=SELECT_TABLES_3.U_NAME;
1 | 18 | A | 1 | 20 | A | 1 | 35 | A
1 | 18 | A | 1 | 20 | A | 2 | 37 | A
1 | 18 | A | 2 | 21 | C | 1 | 35 | A
1 | 18 | A | 2 | 21 | C | 2 | 37 | A
2 | 15 | B | 1 | 20 | A | 1 | 35 | A
2 | 15 | B | 1 | 20 | A | 2 | 37 | A
2 | 15 | B | 2 | 21 | C | 1 | 35 | A
2 | 15 | B | 2 | 21 | C | 2 | 37 | A
SELECT_TABLES_1.ID | SELECT_TABLES_1.AGE | SELECT_TABLES_1.U_NAME | SELECT_TABLES_2.ID | SELECT_TABLES_2.AGE | SELECT_TABLES_2.U_NAME | SELECT_TABLES_3.ID | SELECT_TABLES_3.RES | SELECT_TABLES_3.U_NAME
#30

数据展示出来很骚气

SELECT T_GROUP_BY.ID, T_GROUP_BY.NAME, AVG(T_GROUP_BY.SCORE), AVG(T_GROUP_BY_2.AGE) FROM T_GROUP_BY, T_GROUP_BY_2 WHERE T_GROUP_BY.ID=T_GROUP_BY_2.ID GROUP BY T_GROUP_BY.ID, T_GROUP_BY.NAME;
1 | B | 2 | 10
3 | A | 1 | 23.33
3 | C | 3 | 23.33
3 | D | 3 | 23.33
3 | F | 2 | 23.33
4 | C | 3 | 20
T_GROUP_BY.ID | T_GROUP_BY.NAME | T_GROUP_BY.AVG(SCORE) | T_GROUP_BY_2.AVG(AGE)
#31

啊，我这是表头在最下面？（笑）2333

#32

运来哥，我想问一下DBChat-OB的多表查询和Date哪个地方挂了啊

#33

https://github.com/WangAShao/miniob 麻烦来哥看一下多列索引和text超长字段是哪个case挂了

#34

哥，麻烦看看group by的问题，以及突然发现，date又不对了，但本地测试都还是通的 https://github.com/obcontest/miniob

#35

老师能帮忙看下我们的join那里没过吗?    非常感谢   https://github.com/dongzeyu/miniob/

#36

来哥，可以帮忙看一下JameyWoo的null和drop table有啥问题吗

地址： https://github.com/JameyWoo/miniob

#37

回退了以后，自动测试commit id对不上，出现了一个没有id。

#38 
group by

SELECT NAME, MIN(ID), MAX(SCORE) FROM T_GROUP_BY GROUP BY NAME;
1 | B | 2
3 | A | 1
3 | C | 4
3 | D | 3
3 | F | 2
MIN(ID) | NAME | MAX(SCORE)

SELECT ID,-(COL2*(-1)+1)+(COL4+2)*(COL1+COL3*2),(4+COL2)*COL3/2 FROM EXP_TABLE WHERE -(COL2*(-1)+1)+(COL4+2)*(COL1+COL3*2) > (4+COL2)*COL3/2;
FAILURE


#39

不是的，是你的列顺序输出反了。

表头放下面，是我们测试程序干的

#40 
SELECT SELECT_TABLES_2.AGE FROM SELECT_TABLES_1,SELECT_TABLES_2 WHERE SELECT_TABLES_1.AGE<18 AND SELECT_TABLES_2.U_NAME='C' AND SELECT_TABLES_1.ID=SELECT_TABLES_2.ID;
21
AGE --> 多表查询要带表名字，应该是   SELECT_TABLES_2.AGE
#41 
date 少了一条数据
SELECT * FROM DATE_TABLE WHERE U_DATE<'2019-12-31';
5 | 2019-12-21
6 | 2016-02-29
8 | 2000-01-01
ID | U_DATE

s 少的数据是 7 | 1970-01-01
#42 
    多列索引少一条数据
SELECT * FROM MULTI_INDEX3 WHERE COL2 < 15.0 AND COL4 <> '2021-01-02';
3 | 1 | 11.6 | H | 2023-01-02 | 10 | 17
4 | 2 | 12.2 | E | 2022-01-04 | 13 | 10
ID | COL1 | COL2 | COL3 | COL4 | COL5 | COL6

 少的数据是 5 | 3 | 14.2 | D | 2020-04-02 | 12 | 2

 还有 
UPDATE MULTI_INDEX3 SET COL1=2 WHERE ID=2;
FAILURE

text的测试我改了，更简单了，等下次出现问题再说吧。
1 个赞