[GitHub] [superset] prs1022 commented on issue #17701: v1.3.superset clickhouse database，server paging query compile limit m offset n to limit 0,n

[GitBox <gi...@apache.org>]

prs1022 commented on issue #17701:
URL: https://github.com/apache/superset/issues/17701#issuecomment-992033740


   > hey there @prs1022 -- I'm having trouble following your original issue. Can you post a screen recording?
   > 
   > What's your recommended course of action and are you able to contribute a fix?
   
   Thank you for your reply，I do a test , Here is my test program, you can find the reason for this bug.
   when I want limit 5 offset 10, mysql is good, but clickhouse get a bad result.
   ```
   def test_sql():
       t = table('t', column('x'))
       sa = select([t])
       LIMIT = 5
       OFFSET = 10
       s = sa.where(t.c.x == 5).limit(LIMIT).offset(OFFSET)
   
       engine = create_engine("mysql://root:123456@127.0.0.1:3306/aaaa")
       print(s.compile(engine,compile_kwargs={"literal_binds": True}))
       print("==="*10)
       engine = create_engine("clickhouse://root:123456@127.0.0.1:8123/test")
       sql = str(s.compile(engine,compile_kwargs={"literal_binds": True}))
       print(sql)
   ```
   here is the result
   ```
   SELECT t.x 
   FROM t 
   WHERE t.x = 5 
    LIMIT 10, 5
   ==============================
   SELECT x 
   FROM t 
   WHERE x = 5
    LIMIT 0,10
   
   ```
   I have a temporary solution for the clickhouse database:
   `sql.replace(f'LIMIT 0,{OFFSET}', f'LIMIT {OFFSET},{LIMIT}')`
   
    and I haven't found this bug with other data sources.  I record this in my [zhihu blog](https://zhuanlan.zhihu.com/p/443630138).
   
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org