[MDEV-16360] For every function, document exactly what type it returns - Jira

Details

Type: Task
Status: Open (View Workflow)
Priority: Minor
Resolution: Unresolved
Fix Version/s: None
Component/s: Documentation
Labels:
- foundation

Description

e.g https://mariadb.com/kb/en/library/connection_id/
used to return MYSQL_LONG_LONG in 10.2 in result set metadata, but
and changed to MYSQL_LONG UNSIGNED in 10.3

We need to track the changes between the versions, so there are not many potentially breaking changes in the future.

as noted in ~~MDEV-16347~~, changing types can break client programs
In 10.3, according to Bar, ~~MDEV-12619~~ caused all functions that used to return 64bit now can return 32bit, and those are over 50, and this breaking change went almost undocumented.

Attachments

Issue Links

relates to

MDEV-16347 Integer literal is typed as MYSQL_TYPE_LONG

Closed

Activity

Ascending order - Click to sort in descending order

Ian Gilfillan added a comment - 2018-06-04 14:48

bar do you have a list of the functions affected by this?

Ian Gilfillan added a comment - 2018-06-04 14:48 bar do you have a list of the functions affected by this?

Alexander Barkov added a comment - 2018-06-06 03:14 - edited

greenman, this is a more or less full list:

# Integer constants that fit into 32bit range

SELECT 1;

# Boolean predicates

SELECT TRUE IS TRUE;

SELECT NULL IS NULL;

SELECT 1<2;

SELECT 'a' LIKE 'a';

SELECT 'a' RLIKE 'a';

SELECT 'b' BETWEEN 'a' AND 'c';

SELECT 'a' IN ('a','b','c');

SELECT TRUE AND TRUE;

# Geometry relations

# ST_CONTAINS, ST_CROSSES, ST_DISJOINT, ST_EQUALS, ST_INTERSECTS,

# ST_OVERLAPS, ST_TOUCHES, ST_WITHIN, ST_RELATE

SELECT ST_CONTAINS(POINT(1,1),POINT(1,1));

# Geometry boolean properties (both with and without the ST_  prefix):

# ST_ISEMPTY, ST_ISSIMPLE, ST_ISRING, ST_ISCLOSED

SELECT ST_ISEMPTY(POINT(1,1));

# Geometry numeric properties:

# ST_DIMENSION, ST_NUMGEOMETRIES, ST_NUMINTERIOSRING, ST_NUMPOINTS, ST_SRID

# IP predicates

# IS_IPV4, IS_IPV4_COMPAT, IS_IPV6, IS_IPV6_COMPAT

# JSON predicates

# JSON_VALID, JSON_EXISTS, JSON_CONTAINS, JSON_CONTAINS_PATH

# JSON numeric properties

# JSON_LENGTH, JSON_DEPTH

# Cursor boolean properties (sql_mode=ORACLE)

# cursor%FOUND, cursor%NOTFOUND, cursor%ISOPEN

# LENGTH functions

# LENGTH, OCTET_LENGTH, CHAR_LENGTH, UNCOMPRESSED_LENGTH

SELECT LENGTH('a');

# Temporal unit extraction functions

# TO_DAYS, DAYOFMONTH, DAYOFYEAR, QUARTER, YEAR

# HOUR, MINUTE, SECOND, MICROSECOND

# WEEK, YEARWEEK

# EXTRACT - for a certain set of units

SELECT WEEK('2001-01-01');

SELECT YEARWEEK('2001-01-01');

# Date period functions

SELECT PERIOD_ADD(200915,13);

SELECT PERIOD_DIFF(200915,200811);

# Miscelaneous functions

# strcmp

# crc32

# interval

# regexp_instr

# connection_id

# sign

# coercibility

# locate

# field

# ascii

# ord

# find_in_set

# bit_count

# benchmark

# sleep

# get_lock

# release_lock

# Item_master_gtid_wait

# is_free_lock

# is_used_lock

# sqlcode

# name_const

SELECT STRCMP('a','b');

SELECT CRC32('x');

SELECT NAME_CONST('name',1);

# Hybrid functions that use above expressions as arguments:

# General purpose:

#   CASE, COALESCE, IFNULL, IF, NULLIF

# Specific to sql_mode=ORACLE

#   NLV2, DECODE

SELECT COALESCE(1);

# Recursive calls for these hybrid functions

SELECT COALESCE(COALESCE(1));

# Integer CAST for expressions that fit into 32 bit

# CAST(AS SIGNED), CAST(AS UNSIGNED)

SELECT CAST(1 AS SIGNED);

# Integer negation for 32 bit arguments

SELECT -1;

# Integer conversion functions (when the result fits into  32 bit)

# ABS, FLOOR, CEILING

# Integer arithmetic for results that fit into 32 bit

SELECT 1+1, 1*1;

SELECT 1 DIV 1, 1 MOD 1;

# Functions LEAST and GREATEST for 32bit arguments

