MySQL Scoping Problem with Correlated Subqueries

MySql scoping problem with correlated subqueries

You can only correlate one level deep.

Use:

   SELECT m.nom,
          m.prenom,
          x.categories,
          y.areas
     FROM m3allens m
LEFT JOIN (SELECT m2c.m3allem_id,
                  GROUP_CONCAT(DISTINCT c.category_en) AS categories
             FROM CATEGORIES c
             JOIN m3allems_to_categories m2c ON m2c.category_id = c.id
         GROUP BY m2c.m3allem_id) x ON x.m3allem_id = m.id
LEFT JOIN (SELECT m2a.m3allem_id,
                  GROUP_CONCAT(DISTINCT a.area_en) AS areas
             FROM AREAS a
             JOIN m3allems_to_areas m2a ON m2a.area_id = a.id
         GROUP BY m2a.m3allem_id) y ON y.m3allem_id = m.id
    WHERE m.id = ?

MySQL Correlated sub query table name out of scope

https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html says:

A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.

In SQL:1999, the query becomes legal if the derived tables are preceded by the LATERAL keyword (which means “this derived table depends on previous tables on its left side”):

I have not tested it, but I believe your query could be written this way:

SELECT InvoiceID, TranDate,
  IFNULL(TotPayments,0) AS TotPayments,
  ifnull(CountPayments,0) AS CountPayments
FROM Invoices
LEFT JOIN LATERAL (
  SELECT DebtorID, 
    SUM(TranAmount) AS TotPayments, 
    COUNT(*) AS CountPayments
  FROM CashTrans 
  WHERE CashTrans.TranDate >= Invoices.TranDate
  GROUP BY DebtorID
) AS PY ON PY.DebtorID = Invoices.DebtorID;

Also be aware this requires you to use at least MySQL 8.0.14.

MySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery

No, you can't use correalted subqueries in the FROM clause. But you can use them in the ON conditions:

SELECT AVG(d.DailyData1) Data1_20DayAvg 
       --- other aggregate stuff on d (Datatable)
FROM 
      ( SELECT '2012-01-23' AS DateChecked
      ) AS dd
  JOIN
      DataTable AS d
    ON
      d.Date <= dd.DateChecked
    AND
      d.Date >= COALESCE( 
      ( SELECT DailyData1 
        FROM DataTable AS last20 
        WHERE Date <= dd.DateChecked 
          AND (other conditions for last20)
        ORDER BY Date DESC 
        LIMIT 1 OFFSET 19
      ), '1001-01-01'   )
WHERE (other conditions for d Datatable)

Similar, for many dates:

SELECT dd.DateChecked 
     , AVG(d.DailyData1) Data1_20DayAvg 
       --- other aggregate stuff on d (Datatable)
FROM 
      ( SELECT DISTINCT Date AS DateChecked
        FROM DataTable 
      ) AS dd
  JOIN
      DataTable AS d
    ON
      d.Date <= dd.DateChecked
    AND
      d.Date >= COALESCE( 
      ( SELECT DailyData1 
        FROM DataTable AS last20
        WHERE Date <= dd.DateChecked 
          AND (other conditions for last20)
        ORDER BY Date DESC 
        LIMIT 1 OFFSET 19
      ), '1001-01-01'   )
WHERE (other conditions for d Datatable)
GROUP BY 
      dd.DateChecked 

Both queries assume that Datatable.Date has a UNIQUE constraint.

Column scope with joined subqueries

In your subquery you are using columns like pd.pid for SELECT that are not part of the GROUP BY and are not aggregated. Such columns are called hidden and in standard SQL this would give syntax error, but mysql permits it, though it is free to choose value from any row in every group.

If you restrict your set with WHERE pd.pid = 111 all the values of pd.pid in the group will be the same so it doesn't matter which row will be used to get it, however without WHERE value of pd.pid will be undefined (mysql will probably choose the one that can fetch you fastest). You also use that undefined pid for the JOIN so you are bound to get wrong results.

http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html

It's hard to say however how you should rewrite your query as you don't provide enough info about table schema, what are you trying to achieve and what is the meaning of your table/column names.

Correlated Subquery in a MySQL CASE Statement

The problem is scope related. The nested Selects make the up table invisible inside the internal select. Try this:

SELECT
    up.*,
    CASE
        WHEN EXISTS (
            SELECT * 
            FROM Favorite_Performer fp
              JOIN Performer r  ON fp.RID = r.ID
              JOIN PPerformer pr  ON r.ID = pr.Performer_ID 
            WHERE fp.FanID = 107
              AND pr.Performance_ID = up.pID
        )
        THEN 'yes'
        ELSE 'no'
    END as pText
FROM
    _UpcomingPerformances up

How to replace exist in Hive with two correlated subqueries

Like you said, you can convert them to left join or may be left join since they uses exists in both subquery. Simply convert your subqueries to inline view and join them with original tables.

SELECT u.id, COUNT(*)
FROM users u
inner join  posts p on u.id = p.owneruserid 
left outer join (SELECT COUNT(*) as num, pl.postid postid
              FROM postlinks pl 
              GROUP BY pl.postid
              HAVING num > 1) pl ON pl.postid = p.id --correlated subquery 1 with left join 
left outer join (SELECT postid FROM comments c GROUP BY postid)c ON c.postid = p.id  --correlated subquery 2  with left join 
WHERE ( c.postid is not null AND pl.postid is not null)  -- this ensure data exists in both subquery 
GROUP BY u.id

With left join, there may be chance of duplicates, you can use group by in subqry2 to avoid it.

Truncation of DateTimes in correlated subqueries in MariaDB

This seems a bug for MariaDB, since the data returns is different from the data in table.

mysql> SELECT *, (SELECT MAX(m2.update_time) FROM maria_bug m2 WHERE m1.id <> 0 ) max_update FROM maria_bug m1;
+----+----------------------------+----------------------------+
| id | update_time                | max_update                 |
+----+----------------------------+----------------------------+
|  1 | 2001-01-01 00:00:00.200000 | 2001-01-01 00:00:00.200000 |
|  1 | 2001-01-01 00:00:00.100000 | 2001-01-01 00:00:00.000000 |
+----+----------------------------+----------------------------+
2 rows in set (0.00 sec)

Bug is filed to MariaDB:
https://jira.mariadb.org/browse/MDEV-9707

Update: The bug is confirmed by MariaDB.

What is the Rails way to do correlated subqueries?

I think what you are looking for are scopes:

http://guides.rubyonrails.org/active_record_querying.html#scopes

In particular, you can probably use:

scope :current, order("user_scrapes.created_at DESC").limit(1)

Update:

Scopes are meant to return an ActiveRecord object, so that you can continue chaining methods if you wish. There is nothing to prevent you (last I checked anyways) from writing this instead, however:

scope :current, order("user_scrapes.created_at DESC").first

This returns just the one object, and is not chainable, but it may be a more useful function ultimately.

UserScrape.where(address_id: 1234).current.awesomesauce_preference

---

Related Topics