[Tracking Issue] Failed SQLs in duckdb test

[CookiePieWw]

While migrating duckdb tests into GreptimeDB, there're many SQLs that are not available:

1. Refused by datafusion:
   • Exists:

     > SELECT EXISTS(SELECT 1);
     ERROR 1815 (HY000): (EngineExecuteQuery): DataFusion error: This feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false });

   • Correlated column in predicate

     > SELECT s.n, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND (s.major = 'CS' OR s.major = 'Games Eng') AND e.grade <= (SELECT AVG(e2.grade) - 1 FROM exams e2 WHERE s.id=e2.sid OR (e2.curriculum=s.major AND s.y>=e2.y)) ORDER BY n, course;
      ERROR 1815 (HY000): (EngineExecuteQuery): DataFusion error: Error during planning: Correlated column is not allowed in predicate: outer_ref(s.id) = e2.sid OR e2.curriculum = outer_ref(s.major) AND outer_ref(s.y) >= e2.y;

2. Unsupported:

   • Direct FROM

     > FROM numbers;
     ERROR 1235 (42000): (Unsupported): SQL statement is not supported, keyword: FROM

   • unamed_subquery as an alias to subqueries

     > SELECT unnamed_subquery.a, unnamed_subquery2.b FROM (SELECT 42 a), (SELECT 43 b);
     ERROR 1815 (HY000): (PlanQuery): Failed to plan SQL: No field named unnamed_subquery.a. Valid fields are a, b.

   • Array/list types:

     > CREATE TABLE lists(l INTEGER[]);

   • Digit separators and

     SELECT 1_000_000;

   • typeof function:

     SELECT typeof(100);

If some of above are needed, we can try to introduce them to greptimedb.