金仓数据库KingbaseES整型与浮点类型数据比较隐式转换规则

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

create?table?test_integer(id?integer,desc_info text);

create?table?test_numeric(id?numeric,desc_info text);

create?table?test_real(id?real,desc_info text);

insert?into?test_integer?select?generate_series(10001,20000),repeat('a',500);

insert?into?test_numeric?select?generate_series(10001,20000),repeat('a',500);

insert?into?test_real?select?generate_series(10001,20000),repeat('a',500);

create?index?ind_test_integer?on?test_integer(id);

create?index?ind_test_numeric?on?test_numeric(id);

create?index?ind_test_real?on?test_real(id);

analyze test_integer;

analyze test_numeric;

analyze test_real;

1

2

3

4

5

6

7

8

prepare?test_integer_bind_numeric(numeric)?as?select?*?from?test_integer?where?id=$1;

prepare?test_integer_bind_real(real)?as?select?*?from?test_integer?where?id=$1;

prepare?test_numeric_bind_integer(integer)?as?select?*?from?test_numeric?where?id=$1;

prepare?test_numeric_bind_real(real)?as?select?*?from?test_numeric?where?id=$1;

prepare?test_real_bind_numeric(numeric)?as?select?*?from?test_real?where?id=$1;

prepare?test_real_bind_integer(integer)?as?select?*?from?test_real?where?id=$1;

1

2

3

4

5

6

7

8

9

10

11

testdb=# explain?execute?test_integer_bind_numeric(12345);

???????????????????????????QUERY PLAN???????????????????????????

-----------------------------------------------------------------

?Seq Scan?on?test_integer? (cost=0.00..817.00?rows=50 width=508)

???Filter: ((id)::numeric?=?'12345'::numeric)

(2?rows)<br><br>testdb=# explain?execute?test_numeric_bind_integer(12345);

??????????????????????????????????????QUERY PLAN??????????????????????????????????????

---------------------------------------------------------------------------------------

?Index?Scan using ind_test_numeric?on?test_numeric? (cost=0.29..8.30?rows=1 width=510)

???Index?Cond: (id =?'12345'::numeric)

(2?rows)

1

2

3

4

5

6

7

8

9

10

11

12

13

testdb=# explain?execute?test_integer_bind_real(12345);

???????????????????????????QUERY PLAN???????????????????????????

-----------------------------------------------------------------

?Seq Scan?on?test_integer? (cost=0.00..817.00?rows=50 width=508)

???Filter: ((id)::double?precision?=?'12345'::real)

(2?rows)

testdb=# explain?execute?test_real_bind_integer(12345);

???????????????????????????????????QUERY PLAN???????????????????????????????????

---------------------------------------------------------------------------------

?Index?Scan using ind_test_real?on?test_real? (cost=0.29..8.30?rows=1 width=508)

???Index?Cond: (id =?'12345'::double?precision)

(2?rows)

1

2

3

4

5

6

7

8

9

10

11

12

13

testdb=# explain?execute?test_numeric_bind_real(12345);

???????????????????????????QUERY PLAN???????????????????????????

-----------------------------------------------------------------

?Seq Scan?on?test_numeric? (cost=0.00..817.00?rows=50 width=510)

???Filter: ((id)::double?precision?=?'12345'::real)

(2?rows)

testdb=# explain?execute?test_real_bind_numeric(12345);

???????????????????????????????????QUERY PLAN???????????????????????????????????

---------------------------------------------------------------------------------

?Index?Scan using ind_test_real?on?test_real? (cost=0.29..8.30?rows=1 width=508)

???Index?Cond: (id =?'12345'::double?precision)

(2?rows)