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

Unnecessary usage of to_float() for INSERT into the Spider table with float column

    XMLWordPrintable

    Details

      Description

      Starting from 10.4.7+ (probably due to the fix for MDEV-16248) Spider seems to generate weird and unnecessary syntax for INSERTs in case the table has FLOAT column. Consider the following example (with proper spider user already added):

      [openxs@fc31 maria10.4]$ bin/mysql -uopenxs --socket=/tmp/mariadb.sock test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 2029
      Server version: 10.4.18-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> drop table ti;
      Query OK, 0 rows affected (0.070 sec)
       
      MariaDB [test]> create table ti(id int primary key, i int, f float);
      Query OK, 0 rows affected (0.115 sec)
       
      MariaDB [test]> insert into ti values(1,1,1);
      Query OK, 1 row affected (0.001 sec)
       
      MariaDB [test]> select * from ti;
      +----+------+------+
      | id | i    | f    |
      +----+------+------+
      |  1 |    1 |    1 |
      +----+------+------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> drop table ts;
      Query OK, 0 rows affected (0.017 sec)
       
      MariaDB [test]> create table ts(id int primary key, i int, f float) engine=spider comment 'host "127.0.0.1", user "spider", password "spider", table "ti", port
      "3309"';
      Query OK, 0 rows affected (0.047 sec)
       
      MariaDB [test]> select @@port, @@spider_same_server_link;
      +--------+---------------------------+
      | @@port | @@spider_same_server_link |
      +--------+---------------------------+
      |   3309 |                         1 |
      +--------+---------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> insert into ts values(2,2,2);
      ERROR 1305 (42000): FUNCTION test.to_float does not exist
      MariaDB [test]> exit
      Bye
      

      Looks like function creation on the fly fails. In the general query log we see what happens:

      [openxs@fc31 maria10.4]$ tail data/fc31.log
      201217 10:09:06   2029 Query    select @@port, @@spider_same_server_link
      201217 10:09:20   2029 Query    insert into ts values(2,2,2)
                        2030 Connect  spider@localhost as anonymous on
                        2030 Query    set session transaction isolation level repeatable read;set session autocommit = 1;set session wait_timeout = 604800;set session sql_mode = 'strict_trans_tables,error_for_division_by_zero,no_auto_create_user,no_engine_substitution';set session time_zone = '+00:00';start transaction
                        2030 Query    SET NAMES latin1
                        2030 Init DB  test
                        2030 Query    insert into `test`.`ts`(`id`,`i`,`f`)values(2,2,/* create function to_float(a decimal(20,6)) returns float return a */ to_float(2))
                        2030 Query    rollback
                        2030 Quit
      201217 10:09:24   2029 Quit
      [openxs@fc31 maria10.4]$
      

      INSERT works as expacted without FLOAT columns in the table.

        Attachments

          Activity

            People

            Assignee:
            Kentoku Kentoku Shiba
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: