Re: Question on Structuring Product Attributes

Lannert

On Sunday, 10 February 2013 05:36:01 UTC+11, Lennart Jonsson wrote:
>
> Yes, I think we are in agreement all and all. I'll just lay of a few
> more thoughts below.

Sure.

> The following are my arguments for why I believe that COALESCE((select
> ...),0) should be the norm, and that select COALESCE(0, 1) is an
> exception that happens to work in some products. Not terribly important,
> we can agree to disagree :-)

Yes.

Agreeing to disagree is the closure of an unresolved argument, for gentlemen. There is no conflict here; no right or wrong to be concluded. Just a discussion, to further understanding. Consider the arguments that are set forth, and choose your position carefully and consciously, not as a result of propaganda or passive contamination.

> I think that it is mandated that we select FROM something (at least SQL2003).

There is the rub.

1. From personal interaction, I know that the SQL Committee Is made up of capable, qualified people from vendors (I was a Sybase Partner from 1997 to 2009), as well as unqualified people, as well as lunatics who think themselves genius, as well as pure subversives (I have had years of written communications with some of them). Each has their own interests in mind, and each is in mental war with the others (notice the circus performers on this thread). Getting them to agree to *anything* is a nightmare. Often, they are merely formalising a feature that some vendor or two has implemented years (decades?) before. So the result is commonly contradictory.

They *mandate* insanity. Eg:
• mandate tight ACID Transactions *and also* mandate methods to break ACID, and to break transactions • mandate the correct (logical, required for an implementation to work) handling of Nulls *and also* mandate the insane null handling (that does have a theoretical abstract basis, but is idiotic in any implementation) • and due to their poor understanding of the Relational Model, mandate all kinds nonsense that they allege to be mandated by the RM (which is not!)

So the implementer who is sane, clear of mind, does not follow the SQL Standard as "mandated". The Order in the Universe is God, my mind, then the SQL Committee. I look at the mandate, have a good laugh, remember fondly the days when professionals were proud and not at war. I implement Null handling that is logical for an implementation (half the mandate, but not the second half, that contradicts the first half). I had transaction standards that were developed and perfected in the 1970's, by IBM, rewritten for Sybase, which I could not be asked to contravene by a bunch of circus performers. So I implement the ACID Transactions half, and (by Standard decree) reject the insane half of the mandate. So my implementations remain sane and non-contradictory.

