The Heap™ – Consultancy ©®

5:08 AM

@JackDouglas No.

But for InnoDB tables, he is right. The PK columns are included in any secondary index. Like SQL Server's clustered keys.

All InnoDB tables are "clustered", either by the PK or the columns of the first UNIQUE (if there's no PK) or a hidden 6-byte column if no UNIQUE exists.

So, he is right if he's talking about InnoDB. Not about any table.

1 hour later…

Jack Douglas

6:17 AM

@ypercubeᵀᴹ interesting, thanks

1 hour later…

McNets

7:30 AM

Morning

Jack Douglas

7:49 AM

anyone with a Draytek router needs to read this: draytek.co.uk/support/security-advisories/…

1 hour later…

8:53 AM

I think this answer deserves more attention:

A: MySQL a = 0, b = 0, a <> b

BINARY is a misnomer. It really refers to a string of bytes; it does not refer to binary numbers. Also, = and <> adapt to the datatype(s) involved: SELECT a=0, b=0, -- numeric comparisons a=b, a<>b -- BLOB comparisons; length matters FROM f;

Morning

Paul White

@AndriyM It could be clearer.

9:09 AM

Perhaps it could. I understood that in numeric comparisons a binary is converted to a number as though it were a char/varchar value where basically each byte is considered to be a character.

Maybe saying something along those lines explicitly would make the answer clearer generally

Colin 't Hart

Don't binary operators apply from the right to the left? So the left operand is implicitly cast to the datatype of the right?

@Colin'tHart I haven't heard of that before. In MySQL or SQL Server.

Colin 't Hart

Hmm, testing shows that's indeed not the case on MySQL and SQL Server for + and || at least.

3 hours later…

11:54 AM

@ypercubeᵀᴹ - I answered this question dba.stackexchange.com/questions/207321/… , yesterday and you kindly pointed out that my answer wasn't complete (for DEFAULTs). However, I have found a case where the rule that you have to put in all columns doesn't hold. It's for PostgreSQL - if you leave the last field out of an INSERT (leaving out the columns) it still works.

Of the 5 servers on dbfiddle.uk, pg is the only one for which this works - all the others fail with "Column count doesn't match value count at row 1" or similar (fiddle). Should I add this in, or is it over-egging the custard?

@Vérace Yes, it's a Postgres thing. You could add only the first column(s) and all the rest get their defaults.

INSERT INTO t (a) VALUES (1); works for a 100 column table, as long the rest 99 columns have a DEFAULT: dbfiddle.uk/…

Ah, yes - just tested. I'll just add a line or two for completeness - thanks for that!

dbfiddle.uk/…

INSERT INTO sample DEFAULT VALUES; would be the way to use default for all columns. This would work too, but I don't think it's standard: INSERT INTO sample SELECT;

@ypercubeᵀᴹ I'm with @Lennart on this - one can be too clever for one's own good!

12:37 PM

INSERT INTO tablename DEFAULT VALUES works in SQL Server too, so it could be a standard thing.

Oh, you meant the other thing might not be standard, I see

@Vérace: This one is actually standard: INSERT INTO t (a) VALUES (1); I believe the non-standard one would go without the column list: INSERT INTO t VALUES (1);

What AndriyM says ^^^

Edited

Voted to delete: dba.stackexchange.com/questions/63862/uk-addresses-in-3nf/…

12:52 PM

Obviously one of the more diligent students!

1:26 PM

@AndriyM - thanks for the edit - I "twiddled the fiddle" (is that a sin? :-) ) also to make things clearer (hopefully!).

Ah yes, forgot to correct that one too, sorry.

1 hour later…

jadarnel27

2:47 PM

There are so many question in this question.