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

          elenst Elena Stepanova added a comment -

          While documentation on DUAL is scarce both in MariaDB KB and MySQL manual, it appears that it currently works as initially intended:

          DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MariaDB ignores the clauses. MariaDB does not require FROM DUAL if no tables are referenced.

          FROM DUAL could be used when you only SELECT computed values, but require a WHERE clause,

          So, I'm converting this to a feature request.

          elenst Elena Stepanova added a comment - While documentation on DUAL is scarce both in MariaDB KB and MySQL manual, it appears that it currently works as initially intended: DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MariaDB ignores the clauses. MariaDB does not require FROM DUAL if no tables are referenced. FROM DUAL could be used when you only SELECT computed values, but require a WHERE clause, So, I'm converting this to a feature request.

          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.