Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SQL future Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Again, the problem with replacing SQL with a more "relational" database engine and language is that, despite numerous attempts to do so, nobody has been able to come up with one that actually solves any real-world issues without causing more problems than it solves.

For example, on the point of 3-value logic, there are very, very few situations in this world that can be answered with only "yes" or "no" and that don't allow for "not sure" or "unknown at this time". Even Codd recognized this, despite his early opposition to the concept of Null values in relational data.

The problems with allowing nulls are purely academic. The differences between academia and reality are even more well-documented than the problems with allowing three-value logic in a relational database.

Here's my challenge to you on this subject: Write up, in detail, the actual situations in real databases, where the problems you have sited with regards to SQL have stopped you from modeling a real-world situation into a database. Specifically, I want to know the exact situation where 3-value logic being possible in a database has made it more difficult to model a business (or other real entity) situation. Note that that solution you were forced to hack must be more complex than simply setting the column to not allow Nulls. I want to see the same for each of the other "fundamental problems" you are citing. These must not be hypothetical situations, nor academic exercises, they must be real situations that caused real problems to real people.

I've seen this same set of arguments forwarded dozens of times over the years. It's not "met with resounding silence", it's met with "well, what real world problem are you trying to solve", which is usually answered by "well, nothing, but it's just not right".

If you can prove, in the real world, that these "fundamental problems" really are fundamental, and neither academic, incidental, nor trivial, then I'll buy your argument. Thus far, in a decade of seeing your exact statements brought up dozens of times, I've never seen anyone do this yet.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #820807
Posted Wednesday, November 18, 2009 8:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:12 PM
Points: 451, Visits: 3,466
I didn't want this to become a discussion about nulls because if you fix the other issues (such as providing decent type support and a truly relational language) then the "null problem" can go away and you can get on just as well without them.

You are of course correct that nulls don't actually prevent you from achieving any desired results from the database because it is just about possible to avoid them anyway. You can wrap every SUM() in a COALESCE() function in case it returns a null. You can eliminate nulls from outer joins using CASE expressions. Even if you never use them however, they still have a demonstrable cost. A SQL database engine needs to acommodate nulls internally even if they are never actually returned in a query. That means that certain query rewrites and optimisations just aren't possible. So your DBMS is in fact much less effective than it could be just because SQL needs nulls (the same also applies to duplicate rows).

A compromise might be to have a switch that allows a user to turn off null support. That would have serious consequences for the SQL language however because it would have to either force errors or change the meaning of existing code. It would also leave customers asking why they should have to pay for software complexity that they don't actually use.

All information can be represented without nulls or anything like them and I don't recall that Codd ever said otherwise. Science, mathematics and logic were able to describe the real world without using any symbol like a null for thousands of years before SQL came along. They continue to do so today. In any case, SQL's model of null certainly doesn't match real world requirements. Where in the "real world" (or in common sense for that matter) is x = x anything other than a True proposition (whether x is known or not)? Where in the real world does the sum of nothing equal something other than Zero?

You asked for real world examples of problems that can't be solved by SQL. I've given one already: the ability to enforce a constraint that an Order must contain at least one Item in the item table. That's an example of a problem I've encountered many times professionally and is a well-known textbook limitation of SQL. This and other business rule type of problems have to be solved by procedural code or by other software outside the SQL DBMS - frequently at great expense. A RDBMS with more general constraint capabilities would eliminate or reduce that expense.

A number of RDBMSs and languages have been built and are being used. They include Dataphor, Muldis and various other open source efforts. I'm not sure what problems you are referring to with those systems when compared to SQL DBMSs. Maybe you could elaborate.

I'd like to know what alternatives you think there are. The deficiencies of SQL are a pressing and very topical problem for a great many people right now. That's why there is so much interest and investment in alternative non-relational models. How else do you propose to improve the capabilities of our DBMSs? Do you really think current problems can be solved within the SQL model and if so, how? Or do you propose using alternative (non-relational) data models instead?


