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

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

        Issue Links

          Activity

            It seems to help:

            MariaDB [test]> show create table ti\G
            *************************** 1. row ***************************
                   Table: ti
            Create Table: CREATE TABLE `ti` (
              `id` int(11) NOT NULL,
              `i` int(11) DEFAULT NULL,
              `f` float DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1
            1 row in set (0,000 sec)
             
            MariaDB [test]> insert into ti values (1,2,3);
            Query OK, 1 row affected (0,001 sec)
             
            MariaDB [test]> select * from ti;
            +----+------+------+
            | id | i    | f    |
            +----+------+------+
            |  1 |    2 |    3 |
            +----+------+------+
            1 row in set (0,001 sec)
             
            MariaDB [test]> show create table ts\G
            *************************** 1. row ***************************
                   Table: ts
            Create Table: CREATE TABLE `ts` (
              `id` int(11) NOT NULL,
              `i` int(11) DEFAULT NULL,
              `f` float DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='host "127.0.0.1", user "spider", password "spider", wrapper "mariadb", table "ti", port "3309"'
            1 row in set (0,005 sec)
             
            MariaDB [test]> insert into ts values (2,2,3);
            Query OK, 1 row affected (0,003 sec)
             
            MariaDB [test]> select * from ts;
            +----+------+------+
            | id | i    | f    |
            +----+------+------+
            |  1 |    2 |    3 |
            |  2 |    2 |    3 |
            +----+------+------+
            2 rows in set (0,005 sec)
            

            but I see no reason for this NOT to work that way by default:

                                32 Connect  spider@localhost as anonymous on
                                32 Query    set session time_zone = '+00:00'
                                32 Query    SET NAMES latin1
                                32 Init DB  test
                                32 Query    show table status from `test` like 'ti'
            201217 18:18:04     31 Query    insert into ts values (2,2,3)
                                32 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';start transaction
                                32 Query    insert into `test`.`ti`(`id`,`i`,`f`)values(2,2,cast(3 as float))
                                32 Query    commit
                                32 Quit
            

            I am also not sure why cast is needed above.

            valerii Valerii Kravchuk added a comment - It seems to help: MariaDB [test]> show create table ti\G *************************** 1. row *************************** Table: ti Create Table: CREATE TABLE `ti` ( `id` int(11) NOT NULL, `i` int(11) DEFAULT NULL, `f` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0,000 sec)   MariaDB [test]> insert into ti values (1,2,3); Query OK, 1 row affected (0,001 sec)   MariaDB [test]> select * from ti; +----+------+------+ | id | i | f | +----+------+------+ | 1 | 2 | 3 | +----+------+------+ 1 row in set (0,001 sec)   MariaDB [test]> show create table ts\G *************************** 1. row *************************** Table: ts Create Table: CREATE TABLE `ts` ( `id` int(11) NOT NULL, `i` int(11) DEFAULT NULL, `f` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='host "127.0.0.1", user "spider", password "spider", wrapper "mariadb", table "ti", port "3309"' 1 row in set (0,005 sec)   MariaDB [test]> insert into ts values (2,2,3); Query OK, 1 row affected (0,003 sec)   MariaDB [test]> select * from ts; +----+------+------+ | id | i | f | +----+------+------+ | 1 | 2 | 3 | | 2 | 2 | 3 | +----+------+------+ 2 rows in set (0,005 sec) but I see no reason for this NOT to work that way by default: 32 Connect spider@localhost as anonymous on 32 Query set session time_zone = '+00:00' 32 Query SET NAMES latin1 32 Init DB test 32 Query show table status from `test` like 'ti' 201217 18:18:04 31 Query insert into ts values (2,2,3) 32 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';start transaction 32 Query insert into `test`.`ti`(`id`,`i`,`f`)values(2,2,cast(3 as float)) 32 Query commit 32 Quit I am also not sure why cast is needed above.

            > but I see no reason for this NOT to work that way by default:

            I just changed default wrapper from "mysql" to "mariadb" for 10.6. 2f6970e

            > I am also not sure why cast is needed above.

            Spider always cast it for float column for safety.

            Kentoku Kentoku Shiba (Inactive) added a comment - > but I see no reason for this NOT to work that way by default: I just changed default wrapper from "mysql" to "mariadb" for 10.6. 2f6970e > I am also not sure why cast is needed above. Spider always cast it for float column for safety.

            Kentoku, perhaps spider should be able to switch between "mysql" or "mariadb" wrapper depending on what server it connects to?
            why would one ever want to use "mysql" wrapper when connecting to MariaDB or vice versa?

            serg Sergei Golubchik added a comment - Kentoku , perhaps spider should be able to switch between "mysql" or "mariadb" wrapper depending on what server it connects to? why would one ever want to use "mysql" wrapper when connecting to MariaDB or vice versa?

            Always casting float columns seems to be rather a specification of the Spider, possibly for MDEV-16248. One can avoid the issue by specifying the proper wrapper type. So, this is not a bug. If necessary, please file a new issue for the automatic detection as a task. I expect such a detection would be possible, while I've not looked at it in the detail. ralf.gebhardt@mariadb.com, serg, valerii

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - - edited Always casting float columns seems to be rather a specification of the Spider, possibly for MDEV-16248 . One can avoid the issue by specifying the proper wrapper type. So, this is not a bug. If necessary, please file a new issue for the automatic detection as a task. I expect such a detection would be possible, while I've not looked at it in the detail. ralf.gebhardt@mariadb.com , serg , valerii

            People

              nayuta-yanagisawa Nayuta Yanagisawa (Inactive)
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.