Specific case. Sybase implements the insanity "Null Problem" as mandated, because it is a compliance requirement. But that is not the final word, for engineers who are human and who have not been separated from their brains. They provide us with a session-level option ANSI_NULL_BEHAVIOUR. The default setting is OFF. The behaviour when it is OFF is: • NULL does not equal anything in the range of values for the subject Domain • NULL equals NULL
• NULL does not equal NOT NULL
• the empty set returns NULL
thereby implementing sanity and predictability and allowing simplified Null handling in app code (if other standards are used, but let's not get distracted by tangents)

Therefore we work in an environment that is sane, and coding is simplified. Most developers do not know that there is a "Null Problem". If they are one of the few that do know, they know it is a problem in the SQL Spec, it is not a problem in their platform, and they do not argue the theoretical existence of the "Null problem". The criminally insane, who demand that they should be able to write insane code, and produce unpredictable results, are free to set the option to ON, and enjoy coding insanity, they like bathing in excreta, as insane people do. Generally our hiring practices ensure that we do not hire such people.

Oracle, which does not genuinely comply with the SQL Standard in more important areas, complies with the standard in this area, and provides no relief for sane people. So in that environment, the "Null problem" is very real, and fixed in their platform; they have to concern themselves with it in every code segment. The product is excreat, but the marketing is excellent.

Another context. Say a bank hires me to re-write their existing system, which "works" in the application sense, but that is broken, in the areas of contention; concurrency; difficulty to isolate and fix code problems; etc. This has happened many times, the assignment includes elevating the mindset of their development team, in order to ensure that they do repeat the first insanity. Half way through the project, a senior manager insists that I implement a "transaction" that updates millions of rows because it a "business requirement". I do not get into arguments with the insane. I write up the requirement, with the specific notice that it contradicts the original contract signed by him, and (as required by the original contract), ask for his and the auditors signature, identifying that the contradiction is in his domian, not mine, and that I will not let him contaminate his contract or my project with it. I explain only that without contravening *his* contract, he can have the "business requirement" that he wants, but he can't have it his *way*. They never sign the change to the contract.

Say the priest tells me that this month, my work detail is to feed the homeless at the church kitchen: I will jump to perform the task, and make sure that the women do not carry heavy loads, etc. Say he comes to me and tells me that I should send my niece to him for private tuition on Saturday afternoons: I will look him directly in the eye, and tell him that if he ever says that to anyone in my congregation, I will kill him, very slowly, and feed his organs to the birds.

My righteousness does not come from the SQL Committee, or the purchaser of my services, or the priest. It comes from, the Order in the Universe is: • God first
• Society (the real physical one, not the virtual one) second ••• family, as part of that
• pre-existing standards (produced by greater minds than mine, before I was born) third • and the clarity of an uncorrupted mind, to evaluate representations, and to choose carefully, fifth. Since my memory is intact, and I remember history, I am well aware of the consequences of each such representation, and being older than a toddler, I know I am responsible for the consequences of whatever I choose. If the SQL Committee or the senior manager or the priest tell me something that fits into that domain, I accept it as *sixth*; if the request or mandate contradicts that domain, I reject it, because it violates the Order of nature, God, logic.

Therefore, my implementations have never had the "Null Problem" or insane "transactions", etc.

I keep track of the progress of the SQL Committee, but I do not accept that the mandate is a mandate for app implementers, as presented by the saboteurs. The vendors mostly do the same, but there the "mandate" is a mandate, so they are required to implement the insanity and contradictions, depending on how separated they are from their brains (which allow us to implement insanity and contradictions). But in many cases, their feature is already written, they do not-recode to comply with the mandate, they declare partial compliance (compliance by intent but not by word), and their customers are quite happy.

My vendors have implemented the "Null Problem" because it is a vendor mandate, but in a manner that does not cause insanity in the platform. The reasonably capable and intelligent (say IQ 90) write code that does not violate logic. The unqualified and the not so intelligent, and the God-less, do not understand the "Null Problem", and therefore implement insanity. Especially if they read blog posts, or they have never read history.

The mandate is really aimed at the vendors, not application implementers. There are many books and blog posts that advocate the mandate *for us*. There are many books and blog posts that advocate portability. It is a joke, they are written by subversives and saboteurs. If you really understand databases and systems; the nature of the "mandate"; the relevance of "portability", they are a sad joke.

For the insane database and application implementer (Hugo for instance, but I doubt he has ever written a system for payment), they view the SQL mandate *for vendors* as a mandate for themselves, because it validates their insanity. They wave it around like a flag of honour and obsess about how to write more blog posts to influence others to write contradictions and insanity into their systems. And they laugh at those of us who listen and obey. And they scream at those of us who reject insanity, and furiously wave the flag that means nothing to us, because it constitutes the reason for existence to them.

Something that sane people should keep in mind when dealing with insane people. They are pretending to be sane; their "reality" is fragile. They seek recognition and most of all, validation, from sane people, because (a) they secretly know they are insane, children of the devil, and (b) like vampires, they need validation from us, to constantly support their fragile "reality". And if they do not get it, it is a matter of life and death, the death of their reality. So they vociferously present insanity as "logic"; they wave every flag that one of their fellow devil-worshipping saboteurs wrote as a "mandate". Do not give them any recognition or validation, let them shrivel up and die without your blood.

Nietzsche is famous, especially if you look up the entry in the bible of madness. The problem with his writings is, they are referenced most during the criminal trials of serial killers; used as a defence argument; that every one has the right to "experience" the darkest parts of themselves, in order to "feel whole". Anyone who is close to society will realise that that is dangerous to society. Anyone who reads something deeper than wiki will find out that he spent most of his life locked up in an institution for the insane, he comes from a species that is inbred for thousands of years, that practices devil-worship; cannibalism; paedophilia; bestiality; etc. Anyone who is close to God will realise that he is a child of the devil, against God's Word, against Nature. But those forces that subvert and destroy human society market this insane persons insane cravings as "self actualisation", and "philosophy". So more and more normal people are infected with insanity, under the guise of something that it is is not.

You are neither insane, nor incompetent, but you have not recognised the constant propaganda of sabotage for what it is. You do not have an Order or Hierarchy in your life (eg. God, society, yourself, and last, the insane) and therefore you do not have protection from the forces of evil. And you have taken the propaganda of the saboteurs and subversives to heart.

I am asking you to get closer to God; closer to your race, religion, culture, language, history; closer to those who came before you, who are proud of their work, and did not have to explain it; closer to honour. See all these issues (above) for what they are; seek clarity of mind that comes from authority (the [d]evil will tell use that everyone is equal; we are the centre of the universe; we can think for ourselves in isolation. That is a guaranteed path to mental illness and separation from human society). Do not do anything, or accept anything, that violates that Order. The insane will demand that we think in an isolated, fragmentary way; I ask you to reject anything that is not integrated with the whole. Then we can enjoy integrity of mind.

That's the context all of us work in. Back to the specific context of the question.

No. SELECT does not require a FROM. The mandate of the RM is higher than the vendor-mandate of the SQL Committee. SQL is data sublanguage defined in the RM; it is the servant of the RM, not the other way around.

SELECT "My Report Header"
SELECT <column_list>
____FROM <joined_tables>
SELECT <total_column_list>
____FROM <joind_tables>
SELECT variables = <total_column_list>
____FROM <joind_tables_2>
SELECT "Totals"
____FROM variables

The RM defines the source as Domains, not tables or entities.

SELECT 2 + 3
> I tried to find where it is stated in SQL2003 and SQL2008, but boy, those papers are not for the heart of fainted.

Yes. And the 2012 Spec is twice the size. Good for unemployed lawyers and the criminally insane. Required reading for the vendors. Irrelevant to application and database implementers (there are good articles that inform us, using one thousandth the words; these are relevant).

> Cheating a bit and checking with:
> http://developer.mimer.se/validator/parser200x/index.tml#parser

Well, that is a brain dead parser, it proves nothing, except that that parser parses.

It is not physically possible to parse the total requirement of SQL (any version of the standard, eg. SQL 2008), because it is self-contradictory. If a parser is written, that allows all that, then it is not a parser, it is a syntax-checker (fraudulently promoting itself as a "parser"). Syntax is irrelevant, every vendor has a different syntax for whatever feature is required for compliance.

What you are trying to prove is not relevant to me, or to the explanation I provided in my previous post.

The direction I give you is:
• read and understand my example code
••• anything that you do not understand there, is worthy of question or discussion • figure out how to implement that on your platform (without any regard to the SQL Standard because it does not apply to you, and without too much regard to "potability", both of which are heavily marketed and propagated by those who sabotage any human endeavour) ••• anything that you do not understand there is not worthy of question or discussion, because you clearly have all the resources to figure out (a) the issue and (b) the resolution, for yourself • educate yourself re genuine standards (eg. Transactions, the elimination of the "Null Problem") • forget about the SQL Standard because it applies to vendors, not to implementers • learn to recognise deceit and sabotage, and reject them

> Perhaps it is just a matter of definition, the standard is rather vague
> on a number of corner cases

No kidding!

> (I have a vague memory of a discussion
> concerning partitions of an empty set

The proposition is absurd, do not give it further thought or energy.

> it turned out that you could
> implement an operation (grouping sets(?)) with several semantics without
> violating any rules).

You are giving it your blood, that it feeds on, in order to exist as something relevant to implementers. I don't.

> However, to me the construction COALESCE((select ...),0) is more obvious
> than select COALESCE(1,0) ...

Definitely.

Then code that, always, and forget about code that works on other platforms.

> Some things I'm pretty sure that they had to change. MySQL does not
> support CHECK constraints. A common trick I've seen people use to mimic
> CHECK constraints is an in-memory table such as:

No. MySQL (which is not SQL anyway, and we really should not be discussing this, because it gives relevance to some minor coding issues) has two "engines". The other one supports more of SQL, such as the constraints required. MySQL is a broken toy, the other engine is a broken but serious toy, but we can make it work, if we stick to Standards.

No triggers were used anywhere in the application. He did use them initially, because that was the best he knew, and his transaction context was poor. I gave him my transaction context and rewrote all the non-transactions for one table cluster, as functions (in order to obtain transactions in the stupid thing, because functions are transactional and everything else is not!). Following which he rewrote all his code as functions, and eliminated the triggers.

The almost-final version of the Data Model is here: http://www.softwaregems.com.au/Documents/Student%20Resolutions/Mark%20Simonetti/Mark%20DM%20V0_11.pdf After that, I provided direction for about two months offline, which included about 12 progressions (minor-minor releases of the DM), during which time he completed all the code.

As you see, there are not only subtypes, but subtypes of subtypes. All defined via declarative constraints; all protected by transactions (functions in his case). He did spin his wheels a bit due to circular references (an insanity that his platform supports, along with the deferred constraint checking that is required to sort-of resolve the insanity)), but after I explained the insanity of circular references, the wheel-spinning ended. His mind was clear, but contaminated by the propaganda (like yours); I removed the contamination. He figured out almost everything himself after that.

