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

For every function, document exactly what type it returns

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16347 Integer literal is typed as MYSQL_TYPE_LONG

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            bar Alexander Barkov added a comment - - 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)

            bar Alexander Barkov added a comment - - 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)
            bar Alexander Barkov added a comment - - 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;

            bar Alexander Barkov added a comment - - 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;
            greenman Ian Gilfillan added a comment -

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

            greenman Ian Gilfillan added a comment - bar do you have a list of the functions affected by this?

            People

              greenman Ian Gilfillan
              wlad Vladislav Vaintroub
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.