David
Post #820881
Posted Wednesday, November 18, 2009 8:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
All information can be represented without nulls or anything like them and I don't recall that Codd ever said otherwise. Science, mathematics and logic were able to describe the real world without using any symbol like a null for thousands of years before SQL came along. They continue to do so today. In any case, SQL's model of null certainly doesn't match real world requirements. Where in the "real world" (or in common sense for that matter) is x = x anything other than a True proposition (whether x is known or not)? Where in the real world does the sum of nothing equal something other than Zero?


Null is not "nothing". Null is "unknown value". Nothing is 0.

Keep in mind that the invention of the concept of 0 in mathematics took tens of thousands of years to develop, and revolutionized the whole subject radically.

And, in the real world of real science x != x if x is a real world object/particle/phenomenon. No two apples are equal to a physicist, chemist or biologist. Only in the world of mathematics are they equal, and that's a mental construct used as an abstraction of reality.

As for science not using "unknown value" (null) for thousands of years, you're just dead wrong about that. The whole basis of all higher mathematics is that you can have unknown values. They are called "variables" in math. If you have two that you can't resolve in a particular situation, you end up with "unknown value". Math simply assigns these to non-numeric symbols in order to be able to write them down. They've been in use for thousands of years. Different word, same concept.

On the question of, "Where in the real world does the sum of nothing equal something other than Zero?", you're asking the wrong question. What's the sum of 5 + unknown value? What's the average of 2, 30, and unknown? Null doesn't necessarily equal 0. Null means "unknown". You're using the wrong definition of the concept.

Outside of numeric situations, what would you put for my first, middle and last names in you contact database, if you had one? You could enter an alias, "GSquared" with certainty. If you do a little digging around on this site, you could easily find that I answer to "Gus" as a first name, and you might be able to find my last name if you look hard enough. But what about my middle name? Can you enter into your database that I don't have one? That's pretty much what an empty string would mean. Or would it be more useful to leave the MiddleName column in your Contacts table listed as "Unknown". In database parlance, you'd leave it null, since entering the string "Unknown" could end up with a letter being addressed with that as the middle name, which would look stupid. Also, if you put "Gus" as my first name, you'd technically be incorrect, and you'd be better off entering that as a valid nickname and leaving the first name null as well as the middle name, till you gather further information. So, how would you record my name in a "truly relational database", without something that indicates "unknown/unverified value"?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #820906
Posted Wednesday, November 18, 2009 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
You asked for real world examples of problems that can't be solved by SQL. I've given one already: the ability to enforce a constraint that an Order must contain at least one Item in the item table. That's an example of a problem I've encountered many times professionally and is a well-known textbook limitation of SQL. This and other business rule type of problems have to be solved by procedural code or by other software outside the SQL DBMS - frequently at great expense. A RDBMS with more general constraint capabilities would eliminate or reduce that expense.


Interesting problem, but easily solved by adding a foreign key constraint to the Orders table, referencing the OrdersItems table (a join table between Orders and Items). Expense = one line of code, if you've defined your primary keys correctly and modeled the tables and data correctly. If the people writing those textbooks can't come up with something as simple as that, then they've got bigger problems than they think.

Next unsolvable problem?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #820911
Posted Wednesday, November 18, 2009 9:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:12 PM
Points: 451, Visits: 3,466
GSquared (11/18/2009)
[quote]Interesting problem, but easily solved by adding a foreign key constraint to the Orders table, referencing the OrdersItems table (a join table between Orders and Items).


Then how would you insert a new Order or populate the OrderItems table?

Null does not (accurately) represent the property of being unknown. That's just your assumption or convention, not anything actual or implied by standard SQL. That is demonstrable by the straightforward fact that x = x is True in mathematics and reality if x is unknown but is NOT true in SQL.

If you are seriously going to use quantum mechanics as a criteria for the design of database systems then I have nothing much more to say! If you are really going to be that silly then you would have to disallow ALL deterministic results of any kind from the database wouldn't you? No, don't bother to answer that...

I didn't say null equals nothing. What I meant was that SQL wrongly returns a null as the sum of an empty set, instead of the mathematical and real world answer of zero.


David
Post #820922
Posted Wednesday, November 18, 2009 9:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
David Portas (11/18/2009)
... They include Dataphor, Muldis and various other open source efforts.


Interesting that you mention Dataphor. There have been others that have been religous zealots about Dataphor and I did look at it a while back. What was really interesting about it, it was nothing more than a layer of abstraction over (get this) MS SQL Server. How does that solve anything?

Things may have changed since I last looked at Dataphor, but what good is another layer over something you thing is flawed and broken?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #820931
Posted Wednesday, November 18, 2009 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
A number of RDBMSs and languages have been built and are being used. They include Dataphor, Muldis and various other open source efforts. I'm not sure what problems you are referring to with those systems when compared to SQL DBMSs. Maybe you could elaborate.

I'd like to know what alternatives you think there are. The deficiencies of SQL are a pressing and very topical problem for a great many people right now. That's why there is so much interest and investment in alternative non-relational models. How else do you propose to improve the capabilities of our DBMSs? Do you really think current problems can be solved within the SQL model and if so, how? Or do you propose using alternative (non-relational) data models instead?


First, Dataphor isn't a database engine. It's a C# abstraction layer that runs on top of other databases. Second, if you look at the discussion forums for the product, it has a number of major issues. The only places it receives unending praise are its own marketing pages on its own site.

