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

dual table is implemented a bit flaky

    Details

      Description

      SELECT * FROM dual;
      ERROR 1096 (HY000): No tables used
      

      The DUAL table has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X
      https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries009.htm

      select * from test join dual;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dual' at line 1
       
      select * from dual join test;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'join test' at line 1
      

      So the "correct" results in oracle terms should be:

      select * from `DUAL`;
      +-------+
      | DUMMY |
      +-------+
      | X     |
      +-------+
       
      select * from test join `DUAL`;
      +----+--------------+---------------------+-------+
      | id | data         | ts                  | DUMMY |
      +----+--------------+---------------------+-------+
      |  1 | Some bla bla | 2018-06-07 22:03:52 | X     |
      +----+--------------+---------------------+-------+
       
      select * from `DUAL` join test;
      +-------+----+--------------+---------------------+
      | DUMMY | id | data         | ts                  |
      +-------+----+--------------+---------------------+
      | X     |  1 | Some bla bla | 2018-06-07 22:03:52 |
      +-------+----+--------------+---------------------+
      

      This could matter because the dual table is fundamental in Oracle world and many internal and external queries may rely on exactly this behaviour...

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              oli Oli Sennhauser
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: