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

    • 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' ;;
    • Undefined

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

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

                Created:
                Updated:
                Resolved: