optimizer doesn't convert outer join to inner on views with WHERE clause (MDEV-10006)

[MDEV-10031] Testing for MDEV-10006 Created: 2016-05-05  Updated: 2016-05-11  Resolved: 2016-05-11

Status: Closed
Project: MariaDB Server
Component/s: Tests
Affects Version/s: N/A
Fix Version/s: N/A

Type: Technical task Priority: Major
Reporter: Elena Stepanova Assignee: Elena Stepanova
Resolution: Done Votes: 0
Labels: None


 Description   

Sergei Petrunia wrote:

I need

  • a test of 10.1 vs 10.1 with the above patch
  • we need a grammar with nested LEFT/INNER joins, with various ON expressions
  • ON expression must be simple predicates over null-able as well as NOT NULL columns
  • The primary risk (thing to check for) is different query results. I don't expect crashes.

Patch is in the comment to MDEV-10006 of 2016-05-05 15:46



 Comments   
Comment by Elena Stepanova [ 2016-05-06 ]

Coverage from the first set of tests (standard comparison set):

===== File: sql/sql_select.cc =====
 
  14418 :      17645162 :        if (table->outer_join && !table->embedding && table->table)
      : b 0: 2872348   ::
      : b 1: 14772814   ::
      : b 2: 112143    ::
      : b 3: 2760205   ::
      : b 4: 112143    ::
      : b 5: 0         ::
  14419 :        112143 :          table->table->maybe_null= FALSE;
  14420 :      17645162 :        table->outer_join= 0;
  14421 :               : +      //psergey:
  14422 :      17645162 : +      if (!(straight_join || table->straight))
      : b 0: 16569311   ::
      : b 1: 1075851   ::
      : b 2: 16465229   ::
      : b 3: 104082    ::
  14423 :      16465229 : +        table->dep_tables= table->embedding? table->embedding->dep_tables: 0;
      : b 0: 12202283   ::
      : b 1: 4262946   ::
  14424 :      17645162 :        if (table->on_expr)
      : b 0: 3198905   ::
      : b 1: 14446257   ::
  14425 :               :        {
  14426 :               :          /* Add ON expression to the WHERE or upper-level ON condition. */

Comment by Elena Stepanova [ 2016-05-11 ]

Ran comparison tests against 10.1 without the patch and transformation tests with ExecuteAsView, haven't got any mismatches.
The task will remain open until the patch passes the review, in case there are changes that require retesting.

Generated at Thu Feb 08 07:39:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.