Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7097

Sub-Queries and Outer Joins may return NULL instead of (existing) value over ODBC

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.12, 10.0.14, 10.1.1
    • Fix Version/s: 10.0.15
    • Labels:
    • Environment:
      Windows 2008 R2 Server (amd64), MariaDB 10.0.12 (Windows, amd64), Oracle ODBC 11.02.00.03 (with Oracle 09.02.0060 on another server) as well as MySQL ODBC 5.03.04.00 ANSI driver (with MySQL Server 5.5.40-0ubuntu on another server)

      Description

      On certain conditions, a sub-select or outer join may return NULL instead of the actually existing value.

      You can test that simply with a MySQL table, e.g. `demo.subselectdemo`:

      id INT value VARCHAR(255)
      1 First
      2 Second
      3 Third

      Ideally on another computer, create a DSN with the MySQL ODBC driver (the MariaDB ODBC driver didn't work for me at all) to that database instance. Then create a database on that computer with a CONNECT-Table, e.g.:

      CREATE TABLE `proxy` ENGINE=CONNECT TABLE_TYPE=ODBC BLOCK_SIZE=10 TABNAME='demo.subselectdemo' CONNECTION='DSN=<your DSN>;UID=<username>;PWD=<password>';

      Now try the following query:

      SELECT a.id, (SELECT b.value FROM proxy AS b WHERE a.id = b.id) FROM proxy AS a;

      It returns:

      a.id (SELECT b.value FROM proxy AS b WHERE a.id = b.id)
      1 First
      2 (NULL)
      3 (NULL)

      It should return something like the following, which you can verify on the origin DB with the "real" table:

      SELECT a.id, (SELECT b.value FROM demo.subselectdemo AS b WHERE a.id = b.id) FROM demo.subselectdemo AS a;

      a.id (SELECT b.value FROM demo.subselectdemo AS b WHERE a.id = b.id)
      1 First
      2 Second
      3 Third

      This might have to do with CONNECT's limitation to "forward cursors".

      But similar happens to me with at least Oracle ODBC with a "LEFT OUTER JOIN" on two different, large (>5000 entries) tables, where the "key" used to correlate the entries is pretty random. After a few thousand entries, the OUTER JOIN is always NULL, although there is a corresponding entry in the other table - often it was actually found already for an earlier entry, e.g.:

      SELECT a.id, a.ref, b.ref FROM a LEFT OUTER JOIN b ON a.ref=b.ref;

      a.index a.ref b.ref
      1 45 45
      2 5 5
      3 526 526
      ...
      3503 6 6
      3504 45 (NULL)
      3505 8827 (NULL)
      3506 5 (NULL)
      ...

      A second OUTER JOIN in the same query stops even earlier, e.g.:

      SELECT a.id, a.ref, b.ref, b.otherref, c.otherref FROM a LEFT OUTER JOIN b ON a.ref=b.ref LEFT OUTER JOIN c ON b.otherref=c.otherref;

      a.index a.ref b.ref b.otherref c.otherref
      1 45 45 1 1
      2 5 5 7 7
      3 526 526 3 3
      ...
      1478 5 5 3 3
      1479 526 526 4 (NULL)
      1480 526 526 7 (NULL)
      ...
      3503 6 6 4 (NULL)
      3504 45 (NULL) (NULL) (NULL)
      3505 8827 (NULL) (NULL) (NULL)
      3506 5 (NULL) (NULL) (NULL)
      ...

      I hope you can solve both issues by fixing the "sub-select" issue; if this ticket needs to be splitted, I can try to create a proper test case for the JOIN-issue.

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            bschneider Björn Schneider
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: