一个clickhouse易错的语法问题

WITH RECURSIVE
  digits(z, lp) AS (
    SELECT '1', 1
    UNION ALL SELECT
    toString(lp+1) as z, lp+1 as lp FROM digits WHERE lp<9
  )
SELECT z, lp, toTypeName(z), toTypeName(lp) FROM digits;

的执行结果如下，看起来z是跳过了2，只返回8行

   ┌─z─┬─lp─┬─toTypeName(z)─┬─toTypeName(lp)─┐
1. │ 1 │  1 │ String        │ UInt64         │
2. │ 3 │  2 │ String        │ UInt64         │
3. │ 4 │  3 │ String        │ UInt64         │
4. │ 5 │  4 │ String        │ UInt64         │
5. │ 6 │  5 │ String        │ UInt64         │
6. │ 7 │  6 │ String        │ UInt64         │
7. │ 8 │  7 │ String        │ UInt64         │
8. │ 9 │  8 │ String        │ UInt64         │
   └───┴────┴───────────────┴────────────────┘

而duckdb中如下SQL的执行结果正确

WITH RECURSIVE
           digits(z, lp) AS (
             SELECT '1', 1
             UNION ALL SELECT
             (lp+1)::varchar as z, lp+1 as lp FROM digits WHERE lp<9
           )
         SELECT z, lp, Typeof(z), Typeof(lp) FROM digits;
┌─────────┬───────┬───────────┬────────────┐
│    z    │  lp   │ typeof(z) │ typeof(lp) │
│ varchar │ int32 │  varchar  │  varchar   │
├─────────┼───────┼───────────┼────────────┤
│ 1       │     1 │ VARCHAR   │ INTEGER    │
│ 2       │     2 │ VARCHAR   │ INTEGER    │
│ 3       │     3 │ VARCHAR   │ INTEGER    │
│ 4       │     4 │ VARCHAR   │ INTEGER    │
│ 5       │     5 │ VARCHAR   │ INTEGER    │
│ 6       │     6 │ VARCHAR   │ INTEGER    │
│ 7       │     7 │ VARCHAR   │ INTEGER    │
│ 8       │     8 │ VARCHAR   │ INTEGER    │
│ 9       │     9 │ VARCHAR   │ INTEGER    │
└─────────┴───────┴───────────┴────────────┘

引起此问题的原因是clickhouse 在toString中引用了本轮重新计算的lp列别名，而不是上一轮递归的lp列。where引用的也是本轮重新计算的lp列别名，导致递归少了一轮，结果少了1行。

改为如下就正确了

:) WITH RECURSIVE
  digits(z, lp) AS (
    SELECT '1', 1
    UNION ALL SELECT
    toString(lp+1) as z, lp+1 as lp2 FROM digits WHERE lp<9
  )
SELECT z, lp, toTypeName(z), toTypeName(lp) FROM digits;



   ┌─z─┬─lp─┬─toTypeName(z)─┬─toTypeName(lp)─┐
1. │ 1 │  1 │ String        │ UInt64         │
2. │ 2 │  2 │ String        │ UInt64         │
3. │ 3 │  3 │ String        │ UInt64         │
4. │ 4 │  4 │ String        │ UInt64         │
5. │ 5 │  5 │ String        │ UInt64         │
6. │ 6 │  6 │ String        │ UInt64         │
7. │ 7 │  7 │ String        │ UInt64         │
8. │ 8 │  8 │ String        │ UInt64         │
9. │ 9 │  9 │ String        │ UInt64         │
   └───┴────┴───────────────┴────────────────┘

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/l1t/article/details/168233208