[MDEV-16688] big int still can't be used for an int reference (Implement Feature T201) Created: 2018-07-04  Updated: 2018-11-22

Status: Open
Project: MariaDB Server
Component/s: Data types, Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Minor
Reporter: miracee Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: foreign-keys, upstream

Attachments: PNG File mariadb_bug_bigint_references_int.png    

 Description   

Create a table with an integer primary key.
Create a second table with a foreign key on a bigint column that references to the integer primary key.

Using InnoDB storage engine.

Same as in 2007 - You will get error 150.

I am 120% sure that I already submitted a bug report in 2007 at MySQL. I am also almost sure that the old bug still is open but I couldn't find it.

Anyway, I attached a screen shot.

After Hartmut asked me if this works in PostgreSQL I tested and figured out foreign key bigint references to int works fine.



 Comments   
Comment by Alice Sherepa [ 2018-07-05 ]

I checked in different databases, it is allowed in PostgreSQL and SQLite, but returns an error in Oracle 11g, MS SQL Server 2017, Mysql 8.0

Comment by miracee [ 2018-07-05 ]

SQL Standard says that it should work. bigint is twice of size of int. 4 Byte Int fits into 8 Byte bigint.

As I said, I already filled a bug report in 2007. I can't find the report in the MySQL bugs database but I am pretty sure that I never got a "fixed" message. I worked on so much bug reports that is hard to find bugs form myself.

Comment by Elena Stepanova [ 2018-07-17 ]

I assume you mean https://bugs.mysql.com/bug.php?id=38882

Comment by Elena Stepanova [ 2018-07-17 ]

bar, serg, any opinion on this? Should it be made compliant with the standard (if the standard indeed says so), and in which version can it be realistically done? Should we move it to tasks?

Comment by miracee [ 2018-07-17 ]

Elena: Wow! You found my old bug report. Yeah, I meant that.

Comment by Sergei Golubchik [ 2018-07-17 ]

The standard (2016, part 2, 11.8 <referential constraint definition>, Syntax Rules) says

The declared type of each referencing column shall be comparable to the declared type of the corresponding referenced column.

but later in Corformance Rules:

5) Without Feature T201, “Comparable data types for referential constraints”, conforming SQL language shall not contain a <referencing column list> in which the data type of each referencing column is not the same as the data type of the corresponding referenced column.

In other words, it's perfectly standard to require exact type matching if the Feature T201 is not implemented.

This issue is not about being standard compatible, but about implementing Feature T201.

Generated at Thu Feb 08 08:30:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.