Notice removed Reward existing answer by SQB
    Bounty Ended with David דודו Markovitz's answer chosen by SQB
    Notice added Reward existing answer by SQB
    Bounty Started worth 50 reputation by SQB
5 It's not the self-join.
source | link

My actual case involves a join between the view and a table the view is based on. I've replicated this in the example, using views only. I've also tested it without the "self-join"; this makes no difference.

My actual case involves a join between the view and a table the view is based on. I've replicated this in the example, using views only. I've also tested it without the "self-join"; this makes no difference.

4 Reworked question, clarified example
source | link

More puzzlingly, this behaviour has changed in the last couple of days. I'm not the DBA for this database, but the DBAs assure me nothing has changed recently.

Example

Edited to add: I thought I've tested the following example on MySQL versions 5.5.40-36.1-log and 5.5.53-38.5 this behaviour had only changed recently, but actually this view had no "missing records" until recently, which is why I never encountered this behaviour before. My question still stands if this is expected behaviour.(because that's what I've got on hand) as well as on SQL Fiddle versions 5.5 and 5.6.

ExampleSetup

-- A simple view
CREATE OR REPLACE VIEW foo
AS
      SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
      SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW barbaz
AS
SELECT
   f1f.x AS x1,
   f2b.x AS x2y,
   SIGN(COALESCE(f2b.xy, 0)) AS x1_is_evenz
FROM foo f1f
LEFT JOIN foobar f2b ON f1f.x = 2 * f2b.x;
y;

Query

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN barbaz b ON f.x = b.x2;y;

Expected result

I would expect the following result, where we see one row with records from baz and two rows without.

| x | x1x | x2y | x1_is_evenz |
----------------------------
| 1 |  2 |  1 |          1 |
| 2 |  - |  - |          - |
| 3 |  - |  - |          - |

Actual result

But instead I get the following, with indeed one row with records from baz, but two rows where there are no records from baz except for the calculated column, which is calculated for all three rows!

| x | x1x | x2y | x1_is_evenz |
----------------------------
| 1 |  2 |  1 |          1 |
| 2 |  - |  - |          0 |
| 3 |  - |  - |          0 |

So x1_is_evenz is calculated, even where the viewbaz returns no records (as evidenced by the NULLs for x1x and x2y).

Like I wrote, the behaviour has changed (to this) in the last couple of days.

  • Is this the expected behaviour? Why?
  • What could have caused this behaviour?

Workaround

Version isWhat 5.5.40-36.1-log.


Edited to add:does

My workaround work for nowme, is hiding the viewbaz in a sub-select:, like so.

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM barbaz
) b ON f.x = b.x2;y;
| x | x1x | x2y | x1_is_evenz |
----------------------------
| 1 |  2 |  1 |          1 |
| 2 |  - |  - |          - |
| 3 |  - |  - |          - |

Is this expected behaviour? Why?


‡: worked for me yesterday, can't get it to work today.

More puzzlingly, this behaviour has changed in the last couple of days. I'm not the DBA for this database, but the DBAs assure me nothing has changed recently.

Edited to add: I thought this behaviour had only changed recently, but actually this view had no "missing records" until recently, which is why I never encountered this behaviour before. My question still stands if this is expected behaviour.

Example

-- A simple view
CREATE VIEW foo
AS
SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE VIEW bar
AS
SELECT
   f1.x AS x1,
   f2.x AS x2,
   SIGN(COALESCE(f2.x, 0)) AS x1_is_even
FROM foo f1
LEFT JOIN foo f2 ON f1.x = 2 * f2.x;

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN bar b ON f.x = b.x2;

I would expect the following result

| x | x1 | x2 | x1_is_even |
----------------------------
| 1 |  2 |  1 |          1 |
| 2 |  - |  - |          - |
| 3 |  - |  - |          - |

But instead I get

| x | x1 | x2 | x1_is_even |
----------------------------
| 1 |  2 |  1 |          1 |
| 2 |  - |  - |          0 |
| 3 |  - |  - |          0 |

So x1_is_even is calculated, even where the view returns no records (as evidenced by the NULLs for x1 and x2).

Like I wrote, the behaviour has changed (to this) in the last couple of days.

  • Is this the expected behaviour? Why?
  • What could have caused this behaviour?

Version is 5.5.40-36.1-log.


Edited to add:

My workaround for now is hiding the view in a sub-select:

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM bar
) b ON f.x = b.x2;
| x | x1 | x2 | x1_is_even |
----------------------------
| 1 |  2 |  1 |          1 |
| 2 |  - |  - |          - |
| 3 |  - |  - |          - |

Example

I've tested the following example on MySQL versions 5.5.40-36.1-log and 5.5.53-38.5 (because that's what I've got on hand) as well as on SQL Fiddle versions 5.5 and 5.6.

Setup

-- A simple view
CREATE OR REPLACE VIEW foo
AS
      SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
      SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
   f.x,
   b.y,
   SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;

Query

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;

Expected result

I would expect the following result, where we see one row with records from baz and two rows without.

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Actual result

But instead I get the following, with indeed one row with records from baz, but two rows where there are no records from baz except for the calculated column, which is calculated for all three rows!

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |

So z is calculated, even where baz returns no records (as evidenced by the NULLs for x and y).

Workaround

What does work for me, is hiding baz in a sub-select, like so.

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM baz
) b ON f.x = b.y;
| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Is this expected behaviour? Why?


‡: worked for me yesterday, can't get it to work today.

3 Explanation found, sort of.
source | link

More puzzlingly, this behaviour has changed in the last couple of days. I'm not the DBA for this database, but the DBAs assure me nothing has changed recently.More puzzlingly, this behaviour has changed in the last couple of days. I'm not the DBA for this database, but the DBAs assure me nothing has changed recently.

Edited to add: I thought this behaviour had only changed recently, but actually this view had no "missing records" until recently, which is why I never encountered this behaviour before. My question still stands if this is expected behaviour.

Like I wrote, the behaviour has changed (to this) in the last couple of days.Like I wrote, the behaviour has changed (to this) in the last couple of days.

  • Is this the expected behaviour? Why?
  • What could have caused this behaviour?What could have caused this behaviour?

More puzzlingly, this behaviour has changed in the last couple of days. I'm not the DBA for this database, but the DBAs assure me nothing has changed recently.

Like I wrote, the behaviour has changed (to this) in the last couple of days.

  • Is this the expected behaviour?
  • What could have caused this behaviour?

More puzzlingly, this behaviour has changed in the last couple of days. I'm not the DBA for this database, but the DBAs assure me nothing has changed recently.

Edited to add: I thought this behaviour had only changed recently, but actually this view had no "missing records" until recently, which is why I never encountered this behaviour before. My question still stands if this is expected behaviour.

Like I wrote, the behaviour has changed (to this) in the last couple of days.

  • Is this the expected behaviour? Why?
  • What could have caused this behaviour?
2 Added workaround
source | link
1
source | link