ast tree to sql is error when table join a function

[chentianyou]

    query = """SELECT tmp.year, a 
FROM get_last_trading_day_of_past_years(5) AS tmp 
JOIN table1 ON tabl1.a = tmp.last_trading_day 
GROUP BY tmp.year, a 
ORDER BY tmp.year, a"""
    ast_tree = parse_sql(query)
    parse_tree = ast_tree[0].stmt()
    query_sql = ast_to_sql(parse_tree)
    clz = getattr(ast, parse_tree['@'])
    result_sql = RawStream()(clz(parse_tree))
    print(result_sql)

got result

SELECT tmp.year, a FROM {'@': 'FuncCall', 'funcname': ({'@': 'String', 'sval': 'get_last_trading_day_of_past_years'},), 'args': ({'@': 'A_Const', 'isnull': False, 'val': {'@': 'Integer', 'ival': 5}},), 'agg_order': None, 'agg_filter': None, 'over': None, 'agg_within_group': False, 'agg_star': False, 'agg_distinct': False, 'func_variadic': False, 'funcformat': {'#': 'CoercionForm', 'name': 'COERCE_EXPLICIT_CALL', 'value': 0}, 'location': 24} AS tmp INNER JOIN table1 ON tabl1.a = tmp.last_trading_day GROUP BY tmp.year, a ORDER BY tmp.year, a

@lelit

[lelit]

Thank you for the report, bu I'm not able to replicate the problem:

$ pgpp -S "select tmp.year, a from f(5) as tmp join t1 on t1.a = tmp.b"
SELECT tmp.year
     , a
FROM f(5) AS tmp
     INNER JOIN t1 ON t1.a = tmp.b

Why are you serializing the AST into that parse_tree variable? What is that ast_to_sql function? Can you explain the intent of the apparently complicated dance?

[chentianyou]

query_sql = ast_to_sql(parse_tree)

function ast_to_sql is

    clz = getattr(ast, parse_tree['@'])
    result_sql = RawStream()(clz(parse_tree))

I forgot remove it.

    query = """SELECT tmp.year, a 
FROM get_last_trading_day_of_past_years(5) AS tmp 
JOIN table1 ON tabl1.a = tmp.last_trading_day 
GROUP BY tmp.year, a 
ORDER BY tmp.year, a"""
    ast_tree = parse_sql(query)
    parse_tree = ast_tree[0].stmt()
    clz = getattr(ast, parse_tree['@'])
    result_sql = RawStream()(clz(parse_tree))
    print(result_sql)

[chentianyou]

@lelit I parsed the sql into a syntax tree to alias the column and then regenerate the sql. However, this problem was discovered when the sql was generated. Thanks

[lelit]

Ok, I see. Will investigate this in the following days.

[lelit]

The problem seems to be in the reconstruction, that fails with 2-levels tuples:

>>> import pglast
>>> ast = pglast.parse_sql("select tmp.year, a from f(5) as tmp join t1 on t1.a = tmp.b")
>>> tree = ast[0].stmt()
>>> x = pglast.ast.SelectStmt(tree)
>>> ast[0].stmt.fromClause[0].larg.functions
((<FuncCall funcname=(<String sval='f'>,) args=(<A_Const isnull=False val=<Integer ival=5>>,) agg_within_group=False agg_star=False agg_distinct=False func_variadic=False funcformat=<CoercionForm.COERCE_EXPLICIT_CALL: 0>>, None),)
>>> x.fromClause[0].larg.functions
(({'@': 'FuncCall', 'funcname': ({'@': 'String', 'sval': 'f'},), 'args': ({'@': 'A_Const', 'isnull': False, 'val': {'@': 'Integer', 'ival': 5}},), 'agg_order': None, 'agg_filter': None, 'over': None, 'agg_within_group': False, 'agg_star': False, 'agg_distinct': False, 'func_variadic': False, 'funcformat': {'#': 'CoercionForm', 'name': 'COERCE_EXPLICIT_CALL', 'value': 0}, 'location': 24}, None),)

[lelit]

This should be fixed by commit referenced above.

[chentianyou]

This should be fixed by commit referenced above.

thanks very much