Fix some bugs in Doris implementation

[codenohup]

Hi all. Thanks to Sqlancer, I found a lot of logic bugs in Doris, some of them have been fixed by the Doris community. Now I found some unexpected bugs in the implementation of dors, see below:

1. CAST(0.19511769711971283 AS NULL) , cast a value or column to null
2. CREATE TABLE t0(c0 DATETIME NOT NULL DEFAULT NULL), assign the default value NULL to the non-nullable columns in the CREATE TABLE statement
3. When testing oracle, UNION ALL should be used instead of UNION in some cases
4. SELECT t0.c0 FROM t1, t0 GROUP BY t1.c0, select fields not in group by

This PR tries to fix them.

Thanks for your review and thanks again for the Sqlancer tool!

[codenohup]

To avoid misunderstanding, the original implementation of Doris (https://github.com/sqlancer/sqlancer/pull/758) is done by me, this is another Github account of mine.

[codenohup]

Thanks a lot for keeping working on the Doris implementation! It would be great to fix the CI tests as well. Could you please look into this if you have time?

Sorry, I don't have enough hardware environment to test this CI. I'll try to find someone in the Doris community to do this.

[codenohup]

Thanks a lot for keeping working on the Doris implementation! It would be great to fix the CI tests as well. Could you please look into this if you have time?

I have correct the code, can you help me to review again? Thanks.

[mrigger]

Thanks a lot for keeping working on the Doris implementation! It would be great to fix the CI tests as well. Could you please look into this if you have time?

Sorry, I don't have enough hardware environment to test this CI. I'll try to find someone in the Doris community to do this.

I think you could just do this by creating a PR and seeing if the CI passes with your changes? This would work best after merging this PR, since the CI tests are not automatically executed for new contributors.

[hello-stephen]

I tried this PR and found that no.4 issue(select fields not in group by) still exists， for example, SELECT t1.c1, t0.c0, t1.c0 FROM t1, t0 GROUP BY t1.c1, t1.c0; , SELECT t0.c1, t1.c0 FROM t0, t1 GROUP BY t0.c1, t0.c0;

maybe it needs to have a look again.

[codenohup]

I tried this PR and found that no.4 issue(select fields not in group by) still exists， for example, SELECT t1.c1, t0.c0, t1.c0 FROM t1, t0 GROUP BY t1.c1, t1.c0; , SELECT t0.c1, t1.c0 FROM t0, t1 GROUP BY t0.c1, t0.c0;

maybe it needs to have a look again.

Hey, I updated it again, you can try again to see if it works.

[mrigger]

Can you please fix the redundant important? The CI tests are currently failing because of that.

[hello-stephen]

I tried this PR and found that no.4 issue(select fields not in group by) still exists， for example, SELECT t1.c1, t0.c0, t1.c0 FROM t1, t0 GROUP BY t1.c1, t1.c0; , SELECT t0.c1, t1.c0 FROM t0, t1 GROUP BY t0.c1, t0.c0; maybe it needs to have a look again.

Hey, I updated it again, you can try again to see if it works.

1. SELECT SUM('-1680854620') FROM t0; SELECT SUM(CAST('' AS DATETIME) ) FROM t0; will meet Unexpected exception: sum requires a numeric or boolean parameter: sum('-1680854620') doris does not support this now, could you please modify it?

--java.lang.AssertionError: SELECT DISTINCT * FROM (SELECT DISTINCT t0.c1, t1.c0 FROM t1, t0 WHERE NULL UNION ALL SELECT DISTINCT t0.c1, t1.c0 FROM t1, t0 WHERE (NOT NULL) UNION ALL SELECT DISTINCT t0.c1, t1.c0 FROM t1, t0 WHERE ((NULL) IS NULL)) tmpTable; -- at sqlancer.common.query.SQLQueryAdapter.checkException(SQLQueryAdapter.java:111) -- at sqlancer.common.query.SQLQueryAdapter.executeAndGet(SQLQueryAdapter.java:141) -- at sqlancer.ComparatorHelper.getResultSetFirstColumnAsString(ComparatorHelper.java:55) -- at sqlancer.doris.oracle.tlp.DorisQueryPartitioningDistinctTester.check(DorisQueryPartitioningDistinctTester.java:40) -- at sqlancer.common.oracle.CompositeTestOracle.check(CompositeTestOracle.java:22) -- at sqlancer.ProviderAdapter.generateAndTestDatabase(ProviderAdapter.java:61) -- at sqlancer.Main$DBMSExecutor.run(Main.java:388) -- at sqlancer.Main$2.run(Main.java:583) -- at sqlancer.Main$2.runThread(Main.java:565) -- at sqlancer.Main$2.run(Main.java:556) -- at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) -- at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) -- at java.base/java.lang.Thread.run(Thread.java:829) --Caused by: java.sql.SQLException: errCode = 2, detailMessage = Unexpected exception: argument 1 requires boolean type, however 'NULL' is of null type -- at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) -- at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) -- at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) -- at sqlancer.common.query.SQLQueryAdapter.executeAndGet(SQLQueryAdapter.java:131) -- ... 11 more ---- Time: 2023/07/28 09:42:28 -- Database: database14 -- Database version: 5.7.99 -- seed value: 1690508512305 DROP DATABASE IF EXISTS database14; CREATE DATABASE database14; USE database14; CREATE TABLE t0(c0 DATEV2, c1 CHAR(89) DEFAULT '/e]3') DISTRIBUTED BY HASH (c0) BUCKETS 11 PROPERTIES ("replication_num" = "1"); CREATE TABLE t1(c0 BOOLEAN DEFAULT false) AGGREGATE KEY(c0) DISTRIBUTED BY HASH (c0) PROPERTIES ("replication_num" = "1"); CREATE TABLE t1(c0 INT DEFAULT -525669762) DISTRIBUTED BY HASH (c0) BUCKETS 18 PROPERTIES ("replication_num" = "1"); CREATE TABLE t1(c0 VARCHAR(89) DEFAULT '-525669762') DISTRIBUTED BY HASH (c0) BUCKETS 4 PROPERTIES ("replication_num" = "1"); INSERT INTO t0 (c1, c0) VALUES ('uFAU58', DATE '1971-01-11'); INSERT INTO t1 (c0) VALUES ('&E!f'); INSERT INTO t1 (c0) VALUES ('iH'); INSERT INTO t1 (c0) VALUES (DEFAULT); INSERT INTO t0 (c0) VALUES (DATE '1970-10-24'), (DATE '1970-03-15'); INSERT INTO t0 (c0) VALUES (DATE '1970-05-05'); INSERT INTO t1 (c0) VALUES ('DMO'), (''); INSERT INTO t0 (c1) VALUES (''); INSERT INTO t1 (c0) VALUES ('5hc{}-'); INSERT INTO t1 (c0) VALUES ('a>0'); INSERT INTO t0 (c0) VALUES (DATE '1970-04-27'); INSERT INTO t0 (c0, c1) VALUES (DATE '1970-07-23', ''); INSERT INTO t1 (c0) VALUES ('0.72896520762826'), ('ꡒwroZ'); INSERT INTO t0 (c1) VALUES (''); INSERT INTO t1 (c0) VALUES (DEFAULT), (DEFAULT); INSERT INTO t1 (c0) VALUES ('qSfD%~'); INSERT INTO t0 (c1, c0) VALUES ('P,', DATE '1970-11-29'); INSERT INTO t1 (c0) VALUES ('-1888442286'), ('g'); INSERT INTO t0 (c0) VALUES (DATE '1970-04-12'); INSERT INTO t1 (c0) VALUES (DEFAULT); INSERT INTO t1 (c0) VALUES ('倮M'), (DEFAULT);

