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

          Ascending order - Click to sort in descending order
          oli Oli Sennhauser created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          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...           		{code:sql}
          SELECT * FROM dual;
          ERROR 1096 (HY000): No tables used
          {code}

          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

          {code:sql}
          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
          {code}

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

          {code:sql}
          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 |
          +-------+----+--------------+---------------------+
          {code}

          This could matter because the dual table is fundamental in Oracle world and many internal and external queries may rely on exactly this behaviour...
          elenst Elena Stepanova made changes -
          Labels dual pl/sql, dual pl/sql
          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.
          elenst Elena Stepanova made changes -
          Affects Version/s 10.3.7 [ 23005 ]
          Environment linux ubuntu 14.04
          Issue Type Bug [ 1 ] Task [ 3 ]
          serg Sergei Golubchik made changes -
          Labels dual pl/sql compatibility; dual pl/sql
          serg Sergei Golubchik made changes -
          Labels compatibility; dual pl/sql Compatibility dual pl/sql
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 87737 ] MariaDB v4 [ 130847 ]

          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.