Can't say I'm familiar with Muldis. I searched for it, and it looks like it has verbosity issues (a simple left outer join takes 14 lines of code, as written by it's original developer), but I'm not familiar enough with it to judge the viability of it one way or the other.

Personally, I don't think there is a viable alternative to SQL at this time. I think one or more will evolve, and SQL will go the way of COBOL, PASCAL and FORTRAN. But keep in mind that COBOL still has a simply titanic install-base.

My assertion is not that SQL is perfect. Far from it. For one thing, OOP devs often seem to have horrific problems with comprehending the paradigm at all. That barrier is a real barrier, is not an indictment of OOP devs, and needs to be solved. But it needs to be solved in such a way that it doesn't create more problems than it solves.

Quite likely, as with most human disciplines, specialization will evolve enough that OOP devs won't need to work with SQL, not because SQL will go away, but because it will be hidden behind the scenes enough to effectively disappear.

Take a look at metalworking for an example of this sort of evolution. It used to be, way back when, that the same person mined, refined, and worked the metal into final products. Very inefficient. Now, the mining, refining, working, etc., are all done by specialized industries that very rarely overlap. I seriously doubt that any of the welders who work for Toyota would be able to identify a potential iron mine, much less work out how to best extract the ore from the ground, nor do they need to know that. As well, the tools for it have evolved into a tremendous diversity of highly specialized machines.

Every human industry I can think of works that way. Starts out with a lot of ineffiency and everyone has to know everything in order to get anything done at all, using tools that are barely adequate to the job, then evolves towards more and more specialization and more and more efficiency, with better and better tools that automate more and more of the process.

I think some people are still looking for a magical transformation, not an evolution. I expect an evolution. And I expect that it will be incremental, and will end up somewhere none of us could have anticipated.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #820971
Posted Wednesday, November 18, 2009 9:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Lynn Pettis (11/18/2009)
David Portas (11/18/2009)
... They include Dataphor, Muldis and various other open source efforts.
Interesting that you mention Dataphor. There have been others that have been religous zealots about Dataphor and I did look at it a while back. What was really interesting about it, it was nothing more than a layer of abstraction over (get this) MS SQL Server. How does that solve anything?

Things may have changed since I last looked at Dataphor, but what good is another layer over something you thing is flawed and broken?
Oh!! Snap!

Sorry, that was just the first thing that came to mind..

CEWII
Post #820975
Posted Wednesday, November 18, 2009 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
GSquared (11/18/2009)
I think some people are still looking for a magical transformation, not an evolution. I expect an evolution. And I expect that it will be incremental, and will end up somewhere none of us could have anticipated.
I think this is probably the way it will end up being as well.. The end result will be nothing like we are arguing about now..

CEWII
Post #820982
Posted Wednesday, November 18, 2009 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
David Portas (11/18/2009)
GSquared (11/18/2009)
[quote]Interesting problem, but easily solved by adding a foreign key constraint to the Orders table, referencing the OrdersItems table (a join table between Orders and Items).


Then how would you insert a new Order or populate the OrderItems table?

Null does not (accurately) represent the property of being unknown. That's just your assumption or convention, not anything actual or implied by standard SQL. That is demonstrable by the straightforward fact that x = x is True in mathematics and reality if x is unknown but is NOT true in SQL.

If you are seriously going to use quantum mechanics as a criteria for the design of database systems then I have nothing much more to say! If you are really going to be that silly then you would have to disallow ALL deterministic results of any kind from the database wouldn't you? No, don't bother to answer that...

I didn't say null equals nothing. What I meant was that SQL wrongly returns a null as the sum of an empty set, instead of the mathematical and real world answer of zero.


Null is exactly defined as being an unknown value.
<programming> A special value used in several languages to represent the thing referred to by an uninitialised pointer.

<database> A special value that may be stored in some database columns to represent an unknown, missing, not applicable, or undefined value. Nulls are treated completely differently from ordinary values when evaluating SQL expressions and there are several SQL constructs for dealing with nulls.
Reference: Free Online Dictionary of Computing

Check ANY reference on SQL and you'll find this is far from some assumption I came up with on my own. It's the standard, accepted definition. Check a good dictionary.

On the point of x=x, yes, that's mathematically true. Ask any decent mathematician if you can validly compute 5 + x without knowing what the value of x is. Most high school algebra graduates can answer that one correctly. You have the option in SQL of allowing that null = null, but unless you are comparing the same two nulls, you can't be sure.

For example, if you have an item on an unknown order. You know that Joe is going to order a sandwich for lunch, but you don't have an order number yet for the cash register, and don't yet know what else, if anything, will be ordered, can you really assign that sandwich to EVERY order that has an unknown cash register receipt ID? If you use null = null in that real-life situation, you're going to create HUGE problems for your business. Yet, per your assertion, null = null because x = x, and you're going to add a sandwich to every order for every customer who's still in line to go to the register.

With your empty set example, there you go again assuming that null means something other than unknown value. What's the sum of the weight of all wamingungumas in the world? Per your logic, it's zero, because the weight is currently null, because you can't possibly know what the unit weight is.

My statement about mathematics being an abstract that any decent scientist understands isn't the real thing is not limited to quantum physics. That's an absurd statement. If you have an apple in your left hand and an apple in your right hand, you don't have to go down to the level of them being made of different energy quanta to know that they aren't truly identical. Put them on a ballance, and odds are, they won't even weigh the same amount. We're talking grams here, not electron-volts. Take a good look at them in reasonable light, and I'll bet you can, by eyeball, identify differences in shape, color, length of stem, dimensions. Bite into them, and by that crude sense known as taste, you'll probably find that one is sweeter than the other, one perhaps more tart. If you didn't get that when you took grade school science, and weren't taught that math is an artificial abstraction, then you missed out on a very, very important fundamental to all human knowledge.

Mocking me by making asinine statements about what I wrote doesn't actually win the argument for you. It doesn't make your point. You need to actually defend the statement that a "true relational database" really solves real world situations. You haven't done so yet. Haven't even tried.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #821008
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse