Learning programmer’s idea of bug checking from a SQL error solving process


After migrating data from Oracle to dream, we found that the default values of database were lost. So I want to find out the default value from Oracle database and add it back in Dameng database.
So I searched the Internet to see how to get the default value information of Oracle database field, and found this SQL.
select t.column, t.data_type, t.data_length, t.nullable, t.data_default from USER_TAB_COLS t where TABLE_NAME = ‘AA’
But when I took the SQL to the database, I found that there was a problem and reported an error:

I scanned the error information and didn't understand where the error was. This is the same as when we usually run into a program bug, but the log is not clear. It's the same to see where the problem is.
Take a look at SQL. There are three main parts: select and from_ tab_ Cols is a view). There are also where conditions. The error may be the select part or the user part_ tab_ Cols view statement or where conditional statement, but I don't know which part is wrong.

So I first try to remove the where condition to see if I can report an error.

The result is still the same error, which means that the error location should not be in the where condition, so continue to try to narrow the scope.

This time, I simplified SQL to only one element: table name

If the query is found to be successful, it should be in the select statement. There are multiple fields in the query. I don't know which field has problems at the moment. Try to delete the latter half.

At this time, we found that the problem is still there, which should be the problem of the first two fields. Continue deleting a field and try again.

At this time, an error is still reported, but the error range is very small. People who are familiar with Oracle should know that column is a keyword. Here is the wrong field name( It turns out that the correct field name should be column_ name)
What if I can't remember the cause of the mistake—— Continue to delete. See my next operation, delete alias t

Delete to here, discovery still report an error, but the error prompt is different. At this time, check the column information of oracle on the Internet, and you will find a lot of relevant content in Baidu. At this time, people who have some programming experience should certainly be able to recall.

After finding the problem, modify it. Now you can find out the default value normally.

Here is a programmer's idea of bug checking, which is to reduce the content that may cause errors until the errors do not recur (or the errors change). The problem is simple enough to know the reason at a glance.

By reducing variables and gradually increasing certainty, this is a way for programmers to check bugs. Another way of thinking is to turn it upside down, from the simplest system, gradually increase the variables, determine the problem recently, and share the case later.

Leave a small question: if the where condition is deleted in the first step, the SQL will not report an error. How to check?
If it is found that only the where condition and the select field are reserved at the same time can an error be reported, and no error is reported when the field is modified alone or the where condition is deleted, how to check?

Recommended Today

Talk about dynamic programming

The article begins with the official account.Green plum main code, welcome to pay attention brief introduction Dynamic programming (DP) is an American mathematicianRichard BellmanIn researchDecision process and control system theoryNew method created when. It belongs to a branch of operations research in mathematics and is applied in mathematics, management science, computer science, economics and bioinformaticsBy […]