Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

part of sharding key in multiple sharding strategy can not null #3644

Closed
zhuyaoming opened this issue Dec 3, 2019 · 17 comments
Closed

part of sharding key in multiple sharding strategy can not null #3644

zhuyaoming opened this issue Dec 3, 2019 · 17 comments
Labels

Comments

@zhuyaoming
Copy link

@zhuyaoming zhuyaoming commented Dec 3, 2019

sharding column value cannot insert null ,but '' is ok?

if my sharding column is id_number
insert into a(id,id_number)values(1,''); √
insert into a(id,id_number)values(1,null); ×

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 3, 2019

What is your expected behaviour if sharding value is null?

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 3, 2019

now i change my sql , if sharding column is null ,donot insert.

i know null is not a good design. but i want to known why sharing colomn cannot be null.

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 3, 2019

What is your expected behaviour if sharding value is null?

table have column id, id_number,caseno,tenant_id,customer_id,sex,name .....
i use [id_number,tenant_id,customer_id ] sharding column complex algorithm
some times i insert table only have customer_id,tenant_id. i hope insert normally

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 3, 2019

now i change my sql , if sharding column is null ,donot insert.

Is it a correct behaviour for regular database? For my understanding, only add a column constraint as NOT NULL, then database should not permit the tuple insert into the table.

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 3, 2019

some times i insert table only have customer_id,tenant_id. i hope insert normally

So, I want to know what is the expected behaviour for insert normally?

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 4, 2019

some times i insert table only have customer_id,tenant_id. i hope insert normally

So, I want to know what is the expected behaviour for insert normally?

no error

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 4, 2019

some times i insert table only have customer_id,tenant_id. i hope insert normally

So, I want to know what is the expected behaviour for insert normally?

no error

Which data node should the data inserted into?

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 4, 2019

Which data node should the data inserted into?

i use ComplexShardingStrategy , tenant_id and consumer_id can known data,
and tenant_id + consumer_id correspond only id_number. i save Map when insert have id_number.

so i use tenant_id,consumer_id,id_number as sharding column,
if where tenant_id=1 and consumer_id=* sharding is ok

if where id_number=*

first : find by map get tenant_id and consumer_id second: cal databases

that my questions.

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 5, 2019

Maybe we do not need talk about business logic here, we just make sure if we permit null for sharding value, which data node should the data inserted into?

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 5, 2019

Maybe we do not need talk about business logic here, we just make sure if we permit null for sharding value, which data node should the data inserted into?

other sharding column(tenant_id,customer_id) can known data node.

@lightning-pro

This comment has been minimized.

Copy link

@lightning-pro lightning-pro commented Dec 5, 2019

Hi ,for un sharding fields, once a value of not the sharding fields with null , all the records after that will be set to null

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 6, 2019

Hi ,for un sharding fields, once a value of not the sharding fields with null , all the records after that will be set to null

thank you for your reply, i will change my colum Field cannot be null.

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 6, 2019

I get the point, it means part of sharding key in multiple sharding strategy can be null, is it?

@zhuyaoming

This comment has been minimized.

Copy link
Author

@zhuyaoming zhuyaoming commented Dec 6, 2019

I get the point, it means part of sharding key in multiple sharding strategy can be null, is it?

yes

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Dec 6, 2019

Ok, can you change the title? I can set label to volunteer wanted for this issue

@zhuyaoming zhuyaoming changed the title shardingvalue cannot Null ? part of sharding key in multiple sharding strategy can be not null Dec 6, 2019
@zhuyaoming zhuyaoming changed the title part of sharding key in multiple sharding strategy can be not null part of sharding key in multiple sharding strategy can not null Dec 6, 2019
yuzel pushed a commit to yuzel/incubator-shardingsphere that referenced this issue Jan 4, 2020
yuzel pushed a commit to yuzel/incubator-shardingsphere that referenced this issue Jan 5, 2020
@KomachiSion

This comment has been minimized.

Copy link
Member

@KomachiSion KomachiSion commented Jan 6, 2020

Hi @zhuyaoming, in my opinion, The sharding column in ShardingSphere is similar to the primary key, which should can locate the DataNode accurately.
Value null for sharding column may cause ambiguity, and it strongly depend on users implements, So ShardingSphere do not support null for sharding column in Insert SQL.

@terrymanu

This comment has been minimized.

Copy link
Member

@terrymanu terrymanu commented Jan 6, 2020

+1, I consider carefully, The constraint of sharding value not be null should be followed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.