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

dual table is implemented a bit flaky

    XMLWordPrintable

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

            Unassigned Unassigned
            oli Oli Sennhauser
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.