stephane@skysql.com,

LAST_INSERT_ID() (without an argument) is explicitly defined as a function that "returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column". If applications rely on a non-existing feature, it surely does not mean that the function breaks them, it means that applications have wrong logic. I don't see any bug here whatsoever.

If I understand the goal of SQL in your example, it could have been easily achieved by doing instead

DELIMITER $

CREATE TRIGGER before_insert_t BEFORE INSERT ON t FOR EACH ROW BEGIN

  SET @last_insert_id = uuid_short();

  SET new.id = @last_insert_id;

END$

DELIMITER ;

insert into t select null,1 ,"", null, null;

select @last_insert_id;

Now, for the "improved management of sequences", please explain in more detail what exactly you mean by that, if there is anything other than "In a more generic case it would be appreciate if last_insert_id() could return at least the single column numeric PK of the last inserted record of the statement.". If it's just that, I can of course convert it into a feature request, but I bet it would break more (valid) applications that it would fix, because existing applications now rightfully relay on LAST_INSERT_ID() returning the last automatically generated value, and with the new semantics it would be overridden.

Hello Elnest , did you test this , that's exactly the bug i see , or did you get a working release for that test case

mysql [localhost] {skysql} (spdc) > select version();

+--------------------+

| version()          |

+--------------------+

| 10.1.8-MariaDB-log |

+--------------------+

1 row in set (0.00 sec)

mysql [localhost] {skysql} (spdc) > CREATE OR REPLACE TRIGGER before_insert_t BEFORE INSERT ON t FOR EACH ROW BEGIN

    ->  SET @last_insert_id = uuid_short();

    ->  SET new.id = @last_insert_id;

    -> END//

Query OK, 0 rows affected (0.33 sec)

mysql [localhost] {skysql} (spdc) > DELIMITER ;

mysql [localhost] {skysql} (spdc) > insert into t select null,1 ,"", null, null;

Query OK, 1 row affected (0.06 sec)

Records: 1  Duplicates: 0  Warnings: 0

 mysql [localhost] {skysql} (spdc) > select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                0 |

+------------------+

1 row in set (0.00 sec)

stephane@skysql.com,
You are still using LAST_INSERT_ID() in the last select.
LAST_INSERT_ID() by definition returns the last automatically generated value. You are not using an automatically generated value, so LAST_INSERT_ID cannot work and is not supposed to work here.

In my example I suggested to use the variable that was previously set in the trigger.
select @last_insert_id.

"improved management of sequences", please explain in more detail what exactly you mean by that:

Users looking for way to get a sequence of primary key that won't conflict on a cluster of nodes:

• Like UUID or UUID_SHORT not being force to rely on exotic trigger for that
• Auto increment offset is not reliable if the size of the cluster change as far as i know
• Would like to maintain compatibility with existing application , that may rely on last_insert_id() ( the case for most ORM setup to delegate key management to RDBMS )
• To not break the very few application that would rely on last_insert_id() to return a first auto increment column regardless of it's pk status, i propose to introduce a server variables last_insert_id=primary_key| first_auto_inc
• Overrde this per table DDL would be the best allowing user to chose a column to be return for the value of last_insert_id()
• Some UUID_SHORT native data type would be useful ALTER TABLE ADD COLUMN uuid BIGINT UNSIGNED DEFAULT uuid_short() PRIMARY KEY , ENGINE=INNODB LAST_INSERT_ID=uuid

Similar feature will open MariaDB to multi source to single table consolidation architecture. Can be found in many offline datamart where conflicts are resolve with post synchronisation process

Indeed not a bug , following trigger looks like the type of workaround we would require in a proper implementation with native sequence. It make sens that @insert_id is not yet known in a BEFORE context of a trigger !

Thanks for the passionate chat !

CREATE OR REPLACE TRIGGER before_insert_t BEFORE INSERT ON t FOR EACH ROW BEGIN

  SET  @insert_id = uuid_short();

 END//

mysql [localhost] {skysql} (spdc) > insert into t select 0,1 ,"", null, null;

mysql [localhost] {skysql} (spdc) > select last_insert_id();

+----------------------+

| last_insert_id()     |

+----------------------+

| 10688994220565331976 |

+----------------------+

1 row in set (0.00 sec)

last_insert_id was designed to work for auto increment. it cannot reliably work for uuid_short, think of a multi-row insert, auto-increment values can be calculated from last_insert_id, uuid values cannot be. Also, you can have non-numeric columns, e.g. for UUID(), you can have many uuid/uuid_short columns — last_insert_id cannot possibly cover all use cases.

Note that in 10.2 you can have expressions in the DEFAULT clause (MDEV-10134), so you won't need to use a trigger for cases like this.