Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6046

LEFT JOIN produces incorrect result if is not pushed down and has a filtering condition put to ON clause

    XMLWordPrintable

Details

    • Hide

      1. Add in test-vdb.xml the views.pr virtual procedure:

      <model visible = "true" type = "VIRTUAL" name = "views">
              <metadata type = "DDL"><![CDATA[
      CREATE PROCEDURE pr(str string) RETURNS (proc_res string) AS
      	BEGIN
      		SELECT 'some_value';
      	END
              ]]>
              </metadata>
          </model>
      

      2. Run the test query and check if it returns a correct result (should return NULL):

      --FAIL(returns 1 instead of NULL)
      SELECT 
      	subquery_b.b
      FROM (SELECT 'val' AS a) AS subquery_a
      LEFT JOIN (
      	SELECT 
      		subquery_inner_a.medium || subquery_inner_b.proc_res AS a,
      		1 AS b
      	FROM (
      		SELECT 'medium' AS medium) AS subquery_inner_a
      		, TABLE(CALL views.pr(subquery_inner_a.medium)) AS subquery_inner_b
      --		, TABLE(SELECT 'some_value' proc_res) AS subquery_inner_b
      ) AS subquery_b 
      	ON subquery_b.a = subquery_a.a
      	AND subquery_a.a = 'val' ;;
      

      3. Run the same test query but replace procedure call with a SELECT query it runs inside and check if it returns a correct result (should return NULL):

      --success(returns NULL)
      SELECT 
      	subquery_b.b
      FROM (SELECT 'val' AS a) AS subquery_a
      LEFT JOIN (
      	SELECT 
      		subquery_inner_a.medium || subquery_inner_b.proc_res AS a,
      		1 AS b
      	FROM (
      		SELECT 'medium' AS medium) AS subquery_inner_a
      --		, TABLE(CALL views.pr(subquery_inner_a.medium)) AS subquery_inner_b
      		, TABLE(SELECT 'some_value' proc_res) AS subquery_inner_b
      ) AS subquery_b 
      	ON subquery_b.a = subquery_a.a
      	AND subquery_a.a = 'val' ;;
      
      Show
      1. Add in test-vdb.xml the views.pr virtual procedure: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ CREATE PROCEDURE pr(str string) RETURNS (proc_res string) AS BEGIN SELECT 'some_value' ; END ]]> </metadata> </model> 2. Run the test query and check if it returns a correct result (should return NULL): --FAIL( returns 1 instead of NULL ) SELECT subquery_b.b FROM ( SELECT 'val' AS a ) AS subquery_a LEFT JOIN ( SELECT subquery_inner_a.medium || subquery_inner_b.proc_res AS a , 1 AS b FROM ( SELECT 'medium' AS medium) AS subquery_inner_a , TABLE ( CALL views.pr(subquery_inner_a.medium)) AS subquery_inner_b -- , TABLE ( SELECT 'some_value' proc_res) AS subquery_inner_b ) AS subquery_b ON subquery_b. a = subquery_a. a AND subquery_a. a = 'val' ;; 3. Run the same test query but replace procedure call with a SELECT query it runs inside and check if it returns a correct result (should return NULL): --success( returns NULL ) SELECT subquery_b.b FROM ( SELECT 'val' AS a ) AS subquery_a LEFT JOIN ( SELECT subquery_inner_a.medium || subquery_inner_b.proc_res AS a , 1 AS b FROM ( SELECT 'medium' AS medium) AS subquery_inner_a -- , TABLE ( CALL views.pr(subquery_inner_a.medium)) AS subquery_inner_b , TABLE ( SELECT 'some_value' proc_res) AS subquery_inner_b ) AS subquery_b ON subquery_b. a = subquery_a. a AND subquery_a. a = 'val' ;;

    Description

      LEFT JOIN produces an incorrect result in some cases if is not pushed down and has a filtering condition put to ON clause.

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            dalex005 Dmitrii Pogorelov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: