[#MDEV-24424] Unnecessary usage of to_float() for INSERT into the Spider table with float column

[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

[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]$

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)

                    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

[MDEV-24424] Unnecessary usage of to_float() for INSERT into the Spider table with float column Created: 2020-12-17 Updated: 2024-01-09 Resolved: 2021-08-13
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Affects Version/s:	10.4.7, 10.4.15, 10.4.18
Fix Version/s:	N/A

[MDEV-24424] Unnecessary usage of to_float() for INSERT into the Spider table with float column Created: 2020-12-17 Updated: 2024-01-09 Resolved: 2021-08-13