Generally, we did not discuss specific syntax; there were only two occasions where I researched the MySQL documentation and discussed it with him.

The whole database and app was written with portability in mind, but we did not obsess about specifics, for reasons explained above.

> Now we are talking ugly ;-)

Yes. Don't do that. Figure out a method that does not cause nausea or blindness.

Please understand one thing. At no point did I state that my code is better, or that others should code as I do. Therefore I do not need to defend it. I was merely explaining what my code does, to someone who had questioned it.

By way of explanation re the core question (not the minor implementation details that the contamination has programmed you to obsess about), I offer the following. I'll give you another perspective. Paraphrase, not code:

Let me assume you understand Codd's concept of Domain. I want the intersection of the Domain of EXISTS{ TRUE | FALSE } and the Domain of NOT_NULL(). COALESCE gives me NOT_NULL(). The SELECT gives me EXISTS{ TRUE | FALSE }. It does not matter if I code ____EXISTS{ TRUE | NOT_NULL() }
or
____NOT_NULL( EXISTS{ TRUE | FALSE } ) I will get the same result. The Optimiser can figure it out, and it does not matter which Order it Chooses.

What would you say, if I coded:
--[3]--
SELECT 1 * COALESCE( 1, 0 )

____FROM Product
____WHERE ProductId = _at_ProductId
____AND ProductType = _at_ProductType

COALESCE means the English word coalesce. "select the first non-Null value from a list" is a good definition for a Reference Manual, it cannot comment on the context of its use. Here it means, collect all those things together and give me a 1 or a 0.

[3] is Normalised version of [1]. [2] is a Normalised version of [3].

Thank you for your attention.

Cheers
Derek Received on Sun Feb 10 2013 - 01:34:51 CET