Skip to content

TPC-H Query 15 #166

Closed
Closed

Description

@alamb

Note: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-11528

CREATE VIEW/multiple statement support: "The context currently only supports a single SQL statement"

{{Error: NotImplemented("The context currently only supports a single SQL statement")}}

Activity

  1. andygrove commented on May 10, 2022

    @andygrove
    Member

    Latest failure:

    Error: NotImplemented("The context currently only supports a single SQL statement")
    
  2. DaltonModlin commented on Aug 9, 2022

    @DaltonModlin
    Contributor

    I'm looking into this issue and seeing a new error message:

    Error: NotImplemented("Unsupported SQL statement: Some(\"CREATE VIEW revenue0 (supplier_no, total_revenue) AS SELECT l_suppkey, sum(l_extendedprice * (1 - l_discount)) FROM lineitem WHERE l_shipdate >= DATE '1996-01-01' AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH GROUP BY l_suppkey\")")

    This comes from missing a match case's if statement at datafusion/sql/src/planner.rs: line 192. Query 15's defined aliases (supplier_no, total_revenue) are populating the columns, thus, columns.is_empty() returns false. Removing the check for empty columns allows the view to be generated, but new issues arise when the second SQL statement in Q15 attempts to query the view via:

    select
    	s_suppkey,
    	s_name,
    	s_address,
    	s_phone,
    	total_revenue
    from
    	supplier,
    	revenue0
    

    where 'total_revenue' is a column in 'revenue0' which is the view.

    The table default_catalog.default_schema.revenue0 can't be found in session context via the code path:
    datafusion/sql/src/planner.rs: line 684 -> datafusion/core/src/execution/context.rs: line 1539

    This doesn't currently generate useful error output, but debugging led me here.

    Before moving forward too much further, I'd like context on the columns.is_empty() check if possible. I feel I may be missing something here.

  3. alamb commented on Aug 9, 2022

    @alamb
    ContributorAuthor
  4. avantgardnerio commented on Aug 11, 2022

    @avantgardnerio
    Contributor

    Removing the check for empty columns allows the view to be generated

    @DaltonModlin if the check is removed, do the other tests still pass? If so, perhaps a new test (with aliases) and a new PR targeted at this one isolated change is a good next step. Folks like small PRs around here ;)

  5. DaltonModlin commented on Aug 26, 2022

    @DaltonModlin
    Contributor

    As mentioned in #3266 I believe there are currently 3 more issues to be solved for a successful Q15 run.

    1. The current failure in which the view created in the first part doesn't populate the context's table names hashmap before the select portion's logical plan is being built. I'll create a new issue and mention this issue for easier reference.
    2. Add support for non-correlated subqueries #3266
    3. implement drop view #3267
  6. kmitchener commented on Sep 5, 2022

    @kmitchener
    Contributor

    This runs now, but returns 0 results most of the time. The way views are executed still looks a little funny to me, so to exclude the possibility of some bug in the view code, I converted it to a with statement like below:

    with revenue as (
    	select
    		l_suppkey as supplier_no,
    		sum(l_extendedprice * (1 - l_discount)) as total_revenue
    	from
    		lineitem
    	where
    		l_shipdate >= date '1996-01-01'
    		and l_shipdate < date '1996-01-01' + interval '3' month
    	group by
    		l_suppkey)
    select
    	s_suppkey,
    	s_name,
    	s_address,
    	s_phone,
    	total_revenue
    from
    	supplier,
    	revenue
    where
    	s_suppkey = supplier_no
    	and total_revenue = (
    		select
    			max(total_revenue)
    		from
    			revenue
    	)
    order by
    	s_suppkey;

    Running this query back to back multiple times usually returns 0 results, but sometimes it correctly returns the top supplier as it's supposed to -- 1 result:

    +-----------+--------------------+-------------------+-----------------+--------------------+
    | s_suppkey | s_name             | s_address         | s_phone         | total_revenue      |
    +-----------+--------------------+-------------------+-----------------+--------------------+
    | 8449      | Supplier#000008449 | Wp34zim9qYFbVctdW | 20-469-856-8873 | 1772627.2086999998 |
    +-----------+--------------------+-------------------+-----------------+--------------------+
    1 row in set. Query took 3.363 seconds.

    I extracted just the WITH section to see what it's returning. And 2 back to back runs of this query shows different results for total_revenue:

    with revenue as (
            select
                    l_suppkey as supplier_no,
                    sum(l_extendedprice * (1 - l_discount)) as total_revenue
            from
                    lineitem
            where
                    l_shipdate >= date '1996-01-01'
                    and l_shipdate < date '1996-01-01' + interval '3' month
            group by
                    l_suppkey) select * from revenue order by 2 desc limit 1;
    +-------------+---------------+
    | supplier_no | total_revenue |
    +-------------+---------------+
    | 8449        | 1772627.2087  |
    +-------------+---------------+
    1 row in set. Query took 2.959 seconds.

    then

    +-------------+--------------------+
    | supplier_no | total_revenue      |
    +-------------+--------------------+
    | 8449        | 1772627.2086999998 |
    +-------------+--------------------+
    1 row in set. Query took 2.554 seconds.

    I understand floating point results are uncertain. Is that what's going on here?

  7. Dandandan commented on Sep 5, 2022

    @Dandandan
    Contributor

    I think for this to be correct we should switch to using a decimal type, as using floating point will result in small rounding errors, because parallel execution does not have deterministic ordering.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions