[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-526344999 Looking more closely at this problem, it seems MSSQL is one of the few engines that doesn't support wrapping CTEs in subqueries (even Oracle supports this). Furthermore, most engines do equal predicate pushdown for CTEs and subqueries, which means that wrapping SQL Lab queries in CTEs in most cases wouldn't have a significant positive performance impact (on Postgres 11 and below CTEs actually perform worse than subqueries!). As this problem seems to be mostly an MSSQL problem, I think I'll leave this on the backlog for now, as making this work correctly would require a rather significant development effort that's difficult to justify given other more pressing issues/features. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-524405495 The query under data source is what is created when you click explore in SQL Lab. What I'm currently tinkering with is adding full CTE support, i.e. being able to write a CTE in SQL Lab and then using that as a data source in charts. Having to create a view is an inconvenient step, and being able to bypass that should make data exploration slightly easier. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-524085299 If you check the diff, I added a warning about using CTEs on non-ANSIs. Btw, I'm working on something that might improve CTE support on all engines and should fix this problem on non-ANSIs, too, but we'll see how that works out. But in the meantime this is probably the best compromise I can offer. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-523999886 Unfortunately I an not that familiar with how the docker setup under contrib works (we use a custom Dockerfile in our env). But seems like rebuilding from scratch should help. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-523509535 Please take a look at my PR that fixes the immediate problem with CTEs on MSSQL (apparently this should also fix the same problem on DB2, Oracle and Teradata). Adding full support for limits on non-ANSI engines is not something I feel an immediate urge to do, and quite frankly I don't want to encourage DB vendors to procrastinate on moving away from bespoke syntax. But anyway, let me know if this is ok for your use case. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-523185284 Looking into this, it turns out that the bug stems from the fact that MSSQL uses the non-ANSI TOP syntax for limiting row count (SQL Lab sticks on a limit on each query, which is currently causing this problem). I'm sure we can work around this issue somehow, e.g. by disabling auto-limiting for non-ANSI DBs. However, I don't expect non-ANSI functionality to be as highly prioritized as ANSI SQL compliant syntax, i.e. expect some functionality in Superset to be less comprehensive for MSSQL than e.g. Postgres. We'll be sure to add a warning in the docs for this once we establish to what extent non-standard syntax is supported. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database URL: https://github.com/apache/incubator-superset/issues/8074#issuecomment-523141019 Reproduced on master; running the same on sqlite works just fine. Will take a closer look. 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services - To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org