SELECT LEAST(1,2);

# User variable assignment using a 32bit expression

SELECT @a:=10;

Alexander Barkov added a comment - 2018-06-06 03:14 - edited greenman , this is a more or less full list: # Integer constants that fit into 32bit range SELECT 1; # Boolean predicates SELECT TRUE IS TRUE ; SELECT NULL IS NULL ; SELECT 1<2; SELECT 'a' LIKE 'a' ; SELECT 'a' RLIKE 'a' ; SELECT 'b' BETWEEN 'a' AND 'c' ; SELECT 'a' IN ( 'a' , 'b' , 'c' ); SELECT TRUE AND TRUE ; # Geometry relations # ST_CONTAINS, ST_CROSSES, ST_DISJOINT, ST_EQUALS, ST_INTERSECTS, # ST_OVERLAPS, ST_TOUCHES, ST_WITHIN, ST_RELATE SELECT ST_CONTAINS(POINT(1,1),POINT(1,1)); # Geometry boolean properties (both with and without the ST_ prefix): # ST_ISEMPTY, ST_ISSIMPLE, ST_ISRING, ST_ISCLOSED SELECT ST_ISEMPTY(POINT(1,1)); # Geometry numeric properties: # ST_DIMENSION, ST_NUMGEOMETRIES, ST_NUMINTERIOSRING, ST_NUMPOINTS, ST_SRID # IP predicates # IS_IPV4, IS_IPV4_COMPAT, IS_IPV6, IS_IPV6_COMPAT # JSON predicates # JSON_VALID, JSON_EXISTS, JSON_CONTAINS, JSON_CONTAINS_PATH # JSON numeric properties # JSON_LENGTH, JSON_DEPTH # Cursor boolean properties (sql_mode=ORACLE) # cursor %FOUND, cursor %NOTFOUND, cursor %ISOPEN # LENGTH functions # LENGTH, OCTET_LENGTH, CHAR_LENGTH, UNCOMPRESSED_LENGTH SELECT LENGTH( 'a' ); # Temporal unit extraction functions # TO_DAYS, DAYOFMONTH, DAYOFYEAR, QUARTER, YEAR # HOUR , MINUTE , SECOND , MICROSECOND # WEEK, YEARWEEK # EXTRACT - for a certain set of units SELECT WEEK( '2001-01-01' ); SELECT YEARWEEK( '2001-01-01' ); # Date period functions SELECT PERIOD_ADD(200915,13); SELECT PERIOD_DIFF(200915,200811); # Miscelaneous functions # strcmp # crc32 # interval # regexp_instr # connection_id # sign # coercibility # locate # field # ascii # ord # find_in_set # bit_count # benchmark # sleep # get_lock # release_lock # Item_master_gtid_wait # is_free_lock # is_used_lock # sqlcode # name_const SELECT STRCMP( 'a' , 'b' ); SELECT CRC32( 'x' ); SELECT NAME_CONST( 'name' ,1); # Hybrid functions that use above expressions as arguments: # General purpose: # CASE , COALESCE , IFNULL, IF , NULLIF # Specific to sql_mode=ORACLE # NLV2, DECODE SELECT COALESCE (1); # Recursive calls for these hybrid functions SELECT COALESCE ( COALESCE (1)); # Integer CAST for expressions that fit into 32 bit # CAST ( AS SIGNED), CAST ( AS UNSIGNED) SELECT CAST (1 AS SIGNED); # Integer negation for 32 bit arguments SELECT -1; # Integer conversion functions ( when the result fits into 32 bit ) # ABS , FLOOR, CEILING # Integer arithmetic for results that fit into 32 bit SELECT 1+1, 1*1; SELECT 1 DIV 1, 1 MOD 1; # Functions LEAST and GREATEST for 32bit arguments SELECT LEAST(1,2); # User variable assignment using a 32bit expression SELECT @a:=10;

Alexander Barkov added a comment - 2018-06-06 03:19 - edited

greenman, note you can run this:

mysql --column-type-info test

against a new and an old server and see the difference.

This is an output from an old server (the new server will display LONG instead of LONGLONG):

[bar@home ~]$ mysql --column-type-info test

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 10.0.27-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> SELECT 1;

Field   1:  `1`

Catalog:    `def`

Database:   ``

Table:      ``

Org_table:  ``

Type:       LONGLONG

Collation:  binary (63)

Length:     1

Max_length: 1

Decimals:   0

Flags:      NOT_NULL BINARY NUM

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

Alexander Barkov added a comment - 2018-06-06 03:19 - edited greenman , note you can run this: mysql --column-type-info test against a new and an old server and see the difference. This is an output from an old server (the new server will display LONG instead of LONGLONG): [bar@home ~]$ mysql --column-type-info test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 10.0.27-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]> SELECT 1; Field 1: `1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: NOT_NULL BINARY NUM +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)

MariaDB Server

For every function, document exactly what type it returns

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration