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

ODBC tables do not quote identifier names correctly

    XMLWordPrintable

    Details

      Description

      In Postgres:

      maxuser=# create table space_in_name("something with space" int);
      CREATE TABLE
      maxuser=# insert into space_in_name values(1),(2),(3);
      INSERT 0 3
      

      In MariaDB:

      MariaDB [test]> CREATE TABLE pg_in_maria ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=PostgreSQL Unicode;UID=maxuser;PWD=maxpwd;SERVER=127.0.0.1;PORT=5432' tabname='public.space_in_name';
      Query OK, 0 rows affected (0.038 sec)
       
      MariaDB [test]> select * from pg_in_maria;
      ERROR 1296 (HY000): Got error 174 'SQLExecDirect: ERROR: syntax error at or near "with";
      Error while executing the query' from CONNECT
      

      Looking at the network capture, we can see that the identifiers aren't quoted correctly.

      T 127.0.0.1:40322 -> 127.0.0.1:5432 [AP] #14
        51 00 00 00 3a 53 45 4c    45 43 54 20 73 6f 6d 65    Q...:SELECT some
        74 68 69 6e 67 20 77 69    74 68 20 73 70 61 63 65    thing with space
        20 46 52 4f 4d 20 70 75    62 6c 69 63 2e 73 70 61     FROM public.spa
        63 65 5f 69 6e 5f 6e 61    6d 65 00                   ce_in_name.    
      

      A workaround is to use SrcDef to define the SQL statement:

      MariaDB [test]> CREATE TABLE pg_in_maria ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=PostgreSQL Unicode;UID=maxuser;PWD=maxpwd;SERVER=127.0.0.1;PORT=5432' SrcDef='SELECT * FROM public.space_in_name';
      Query OK, 0 rows affected (0.035 sec)
       
      MariaDB [test]> SELECT * FROM pg_in_maria;
      +----------------------+
      | something with space |
      +----------------------+
      |                    1 |
      |                    2 |
      |                    3 |
      +----------------------+
      3 rows in set (0.009 sec)
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              anel Anel Husakovic
              Reporter:
              markus makela markus makela
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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