here, meet error Unexpected exception: argument 1 requires boolean type, however 'NULL' is of null type but when I try to run the SQL manually, it just works, but I don't know why

[hello-stephen]

one more, I found this log generate by sqlancer,

--java.lang.AssertionError: SELECT SUM(count) FROM (SELECT CAST(CAST('p^M|-Ft2O' AS BOOLEAN)  IS NOT NULL AND CAST('p^M|-Ft2O' AS BOOLEAN)  AS INT) as count FROM  t0 RIGHT  OUTER JOIN t1 ON (((NOT ((-548572585)<<(1443052302))))and(((NULL) IS NOT NULL)))) as res;
--  at sqlancer.common.query.SQLQueryAdapter.checkException(SQLQueryAdapter.java:111)
--  at sqlancer.common.query.SQLQueryAdapter.executeAndGet(SQLQueryAdapter.java:141)
--  at sqlancer.common.query.Query.executeAndGetLogged(Query.java:51)
--  at sqlancer.doris.oracle.DorisNoRECOracle.getUnoptimizedQueryCount(DorisNoRECOracle.java:89)
--  at sqlancer.doris.oracle.DorisNoRECOracle.check(DorisNoRECOracle.java:61)
--  at sqlancer.common.oracle.CompositeTestOracle.check(CompositeTestOracle.java:22)
--  at sqlancer.ProviderAdapter.generateAndTestDatabase(ProviderAdapter.java:61)
--  at sqlancer.Main$DBMSExecutor.run(Main.java:388)
--  at sqlancer.Main$2.run(Main.java:583)
--  at sqlancer.Main$2.runThread(Main.java:565)
--  at sqlancer.Main$2.run(Main.java:556)
--  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
--  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
--  at java.base/java.lang.Thread.run(Thread.java:829)
--Caused by: java.sql.SQLException: ArrayIndexOutOfBoundsException, msg: 2
--  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
--  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
--  at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
--  at sqlancer.common.query.SQLQueryAdapter.executeAndGet(SQLQueryAdapter.java:131)
--  ... 12 more
---- Time: 2023/08/11 15:18:37
-- Database: database39
-- Database version: 5.7.99
-- seed value: 1691738082566
DROP DATABASE IF EXISTS database39;
CREATE DATABASE database39;
USE database39;
CREATE TABLE t0(c0 CHAR(190) DEFAULT 'W6뻛뻛UpD', c1 VARCHAR(190) DEFAULT 'W6뻛뻛UpD') DISTRIBUTED BY HASH (c0, c1) PROPERTIES ("replication_num" = "1");
CREATE TABLE t1(c0 VARCHAR(190) DEFAULT '') DISTRIBUTED BY HASH (c0) BUCKETS 15 PROPERTIES ("replication_num" = "1");
INSERT INTO t1 (c0) VALUES ('-781402405');
INSERT INTO t1 (c0) VALUES ('0.9339626230427275'), ('굕+78P>q'), ('-781402405');
INSERT INTO t0 (c1) VALUES ('-781402405');
INSERT INTO t0 (c1) VALUES ('-296506027'), (DEFAULT), ('');
INSERT INTO t1 (c0) VALUES ('F]H');
INSERT INTO t1 (c0) VALUES ('+_z ');
INSERT INTO t0 (c1, c0) VALUES ('AqYv\'_', 'W6뻛뻛UpD');
INSERT INTO t1 (c0) VALUES ('-781402405');
INSERT INTO t0 (c1) VALUES ('g');
INSERT INTO t0 (c1) VALUES ('镃쿥');
INSERT INTO t0 (c0, c1) VALUES ('', '-1e500'), ('', '');
INSERT INTO t1 (c0) VALUES (DEFAULT);
INSERT INTO t0 (c1, c0) VALUES ('H7\\z', 'E*EOsWy6镃');
INSERT INTO t0 (c1) VALUES ('mnE1Nr굕\n ');
INSERT INTO t1 (c0) VALUES ('Gk');

by when try to reproduce，I got this

mysql> SELECT SUM(count) FROM (SELECT CAST(CAST('p^M|-Ft2O' AS BOOLEAN)  IS NOT NULL AND CAST('p^M|-Ft2O' AS BOOLEAN)  AS INT) as count FROM  t0 RIGHT  OUTER JOIN t1 ON (((NOT ((-548572585)<<(1443052302))))and(((NULL) IS NOT NULL)))) as res;
ERROR 1105 (HY000): errCode = 2, detailMessage = Syntax error in line 1:
...1 ON (((NOT ((-548572585)<<(1443052302))))and(((NULL) ...
                             ^
Encountered: <
Expected: IDENTIFIER

Doris seems do not support operator << , why sqlancer can run?